GRANT și ALTER DEFAULT PRIVILEGES în PostgreSQL

GRANT {SELECT | INSERT | UPDATE | DELETE | ALL} ON tabel | ALL TABLES IN SCHEMA schema TO rol;
GRANT USAGE [, CREATE] ON SCHEMA schema TO rol;
GRANT CONNECT ON DATABASE baza TO rol;
GRANT grup TO rol;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema GRANT SELECT ON TABLES TO rol;

Ce face GRANT și ALTER DEFAULT PRIVILEGES

Pentru ca un utilizator să citească un tabel are nevoie de trei niveluri de drepturi: CONNECT pe baza de date, USAGE pe schemă și SELECT pe tabel. Lipsa oricăruia dă permission denied.

GRANT ... ON ALL TABLES IN SCHEMA se aplică doar tabelelor care există acum. Pentru tabelele create mai târziu folosește ALTER DEFAULT PRIVILEGES; regula se aplică obiectelor create de rolul care rulează comanda (sau de cel dat cu FOR ROLE).

Coloanele cu GENERATED ... AS IDENTITY sau serial folosesc secvențe: aplicația care face INSERT pe tabele cu serial are nevoie și de USAGE pe secvență. Din PostgreSQL 14 există roluri predefinite ca pg_read_all_data și pg_write_all_data.

Parametri

Parametru	Descriere
SELECT, INSERT, UPDATE, DELETE, TRUNCATE	Drepturi pe tabele; se pot da și pe coloane: GRANT SELECT (id, nume) ON clienti.
USAGE / CREATE ON SCHEMA	Acces la obiectele din schemă, respectiv dreptul de a crea obiecte noi.
CONNECT ON DATABASE	Dreptul de a se conecta la baza de date.
WITH GRANT OPTION	Rolul poate da mai departe dreptul primit.

Ce returnează

Mesajul GRANT (sau ALTER DEFAULT PRIVILEGES).

Exemple

Exemplul 1: Acces doar de citire pentru grupul cititori

GRANT CONNECT ON DATABASE magazin TO cititori;
GRANT USAGE ON SCHEMA public TO cititori;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cititori;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO cititori;

Rezultat în psql

GRANT
GRANT
GRANT
ALTER DEFAULT PRIVILEGES

Exemplul 2: Drepturi pentru aplicație și pe coloane

GRANT SELECT, INSERT, UPDATE ON comenzi, linii_comanda TO aplicatie_web;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO aplicatie_web;
GRANT SELECT (id, nume, oras) ON clienti TO suport;

SELECT has_table_privilege('aplicatie_web', 'comenzi', 'DELETE') AS poate_sterge;

Rezultat în psql

GRANT
GRANT
GRANT
 poate_sterge 
--------------
 f
(1 row)

Exemplul 3: Ce vede un utilizator fără drept

Conectat ca ana, care face parte din cititori, dar tabelul salarii este în schema hr, pe care grupul nu o poate folosi.

SELECT * FROM hr.salarii;

Rezultat în psql

ERROR:  permission denied for schema hr
LINE 1: SELECT * FROM hr.salarii;
                      ^

Greșeli frecvente

• Dai SELECT ON ALL TABLES și a doua zi utilizatorul nu vede tabelul nou. Adaugă ALTER DEFAULT PRIVILEGES.
• Dai drepturi pe tabel, dar uiți USAGE pe schemă și primești permission denied for schema.
• Folosești GRANT ALL pentru aplicații. Dă doar ce folosește aplicația: de obicei SELECT, INSERT, UPDATE, DELETE.

Comenzi înrudite

• CREATE ROLE / CREATE USER: Creează un rol: un utilizator care se poate conecta (LOGIN) sau un grup de drepturi atribuit altor roluri.
• REVOKE: Retrage drepturi date anterior cu GRANT, de la un rol sau de la toți (PUBLIC).
• CREATE SCHEMA și search_path: O schemă este un dosar de tabele în interiorul unei baze de date. search_path stabilește în ce scheme caută PostgreSQL numele fără prefix.