Pistes d'audit inviolables dans PostgreSQL avec chainage par hash

Pourquoi les logs d'audit classiques sont faciles à contester

Une piste d'audit est l'enregistrement sur lequel vous vous appuyez quand quelque chose semble étrange : un remboursement inhabituel, un changement de permission que personne ne se rappelle, ou un enregistrement client qui a « disparu ». Si la piste d'audit peut être modifiée, elle cesse d'être une preuve et devient une donnée que quelqu'un peut réécrire.

Beaucoup de « logs d'audit » sont juste des tables ordinaires. Si les lignes peuvent être mises à jour ou supprimées, l'histoire peut aussi être modifiée ou effacée.

Une distinction clé : empêcher les modifications n'est pas la même chose que rendre les modifications détectables. Vous pouvez réduire les changements avec des permissions, mais quiconque a suffisamment d'accès (ou un identifiant admin volé) peut toujours altérer l'historique. La preuve d'altération accepte cette réalité. Vous ne prévenez peut‑être pas chaque changement, mais vous faites en sorte que toute modification laisse une empreinte évidente.

Les logs normaux sont contestés pour des raisons prévisibles. Les utilisateurs privilégiés peuvent « corriger » le log après coup. Un compte applicatif compromis peut écrire des entrées crédibles qui ressemblent à du trafic normal. Les horodatages peuvent être rétrodatés pour masquer une modification tardive. Ou quelqu'un supprime seulement les lignes les plus compromettantes.

« Tamper-evident » (preuve d'altération) signifie que vous concevez la piste d'audit de sorte que même une petite modification (changer un champ, supprimer une ligne, réordonner des événements) devienne détectable plus tard. Ce n'est pas de la magie ; c'est la promesse que, quand on vous demandera « Comment savons-nous que ce log est réel ? », vous pourrez exécuter des contrôles montrant si le log a été touché.

Décidez ce que vous devez prouver

Une piste d'audit inviolable n'est utile que si elle répond aux questions que vous aurez plus tard : qui a fait quoi, quand, et qu'est‑ce qui a changé.

Commencez par les événements qui comptent pour votre activité. Les changements de données (création, mise à jour, suppression) sont la base, mais les enquêtes portent souvent aussi sur la sécurité et les accès : connexions, réinitialisations de mot de passe, changements de permissions et verrouillages de comptes. Si vous gérez des paiements, remboursements, crédits ou paiements, considérez les mouvements d'argent comme des événements de première classe, pas comme un simple effet secondaire d'une ligne mise à jour.

Ensuite, décidez ce qui rend un événement crédible. Les auditeurs attendent généralement un acteur (utilisateur ou service), un horodatage côté serveur, l'action effectuée et l'objet affecté. Pour les mises à jour, stockez les valeurs avant et après (ou au moins les champs sensibles), ainsi qu'un request id ou correlation id pour relier plusieurs petits changements de base de données à une seule action utilisateur.

Enfin, soyez explicite sur ce que « immuable » signifie dans votre système. La règle la plus simple : ne jamais mettre à jour ni supprimer les lignes d'audit, seulement insérer. Si quelque chose est faux, écrivez un nouvel événement qui corrige ou remplace l'ancien, et conservez l'original visible.

Construire une table d'audit append-only

Gardez les données d'audit séparées de vos tables normales. Un schéma dédié audit réduit les modifications accidentelles et facilite la gestion des permissions.

L'objectif est simple : on peut ajouter des lignes, mais jamais les modifier ni les supprimer. Dans PostgreSQL, vous l'imposez par les privilèges (qui peut faire quoi) et quelques garde‑fous dans la conception de la table.

Voici une table de départ pratique :

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
);

Quelques champs sont particulièrement utiles lors des enquêtes :

• occurred_at avec DEFAULT now() pour que le temps soit horodaté par la base, pas par le client.
• entity_type et entity_id pour suivre un même enregistrement au fil des changements.
• request_id pour relier une action utilisateur à plusieurs lignes.

Verrouillez l'accès avec des rôles. Le rôle applicatif doit pouvoir INSERT et SELECT sur audit.events, mais pas UPDATE ni DELETE. Réservez les changements de schéma et les permissions plus fortes à un rôle admin qui n'est pas utilisé par l'application.

Capturer les changements avec des triggers (propres et prévisibles)

Si vous voulez une piste d'audit preuve d'altération, l'endroit le plus fiable pour capturer les modifications est la base de données. Les logs applicatifs peuvent être sautés, filtrés ou réécrits. Un trigger se déclenche quelle que soit l'app, le script ou l'outil admin qui touche la table.

Gardez les triggers simples. Leur travail doit être unique : ajouter un événement d'audit pour chaque INSERT, UPDATE et DELETE sur les tables importantes.

Un enregistrement d'audit pratique inclut généralement le nom de la table, le type d'opération, la clé primaire, les valeurs avant et après, un horodatage et des identifiants qui permettent de grouper les changements liés (id de transaction et correlation id).

Les ids de corrélation font la différence entre « 20 lignes mises à jour » et « ceci était un seul clic de bouton ». Votre application peut définir un correlation id une fois par requête (par exemple, dans un paramètre de session DB), et le trigger peut le lire. Stockez aussi txid_current() pour pouvoir grouper les changements quand le correlation id est absent.

Voici un modèle de trigger simple et prévisible qui n'insère que dans la table d'audit (ajustez les noms à votre schéma) :

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;

Résistez à l'envie d'en faire trop dans les triggers. Évitez les requêtes supplémentaires, appels réseau ou branches complexes. Les triggers simples sont plus faciles à tester, plus rapides et plus difficiles à contester lors d'une revue.

Ajouter le chainage par hash pour que les modifications laissent des empreintes

Une table append-only aide, mais quelqu'un avec assez d'accès peut toujours réécrire des lignes passées. Le chainage par hash rend ce type de falsification visible.

Ajoutez deux colonnes à chaque ligne d'audit : prev_hash et row_hash (parfois chain_hash). prev_hash stocke le hash de la ligne précédente dans la même chaîne. row_hash stocke le hash de la ligne courante, calculé à partir des données de la ligne plus prev_hash.

Ce que vous hashez a de l'importance. Vous voulez une entrée stable et reproductible pour que la même ligne produise toujours le même hash.

Une approche pratique est de hasher une chaîne canonique construite à partir de colonnes fixes (horodatage, acteur, action, id d'entité), d'une charge utile canonique (souvent jsonb, car les clés y sont stockées de façon cohérente) et de prev_hash.

Faites attention aux détails qui peuvent changer sans signification, comme les espaces, l'ordre des clés JSON en texte brut ou le formatage spécifique à une locale. Gardez les types cohérents et sérialisez d'une manière prédictible.

Chainer par flux, pas par base globale

Si vous enchaînez chaque événement d'audit dans une seule séquence globale, les écritures peuvent devenir un goulot d'étranglement. Beaucoup de systèmes enchaînent au sein d'un « flux », par exemple par tenant, par type d'entité ou par objet métier.

Chaque nouvelle ligne récupère le dernier row_hash pour son flux, le stocke en prev_hash, puis calcule son propre 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 de la tête de chaîne

Pour des revues plus rapides, stockez périodiquement le dernier row_hash (la « tête de chaîne »), par exemple quotidiennement par flux, dans une petite table de snapshots. Lors d'une enquête, vous pouvez vérifier la chaîne jusqu'à chaque snapshot au lieu de balayer tout l'historique. Les snapshots facilitent aussi la comparaison d'exportations et la détection de lacunes suspectes.

Concurrence et ordonnancement sans casser la chaîne

Le chainage par hash devient délicat sous trafic réel. Si deux transactions écrivent des lignes d'audit en même temps et utilisent toutes deux le même prev_hash, vous pouvez obtenir des fourches. Cela affaiblit votre capacité à prouver une séquence unique et linéaire.

Décidez d'abord ce que représente votre chaîne. Une chaîne globale est la plus simple à expliquer mais a le plus de contention. Plusieurs chaînes réduisent la contention, mais vous devez être clair sur ce que prouve chaque chaîne.

Quel que soit le modèle choisi, définissez un ordre strict avec un id d'événement monotone (généralement un id alimenté par une séquence). Les horodatages ne suffisent pas car ils peuvent se chevaucher et être manipulés.

Pour éviter les conditions de course lors du calcul de prev_hash, sérialisez l'opération « récupérer le dernier hash + insérer la ligne suivante » pour chaque flux. Les approches courantes consistent à verrouiller une seule ligne représentant la tête du flux, ou utiliser un advisory lock indexé par l'id du flux. L'objectif est que deux écrivains sur le même flux ne puissent pas lire le même dernier hash.

Le partitionnement et le sharding influent sur l'endroit où se trouve « la dernière ligne ». Si vous prévoyez de partitionner les données d'audit, gardez chaque chaîne entièrement contenue dans une partition en utilisant la même clé de partition que la clé de flux (par exemple, tenant id). Ainsi, les chaînes par tenant restent vérifiables même si des tenants sont déplacés entre serveurs.

Comment vérifier la chaîne lors d'une enquête

Le chainage par hash n'aide que si vous pouvez prouver que la chaîne tient toujours quand quelqu'un le demande. L'approche la plus sûre est une requête de vérification en lecture seule (ou un job) qui recompute le hash de chaque ligne à partir des données stockées et le compare à ce qui est enregistré.

Un vérificateur simple à exécuter à la demande

Un vérificateur doit : reconstruire le hash attendu pour chaque ligne, confirmer que chaque ligne pointe vers la précédente et signaler tout écart.

Voici un schéma courant utilisant des fonctions fenêtrées. Ajustez les noms de colonnes pour correspondre à votre table.

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;

Au‑delà de « cassé ou non », il vaut la peine de vérifier les trous (ids manquants dans une plage), les liens hors ordre et les doublons suspects qui ne correspondent pas aux workflows réels.

Enregistrer les résultats de vérification comme événements immuables

Ne lancez pas une requête et enterrez le résultat dans un ticket. Stockez les résultats de vérification dans une table append-only séparée (par exemple audit_verification_runs) avec l'heure d'exécution, la version du vérificateur, qui l'a déclenché, la plage vérifiée et des comptes pour les liens cassés et les mismatches de hash.

Cela vous donne une seconde piste : non seulement le journal d'audit est intact, mais vous pouvez montrer que vous l'avez vérifié.

Un rythme pratique : exécuter après tout déploiement touchant la logique d'audit, quotidiennement pour les systèmes actifs, et toujours avant un audit planifié.

Erreurs courantes qui brisent la preuve d'altération

La plupart des échecs ne tiennent pas à l'algorithme de hash. Ils viennent d'exceptions et de trous qui donnent aux gens des arguments.

La façon la plus rapide de perdre la confiance est d'autoriser les mises à jour des lignes d'audit. Même si c'est « juste cette fois », vous créez un précédent et une voie fonctionnelle pour réécrire l'histoire. Si vous devez corriger quelque chose, ajoutez un nouvel événement d'audit qui explique la correction et conservez l'original.

Le chainage par hash échoue aussi quand vous hashez des données instables. Le JSON est un piège courant. Si vous hashez une chaîne JSON, des différences innocentes (ordre des clés, espaces, formatage des nombres) peuvent changer le hash et rendre la vérification bruyante. Préférez une forme canonique : champs normalisés, jsonb ou une autre sérialisation cohérente.

D'autres pratiques qui affaiblissent une piste défendable :

• Hasher seulement la payload en sautant le contexte (horodatage, acteur, id d'objet, action).
• Capturer les changements uniquement dans l'application en supposant que la base correspondra toujours.
• Utiliser un seul rôle DB qui peut écrire les données métier et aussi altérer l'historique d'audit.
• Autoriser des NULLs pour prev_hash dans une chaîne sans règle claire et documentée.

La séparation des responsabilités compte. Si le même rôle peut insérer des événements d'audit et aussi les modifier, la preuve d'altération devient une promesse plutôt qu'un contrôle.

Checklist rapide pour une piste d'audit défendable

Une piste d'audit défendable doit être difficile à changer et facile à vérifier.

Commencez par le contrôle d'accès : la table d'audit doit être append-only en pratique. Le rôle applicatif doit insérer (et généralement lire), mais pas mettre à jour ni supprimer. Les changements de schéma doivent être strictement restreints.

Assurez-vous que chaque ligne répond aux questions d'une enquêteur : qui a fait, quand (côté serveur), ce qui s'est passé (nom d'événement clair + opération), ce que ça a touché (nom d'entité et id), et comment c'est relié (request/correlation id et id de transaction).

Puis validez la couche d'intégrité. Un test rapide consiste à rejouer un segment et confirmer que chaque prev_hash correspond au hash de la ligne précédente, et que chaque hash stocké correspond à celui recomputé.

Sur le plan opérationnel, traitez la vérification comme un job normal :

• Lancez des contrôles d'intégrité planifiés et stockez les résultats pass/échec et les plages.
• Alertez sur les mismatches, les trous et les liens cassés.
• Gardez des backups suffisamment longtemps pour couvrir votre fenêtre de rétention, et verrouillez la rétention pour que l'historique d'audit ne puisse pas être « nettoyé » prématurément.

Exemple : repérer une modification suspecte lors d'une revue de conformité

Un cas courant est un litige sur un remboursement. Un client affirme qu'on lui a approuvé un remboursement de 250 $, mais le système affiche maintenant 25 $. Le support insiste que l'approbation était correcte et la conformité veut une réponse.

Commencez par restreindre la recherche avec un correlation id (order id, ticket id, ou refund_request_id) et une fenêtre temporelle. Récupérez les lignes d'audit pour ce correlation id et placez‑les autour du moment de l'approbation.

Vous cherchez l'ensemble complet des événements : demande créée, remboursement approuvé, montant du remboursement défini, et toute mise à jour ultérieure. Avec une conception preuve d'altération, vous vérifiez aussi si la séquence est restée intacte.

Un flux d'enquête simple :

• Récupérer toutes les lignes d'audit pour le correlation id en ordre temporel.
• Recalculer le hash de chaque ligne à partir de ses champs stockés (incluant prev_hash).
• Comparer les hashes calculés aux hashes stockés.
• Identifier la première ligne qui diffère et vérifier si les lignes suivantes échouent aussi.

Si quelqu'un a modifié une seule ligne d'audit (par exemple en changeant le montant de 250 à 25), le hash de cette ligne ne correspondra plus. Comme la ligne suivante inclut le hash précédent, la discordance se propage en général vers l'avant. Cette propagation est l'empreinte : elle montre que l'enregistrement d'audit a été altéré après coup.

Ce que la chaîne peut vous dire : une modification a eu lieu, où la chaîne s'est d'abord cassée et l'étendue des lignes affectées. Ce qu'elle ne peut pas dire seule : qui a fait la modification, quelle était la valeur originale si elle a été écrasée, ou si d'autres tables ont aussi été modifiées.

Prochaines étapes : déployer en sécurité et maintenir

Traitez votre piste d'audit comme tout autre contrôle de sécurité. Déployez par petites étapes, prouvez que ça marche, puis étendez.

Commencez par les actions qui vous porteraient le plus préjudice en cas de contestation : changements de permissions, paiements, remboursements, exportations de données et interventions manuelles. Une fois ces cas couverts, ajoutez des événements à plus faible risque sans changer le design central.

Documentez le contrat de vos événements d'audit : quels champs sont enregistrés, ce que signifie chaque type d'événement, comment le hash est calculé et comment lancer la vérification. Gardez cette documentation proche de vos migrations de base et rendez la procédure de vérification reproductible.

Les exercices de restauration sont importants car les enquêtes démarrent souvent depuis des sauvegardes, pas depuis le système en direct. Restaurez régulièrement sur une base de test et vérifiez la chaîne de bout en bout. Si vous ne pouvez pas reproduire le même résultat de vérification après une restauration, votre preuve d'altération sera difficile à défendre.

Si vous construisez des outils internes et des workflows admin avec AppMaster (appmaster.io), standardiser l'écriture des événements d'audit via des processus serveur cohérents aide à maintenir le schéma d'événement et les correlation ids uniformes entre les fonctionnalités, ce qui simplifie beaucoup la vérification et les enquêtes.

Planifiez du temps de maintenance pour ce système. Les pistes d'audit échouent souvent silencieusement quand les équipes livrent de nouvelles fonctionnalités mais oublient d'ajouter des événements, de mettre à jour les entrées du hash ou de maintenir les jobs de vérification et les exercices de restauration.