Centro di Conoscenza DevOps
Centro di Conoscenza DevOps

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

Padroneggia i comandi SQL essenziali per una solida sicurezza e gestione degli utenti di PostgreSQL. Questa guida fornisce passi pratici per la creazione, la modifica e l'eliminazione dei ruoli, l'impostazione di attributi complessi (come LOGIN e CREATEDB), e la gestione dell'appartenenza ai gruppi. Impara a controllare l'accesso con precisione utilizzando i potenti comandi `GRANT` e `REVOKE`, definendo i permessi a livello di oggetto su tabelle, schemi e funzioni. Implementa il principio del minimo privilegio con esempi dettagliati e scopri i comandi psql chiave per l'audit delle attuali impostazioni di sicurezza.

374 visualizzazioni

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

La sicurezza dei database dipende da un robusto controllo degli accessi. In PostgreSQL, questo controllo è gestito principalmente tramite Ruoli e Permessi (o privilegi) associati. Comprendere come creare, modificare e gestire queste entità di sicurezza è fondamentale per qualsiasi amministratore o sviluppatore di database.

Questa guida completa esplora i comandi SQL essenziali necessari per gestire in sicurezza gli utenti, definire le politiche di accesso e implementare il principio del minimo privilegio all'interno del tuo ambiente PostgreSQL. Tratteremo la creazione dei ruoli, l'impostazione degli attributi, l'appartenenza a gruppi e la gestione dettagliata dei permessi sugli oggetti utilizzando GRANT e REVOKE.

Comprendere i Ruoli di PostgreSQL

A differenza di alcuni altri sistemi di database che distinguono rigorosamente tra utenti e gruppi, PostgreSQL utilizza un concetto unificato: il Ruolo. Un ruolo può rappresentare:

  1. Un utente di database (un'entità che può effettuare il login, tipicamente con l'attributo LOGIN).
  2. Un gruppo di utenti (un'entità utilizzata esclusivamente per raggruppare i privilegi, tipicamente priva dell'attributo LOGIN).

Una sicurezza efficace inizia con la definizione di ruoli che riflettano accuratamente le responsabilità degli individui o delle applicazioni che accedono al database.

1. Gestione dei Ruoli: Creazione, Modifica ed Eliminazione

I comandi fondamentali per la gestione dei principali di database ruotano attorno a CREATE ROLE, ALTER ROLE e DROP ROLE.

Creazione di Nuovi Ruoli

Quando si crea un ruolo, è necessario specificarne gli attributi, in particolare se può effettuare il login (LOGIN) e le sue credenziali di autenticazione (PASSWORD).

Creazione di un Utente di Login Base

Per creare un ruolo utente standard che richiede una password per la connessione:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

Creazione di un Ruolo con Attributi Specifici

I ruoli possono avere diversi attributi che definiscono le loro capacità e i permessi di sistema:

Attributo Descrizione
LOGIN Consente al ruolo di connettersi al database.
SUPERUSER Concede tutti i privilegi del database (usare con parsimonia).
CREATEROLE Consente al ruolo di creare, modificare ed eliminare altri ruoli.
CREATEDB Consente al ruolo di creare nuovi database.
REPLICATION Consente al ruolo di avviare la replica in streaming.
INHERIT / NOINHERIT Controlla se il ruolo eredita automaticamente i privilegi dai ruoli di cui è membro (il valore predefinito è INHERIT).

Esempio: Creazione di un Ruolo Amministratore (Non-Superuser)

CREATE ROLE db_admin WITH
    LOGIN
    PASSWORD 'admin_secret'
    CREATEROLE
    CREATEDB
    VALID UNTIL '2025-01-01'; -- Data di scadenza opzionale

Modifica dei Ruoli Esistenti

Utilizzare ALTER ROLE per cambiare gli attributi o aggiornare la password per un ruolo esistente.

-- Cambia la password per un utente esistente
ALTER ROLE app_user WITH PASSWORD 'new_strong_password';

-- Revoca la capacità di login (trasformando un utente in un gruppo)
ALTER ROLE db_admin NOLOGIN;

-- Imposta un utente come bloccato
ALTER ROLE old_employee NOLOGIN;

Eliminazione dei Ruoli

Prima di eliminare un ruolo, assicurarsi che il ruolo non sia proprietario di alcun oggetto di database (tabelle, schemi, ecc.). In caso contrario, è necessario prima trasferire la proprietà utilizzando REASSIGN OWNED.

-- Elimina tutti gli oggetti posseduti dal ruolo e riassegnali a 'postgres'
REASSIGN OWNED BY old_employee TO postgres;

-- Quindi, elimina il ruolo
DROP ROLE old_employee;

Avviso: L'eliminazione di un ruolo è irreversibile. Usare cautela, specialmente con ruoli che possiedono molti oggetti.

2. Gestione dell'Appartenenza a Gruppi

I ruoli spesso funzionano come gruppi per semplificare la gestione dei permessi. Invece di concedere i permessi a 50 singoli utenti, li si concede a un unico ruolo di gruppo e quindi si rende l'utente membro di quel gruppo.

Creazione di un Ruolo di Gruppo

I gruppi vengono tipicamente creati senza l'attributo LOGIN.

CREATE ROLE data_analysts NOLOGIN;

Concessione e Revoca dell'Appartenenza a Gruppi

Utilizzare il comando GRANT per aggiungere membri a un ruolo di gruppo e REVOKE per rimuoverli.

-- Aggiunge l'utente 'alice' e l'utente 'bob' al gruppo 'data_analysts'
GRANT data_analysts TO alice, bob;

-- Rimuove 'bob' dal gruppo 'data_analysts'
REVOKE data_analysts FROM bob;

L'opzione WITH ADMIN OPTION

Se si include WITH ADMIN OPTION durante la concessione dell'appartenenza, il ruolo destinatario può a sua volta concedere l'appartenenza a quel gruppo ad altri e può anche effettuare il DROP (eliminazione) del ruolo di gruppo.

GRANT data_analysts TO supervisor WITH ADMIN OPTION;

3. Gestione dei Permessi sugli Oggetti (Privilegi)

I permessi definiscono quali azioni un ruolo può eseguire su quali oggetti di database (tabelle, viste, funzioni, schemi, ecc.). Questo è il fulcro della sicurezza del database.

La Sintassi del Comando GRANT

GRANT privilege_list ON object_type object_name TO role_name [WITH GRANT OPTION];

Privilegi Comuni ed Esempi

Privilegi sulle Tabelle

Privilegio Azione Consentita
SELECT Leggere i dati dalla tabella.
INSERT Aggiungere nuove righe.
UPDATE Modificare le righe esistenti.
DELETE Rimuovere le righe esistenti.
TRUNCATE Svuotare completamente la tabella.
REFERENCES Creare vincoli di chiave esterna (foreign key constraints).

Esempio: Concessione dell'accesso in sola lettura a una tabella specifica.

GRANT SELECT ON TABLE production.orders TO data_analysts;

-- Concede tutte le operazioni DML su una tabella
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE staging.temp_data TO app_user;

Privilegi di Database e Schema

I privilegi di database e schema sono cruciali per controllare la struttura dell'ambiente.

Oggetto Privilegio Chiave Scopo
DATABASE CONNECT Consente la connessione al database.
DATABASE CREATE Consente la creazione di nuovi schemi, tablespace, ecc.
SCHEMA USAGE Consente l'accesso agli oggetti all'interno dello schema.
SCHEMA CREATE Consente la creazione di nuovi oggetti all'interno dello schema.

Esempio: Concessione dell'Accesso allo Schema

Se un utente deve accedere alle tabelle all'interno di app_schema, deve disporre del privilegio USAGE su quello schema.

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

Privilegi su Sequenze e Funzioni

L'utilizzo delle sequenze (per gli ID auto-incrementanti) e l'esecuzione delle funzioni richiedono privilegi specifici.

-- Consente all'utente di avanzare la sequenza (necessario per gli INSERT)
GRANT USAGE, SELECT ON SEQUENCE app_schema.user_id_seq TO app_user;

-- Consente l'esecuzione di una specifica stored procedure o funzione
GRANT EXECUTE ON FUNCTION audit_log_insert(text) TO app_user;

Revoca dei Permessi

Utilizzare il comando REVOKE per rimuovere permessi specifici precedentemente concessi. La sintassi rispecchia quella di GRANT.

-- Revoca la capacità di inserire nuovi record nella tabella orders
REVOKE INSERT ON TABLE production.orders FROM app_user;

-- Revoca tutti i permessi precedentemente concessi (nota: non revoca la proprietà)
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM data_analysts;

Nota sulla Revoca: Se un privilegio è stato concesso a un ruolo, e tale ruolo è membro di un altro gruppo, la revoca del privilegio interessa solo il permesso concesso direttamente. Se il ruolo eredita ancora il privilegio tramite l'appartenenza al gruppo, conserva l'accesso.

Applicare i Permessi agli Oggetti Futuri

La gestione dei permessi per oggetti che non esistono ancora richiede l'utilizzo di ALTER DEFAULT PRIVILEGES. Questo è essenziale per gli schemi in cui le applicazioni creano frequentemente nuove tabelle.

Esempio: Assicurare che un utente possa eseguire SELECT su tutte le tabelle future create dal ruolo app_owner all'interno di app_schema:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app_schema
    GRANT SELECT ON TABLES TO app_user;

4. Visualizzazione dei Permessi Attuali

Per controllare le impostazioni di sicurezza, PostgreSQL fornisce diversi strumenti e viste di catalogo.

Comando/Vista Descrizione
\du (in psql) Elenca tutti i ruoli e i loro attributi.
\du+ role_name (in psql) Mostra gli attributi dettagliati del ruolo e l'appartenenza.
\dp table_name (in psql) Elenca i permessi (privilegi) concessi su una tabella.
pg_roles Vista del catalogo di sistema contenente le definizioni dei ruoli.
information_schema.table_privileges Vista che mostra i privilegi concessi sulle tabelle.

Esempio: Verifica dei privilegi su una tabella tramite psql

=> \dp production.orders

Migliori Pratiche di Sicurezza per la Gestione degli Utenti

  1. Principio del Minimo Privilegio (PoLP): Concedere solo l'insieme minimo di permessi necessari a un ruolo per svolgere la sua funzione. Evitare di concedere ALL PRIVILEGES o SUPERUSER se non assolutamente necessario.
  2. Ruoli Applicativi Separati: Utilizzare ruoli dedicati per le applicazioni (ad esempio, api_service_role) separati dai ruoli DBA umani. Le applicazioni dovrebbero tipicamente avere solo permessi DML (SELECT, INSERT, UPDATE, DELETE) e non permessi DDL (CREATE, DROP).
  3. Utilizzare i Gruppi in Modo Estensivo: Creare ruoli senza login (gruppi) per gestire insiemi di permessi (ad esempio, read_only_group, etl_writer_group). Assegnare i singoli utenti a questi gruppi anziché concedere i permessi individualmente.
  4. Evitare di Utilizzare Ruoli Predefiniti: Non utilizzare mai il ruolo superutente postgres per attività generiche di applicazione o sviluppo.
  5. Autenticazione Sicura: Utilizzare sempre password forti e, ove possibile, sfruttare l'autenticazione tramite certificato client o soluzioni di gestione centralizzata dell'identità anziché l'autenticazione basata su password.

Conclusione

La gestione efficace di ruoli e permessi è la base della sicurezza di PostgreSQL. Padroneggiando CREATE ROLE, ALTER ROLE, GRANT e REVOKE, gli amministratori di database possono implementare un controllo granulare, assicurando che ogni utente o applicazione che accede al database abbia esattamente l'accesso richiesto e niente di più. Applicare costantemente il principio del minimo privilegio e utilizzare i ruoli di gruppo semplifica la manutenzione a lungo termine e rafforza la posizione del database contro gli accessi non autorizzati.