Postgres - Role

Els rols permeten controlar l'accés a les diferents bases de dades.

• Introducció
• Entorn de treball
• Crear un rol
• grant
• revoke
• Role Membership
• current_user
• Tasks

Introducció

Cada rol té assignats uns privilegis co ara login que permet al rol iniciar sessió, createdb que permet al rol crear una nova base de dades o superuser que permet al rol tenir tots els privilegis.

A més un rol pot ser membre d’altres rols.

Entorn de treball

Entra dins una màquina Windows Subsystem for Linux (WSL).

Crea un contenidor postgres:

docker run -d --name role -e POSTGRES_PASSWORD=password postgres:18

Obre un terminal interactiu:

docker exec -it role psql -U postgres

Crear un rol

Per crear un nou rol en un servidor PostgreSQL, utilitza la instrucció create role:

Per exemple, la següent instrucció utilitza la instrucció create role per crear un nou rol anomenat tortuga:

create role tortuga;

Quan crees un rol, aquest és vàlid en totes les bases de dades dins del servidor de bases de dades (o clúster).

Pots consultar tots els rols de la base de dades en el catàleg del sistema pg_roles:

select rolname from pg_roles;

           rolname
-----------------------------
 pg_database_owner
 ...
 pg_signal_autovacuum_worker
 postgres
 tortuga
(18 rows)

Observa que:

• Els rols els noms dels quals comencen amb pg_ són rols del sistema.
• postgres és un rol de superusuari creat per defecte.

També utilitzar l’ordre \du per mostrar tots els rols que has creat (també inclou el rol postgres):

\du

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 tortuga   | Cannot login

La sortida indica que el rol tortuga no pot iniciar sessió.

Per permetre que tortuga iniciï sessió al servidor has d’afegir-li l’atribut login.

Atributs de rol

Els atributs d’un rol defineixen els privilegis per a aquest rol, incloent l’inici de sessió, l’estat de superusuari, la creació de bases de dades, la creació de rols, la gestió de contrasenyes, etc.

Aquesta és la sintaxi per crear un nou rol amb atributs:

create role name with option;

En aquesta sintaxi, la paraula clau with és opcional.

L’opció pot ser un o més atributs com superuser, createdb, createrole, etc.

Crear rols d’inici de sessió

Per exemple, la següent instrucció crea un rol anomenat nobita que té el privilegi d’inici de sessió i una contrasenya inicial:

create role nobita login password '1234';

Observa que col·loques la contrasenya entre cometes simples (').

Aquí tens la nova llista de rols:

\du

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 nobita    |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 tortuga   | Cannot login

Surt de la base de dades:

\q

Ara pots utilitzar el rol nobita per iniciar sessió al servidor de bases de dades PostgreSQL utilitzant l’eina client psql:

docker exec -it role psql -U nobita -d postgres

Et demanarà una contrasenya … 😐 (en aquest context no).

Has d’introduir la contrasenya que vas introduir a la instrucció create role per iniciar sessió al servidor.

docker exec -it rol psql -U nobita

psql: error:
    connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed:
        FATAL:  database "nobita" does not exist

Pots verificar que és el rol nobita qui està connectat:

select current_user;

 current_user
--------------
 nobita
(1 row)

Crear rols de superusuari

La següent instrucció crea un rol anomenat doraemon que té l’atribut de superuser:

create role doraemon superuser login password '1234';

Però com que ara ets l’usuari nobita, no pots crear altres usuaris.

Només un rol de superusuari pot crear un altre rol de superusuari:

ERROR:  permission denied to create role
DETAIL:  Only roles with the CREATEROLE attribute may create roles.

Canvia al rol postgres:

set role postgres;

Tampoc 😺 … És una base de dades segura!

ERROR:  permission denied to set role "postgres"

docker exec -it rol psql -U postgres

create role doraemon superuser login password '1234';

set role doraemon;

El rol de superusuari té tots els permisos dins del servidor.

Per tant, només has de crear el rol de superusuari quan sigui necessari.

Crear rols amb permís de creació de bases de dades

Si vols crear rols que tinguin el privilegi de creació de bases de dades, pots utilitzar l’atribut createdb:

create role shizuka createdb login password '1234';

set role shizuka;
create database universe;

Crear rols amb un període de validesa

Per establir una data i hora després de la qual la contrasenya del rol ja no sigui vàlida, utilitza l’atribut valid until:

valid until 'timestamp'

Un segon després del marcat per timestamp la contrasenya ja no serà válida.

Per exemple, la següent instrucció crea un rol dev_api amb contrasenya vàlida fins a …:

create role takeshi with login password '1234' valid until '2025-11-20';

ERROR:  permission denied to create role
DETAIL:  Only roles with the CREATEROLE attribute may create roles.

set role postgres;
create role shizuka with login password '1234' valid until '2025-11-20';

Crear rols amb límit de connexió

Per especificar el nombre de connexions simultànies que pot fer un rol, utilitza l’atribut connection limit.

La següent instrucció crea un nou rol anomenat api que pot fer 1000 connexions simultànies:

create role api login password '1234' connection limit 1000;

Conclusió

Ara tens diversos rols en la teva base de dades amb permisos diferents:

\du

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 api       | 1000 connections
 doraemon  | Superuser
 nobita    |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 shizuka   | Create DB
 takeshi   | Password valid until 2025-11-20 00:00:00+00
 tortuga   | Cannot login

grant

After creating a role with the LOGIN attribute, the role can log in to the PostgreSQL database server.

However, it cannot do anything to the database objects like tables, views, functions, etc. For example, the role cannot select data from a table or execute a specific function.

To allow a role to interact with database objects, you need to grant privileges on the database objects to the role using the grant statement.

The following shows the simple form of the GRANT statement that grants one or more privileges on a table to a role:

GRANT privilege_list | ALL
ON  table_name
TO  role_name;

In this syntax:

• First, specify the privilege_list that can be SELECT, INSERT,UPDATE, DELETE,TRUNCATE, etc. Use the ALL option to grant all privileges on a table to the role.
• Second, provide the name of the table after the ON keyword.
• Third, indicate the name of the role to which you want to grant privileges.

Examples

Create a new user role called joe that can log in to the PostgreSQL server:

create role joe login password 'password';

Create a new database, for example acme, and connect to it:

create database acme;

Create a new table called candidates:

create table candidates (
    candidate_id int generated always as identity,
    first_name varchar(100) not null,
    last_name varchar(100) not null,
    email varchar(255) not null unique,
    phone varchar(25) not null,
    primary key(candidate_id)
);

Use the role joe to log in to the PostgreSQL server in a separate session.

Attempt to select data from the candidates table from the joe’s session:

SELECT * FROM candidates;

PostgreSQL issued an error:

ERROR:  permission denied for table candidates

To grant the SELECT privilege on the candidates table to the role joe, you execute the following GRANT statement in the postgres’ session:

GRANT SELECT
ON candidates
TO joe;

Now, you can select data from the candidates table using the joe role:

SELECT * FROM candidates;

PostgreSQL returns an empty result set instead of an error.

Execute the following INSERT statement:

INSERT INTO candidates(first_name, last_name, email, phone)
VALUES('Joe','Com','joe.com@example.com','408-111-2222');

PostgreSQL issued the following error because joe does not have the INSERT privilege on the candidates table:

ERROR:  permission denied for table candidates

Grant INSERT, UPDATE, and DELETE privileges on the candidates table to the role joe:

GRANT INSERT, UPDATE, DELETE
ON candidates
TO joe;

Execute the INSERT statement again from the joe’s session:

INSERT INTO candidates(first_name, last_name, email, phone)
VALUES('Joe','Com','joe.com@example.com','408-111-2222');

Now, joe can insert data into the candidates table. Additionally, it can update or delete data from the table.

More examples

Grant all privileges on a table to a role

The following statement grants all privileges on the candidates table to the role joe:

GRANT ALL
ON candidates
TO joe;

Grant all privileges on all tables in a schema to a role

The following statement grants all privileges on all tables in the public schema of the database to the role joe:

GRANT ALL
ON ALL TABLES
IN SCHEMA "public"
TO joe;

revoke

he REVOKE statement revokes previously granted privileges on database objects from a role.

The following shows the syntax of the REVOKE statement that revokes privileges on one or more tables from a role:

REVOKE privilege | ALL
ON TABLE table_name |  ALL TABLES IN SCHEMA schema_name
FROM role_name;

In this syntax:

• First, specify one or more privileges that you want to revoke or use the ALL option to revoke all privileges.
• Second, provide the name of the table after the ON keyword or use the ALL TABLES to revoke specified privileges from all tables in a schema.
• Third, specify the name of the role from which you want to revoke privileges.

Examples

Create a new role called jim:

CREATE ROLE jim;

grant all privileges to the role jim on the candidates table:

GRANT ALL ON candidates TO jim;

To revoke all privileges on the candidates table from the role jim, you use REVOKE statement with the ALL option like this:

REVOKE ALL ON candidates FROM jim;

Role Membership

In PostgreSQL, a group role is a role that serves as a container for other individual roles.

Unlike individual roles, which typically represent users, a group role is used to manage collections of roles.

Typically, you create a role to represent a group and then grant a membership in the group role to individual roles.

Group roles allow you to simplify permission management. Instead of granting privileges to individual roles, you can group these roles into a group, grant privileges to a group role, and all the members of that group role will inherit those privileges.

Group roles can have individual roles or other group roles as their members. This allows you to create hierarchical structures where you can manage privileges at different levels.

Individual roles can be members of multiple group roles. This allows for flexible assignment of permissions and roles within the database.

Creating a group role

Creating a group role is like creating a role by using the CREATE ROLE statement:

CREATE ROLE group_role;

In this syntax, you specify the name of the group role after the CREATE ROLE keyword.

By convention, a group role does not have the LOGIN privilege, meaning that you will not use the group role to log in to PostgreSQL even though you can.

For example, the following statement creates a group role called sales:

create role sales;

When you use the \du command in the psql tool, you will see that the group roles are listed together with individual roles:

List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 sales     | Cannot login

To add a role to a group, you use the following form of the GRANT statement:

GRANT group_role TO role;

In this syntax:

• First, specify the name of the group role after the GRANT keyword.
• Second, specify the name of the role after the TO keyword.

For example, the following statement creates a new role alice:

create role alice with login password 'password';

This CREATE ROLE implicitly uses the INHERIT attribute to ensure that the role alice automatically inherits privileges from the group roles to which it belongs.

Add alice to the group role sales:

grant sales to alice;

After you execute this statement, the alice role will automatically inherit all privileges of the sales group role if the alice role has INHERIT attribute.

If you don’t want the role alice to inherit the privileges of its group roles, you can use the NOINHERIT attribute:

create role alice with login noinherit password 'password';

The following grants the SELECT privilege on the candidates table to the sales group role:

GRANT SELECT ON candidates TO sales;

The role alice will automatically inherit the SELECT privilege on the rental table from the sales group role.

Let’s test it out.

grant select on candidates to sales;
set role alice;
select * from candidates;

The output indicates that the role alice has the SELECT privilege on the candidates table even though we did not explicitly assign it.

Removing a role to a group role

To remove a role from a group role, you use the REVOKE statement:

REVOKE group_role FROM role;

In this syntax:

• First, specify the name of the group role after the REVOKE keyword.
• Second, specify the name of the role after the FROM keyword.

A useful tip to remember when using GRANT and REVOKE statements with the group role is that the group role should come first, followed by the individual role.

For example, the following statement uses the REVOKE statement to remove the role alice from the group role sales:

REVOKE sales FROM alice;

Notice that PostgreSQL does not permit circular membership loops, where a role is the member of another role and vice versa.

current_user

The PostgreSQL CURRENT_USER is a function that returns the name of the currently logged-in database user.

Here’s the syntax of the CURRENT_USER function:

current_user;

The function returns the name of the current effective user within the session.

In other words, if you use the SET ROLE statement to change the role of the current user to the new one, the CURRENT_USER will reflect the new role.

In PostgreSQL, a role with the LOGIN attribute represents a user. Therefore, we use the terms role and user interchangeably.

To get the original user who connected to the session, you use the SESSION_USER function.

set role alice;
select current_user;

 current_user
--------------
 alice
(1 row)

Use the SESSION_USER function to retrieve the original user who connected to the session:

 select session_user;
 session_user
--------------
 postgres
(1 row)

The SESSION_USER function returns postgres, not alice.

Tasks