pgAudit
Registrazione di Audit Open Source per PostgreSQL

Introduzione

L'estensione di audit PostgreSQL (pgAudit) fornisce una registrazione dettagliata degli audit di sessione e/o di oggetto tramite il meccanismo di logging standard di PostgreSQL.

L'obiettivo di pgAudit è fornire agli utenti PostgreSQL la capacità di produrre log di audit spesso richiesti per conformarsi a certificazioni governative, finanziarie o ISO.

Un audit è un'ispezione ufficiale dei conti di un individuo o di un'organizzazione, tipicamente condotta da un organismo indipendente. Le informazioni raccolte da pgAudit sono propriamente chiamate trail di audit o log di audit. In questa documentazione viene utilizzato il termine log di audit.

Perché pgAudit?

La registrazione di base delle istruzioni può essere fornita dal meccanismo di logging standard con log_statement = all. Questo è accettabile per il monitoraggio e altri usi, ma non fornisce il livello di dettaglio generalmente richiesto per un audit. Non basta avere un elenco di tutte le operazioni eseguite sul database. Deve anche essere possibile trovare istruzioni specifiche che interessano a un revisore. Il meccanismo di logging standard mostra cosa ha richiesto l'utente, mentre pgAudit si concentra sui dettagli di ciò che è accaduto mentre il database soddisfava la richiesta.

Ad esempio, un revisore potrebbe voler verificare che una particolare tabella sia stata creata all'interno di una finestra di manutenzione documentata. Potrebbe sembrare un semplice compito per grep, ma cosa succederebbe se venisse presentato un esempio come questo (volutamente offuscato):

DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

La registrazione standard ti darà questo:

LOG:  statement: DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

Sembra che trovare la tabella di interesse possa richiedere una certa conoscenza del codice nei casi in cui le tabelle vengono create dinamicamente. Questo non è ideale poiché sarebbe preferibile cercare semplicemente per nome della tabella. È qui che entra in gioco pgAudit. Per lo stesso input, produrrà questo output nel log:

AUDIT: SESSION,33,1,FUNCTION,DO,,,"DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;"
AUDIT: SESSION,33,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)

Non solo il blocco DO viene registrato, ma la sotto-istruzione 2 contiene il testo completo della CREATE TABLE con il tipo di istruzione, il tipo di oggetto e il nome completo per rendere facili le ricerche.

Quando si registrano istruzioni SELECT e DML, pgAudit può essere configurato per registrare una voce separata per ogni relazione referenziata in un'istruzione. Non è necessario alcun parsing per trovare tutte le istruzioni che toccano una particolare tabella. In effetti, l'obiettivo è che il testo dell'istruzione sia fornito principalmente per analisi forensi approfondite e non dovrebbe essere richiesto per un audit.

Considerazioni sull'uso

A seconda delle impostazioni, è possibile che pgAudit generi un volume enorme di registrazioni. Fare attenzione a determinare esattamente cosa deve essere registrato per l'audit nel proprio ambiente per evitare di registrare troppo.

Ad esempio, quando si lavora in un ambiente OLAP probabilmente non sarebbe saggio registrare gli insert in una grande tabella dei fatti. La dimensione del file di log sarà probabilmente molte volte la dimensione effettiva dei dati degli insert perché il file di log è espresso come testo. Poiché i log sono generalmente memorizzati con il sistema operativo, questo può portare a esaurire molto rapidamente lo spazio su disco. Nei casi in cui non è possibile limitare la registrazione dell'audit a determinate tabelle, assicurarsi di valutare l'impatto sulle prestazioni durante i test e allocare molto spazio sul volume dei log. Questo può essere vero anche per ambienti OLTP. Anche se il volume degli insert non è così elevato, l'impatto sulle prestazioni della registrazione dell'audit può comunque influire notevolmente sulla latenza.

Per limitare il numero di relazioni registrate per le istruzioni SELECT e DML, considerare l'uso della registrazione dell'audit sugli oggetti (vedere Registrazione dell'audit sugli oggetti). La registrazione dell'audit sugli oggetti consente di selezionare le relazioni da registrare, permettendo di ridurre il volume complessivo dei log. Tuttavia, quando vengono aggiunte nuove relazioni, queste devono essere esplicitamente aggiunte alla registrazione dell'audit sugli oggetti. In questo caso può essere una buona opzione una soluzione programmatica in cui le tabelle specificate sono escluse dalla registrazione e tutte le altre sono incluse.

Compatibilità con le versioni di PostgreSQL

pgAudit supporta PostgreSQL 14 o successivo.

Per supportare le nuove funzionalità introdotte in ogni release di PostgreSQL, pgAudit mantiene un ramo separato per ogni versione major di PostgreSQL (attualmente PostgreSQL 14 - 19) che sarà mantenuto in modo simile al progetto PostgreSQL.

Oltre alle correzioni di bug, non è consentito alcun ulteriore sviluppo per i rami stabili. Il nuovo sviluppo, se presente, sarà strettamente per la prossima versione major non rilasciata di PostgreSQL.

Le versioni di pgAudit sono correlate alle versioni major di PostgreSQL come segue:

• pgAudit v19.X è pensato per supportare PostgreSQL 19.

• pgAudit v18.X è pensato per supportare PostgreSQL 18.

• pgAudit v17.X è pensato per supportare PostgreSQL 17.

• pgAudit v16.X è pensato per supportare PostgreSQL 16.

• pgAudit v1.7.X è pensato per supportare PostgreSQL 15.

• pgAudit v1.6.X è pensato per supportare PostgreSQL 14.

Compilazione e installazione

pgAudit può essere compilato contro una copia installata di PostgreSQL con i pacchetti di sviluppo usando PGXS. Le seguenti istruzioni dovrebbero funzionare sulla maggior parte dei sistemi operativi simili a Unix.

Clonare l'estensione pgAudit:

git clone https://github.com/pgaudit/pgaudit.git

Spostarsi nella directory pgAudit:

cd pgaudit

Eseguire il checkout del ramo REL_19_STABLE (notare che il ramo stabile potrebbe non esistere per versioni non rilasciate di PostgreSQL):

git checkout REL_19_STABLE

Compilare e installare pgAudit:

make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-19/bin/pg_config

Le istruzioni per test e sviluppo possono essere trovate in test.

Impostazioni

Le impostazioni possono essere modificate solo da un superuser. Consentire agli utenti normali di modificare le proprie impostazioni vanificherebbe lo scopo di un log di audit.

Le impostazioni possono essere specificate a livello globale (in postgresql.conf o usando ALTER SYSTEM ... SET), a livello di database (usando ALTER DATABASE ... SET) o a livello di ruolo (usando ALTER ROLE ... SET). Notare che le impostazioni non vengono ereditate tramite la normale ereditarietà dei ruoli e SET ROLE non modifica le impostazioni pgAudit di un utente. Questa è una limitazione del sistema dei ruoli e non intrinseca a pgAudit.

L'estensione pgAudit deve essere caricata in shared_preload_libraries. In caso contrario, verrà generato un errore al momento del caricamento e non si verificherà alcuna registrazione di audit.

Inoltre, CREATE EXTENSION pgaudit deve essere chiamato prima che pgaudit.log venga impostato per garantire la corretta funzionalità di pgaudit. L'estensione installa trigger di evento che aggiungono un audit aggiuntivo per DDL. pgAudit funzionerà senza l'estensione installata, ma le istruzioni DDL non avranno informazioni sul tipo e sul nome dell'oggetto.

Se l'estensione pgaudit viene eliminata e deve essere ricreata, pgaudit.log deve essere prima disimpostato, altrimenti verrà generato un errore.

pgaudit.log

Specifica quali classi di istruzioni saranno registrate dalla registrazione dell'audit di sessione. I valori possibili sono:

• READ: SELECT e COPY quando la sorgente è una relazione o una query.

• WRITE: INSERT, UPDATE, DELETE, TRUNCATE e COPY quando la destinazione è una relazione.

• FUNCTION: Chiamate di funzioni e blocchi DO.

• ROLE: Istruzioni relative a ruoli e privilegi: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.

• DDL: Tutto il DDL non incluso nella classe ROLE.

• MISC: Comandi vari, es. DISCARD, FETCH, CHECKPOINT, VACUUM, SET.

• MISC_SET: Comandi SET vari, es. SET ROLE.

• ALL: Include tutte le precedenti.

È possibile fornire più classi usando un elenco separato da virgole e le classi possono essere sottratte anteponendo alla classe un segno - (vedere Registrazione dell'audit di sessione).

Il valore predefinito è none.

pgaudit.log_catalog

Specifica che la registrazione di sessione deve essere abilitata nel caso in cui tutte le relazioni in un'istruzione siano in pg_catalog. La disattivazione di questa impostazione ridurrà il rumore nel log da strumenti come psql e PgAdmin che interrogano pesantemente il catalogo.

Il valore predefinito è on.

pgaudit.log_client

Specifica se i messaggi di log saranno visibili a un processo client come psql. Questa impostazione dovrebbe generalmente essere lasciata disabilitata ma può essere utile per il debug o altri scopi.

Notare che pgaudit.log_level è abilitato solo quando pgaudit.log_client è on.

Il valore predefinito è off.

pgaudit.log_level

Specifica il livello di log che verrà utilizzato per le voci di log (vedere Livelli di gravità dei messaggi per i livelli validi), ma notare che ERROR, FATAL e PANIC non sono consentiti. Questa impostazione viene utilizzata per i test di regressione e può anche essere utile agli utenti finali per test o altri scopi.

Notare che pgaudit.log_level è abilitato solo quando pgaudit.log_client è on; altrimenti verrà utilizzato il valore predefinito.

Il valore predefinito è log.

pgaudit.log_parameter

Specifica che la registrazione dell'audit deve includere i parametri passati con l'istruzione. Quando i parametri sono presenti, verranno inclusi in formato CSV dopo il testo dell'istruzione.

Il valore predefinito è off.

pgaudit.log_parameter_max_size

Specifica che i valori dei parametri più lunghi di questa impostazione (in byte) non devono essere registrati, ma sostituiti con <long param suppressed>. Questa impostazione è in byte, non in caratteri, quindi non tiene conto dei caratteri multi-byte nella codifica di un parametro di testo. Questa impostazione non ha effetto se log_parameter è off. Se questa impostazione è 0 (il valore predefinito), tutti i parametri vengono registrati indipendentemente dalla lunghezza.

Il valore predefinito è 0.

pgaudit.log_relation

Specifica se la registrazione dell'audit di sessione deve creare una voce di log separata per ogni relazione (TABLE, VIEW, ecc.) referenziata in un'istruzione SELECT o DML. Questa è una scorciatoia utile per una registrazione esaustiva senza usare la registrazione dell'audit sugli oggetti.

Il valore predefinito è off.

pgaudit.log_rows

Specifica che la registrazione dell'audit deve includere il numero di righe recuperate o interessate da un'istruzione. Quando abilitata, il campo rows verrà incluso dopo il campo parameter.

Il valore predefinito è off.

pgaudit.log_statement

Specifica se la registrazione includerà il testo dell'istruzione e i parametri (se abilitati). A seconda dei requisiti, un log di audit potrebbe non richiederlo e questo rende i log meno verbosi.

Il valore predefinito è on.

pgaudit.log_statement_once

Specifica se la registrazione includerà il testo dell'istruzione e i parametri con la prima voce di log per una combinazione istruzione/sotto-istruzione o con ogni voce. L'abilitazione di questa impostazione comporterà una registrazione meno verbosa ma potrebbe rendere più difficile determinare l'istruzione che ha generato una voce di log, anche se la coppia istruzione/sotto-istruzione insieme all'id del processo dovrebbe essere sufficiente per identificare il testo dell'istruzione registrato con una voce precedente.

Il valore predefinito è off.

pgaudit.role

Specifica il ruolo principale da usare per la registrazione dell'audit sugli oggetti. È possibile definire più ruoli di audit concedendoli al ruolo principale. Ciò consente a più gruppi di essere responsabili di diversi aspetti della registrazione dell'audit.

Non esiste un valore predefinito.

Registrazione dell'audit di sessione

La registrazione dell'audit di sessione fornisce log dettagliati di tutte le istruzioni eseguite da un utente nel backend.

Configurazione

La registrazione di sessione è abilitata con l'impostazione pgaudit.log.

Abilita la registrazione di sessione per tutto DML e DDL e registra tutte le relazioni nelle istruzioni DML:

set pgaudit.log = 'write, ddl';
set pgaudit.log_relation = on;

Abilita la registrazione di sessione per tutti i comandi tranne MISC e alza i messaggi di log di audit come NOTICE:

set pgaudit.log = 'all, -misc';
set pgaudit.log_level = notice;

Esempio

In questo esempio la registrazione dell'audit di sessione viene utilizzata per registrare le istruzioni DDL e SELECT. Notare che l'istruzione insert non viene registrata poiché la classe WRITE non è abilitata.

SQL:

set pgaudit.log = 'read, ddl';

create table account
(
    id int,
    name text,
    password text,
    description text
);

insert into account (id, name, password, description)
             values (1, 'user1', 'HASH1', 'blah, blah');

select *
    from account;

Output del Log:

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account
(
    id int,
    name text,
    password text,
    description text
);,<not logged>
AUDIT: SESSION,2,1,READ,SELECT,,,select *
    from account,,<not logged>

Registrazione dell'audit sugli oggetti

La registrazione dell'audit sugli oggetti registra le istruzioni che interessano una particolare relazione. Sono supportati solo i comandi SELECT, INSERT, UPDATE e DELETE. TRUNCATE non è incluso nella registrazione dell'audit sugli oggetti.

La registrazione dell'audit sugli oggetti è pensata per essere un sostituto più granulare di pgaudit.log = 'read, write'. Come tale, potrebbe non avere senso usarli insieme, ma uno scenario possibile sarebbe usare la registrazione di sessione per catturare ogni istruzione e poi integrarla con la registrazione sugli oggetti per ottenere maggiori dettagli su relazioni specifiche.

Configurazione

La registrazione dell'audit a livello di oggetto è implementata tramite il sistema dei ruoli. L'impostazione pgaudit.role definisce il ruolo che verrà utilizzato per la registrazione dell'audit. Una relazione (TABLE, VIEW, ecc.) verrà registrata per l'audit quando il ruolo di audit ha i permessi per il comando eseguito o eredita i permessi da un altro ruolo. Ciò consente di avere effettivamente più ruoli di audit anche se in qualsiasi contesto esiste un unico ruolo principale.

Imposta pgaudit.role su auditor e concedi i privilegi SELECT e DELETE sulla tabella account. Qualsiasi istruzione SELECT o DELETE sulla tabella account verrà ora registrata:

set pgaudit.role = 'auditor';

grant select, delete
   on public.account
   to auditor;

Esempio

In questo esempio la registrazione dell'audit sugli oggetti viene utilizzata per illustrare come si possa adottare un approccio granulare alla registrazione delle istruzioni SELECT e DML. Notare che la registrazione sulla tabella account è controllata dai permessi a livello di colonna, mentre la registrazione sulla tabella account_role_map è a livello di tabella.

SQL:

set pgaudit.role = 'auditor';

create table account
(
    id int,
    name text,
    password text,
    description text
);

grant select (password)
   on public.account
   to auditor;

select id, name
  from account;

select password
  from account;

grant update (name, password)
   on public.account
   to auditor;

update account
   set description = 'yada, yada';

update account
   set password = 'HASH2';

create table account_role_map
(
    account_id int,
    role_id int
);

grant select
   on public.account_role_map
   to auditor;

select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id

Output del Log:

AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,select password
  from account,<not logged>
AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,update account
   set password = 'HASH2',<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account_role_map,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>

Formato

Le voci di audit vengono scritte nel meccanismo di logging standard e contengono le seguenti colonne in formato separato da virgole. L'output è conforme al formato CSV solo se la parte del prefisso della riga di log di ogni voce viene rimossa.

• AUDIT_TYPE - SESSION o OBJECT.

• STATEMENT_ID - ID univoco dell'istruzione per questa sessione. Ogni ID di istruzione rappresenta una chiamata del backend. Gli ID delle istruzioni sono sequenziali anche se alcune istruzioni non vengono registrate. Possono esserci più voci per un ID di istruzione quando viene registrata più di una relazione.

• SUBSTATEMENT_ID - ID sequenziale per ogni sotto-istruzione all'interno dell'istruzione principale. Ad esempio, chiamare una funzione da una query. Gli ID delle sotto-istruzioni sono continui anche se alcune sotto-istruzioni non vengono registrate. Possono esserci più voci per un ID di sotto-istruzione quando viene registrata più di una relazione.

• CLASS - es. READ, ROLE (vedere pgaudit.log).

• COMMAND - es. ALTER TABLE, SELECT.

• OBJECT_TYPE - TABLE, INDEX, VIEW, ecc. Disponibile per istruzioni SELECT, DML e per la maggior parte delle istruzioni DDL.

• OBJECT_NAME - Il nome completo dell'oggetto (es. public.account). Disponibile per istruzioni SELECT, DML e per la maggior parte delle istruzioni DDL.

• STATEMENT - Istruzione eseguita sul backend.

• PARAMETER - Se pgaudit.log_parameter è impostato, questo campo conterrà i parametri dell'istruzione come CSV tra virgolette o <none> se non ci sono parametri. Altrimenti, il campo è <not logged>.

Usare log_line_prefix per aggiungere qualsiasi altro campo necessario a soddisfare i requisiti del proprio log di audit. Un tipico prefisso di riga di log potrebbe essere '%m %u %d [%p]: ', che fornirebbe data/ora, nome utente, nome del database e id del processo per ogni log di audit.

Avvertenze

La registrazione dell'audit è best-effort e non transazionale. pgAudit scrive le voci di audit tramite il meccanismo di logging standard di PostgreSQL, che non scarica ogni voce su disco in modo sincrono con la transazione che l'ha prodotta, né propaga gli errori di scrittura alla sessione. Non c'è garanzia che una transazione committata abbia una corrispondente voce di log di audit. Se il server si blocca o perde alimentazione, o la destinazione del log diventa non disponibile (ad esempio, il volume dei log si riempie) dopo che una transazione è stata committata ma prima che le sue voci di audit siano scritte in modo durevole, queste voci potrebbero andare perse. Al contrario, un'istruzione viene registrata quando viene eseguita, quindi una voce può essere scritta anche se la sua transazione viene successivamente annullata.

I rinomina di oggetti vengono registrati con il nome a cui sono stati rinominati. Ad esempio, rinominare una tabella produrrà il seguente risultato:

ALTER TABLE test RENAME TO test2;

AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE,public.test2,ALTER TABLE test RENAME TO test2,<not logged>

È possibile che un comando venga registrato più di una volta. Ad esempio, quando una tabella viene creata con una chiave primaria specificata al momento della creazione, l'indice per la chiave primaria verrà registrato indipendentemente e verrà creata un'altra voce di audit per l'indice nella voce di creazione. Le voci multiple saranno comunque contenute all'interno di un unico ID di istruzione.

Autovacuum e Autoanalyze non vengono registrati.

Le istruzioni eseguite dopo che una transazione entra in uno stato di abort non verranno registrate per l'audit. Tuttavia, l'istruzione che ha causato l'errore e qualsiasi istruzione successiva eseguita nella transazione annullata verranno registrate come ERROR dal meccanismo di logging standard.

Non è possibile eseguire un audit affidabile dei superuser con pgAudit. Una soluzione è limitare l'accesso agli account superuser e usare l'estensione set_user per elevare i permessi quando necessario.

Autori

L'estensione di audit PostgreSQL si basa sul progetto pgaudit di 2ndQuadrant, scritto da Simon Riggs, Abhijit Menon-Sen e Ian Barwick e presentato come estensione al core di PostgreSQL. Ulteriori sviluppi sono stati effettuati da David Steele di Crunchy Data.