Tracce di audit a prova di manomissione in PostgreSQL con catena di hash

Perché i normali audit log sono facili da contestare

Una traccia di audit è il registro a cui ti appoggi quando qualcosa sembra sbagliato: un rimborso strano, una modifica dei permessi che nessuno ricorda o un record cliente che “è sparito”. Se la traccia di audit può essere modificata, smette di essere prova e diventa un altro dato che qualcuno può riscrivere.

Molti “audit log” sono semplicemente tabelle normali. Se le righe possono essere aggiornate o cancellate, anche la storia può essere aggiornata o cancellata.

Una distinzione importante: impedire le modifiche non è la stessa cosa che renderle rilevabili. Puoi ridurre le modifiche con i permessi, ma chi ha accesso sufficiente (o una credenziale admin rubata) può comunque alterare la storia. La prova di manomissione accetta questa realtà. Potresti non prevenire ogni modifica, ma puoi fare in modo che le modifiche lascino un'impronta evidente.

I log normali vengono contestati per motivi prevedibili. Utenti privilegiati possono “aggiustare” il log a posteriori. Un account dell’app compromesso può scrivere voci credibili che sembrano traffico normale. I timestamp possono essere riempiti a ritroso per nascondere una modifica tardiva. Oppure qualcuno cancella solo le righe più dannose.

"A prova di manomissione" significa progettare la traccia di audit in modo che anche una piccola modifica (cambiare un campo, rimuovere una riga, riordinare eventi) diventi rilevabile in seguito. Non stai promettendo magia. Stai promettendo che quando qualcuno chiede: “Come facciamo a sapere che questo log è reale?”, puoi eseguire controlli che mostrano se il log è stato toccato.

Decidi cosa hai bisogno di provare

Una traccia di audit a prova di manomissione è utile solo se risponde alle domande che affronterai dopo: chi ha fatto cosa, quando l'ha fatto e cosa è cambiato.

Inizia dagli eventi che contano per il tuo business. Le modifiche ai dati (create, update, delete) sono la base, ma le indagini spesso dipendono anche da sicurezza e accessi: login, reset di password, cambi di permessi e blocchi account. Se gestisci pagamenti, rimborsi, crediti o payout, tratta i movimenti di denaro come eventi di prima classe, non come effetto collaterale di una riga aggiornata.

Poi decidi cosa rende credibile un evento. Gli auditor si aspettano solitamente un attore (utente o servizio), un timestamp lato server, l'azione effettuata e l'oggetto interessato. Per gli update, conserva i valori prima e dopo (o almeno i campi sensibili), più un request id o correlation id così puoi legare molte piccole modifiche di DB a una singola azione utente.

Infine, sii esplicito su cosa significa “immutabile” nel tuo sistema. La regola più semplice è: mai aggiornare o cancellare righe di audit, solo inserire. Se qualcosa è sbagliato, scrivi un nuovo evento che corregge o sovrascrive il vecchio e conserva l'originale visibile.

Costruisci una tabella di audit append-only

Tieni i dati di audit separati dalle tue tabelle normali. Uno schema dedicato audit riduce modifiche accidentali e rende i permessi più semplici da gestire.

L'obiettivo è semplice: le righe possono essere aggiunte, ma mai modificate o rimosse. In PostgreSQL lo fai rispettando i privilegi (chi può fare cosa) e applicando qualche precauzione nel design della tabella.

Ecco una tabella di partenza pratica:

CREATE SCHEMA IF NOT EXISTS audit;

CREATE TABLE audit.events (
  id            bigserial PRIMARY KEY,
  entity_type   text        NOT NULL,
  entity_id     text        NOT NULL,
  event_type    text        NOT NULL CHECK (event_type IN ('INSERT','UPDATE','DELETE')),
  actor_id      text,
  occurred_at   timestamptz NOT NULL DEFAULT now(),
  request_id    text,
  before_data   jsonb,
  after_data    jsonb,
  notes         text
);

Alcuni campi sono particolarmente utili durante le indagini:

• occurred_at con DEFAULT now() così il tempo è registrato dal database, non dal client.
• entity_type e entity_id per poter seguire un record attraverso le modifiche.
• request_id per tracciare una singola azione utente che genera più righe.

Blocca gli accessi con i ruoli. Il ruolo dell'app dovrebbe poter fare INSERT e SELECT su audit.events, ma non UPDATE o DELETE. Mantieni le modifiche di schema e permessi più forti per un ruolo admin che non sia usato dall'app.

Cattura le modifiche con trigger (puliti e prevedibili)

Se vuoi una traccia di audit a prova di manomissione, il posto più affidabile per catturare i cambiamenti è il database. I log applicativi possono essere saltati, filtrati o riscritti. Un trigger scatta indipendentemente dall'app, dallo script o dallo strumento admin che tocca la tabella.

Mantieni i trigger noiosi. Il loro compito deve essere uno solo: inserire un evento di audit per ogni INSERT, UPDATE e DELETE sulle tabelle importanti.

Un record di audit pratico include di solito il nome della tabella, il tipo di operazione, la chiave primaria, i valori prima e dopo, un timestamp e identificatori che permettono di raggruppare cambi correlati (tx id e correlation id).

I correlation id sono la differenza tra “20 righe aggiornate” e “Questo è stato un click su un bottone”. La tua app può impostare un correlation id per ogni richiesta (ad esempio in una setting di sessione DB) e il trigger può leggerlo. Conserva anche txid_current() così puoi raggruppare le modifiche anche quando manca il correlation id.

Ecco un pattern di trigger semplice che rimane prevedibile perché fa solo insert nella tabella di audit (adatta i nomi allo schema):

CREATE OR REPLACE FUNCTION audit_row_change() RETURNS trigger AS $$
DECLARE
  corr_id text;
BEGIN
  corr_id := current_setting('app.correlation_id', true);

  INSERT INTO audit_events(
    occurred_at, table_name, op, row_pk,
    old_row, new_row, db_user, txid, correlation_id
  ) VALUES (
    now(), TG_TABLE_NAME, TG_OP, COALESCE(NEW.id, OLD.id),
    to_jsonb(OLD), to_jsonb(NEW), current_user, txid_current(), corr_id
  );

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

Resisti alla tentazione di fare di più all'interno dei trigger. Evita query extra, chiamate di rete o ramificazioni complesse. Trigger piccoli sono più facili da testare, più veloci da eseguire e più difficili da contestare durante una review.

Aggiungi hash chaining così le modifiche lasciano impronte

Una tabella append-only aiuta, ma qualcuno con accesso sufficiente può comunque riscrivere righe passate. L'hash chaining rende quel tipo di manomissione visibile.

Aggiungi due colonne a ogni riga di audit: prev_hash e row_hash (a volte chiamato chain_hash). prev_hash memorizza l'hash della riga precedente nella stessa catena. row_hash memorizza l'hash della riga corrente, calcolato dai dati della riga più prev_hash.

Cosa si hash è importante. Vuoi un input stabile e ripetibile così la stessa riga produce sempre lo stesso hash.

Un approccio pratico è hashare una stringa canonica costruita da colonne fisse (timestamp, actor, azione, id dell'entità), un payload canonico (spesso jsonb, perché le chiavi sono memorizzate in modo consistente) e il prev_hash.

Fai attenzione ai dettagli che possono cambiare senza significato, come gli spazi, l'ordine delle chiavi JSON in testo plain o formattazioni dipendenti dalla località. Mantieni i tipi coerenti e serializza in un modo prevedibile.

Catena per stream, non per l'intero database

Se concatenassi ogni evento di audit in una singola sequenza globale, le scritture possono diventare un collo di bottiglia. Molti sistemi concatenano all'interno di uno “stream”, per esempio per tenant, per tipo di entità o per oggetto di business.

Ogni nuova riga cerca l'ultimo row_hash del suo stream, lo memorizza come prev_hash, poi calcola il proprio row_hash.

-- Requires pgcrypto
-- digest() returns bytea; store hashes as bytea
row_hash = digest(
  concat_ws('|',
    stream_key,
    occurred_at::text,
    actor_id::text,
    action,
    entity,
    entity_id::text,
    payload::jsonb::text,
    encode(prev_hash, 'hex')
  ),
  'sha256'
);

Snapshot della testa della catena

Per revisioni più veloci, memorizza periodicamente l'ultimo row_hash (la “testa della catena”), ad esempio quotidianamente per stream, in una piccola tabella snapshot. Durante un'investigazione puoi verificare la catena fino a ciascuno snapshot invece di scansionare l'intera cronologia in una volta sola. Gli snapshot rendono anche più semplice confrontare export e individuare gap sospetti.

Concorrenza e ordinamento senza rompere la catena

L'hash chaining diventa complicato sotto carico reale. Se due transazioni scrivono righe di audit contemporaneamente e entrambe usano lo stesso prev_hash, puoi ritrovarti con dei fork. Questo indebolisce la tua capacità di dimostrare una singola sequenza lineare.

Decidi prima cosa rappresenta la tua catena. Una catena globale è la più semplice da spiegare ma ha la massima contendibilità. Più catene riducono la contendibilità, ma devi essere chiaro su cosa prova ciascuna catena.

Qualunque modello tu scelga, definisci un ordine rigoroso con un id evento monotono (di solito un id supportato da sequence). I timestamp non sono sufficienti perché possono collidere e possono essere manipolati.

Per evitare condizioni di gara quando si calcola prev_hash, serializza l'operazione “leggi ultimo hash + inserisci riga successiva” per ogni stream. Approcci comuni sono bloccare una singola riga che rappresenta la testa dello stream, o usare un advisory lock indicizzato dallo stream id. L'obiettivo è che due scrittori sullo stesso stream non possano leggere entrambi lo stesso hash precedente.

Partizionamento e sharding influenzano dove si trova “l'ultima riga”. Se prevedi di partizionare i dati di audit, mantieni ogni catena completamente contenuta in una partizione usando la stessa chiave di partizionamento dello stream (per esempio tenant id). In questo modo, le catene dei tenant restano verificabili anche se i tenant vengono spostati tra server.

Come verificare la catena durante un'investigazione

L'hash chaining aiuta solo se puoi dimostrare che la catena tiene quando qualcuno lo chiede. L'approccio più sicuro è una query di verifica in sola lettura (o un job) che ricalcola l'hash di ogni riga dai dati memorizzati e lo confronta con quello registrato.

Un verificatore semplice da eseguire on demand

Un verificatore dovrebbe: ricostruire l'hash atteso per ogni riga, confermare che ogni riga si collega alla precedente e segnalare qualsiasi anomalia.

Ecco uno schema comune che usa funzioni di finestra. Adatta i nomi delle colonne alla tua tabella.

WITH ordered AS (
  SELECT
    id,
    created_at,
    actor_id,
    action,
    entity,
    entity_id,
    payload,
    prev_hash,
    row_hash,
    LAG(row_hash) OVER (ORDER BY created_at, id) AS expected_prev_hash,
    /* expected row hash, computed the same way as in your insert trigger */
    encode(
      digest(
        coalesce(prev_hash, '') || '|' ||
        id::text || '|' ||
        created_at::text || '|' ||
        coalesce(actor_id::text, '') || '|' ||
        action || '|' ||
        entity || '|' ||
        entity_id::text || '|' ||
        payload::text,
        'sha256'
      ),
      'hex'
    ) AS expected_row_hash
  FROM audit_log
)
SELECT
  id,
  created_at,
  CASE
    WHEN prev_hash IS DISTINCT FROM expected_prev_hash THEN 'BROKEN_LINK'
    WHEN row_hash IS DISTINCT FROM expected_row_hash THEN 'HASH_MISMATCH'
    ELSE 'OK'
  END AS status
FROM ordered
WHERE prev_hash IS DISTINCT FROM expected_prev_hash
   OR row_hash IS DISTINCT FROM expected_row_hash
ORDER BY created_at, id;

Oltre al semplice “rotto o meno”, vale la pena controllare gap (id mancanti in un intervallo), link fuori ordine e duplicati sospetti che non corrispondono ai flussi di lavoro reali.

Registra i risultati della verifica come eventi immutabili

Non eseguire una query e nascondere l'output in un ticket. Memorizza i risultati delle verifiche in una tabella append-only separata (per esempio audit_verification_runs) con tempo di esecuzione, versione del verificatore, chi l'ha avviato, l'intervallo controllato e i conteggi di link rotti e mismatch di hash.

Questo ti dà una seconda traccia: non solo il log di audit è intatto, ma puoi dimostrare che lo stai controllando.

Una cadenza pratica: esegui dopo ogni deploy che tocca la logica di audit, ogni notte per sistemi attivi e sempre prima di un audit pianificato.

Errori comuni che compromettono la prova di manomissione

La maggior parte dei fallimenti non riguarda l'algoritmo di hash. Riguardano eccezioni e gap che danno alle persone spazio per discutere.

Il modo più rapido per perdere fiducia è permettere aggiornamenti alle righe di audit. Anche se è “solo questa volta”, hai creato sia un precedente sia una via funzionante per riscrivere la storia. Se devi correggere qualcosa, aggiungi un nuovo evento di audit che spieghi la correzione e conserva l'originale.

L'hash chaining fallisce anche quando hashate dati instabili. JSON è una trappola comune. Se hashate una stringa JSON, differenze innocue (ordine delle chiavi, spazi, formattazione numerica) possono cambiare l'hash e rendere la verifica rumorosa. Preferisci una forma canonica: campi normalizzati, jsonb o un'altra serializzazione consistente.

Altri pattern che minano una traccia difendibile:

• Hashare solo il payload e saltare il contesto (timestamp, actor, id dell'oggetto, azione).
• Catturare le modifiche solo nell'applicazione assumendo che il database corrisponda per sempre.
• Usare un unico ruolo di database che può scrivere dati di business e anche alterare la storia di audit.
• Permettere NULL per prev_hash dentro una catena senza una regola chiara e documentata.

La separazione dei compiti conta. Se lo stesso ruolo può inserire eventi di audit e anche modificarli, la prova di manomissione diventa una promessa invece che un controllo.

Checklist rapida per una traccia di audit difendibile

Una traccia di audit difendibile dovrebbe essere difficile da cambiare e facile da verificare.

Inizia dal controllo degli accessi: la tabella di audit deve essere append-only nella pratica. Il ruolo dell'app dovrebbe inserire (e solitamente leggere), ma non aggiornare o cancellare. Le modifiche di schema devono essere strettamente limitate.

Assicurati che ogni riga risponda alle domande che un investigatore farà: chi l'ha fatta, quando è successo (server-side), cosa è successo (nome evento chiaro più operazione), cosa ha toccato (nome entità e id) e come si connette (request/correlation id e transaction id).

Poi valida lo strato di integrità. Un test rapido è riprodurre un segmento e confermare che ogni prev_hash corrisponde all'hash della riga precedente e che ogni hash memorizzato corrisponde a quello ricalcolato.

Operativamente, tratta la verifica come un job normale:

• Esegui controlli di integrità pianificati e memorizza risultati pass/fail e intervalli.
• Allerta su mismatch, gap e link rotti.
• Conserva i backup abbastanza a lungo da coprire la finestra di retention, e limita la retention così la storia di audit non possa essere "pulita" prima del tempo.

Esempio: individuare una modifica sospetta in una revisione di compliance

Un caso di test comune è una disputa su un rimborso. Un cliente afferma di aver ricevuto un rimborso di $250, ma il sistema ora mostra $25. Il supporto insiste che l'approvazione era corretta e la compliance vuole una risposta.

Inizia restringendo la ricerca usando un correlation id (order id, ticket id o refund_request_id) e una finestra temporale. Estrai le righe di audit per quel correlation id e circoscrivile intorno al tempo di approvazione.

Cerchi l'insieme completo di eventi: richiesta creata, rimborso approvato, importo del rimborso impostato e eventuali aggiornamenti successivi. Con un design a prova di manomissione, controlli anche se la sequenza è rimasta intatta.

Un semplice flusso di indagine:

• Estrai tutte le righe di audit per il correlation id in ordine temporale.
• Ricalcola l'hash di ogni riga dai campi memorizzati (incluso prev_hash).
• Confronta gli hash calcolati con quelli memorizzati.
• Identifica la prima riga che differisce e verifica se anche le righe successive falliscono.

Se qualcuno ha modificato una singola riga di audit (per esempio cambiando l'importo da 250 a 25), l'hash di quella riga non corrisponderà più. Poiché la riga successiva include l'hash precedente, il mismatch di solito si propaga in avanti. Quella propagazione è l'impronta: mostra che il record di audit è stato alterato dopo il fatto.

Cosa può dirti la catena: è avvenuta una modifica, dove la catena si è prima rotta e l'ambito delle righe interessate. Cosa non può dirti da sola: chi ha fatto la modifica, quale fosse il valore originale se è stato sovrascritto, o se anche altre tabelle sono state cambiate.

Passi successivi: distribuisci con prudenza e mantieni il sistema

Tratta la tua traccia di audit come qualsiasi altro controllo di sicurezza. Distribuiscila a piccoli passi, dimostra che funziona, poi espandi.

Inizia con le azioni che ti danneggerebbero di più se contestate: cambi di permessi, payout, rimborsi, esportazioni di dati e override manuali. Una volta coperti questi, aggiungi eventi a rischio minore senza cambiare il design core.

Documenta il contratto per i tuoi eventi di audit: quali campi vengono registrati, cosa significa ogni tipo di evento, come viene calcolato l'hash e come eseguire la verifica. Conserva quella documentazione accanto alle migrazioni del database e mantieni la procedura di verifica ripetibile.

I drill di restore sono importanti perché le indagini spesso partono dai backup, non dal sistema live. Ripristina regolarmente su un database di test e verifica la catena end-to-end. Se non riesci a riprodurre lo stesso risultato di verifica dopo un restore, la tua prova di manomissione sarà difficile da difendere.

Se costruisci strumenti interni e workflow admin con AppMaster (appmaster.io), standardizzare le scritture degli eventi di audit tramite processi server-side coerenti aiuta a mantenere uniforme lo schema degli eventi e i correlation id tra le funzionalità, semplificando molto verifica e indagini.

Programma tempo di manutenzione per questo sistema. Le tracce di audit falliscono silenziosamente quando i team consegnano nuove funzionalità ma dimenticano di aggiungere eventi, aggiornare gli input dell'hash o mantenere i job di verifica e i drill di restore in funzione.