Comandi Essenziali per la Gestione di Utenti, Ruoli e Permessi in PostgreSQL

Utenti, ruoli e permessi in PostgreSQL determinano chi può connettersi, cosa può leggere e cosa può modificare. Se questi permessi crescono in modo approssimativo, il tuo database diventa difficile da controllare e facile da esporre eccessivamente.

PostgreSQL utilizza un unico concetto, il ruolo, sia per gli utenti di login che per i gruppi. Un ruolo con LOGIN può connettersi. Un ruolo senza LOGIN viene spesso utilizzato come gruppo che possiede privilegi condivisi.

Creare e Modificare Ruoli

Crea un ruolo di login per un'applicazione con una password:

CREATE ROLE app_user WITH LOGIN PASSWORD 'password_forte_qui';

Crea un ruolo di gruppo per accesso in sola lettura condiviso:

CREATE ROLE reporting_readonly NOLOGIN;

Gli attributi comuni dei ruoli includono:

Attributo	Cosa permette
LOGIN	Connettersi al server del database.
CREATEDB	Creare database.
CREATEROLE	Creare, modificare ed eliminare molti ruoli, con importanti limitazioni riguardo ai ruoli superuser.
REPLICATION	Utilizzare connessioni di replica.
SUPERUSER	Bypassare i normali controlli dei permessi. Usare solo per ruoli admin strettamente controllati.
INHERIT	Utilizzare i privilegi dei ruoli di cui è membro. Questo è il comportamento predefinito.

Cambia una password o disabilita il login con ALTER ROLE:

ALTER ROLE app_user WITH PASSWORD 'nuova_password_forte';
ALTER ROLE vecchio_impiegato NOLOGIN;

Prima di eliminare un ruolo, gestisci tutto ciò che possiede e tutti i privilegi che gli sono stati concessi:

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

REASSIGN OWNED trasferisce gli oggetti posseduti. DROP OWNED rimuove i privilegi concessi al ruolo e può eliminare oggetti ancora posseduti da esso nel database corrente, quindi rivedi attentamente prima di eseguirlo.

Utilizzare Ruoli di Gruppo per un Accesso più Pulito

Concedi permessi ai ruoli di gruppo, poi aggiungi persone o ruoli applicativi a quei gruppi.

GRANT reporting_readonly TO alice, bob;
REVOKE reporting_readonly FROM bob;

WITH ADMIN OPTION permette al membro di concedere e revocare l'appartenenza a quel ruolo per altri. Di per sé non rende il membro un superuser né dà potere illimitato sul database.

GRANT reporting_readonly TO team_lead WITH ADMIN OPTION;

Questo schema mantiene l'accesso più facile da controllare. Invece di controllare 50 utenti per le concessioni sulle tabelle, puoi controllare il ruolo di gruppo.

Concedere Permessi sugli Oggetti

Usa GRANT per permettere azioni specifiche su oggetti specifici.

GRANT SELECT ON TABLE production.orders TO reporting_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app.orders TO app_user;

I privilegi comuni sulle tabelle sono SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES e TRIGGER.

I permessi sugli schemi sono separati dai permessi sulle tabelle. Un ruolo spesso necessita di USAGE sullo schema e dei privilegi sulle tabelle al suo interno:

GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_user;

Per gli inserimenti che utilizzano sequenze, concedi anche l'accesso alla sequenza:

GRANT USAGE, SELECT ON SEQUENCE app.orders_id_seq TO app_user;

Per le funzioni, concedi esplicitamente l'esecuzione:

GRANT EXECUTE ON FUNCTION app.audit_log_insert(text) TO app_user;

Revocare Permessi

REVOKE rimuove una concessione diretta.

REVOKE INSERT ON TABLE production.orders FROM app_user;
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM reporting_readonly;

Se un utente ha ancora accesso dopo una revoca, controlla l'appartenenza al gruppo. L'utente potrebbe ereditare lo stesso privilegio attraverso un altro ruolo.

Impostare Permessi per Tabelle Future

GRANT ON ALL TABLES riguarda solo le tabelle esistenti. Per le tabelle create successivamente, usa ALTER DEFAULT PRIVILEGES dal ruolo che creerà quelle tabelle.

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting_readonly;

Questo è uno dei passaggi mancanti più comuni nelle configurazioni dei permessi di PostgreSQL. Un utente può leggere le tabelle di oggi ma viene negato sulle tabelle di domani perché i privilegi predefiniti non sono mai stati configurati.

Controllare l'Accesso Corrente

In psql, questi comandi sono controlli rapidi:

\du
\du+ app_user
\dp app.orders
\dn+

Le viste del catalogo sono migliori per report ripetibili:

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
ORDER BY rolname;

Per le concessioni sulle tabelle:

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'app'
ORDER BY grantee, table_name, privilege_type;

Abitudini di Sicurezza che Ripagano

Assegna ruoli dedicati alle applicazioni invece di utilizzare un account DBA umano. Concedi il set più piccolo di privilegi di cui l'applicazione ha bisogno. Evita SUPERUSER e concessioni ampie di ALL PRIVILEGES per il lavoro quotidiano.

Usa ruoli di gruppo per l'accesso condiviso e rivedi regolarmente le appartenenze. Quando qualcuno cambia team, rimuovere un'appartenenza a un gruppo è più sicuro che cercare concessioni a livello di tabella.

Il passo pratico successivo è scegliere uno schema di produzione ed elencare i suoi ruoli, appartenenze, concessioni sulle tabelle e privilegi predefiniti. Correggi le lacune lì prima di espandere lo stesso schema al resto dei tuoi database.