Usuaris i rols

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

Introducció¶

El control d'accés decideix qui es pot connectar a la base de dades i què hi pot fer. El primer pas és identificar qui accedeix: cada persona o aplicació que es connecta ho fa amb un compte propi, de manera que se li poden donar només els permisos que necessita i es pot saber qui ha fet cada operació.

En PostgreSQL, tant els usuaris com els grups d'usuaris es representen amb un únic concepte: el rol. Un rol és una identitat de la base de dades que pot tindre permisos, ser propietari d'objectes i contindre altres rols. La diferència entre un «usuari» i un «grup» és només d'ús:

Usuari: rol amb l'atribut LOGIN, que es pot utilitzar per iniciar sessió.
Grup: rol sense LOGIN, que serveix per agrupar permisos i assignar-los a altres rols.

Els rols són globals al clúster: un mateix rol pot accedir a totes les bases de dades del servidor, segons els permisos que tinga en cadascuna.

Preparació de l'entorn d'exemple

Els exemples d'aquests apunts s'executen en la base de dades botiga, connectat amb l'usuari administrador postgres. Crea la base de dades i les taules següents:

CREATE DATABASE botiga;
\c botiga

CREATE TABLE productes (
    id    SERIAL PRIMARY KEY,
    nom   VARCHAR(100),
    preu  NUMERIC(10,2),
    stock INTEGER
);

CREATE TABLE clients (
    id     SERIAL PRIMARY KEY,
    nom    VARCHAR(100),
    email  VARCHAR(100)
);

Rols¶

Creació d'un rol (`CREATE ROLE`)¶

Un rol es crea amb l'ordre CREATE ROLE, seguida dels atributs que en defineixen les capacitats:

CREATE ROLE <nom> [[WITH] <atribut> [...]];

<nom>: nom del rol.

[<atribut>]: (opcional) capacitats del rol. Cada atribut té una forma positiva i una negativa (LOGIN/NOLOGIN, CREATEDB/NOCREATEDB…), i per defecte s'aplica la negativa.

Atributs de CREATE ROLE

Atribut	Significat
`LOGIN`	Permet iniciar sessió amb el rol
`PASSWORD '<contrasenya>'`	Estableix la contrasenya del rol
`VALID UNTIL '<data>'`	Data a partir de la qual la contrasenya deixa de ser vàlida
`CONNECTION LIMIT <n>`	Nombre màxim de connexions simultànies (`-1`, sense límit)
`SUPERUSER`	Salta totes les comprovacions de permisos
`CREATEDB`	Permet crear bases de dades
`CREATEROLE`	Permet crear, modificar i eliminar rols
`INHERIT`	Hereta els permisos dels rols dels quals és membre (per defecte)
`REPLICATION`	Permet iniciar la replicació

Documentació oficial: CREATE ROLE – PostgreSQL

L'ordre CREATE USER és equivalent a CREATE ROLE, amb l'única diferència que afig l'atribut LOGIN per defecte. Per tant, les dues ordres següents són equivalents:

CREATE USER ana PASSWORD 'ana1234';
CREATE ROLE ana LOGIN PASSWORD 'ana1234';

Exemple: Crear usuaris i grups

Es crea el grup vendes, que no pot iniciar sessió, i dos usuaris: ana, amb una contrasenya que caduca a final d'any, i marc, amb un màxim de dues connexions simultànies.

CREATE ROLE vendes NOLOGIN;

CREATE ROLE ana LOGIN PASSWORD 'ana1234' VALID UNTIL '2026-12-31';
CREATE ROLE marc LOGIN PASSWORD 'marc1234' CONNECTION LIMIT 2;

Si s'intenta iniciar sessió amb el grup vendes, el servidor rebutja la connexió:

jpuigcerver@fp:~ $ psql -h localhost -U vendes -d botiga
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  role "vendes" is not permitted to log in

S'observa que un rol sense LOGIN existeix i pot tindre permisos, però no es pot utilitzar per connectar-se.

Consulta dels rols¶

Els rols existents es poden consultar amb la metaordre \du de psql:

\du

O bé mitjançant la vista pg_roles del catàleg, que mostra els atributs de cada rol:

SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole, rolconnlimit, rolvaliduntil
FROM pg_roles
WHERE rolname IN ('vendes', 'ana', 'marc');

 rolname | rolcanlogin | rolsuper | rolcreatedb | rolcreaterole | rolconnlimit |     rolvaliduntil
---------+-------------+----------+-------------+---------------+--------------+------------------------
 vendes  | f           | f        | f           | f             |           -1 |
 ana     | t           | f        | f           | f             |           -1 | 2026-12-31 00:00:00+00
 marc    | t           | f        | f           | f             |            2 |

PostgreSQL crea alguns rols predefinits, amb el prefix pg_, que donen accés a funcionalitats concretes.

Per exemple, pg_read_all_data permet llegir totes les taules, pg_monitor permet consultar les vistes de monitoratge i pg_signal_backend permet finalitzar sessions d'altres usuaris. Es poden consultar amb SELECT rolname FROM pg_roles WHERE rolname LIKE 'pg_%';.

Modificació d'un rol (`ALTER ROLE`)¶

L'ordre ALTER ROLE permet canviar els atributs d'un rol existent, la seua contrasenya o el seu nom:

ALTER ROLE <nom> [WITH] <atribut> [...];
ALTER ROLE <nom> RENAME TO <nou_nom>;
ALTER ROLE <nom> SET <parametre> = <valor>;

<atribut>: qualsevol dels atributs de CREATE ROLE.
RENAME TO <nou_nom>: canvia el nom del rol.
SET <parametre> = <valor>: assigna un valor per defecte a un paràmetre de configuració per a totes les sessions del rol.

Documentació oficial: ALTER ROLE – PostgreSQL

Exemple: Modificar un rol

Es canvia la contrasenya d'ana, es permet a marc crear bases de dades i es fixa el format de data de les sessions d'ana:

ALTER ROLE ana PASSWORD 'nova_ana1234';
ALTER ROLE marc CREATEDB;
ALTER ROLE ana SET datestyle = 'ISO, DMY';

Per bloquejar temporalment un usuari sense eliminar-lo, n'hi ha prou amb llevar-li el LOGIN:

ALTER ROLE marc NOLOGIN;

S'observa que el rol continua existint, amb tots els seus permisos, però ja no pot iniciar sessió.

Per als exemples següents, es torna a permetre l'inici de sessió:

ALTER ROLE marc LOGIN;

Eliminació d'un rol (`DROP ROLE`)¶

Un rol s'elimina amb l'ordre DROP ROLE:

DROP ROLE [IF EXISTS] <nom>;

[IF EXISTS]: (opcional) evita l'error si el rol no existeix.
<nom>: nom del rol que s'elimina.

No obstant això, PostgreSQL no permet eliminar un rol que siga propietari d'objectes o que tinga permisos en alguna base de dades. Abans cal reassignar o eliminar eixos objectes i permisos, en cada base de dades on n'hi haja:

REASSIGN OWNED BY <rol> TO <altre_rol>: transfereix la propietat de tots els objectes del rol a un altre rol.
DROP OWNED BY <rol>: elimina els objectes que encara siguen del rol i li revoca tots els permisos.

Documentació oficial: DROP ROLE – PostgreSQL

Exemple: Eliminar un rol propietari d'objectes

Es crea l'usuari de proves proves, se li permet crear taules en l'esquema public i, connectat com proves, es crea la taula comandes:

CREATE ROLE proves LOGIN PASSWORD 'proves1234';
GRANT CREATE ON SCHEMA public TO proves;
-- Connectat com a proves:
CREATE TABLE comandes (id SERIAL PRIMARY KEY);

Com que el rol és propietari d'una taula i té permisos, no es pot eliminar directament:

DROP ROLE proves;

ERROR:  role "proves" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema public
owner of sequence comandes_id_seq
owner of table comandes

Es transfereixen els seus objectes a l'usuari postgres, se li revoquen els permisos i, finalment, s'elimina:

REASSIGN OWNED BY proves TO postgres;
DROP OWNED BY proves;
DROP ROLE proves;

S'observa que la taula comandes no s'ha perdut: ara és propietat de postgres.

Pertinença a rols¶

Assignar els permisos un a un a cada usuari és difícil de mantindre. En canvi, si els permisos s'assignen a un grup i els usuaris es fan membres del grup, n'hi ha prou amb afegir o llevar usuaris del grup perquè guanyen o perden tots els permisos alhora.

Afegir membres a un rol (`GRANT`)¶

Per fer un rol membre d'un altre, s'utilitza l'ordre GRANT:

GRANT <grup> TO <rol> [WITH ADMIN OPTION];

<grup>: rol del qual es farà membre.
<rol>: rol que passa a ser membre.
[WITH ADMIN OPTION]: (opcional) permet al membre afegir o llevar altres membres del grup.

Els membres amb l'atribut INHERIT, que és el valor per defecte, hereten automàticament els permisos del grup. Els membres amb NOINHERIT, en canvi, han d'activar el grup explícitament amb SET ROLE <grup> per utilitzar-ne els permisos.

Documentació oficial: Role Membership – PostgreSQL

Exemple: Afegir usuaris al grup vendes

Es fan membres del grup vendes els usuaris ana i marc:

GRANT vendes TO ana, marc;

La pertinença es pot comprovar amb la metaordre \drg de psql:

\drg

                  List of role grants
 Role name | Member of |       Options       | Grantor
-----------+-----------+---------------------+----------
 ana       | vendes    | INHERIT, SET        | postgres
 marc      | vendes    | INHERIT, SET        | postgres

S'observa que ana i marc apareixen com a membres de vendes. A partir d'ara, qualsevol permís que es done a vendes s'aplicarà també a tots dos.

Llevar membres d'un rol (`REVOKE`)¶

Per llevar un membre d'un rol, s'utilitza l'ordre REVOKE:

REVOKE <grup> FROM <rol>;

REVOKE vendes FROM marc;

La pertinença també es pot consultar amb SQL, a partir de la vista pg_auth_members:

SELECT g.rolname AS grup, m.rolname AS membre
FROM pg_auth_members am
JOIN pg_roles g ON am.roleid = g.oid
JOIN pg_roles m ON am.member = m.oid
WHERE g.rolname = 'vendes';

  grup  | membre
--------+--------
 vendes | ana

Seguretat dels comptes¶

A més de crear els comptes, l'administrador ha de garantir que es protegeixen adequadament. En PostgreSQL, les mesures principals són les següents:

Contrasenyes xifrades: el paràmetre password_encryption indica com es guarden les contrasenyes. El valor per defecte, scram-sha-256, és el més segur, i les regles de pg_hba.conf haurien d'utilitzar el mateix mètode (vegeu Configuració de connexions).
Caducitat: l'atribut VALID UNTIL obliga a renovar la contrasenya periòdicament.
Límit de connexions: l'atribut CONNECTION LIMIT evita que un compte acapare els recursos del servidor.
Mínim privilegi: cada rol ha de tindre només els atributs i els permisos imprescindibles. En particular, s'ha d'evitar l'atribut SUPERUSER fora dels comptes d'administració.

PostgreSQL no inclou polítiques de complexitat de contrasenyes ni bloqueig per intents fallits: s'aconsegueixen amb mòduls com passwordcheck o amb ferramentes externes.

📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.

🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.

Usuaris i rols

Introducció¶

Rols¶

Creació d'un rol (CREATE ROLE)¶

Consulta dels rols¶

Modificació d'un rol (ALTER ROLE)¶

Eliminació d'un rol (DROP ROLE)¶

Pertinença a rols¶

Afegir membres a un rol (GRANT)¶

Llevar membres d'un rol (REVOKE)¶

Seguretat dels comptes¶

Creació d'un rol (`CREATE ROLE`)¶

Modificació d'un rol (`ALTER ROLE`)¶

Eliminació d'un rol (`DROP ROLE`)¶

Afegir membres a un rol (`GRANT`)¶

Llevar membres d'un rol (`REVOKE`)¶