Partiamo dalle basi: definiamo alcuni concetti di base che saranno ripetuti spesso all’interno di questo post, come il concetto di utente, ruolo e permessi.

Utenti e ruoli

Un utente in Postgres è un ruolo che ha il permesso di eseguire il login by default, nulla di più. Questo potrebbe sembrare confusionario, ma la realtà è che in questo database utenti, ruoli e anche gruppi sono la stessa cosa.

Rappresentazione dell’istruzione CREATE USER

Questo vuole dire che le istruzioni CREATE USER e CREATE GROUP sono in realtà degli alias dell’istruzione CREATE ROLE, e che quando andremo a creare un utente, gli verrà assegnato automaticamente il ruolo LOGIN. Per questa ragione, nella documentazione ufficiale e su molti tutorial online, i due termini sono utilizzati in maniera ambivalente; per non fare confusione, ci atterremo all’utilizzo della parola utente e ruolo come intese in senso più generale, anche se consapevoli della differenza.

Infatti, in altri database relazionali come Oracle, utenti e ruoli sono due entità molto diverse: per esempio, un ruolo non può essere usato per eseguire il login all’interno del database, ma serve a garantire una serie di permessi a gruppi e utenti.

E cosa sono i ruoli?

Un ruolo è un’entità che può possedere tutti gli oggetti presenti nel database e avere privilegi sul database stesso.

Un esempio di ruolo è SUPERUSER: questo permette all’utente di bypassare tutti i permessi richiesti per qualsiasi operazione all’interno del database, eccetto il login (se non diversamente specificato).

Altri ruoli sono:

• LOGIN: consente agli utenti di connettersi inizialmente al database;
• CREATEDB: consente di creare nuovi database;
• CREATEROLE: permette all’utente di creare, modificare e cancellare altri ruoli. Questo attributo consente anche al ruolo di assegnare o modificare l’appartenenza a un ruolo.
• REPLICATION: consente al ruolo di avviare la replica dei dati nel database su repliche secondarie.
• PASSWORD: assegna al ruolo una password che verrà utilizzata tramite i meccanismi di autenticazione.
• INHERIT: Determina se il ruolo eredita i privilegi dei ruoli.

Questi permessi vengono attribuiti tramite due istruzioni: GRANT, che serve ad assegnare dei privilegi a un utente, oppure ALTER, che modifica lo stato attuale di questi permessi per un dato utente.

Un privilegio è un riferimento a un’istruzione SQL ben precisa, come può essere SELECT, CREATE o TRUNCATE. I permessi -o privilegi- definiscono l’insieme specifico di operazioni e di accesso disponibili per i vari ruoli; di base, PostgreSQL segue un modello che richiede la specifica di un “elenco di permessi”, il che significa che gli utenti non possono accedere agli oggetti presenti del database se non esplicitamente concessi.

L’elenco completo è il seguente: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE, SET e ALTER SYSTEM.

Rappresentazione di privilegi, ruoli e utenti in PG

Fatte queste premesse, andiamo a vedere un esempio pratico: creiamo un utente myuser, a cui permetteremo di eseguire una serie di operazioni sulle tabelle tramite l’istruzione GRANT per la prima volta e ALTER per le volte successive.

Creare un utente

All’interno del database mydatabase, creiamo un utente chiamato myuser con la seguente sintassi:

CREATE USER myuser

Attenzione: per fare questo, avremo bisogno di aver eseguito la lo**gin tramite un utente che abbia i permessi di creazione, e che quindi sia -ad esempio- SUPERUSER.

Assegnare dei permessi

Immaginiamo di avere all’interno del database uno schema chiamato acme all’interno del quale sono state create due tabelle, una chiamata products e l’altra orders:

Esempio di database con delle tabelle

La sintassi generica dell’istruzione GRANT è la seguente:

GRANT <privilege> ON <oggetto> TO <utente/ruolo> [WITH GRANT OPTION];

Se volessimo assegnare all’utente myuser i permessi per inserire nuovi record ed elencare sulla tabella orders, ma solo per recuperare l’elenco dei prodotti presenti in products, potremmo scrivere le seguenti query:

GRANT SELECT ON products IN SCHEMA acme TO myuser;

GRANT SELECT, INSERT ON orders IN SCHEMA acme TO myuser; 

Se invece avessimo un utente che deve avere tutti i permessi su queste due tabelle, potremmo riscrivere la precedente istruzione in questo modo:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA acme TO mysuperuser;

Per modificare i ruoli di un utente, possiamo anche sfruttare l’istruzione ALTER in questo modo: qui andiamo ad assegnare all’utente mysuperuser i permessi di creazione di un database:

ALTER USER mysuperuser CREATEDB;

Per renderlo un utente con privilegi amministrativi, potremmo invece usare questa istruzione, configurando anche la relativa password attraverso il relativo ruolo:

ALTER USER mysuperuser WITH SUPERUSER PASSWORD 'mypassword';

Revocare dei permessi

Piuttosto semplice finora, no?

Proviamo a revocare i permessi agli utenti: ad esempio, rimuoviamo i permessi di inserimento sulla tabella orders all’utente myuser tramite l’istruzione REVOKE.

La sintassi generica dell’istruzione GRANT è la seguente:

REVOKE <privilege> ON <oggetto> FROM <utente/ruolo>;

Quindi, per l’esempio precedente, l’istruzione sarà questa:

REVOKE INSERT ON orders FROM myuser;

Per rimuovere del tutto questi permessi, è possibile eseguire un’istruzione che contenga la parola ALL, in questo modo:

REVOKE ALL ON orders FROM myuser;

Per saperne di più sulle basi di dati e su PostgreSQL (e non solo), dai un’occhiata a questo manuale!