Auditing in PostgreSQL

Questa paginetta riporta i passi per la configurazione dell'auditing in PostgreSQL.

La registrazione degli accessi e delle attivita' (AUDIT) e' importante sia per ragioni di sicurezza che per ottemperare alle leggi ed ai regolamenti sulla gestione delle basi dati.

Anche PostgreSQL ha la funzionalita' di audit ed in questo documento vediamo come configurare l'audit per la registrazione delle attivita' occorse sulla base dati.
In realta' a seconda delle esigenze e delle versioni di PostgreSQL vi sono diverse alternative... cercheremo di riportarle tutte!

Nel seguito sono riportate alcune informazioni di interesse organizzate in paragrafi specifici: Introduzione, Logging, Trigger, PGAudit, Obblighi di legge, Varie ed eventuali.

Introduzione

La rilevazione delle attivita' svolte su un database e' molto importante sia per necessita' di logging/tracing che per adempiere agli eventuali requisiti di legge. PostgreSQL permette di effettuare un auditing delle attivita' con diversi strumenti che rispondono alle crescenti esigenze di auditing.

Le tecniche che riportiamo in questo documento sono:

• Logging
• Trigger
• PGAudit

Le versioni di riferimento sono quelle di produzione piu' recenti (PostgreSQL 9.6), eventuali riferimenti a versioni precedenti sono riportati quando necessario.

Logging

La modalita' di base con PostgreSQL per registrare gli accessi e le attivita' eseguite sul database e' quella di attivare il logging.

Il logging si abilita impostando gli opportuni parametri nel file di configurazione postgres.conf. Le possibilita' di configurazione sono molto ampie... vediamo un semplice esempio che copre i casi principali:

Con questa configurazione vengono monitorati tutte le connessioni alla base dati e le istruzioni di DDL. Vengono cosi' registrati gli accessi e le eventuali variazioni di privilegi degli utenti/ruoli [NdE che e' quanto richiede il provvedimento del Garante in Italia dal 15 dicembre 2009].

I parametri di configurazione sono diversi ed e' possibile variare dove (su file, con syslog, ...), cosa (le connessioni, i tipi di comandi SQL, i lock, ...) e quando (livello d'errore, durata dello statement, ...) effettuare il logging.

Un'altra tipica configurazione e' quella con syslog. Il syslog utilizza i propri timestamp, quindi non e' opportuno richiederli nella log_line_prefix; mentre vanno indicati facility ed ident. Vediamo cosa cambia se vogliamo avere il logging anche su syslog [NdA con Postgres e' possibile avere piu' modalita' di logging in parallelo]:

Con log_statement e' possibile variare il tipo di statement monitorati... tuttavia l'impostazione a all la verbosita' del logging e l'impatto sulle performance sono troppo elevati e viene quindi utilizzata solo per periodi limitati di tempo durante un debug. Per monitorare tutte le azioni svolte su alcune tabelle critiche e' necessario utilizzare un'altra tecnica: i trigger.

La documentazione completa sul logging con PostgreSQL si trova sul sito ufficiale.

Trigger

PostgreSQL e' un database ad oggetti e supporta i trigger dalla prima versione.

E' quindi semplice tracciare le modifiche occorse su una tabella definendo una serie di trigger come:

CREATE TRIGGER log_update
    AFTER UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE PROCEDURE log_account_update();

Oltre ai valori delle colonne tra i parametri piu' utili in un trigger di auditing riportiamo: session_user::text, current_timestamp, statement_timestamp(), clock_timestamp(), txid_current(), current_setting('application_name'), inet_client_addr(), inet_client_port(), current_query(), ...
La documentazione ufficiale riporta tutti i dettagli.

E' anche abbastanza semplice costruire un trigger che riporta su una tabella di audit di accessi agli oggetti piu' importanti della base dati.

BEGIN;

CREATE TABLE foo (id SERIAL PRIMARY KEY, data TEXT);
CREATE TABLE foo_track(tracktime TIMESTAMP DEFAULT now(), foo_row foo);

INSERT INTO foo (data) SELECT 'Some Data'||id FROM generate_series(1,10) AS
id;

CREATE OR REPLACE FUNCTION foo_track_func(foo) RETURNS integer AS
$$
  INSERT INTO foo_track(foo_row) VALUES ($1) RETURNING (foo_row).id
$$
LANGUAGE sql;

CREATE VIEW v_foo AS SELECT foo.*, foo_track_func(foo.*) FROM foo;

SELECT * FROM v_foo;
SELECT * FROM foo_track;

COMMIT;

Oltre a sviluppare applicativamente un proprio trigger e' possibile utilizzare un ottimo trigger generico disponibile su Github e descritto in questa pagina [NdE il trigger e' stato sviluppato da 2ndQuadrant].

Viene creato lo schema audit e, tra le altre, la tabella audit.logged_actions che conterra' tutte le attivita'. L'utilizzo e' semplicissimo:

PGAudit

2ndQuadrant ha sviluppato e reso disponibile su Github l'estensione PGAudit.

Si tratta di un oggetto molto stabile disponibile dalla versione 9.5 di PostgreSQL. Il rilascio delle release non e' frequente come per le minor releases di PostgreSQL ma viene sempre rilasciata una versione PGAudit per ogni nuova major release PostgreSQL.
La corrispondenza tra versioni e' la seguente [NdA sono riportate le ultime versioni consigliate di PGAudit]:

PGAudit non fa parte delle extension core di PostgreSQL e quindi generalmente e' necessario compilarlo per la piattaforma in uso. L'installazione richiede l'aggiunta della libreria nelle shared_preload_libraries e la creazione dell'extension pgaudit. Per una completa visualizzazione nei log PostgreSQL e' consigliabile utilizzare il formato CSV impostando il parametro log_destination=csvlog.

Una volta installato le impostazioni di pgaudit si effettuano come amministratore e sono molto semplici. L'auditing a livello di sessione si imposta con la variabile pgaudit.log mentre quello a livello di oggetto si imposta con un GRANT:

set pgaudit.log = 'ddl';

set pgaudit.role = 'auditor';
grant select, insert, update
   on mydb.salary
   to auditor;

Una descrizione piu' completa dell'utilizzo pratico di PGAudit e' riportata in questa paginetta sul logging in RDS.

Obblighi di legge

Ci sono diverse leggi e regolamenti che richiedono l'auditing sull'accesso ai dati...

La legge in vigore in Italia e' il codice in materia di protezione dei dati personali (d.lg. 30 giugno 2003, n. 196) nonche' il disciplinare tecnico in materia di misure minime di sicurezza di cui all'allegato B del medesimo codice. Tale legge inquadra in modo completo la materia ma, dal punto di vista pratico, il riferimento sono i provvedimenti del Garante. I dettagli sono riportati su questa pagina: in pratica dal 15 Dicembre 2009 vanno registrati tutti gli accessi amministrativi ai sistemi ed alle basi dati!

Inoltre il 27 aprile 2016 e' stato adottato il REGOLAMENTO (UE) 2016/679 DEL PARLAMENTO EUROPEO E DEL CONSIGLIO relativo alla protezione delle persone fisiche con riguardo al trattamento dei dati personali, nonche' alla libera circolazione di tali dati e che abroga la direttiva 95/46/CE (regolamento generale sulla protezione dei dati). Tale regolamento, generalmente riferito con l'acronimo inglese GDPR (General Data Protection Regulation) ha validita' di legge senza bisogno di essere approvato dal Parlamento Italiano ed entrera' in vigore il 25 Maggio 2018.
Il GDPR e' molto ampio (cfr. testo ufficiale) e prevede regole comuni per tutti i paese della comunita' europea, anzi ha validita' extraterritoriale quando i dati riguardano cittadini europei. Oltre all'inasprimento delle sanzioni con il GDPR cambia sopratutto la prospettiva d'insieme e richiede che la protezione dei dati faccia parte del progetto di sviluppo dei processi aziendali per i prodotti ed i servizi offerti dall'azienda.

Per quanto riguarda gli USA i principali obblighi sull'auditing riguardano:

• Sarbanes-Oxley (SOX) Act of 2002: legge federale che specifica come debbono essere gestiti e protetti i dati finanziari.
• Payment Card Industry Data Security Standard (PCI-DSS): standard internazionale a protezione dei dati delle carte di credito.
• Health Insurance Portability and Accountability Act (HIPAA): legge a protezione dei dati personali e sanitari.

Molto completi e dettagliati sono i CIS Benchmarks che prevedono tutti i controlli necessari suddivisi per versione di database.

E' importante sottolineare che i requisiti minimi richiesti dalle normative non si limitano all'audit... I risultati dell'audit vanno anche salvati in modo corretto e resi consultabili. Fondamentali sono anche una corretta definizione dei privilegi [NdE improntata al principio del minimo privilegio] e la crittografia [NdE sia sui dati che sulla loro trasmissione].
E' necessario procedere prima ad un assessment tecnico e funzionale della base dati e quindi a provvedere alla messa in sicurezza del database con tutti gli strumenti necessari, tra cui l'audit.

Varie ed eventuali

Se si utilizza PostgreSQL su AWS puo' essere utile questo documento.

Titolo: Auditing in PostgreSQL
Livello: Medio (2/5)
Data: 14 Febbraio 2017 ❤️
Versione: 1.0.4 - 31 Ottobre 2021 🎃
Autore: mail [AT] meo.bogliolo.name