Article Overview

In this article, we will cover the PostgreSQL database role object that groups together one or more privileges and can be assigned to users. A role is a collection of permission or privileges that can be granted to one or more users or other roles.

There are two types of roles in PostgreSQL.

• Login Role - Roles that can log in are called login roles. They are equivalent to users in other database systems.
• Group Role - When roles contain other roles, they are called group roles.

Create Role

In this section, with the help of some examples we will see how to create different types of roles in PostgreSQL and also how to update the role by adding necessary permission to it.

Basic syntax:

CREATE ROLE role_name;

This SQL statement helps to create a role in the database, important point to note here is the role doesn't come with any permission, not even with the one used to login. Image below explains this scenario. When a role is created, it is valid across all databases in the database server (or cluster).

Example:

CREATE ROLE admin1;

To list all existing roles in the PostgreSQL database server, the '\du' command is used as shown below:

As explained in the above section, a role is database independent and that's exactly what we see here. It is available to both the databases, 'postgres' as well as 'demo'.

Note: the 'admin1' role has the 'cannot login' attribute assigned to it, which means it doesn't have the rights to login to the database(s). However there ways to give permission(s) to a role. For instance, we want to give login rights to a role. Let us see the different ways it can be achieved in the section below.

ALTER ROLE

The ALTER ROLE clause in association with the 'WITH' keyword helps to update the role with necessary permission.

Example

ALTER ROLE "admin1" WITH LOGIN;

The second option to associate a role is to drop the existing role and then re-create it with necessary permission.

Basic syntax:

Drop role role_name;
CREATE ROLE role_name WITH OPTION;

In the second syntax, the 'WITH' keyword is optional. Other permissions available for use are SUPERUSER, CREATEDB, CREATEROLE etc. as seen for the other roles in the snapshot below:

Create role with password

In the previous section we learned how to create roles but by default those roles didn't have a password and therefore if we try to connect to the database, the authorization would fail as shown below.

As there is no default password, one can't login until a password is set. To solve this situation, Postgres allows to create a role along with its password.

Basic syntax:

CREATE ROLE role_name 
LOGIN 
PASSWORD 'password';

The password must be enclosed in single quotes ('). Let us see one example for this scenario:

CREATE ROLE admin3
LOGIN 
PASSWORD 'test123';

Using the 'select current_user' statement, it is possible to find out the logged in user. Role and user are one and the same thing in PostgreSQL.

To switch to another role, the following command can be used:

\c database_name role_name

The important point to note here is both the database and role needs to be supplied. In the next step, system would prompt for the password for authorization. Sample shared below:

Create role with superuser privilege

A 'SUPERUSER' role bypasses all permission checks, except the right to log in. This is a dangerous privilege and should not be used carelessly; it is best to do most of your work as a role that is not a superuser. To create a new database 'superuser', use the following syntax . The 'superuser' can override all access restrictions within the database therefore it should be created only when needed. Another point to note here is that you must be a 'superuser' in order to create another superuser role.

CREATE ROLE shivayan 
SUPERUSER 
LOGIN 
PASSWORD 'test123';

Since by default the 'superuser' role doesn't have the login rights, it is here explicitly mentioned at the time of creation.

Create role with create database privilege

These clauses define a role's ability to create databases. When 'CREATEDB 'is mentioned, the role being created will be allowed to construct new databases. Specifying 'NOCREATEDB' will deny a role the ability to construct databases. If not specified, 'NOCREATEDB' is the default

CREATE ROLE sysdba 
CREATEDB 
LOGIN 
PASSWORD 'test123';

The 'CREATE DB' role gives you the right to create database.

Role Access Via pgAdmin

In pgAdmin you can navigate to 'Login/Group Roles' as shown below to find the list of all available roles.

Notice that the roles which starts with 'pg_' are system roles. System roles are a set of default roles which provide access to certain, often needed, special capabilities and information. Administrators can GRANT these roles to users and/or other roles in their environment, providing those users with access to the specified capabilities and information.

The role privileges can be accessed and updated from 'Privileges' tab as shown below:

Conclusion

In this article we learned about PostgreSQL role, the different ways of creating a role along with its attributes and functionality of each. This article also gives a sneak peak into how a role can be accessed and managed from pgAdmin tool. We hope this article will help you get started on your journey with PostgreSQL role.