Audit degli accessi e logging delle azioni degli utenti

Immagina che il tuo database sia tipo un club con regole di membership super rigide. Solo i membri possono entrare, ma vogliamo sapere chi è arrivato, quando, quanto tempo è rimasto e cosa ha fatto. Ecco a cosa serve l’audit in PostgreSQL. Con lui puoi:

1. Tracciare le azioni degli utenti. Per esempio, chi e a che ora ha eseguito una query importante.

2. Individuare attività sospette. Tipo qualcuno che cerca di leggere dati a cui non dovrebbe accedere.

3. Rispettare le leggi e gli standard. In molti settori (tipo quello finanziario o medico) è obbligatorio tenere un audit dettagliato delle azioni degli utenti.

4. Capire il sistema dall’interno. Il logging aiuta a capire quali query vengono eseguite più spesso, dove ci sono colli di bottiglia e come ottimizzare le performance.

Configurazione dei parametri di logging

PostgreSQL ti permette di configurare l’audit delle azioni tramite parametri di configurazione come log_statement e log_connections. Vediamoli meglio.

1. log_statement: cosa registriamo?

Il parametro log_statement decide quali query SQL verranno scritte nei log. È super utile per capire cosa succede nel sistema.

Valori possibili per log_statement:

• none — non registrare nulla (ottimo se ti piace vivere pericolosamente).
• ddl — registra solo i comandi che cambiano la struttura del database (tipo CREATE TABLE).
• mod — registra i comandi che modificano i dati (tipo INSERT, UPDATE, DELETE).
• all — registra tutto, senza pietà.

Esempio di configurazione: per cambiare questo parametro devi modificare il file postgresql.conf:

# Configuriamo il logging di tutte le query SQL
log_statement = 'all'

Dopo aver salvato le modifiche, riavvia il server PostgreSQL:

pg_ctl reload

Ora il tuo club registrerà ogni azione nel log, dal drink ordinato ai passi di danza.

2. log_connections: chi entra nel club?

Il parametro log_connections serve per scrivere nei log ogni nuova connessione al database. C’è anche il parametro correlato log_disconnections, che registra quando una connessione viene chiusa.

Esempio di configurazione: Anche qui, aggiorna il file postgresql.conf:

# Logghiamo le connessioni
log_connections = on

# Logghiamo le disconnessioni
log_disconnections = on

Cosa ci dà tutto questo? Per esempio, puoi vedere nei log che il tuo manager ha provato a connettersi per due ore con la password sbagliata. Sì, alle 3 di notte.

Analisi dei log: cosa puoi trovare?

Dopo aver configurato log_statement e log_connections, PostgreSQL inizierà a scrivere i log. Ecco un esempio di come può apparire un file di log con log_statement = 'mod' attivo:

2023-11-01 12:45:01 UTC [12345] LOG:  connection authorized: user=admin database=universita
2023-11-01 12:46:15 UTC [12345] STATEMENT:  INSERT INTO studenti (nome, eta) VALUES ('Alice', 22);
2023-11-01 12:47:30 UTC [12345] STATEMENT:  UPDATE studenti SET eta = 23 WHERE nome = 'Alice';
2023-11-01 12:48:45 UTC [12345] LOG:  disconnection: session time: 2:45 connection: 1/5

Cose interessanti:

1. Chi si è connesso: user=admin database=universita — è il nostro capo.
2. Cosa ha fatto: ha inserito una riga con il nome Alice e aggiornato la sua età.
3. Quando se n’è andato: 12:48:45, dopo due minuti e mezzo di attività.

Esempi pratici: come usare l’audit nella vita reale

Vediamo qualche scenario dove audit e logging possono essere davvero utili.

Scenario 1: tracciare le modifiche ai dati

Sospetti che qualcuno stia cambiando i record nella tabella studenti. Ecco come puoi configurare l’audit:

1. Imposta log_statement = 'mod' per registrare tutte le query che modificano i dati.
2. Analizza i log:

cat /var/log/postgresql/postgresql.log | grep "UPDATE studenti"

Così puoi vedere chi, quando e quali modifiche ha fatto.

Scenario 2: individuare connessioni sospette

Se nei log vedi connessioni frequenti da diversi indirizzi IP, potrebbe esserci qualcosa che non va. Per analizzare:

1. I log delle connessioni (log_connections).
2. Filtra per indirizzo IP:

cat /var/log/postgresql/postgresql.log | grep "connection authorized"

Scenario 3: analisi delle performance delle query

Vuoi capire perché il tuo server è lento? Un modo semplice è attivare log_statement = 'all' per un’ora, raccogliere i log e vedere dove il server passa più tempo.

Best practice

Non loggare tutto a caso. Configura log_statement per registrare solo le azioni importanti — DDL e modifiche ai dati.

Automatizza l’analisi dei log. Usa script o tool per leggere e analizzare i log regolarmente, tipo grep, awk o anche ELK Stack (Elasticsearch, Logstash, Kibana).

Gestisci la dimensione dei log. Configura la rotazione dei log tramite i parametri di PostgreSQL o strumenti del sistema operativo (tipo logrotate su Linux).

Spero che ora ti senta un vero sceriffo del tuo database. Logging e audit non sono solo protezione, ma anche un modo top per capire meglio cosa succede nel tuo sistema. Vedremo come queste conoscenze si usano nei progetti veri!