Salta el contingut
 

Privilegis

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

License

Introducció

Un privilegi (o permís) és el dret a fer una operació concreta sobre un objecte de la base de dades, com consultar una taula o crear-ne de noves. Els privilegis s'assignen als rols, de manera que cada usuari pot fer exactament allò que necessita i res més. És el que es coneix com a principi de mínim privilegi.

En PostgreSQL, el control d'accés es basa en tres idees:

  • Propietari: el rol que crea un objecte n'és el propietari i hi té tots els privilegis. A més, és l'únic que el pot modificar (ALTER) o eliminar (DROP).
  • Privilegis sobre objectes: la resta de rols només poden accedir a un objecte si el propietari (o un superusuari) els concedeix els privilegis corresponents.
  • Atributs de rol: les capacitats globals, com crear bases de dades o rols, no són privilegis sinó atributs del rol (vegeu Usuaris i rols).

Els exemples d'aquests apunts continuen l'escenari dels apunts d'usuaris: la base de dades botiga, amb les taules productes i clients, el grup vendes i els usuaris ana i marc.

Tipus de privilegis

Cada tipus d'objecte admet uns privilegis concrets. La taula següent recull els més habituals:

Privilegi Objectes Permet
SELECT Taules, vistes, columnes, seqüències Consultar les dades
INSERT Taules, columnes Inserir files
UPDATE Taules, columnes, seqüències Modificar files
DELETE Taules Eliminar files
TRUNCATE Taules Buidar la taula
REFERENCES Taules, columnes Crear claus foranes que hi fan referència
TRIGGER Taules Crear triggers sobre la taula
CONNECT Bases de dades Connectar-se a la base de dades
CREATE Bases de dades, esquemes Crear esquemes (en una base de dades) o objectes (en un esquema)
TEMPORARY Bases de dades Crear taules temporals
USAGE Esquemes, seqüències Accedir als objectes de l'esquema o utilitzar la seqüència
EXECUTE Funcions, procediments Executar la funció o el procediment
ALL PRIVILEGES Qualsevol Tots els privilegis aplicables a l'objecte

Per accedir a una taula no n'hi ha prou amb el privilegi sobre la taula: cal també CONNECT sobre la base de dades i USAGE sobre l'esquema.

Per defecte, tots els rols (PUBLIC) tenen CONNECT sobre les bases de dades i USAGE sobre l'esquema public. Des de PostgreSQL 15, en canvi, PUBLIC ja no té CREATE sobre l'esquema public, de manera que els usuaris no poden crear-hi taules si no se'ls concedeix.

Documentació oficial: Privileges – PostgreSQL

Assignació de privilegis (GRANT)

Els privilegis es concedeixen amb l'ordre GRANT. La sintaxi varia lleugerament segons el tipus d'objecte, però sempre segueix la mateixa estructura:

GRANT <privilegis> ON <tipus_objecte> <objecte> TO <rol> [WITH GRANT OPTION];
  • <privilegis>: llista de privilegis separats per comes, o ALL PRIVILEGES. En les taules, es poden limitar a unes columnes concretes: SELECT (nom, preu).
  • <tipus_objecte> <objecte>: objecte sobre el qual es concedeixen (TABLE productes, SCHEMA public, DATABASE botiga…). Per a les taules, la paraula TABLE és opcional.
  • <rol>: rol que rep els privilegis. La paraula especial PUBLIC els concedeix a tots els rols.
  • [WITH GRANT OPTION]: (opcional) permet al rol concedir, al seu torn, els mateixos privilegis a altres rols.

Documentació oficial: GRANT – PostgreSQL

Exemple: Concedir privilegis al grup vendes

Es concedeix al grup vendes permís per consultar i modificar els productes, però només per consultar el nom i el correu dels clients:

GRANT SELECT, INSERT, UPDATE ON productes TO vendes;
GRANT USAGE ON SEQUENCE productes_id_seq TO vendes;
GRANT SELECT (nom, email) ON clients TO vendes;

Es comprova connectat com ana, membre de vendes:

INSERT INTO productes (nom, preu, stock) VALUES ('Teclat', 25.90, 10);
SELECT nom, email FROM clients;
SELECT * FROM clients;

INSERT 0 1
 nom | email
-----+-------
(0 rows)

ERROR:  permission denied for table clients

S'observa que ana hereta els privilegis del grup: pot inserir productes i consultar les columnes permeses de clients, però no la resta de columnes. A més, per inserir en una taula amb una columna SERIAL, cal també el privilegi USAGE sobre la seqüència associada.

Privilegis sobre tots els objectes d'un esquema

Quan un esquema té moltes taules, es poden concedir els privilegis sobre totes alhora amb la clàusula ALL TABLES IN SCHEMA:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO vendes;

Aquesta ordre només afecta les taules que ja existeixen. Perquè els privilegis s'apliquen també a les taules que es creen en el futur, cal definir els privilegis per defecte amb ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO vendes;

Els privilegis per defecte només s'apliquen als objectes que cree el rol que executa l'ordre (o el rol indicat amb FOR ROLE).

Documentació oficial: ALTER DEFAULT PRIVILEGES – PostgreSQL

Revocació de privilegis (REVOKE)

Els privilegis es retiren amb l'ordre REVOKE, amb una sintaxi simètrica a la de GRANT:

REVOKE [GRANT OPTION FOR] <privilegis> ON <tipus_objecte> <objecte>
    FROM <rol> [CASCADE | RESTRICT];
  • [GRANT OPTION FOR]: (opcional) retira només la possibilitat de concedir el privilegi a altres rols, però no el privilegi.
  • <privilegis>, <tipus_objecte> <objecte> i <rol>: els mateixos valors que en GRANT.
  • [CASCADE | RESTRICT]: (opcional) indica què passa si el rol havia concedit el privilegi a altres rols amb WITH GRANT OPTION. Amb CASCADE també es revoca als altres rols; amb RESTRICT, que és el valor per defecte, l'ordre falla.

Documentació oficial: REVOKE – PostgreSQL

Exemple: Retirar privilegis

Es retira al grup vendes el permís per modificar productes:

REVOKE UPDATE ON productes FROM vendes;

Si ana intenta modificar un producte, ara obté un error:

UPDATE productes SET stock = 5 WHERE id = 1;

ERROR:  permission denied for table productes

S'observa que, en retirar el privilegi al grup, el perden tots els seus membres alhora.

Un privilegi concedit a un grup no es pot retirar a un sol membre: REVOKE ... FROM ana no té efecte si ana l'hereta de vendes.

Consulta dels privilegis

Per saber quins privilegis té cada rol sobre un objecte, es pot utilitzar la metaordre \dp (o \z) de psql:

\dp productes

                                  Access privileges
 Schema |   Name    | Type  |     Access privileges     | Column privileges | Policies
--------+-----------+-------+---------------------------+-------------------+----------
 public | productes | table | postgres=arwdDxt/postgres+ |                   |
        |           |       | vendes=ar/postgres        |                   |

Cada entrada té el format rol=privilegis/qui_els_ha_concedit, on cada lletra representa un privilegi.

Abreviatures dels privilegis
Lletra Privilegi Lletra Privilegi
r SELECT D TRUNCATE
a INSERT x REFERENCES
w UPDATE t TRIGGER
d DELETE X EXECUTE
U USAGE C CREATE
c CONNECT T TEMPORARY
m MAINTAIN * Opció de concedir (WITH GRANT OPTION)

El privilegi MAINTAIN només existeix a partir de PostgreSQL 17.

Amb SQL, la vista information_schema.role_table_grants mostra els mateixos privilegis en un format més llegible:

SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'vendes';

Finalment, les funcions has_*_privilege permeten comprovar si un rol té un privilegi concret, tant si el té directament com si l'hereta d'un grup:

SELECT has_table_privilege('ana', 'productes', 'INSERT');

 has_table_privilege
---------------------
 t

Propietat dels objectes

Com s'ha vist en la introducció, el propietari d'un objecte hi té tots els privilegis i és l'únic que el pot modificar o eliminar. Per això, és habitual que els objectes d'una aplicació pertanguen a un rol sense LOGIN i que els usuaris hi accedisquen amb privilegis concedits.

La propietat d'un objecte es pot transferir amb la clàusula OWNER TO de l'ordre ALTER corresponent:

ALTER TABLE productes OWNER TO vendes;
ALTER DATABASE botiga OWNER TO vendes;

Si el grup vendes és propietari de les taules, tots els seus membres les poden modificar i eliminar, ja que hereten la propietat.

📌 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.
 
CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez