Postgres - Rol

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

• Introducció
• Entorn de treball
• Crear un rol
• Concedir privilegis

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 postgres -e POSTGRES_PASSWORD=password postgres:18
docker exec -it postgres

Obre un terminal interactiu:

docker exec -it rol 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 rol 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

Concedir privilegis