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.
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.
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).
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.
The ALTER ROLE clause in association with the 'WITH' keyword helps to update the role with necessary permission.
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.
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.
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:
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.