Journal d'audit PostgreSQL en ajout seul, par la base

Un journal d’audit PostgreSQL digne de ce nom est une table que l’application peut lire et compléter, mais jamais modifier ni vider : le rôle applicatif n’y reçoit que SELECT et INSERT, et c’est la base qui refuse le reste. Chaque ligne s’écrit dans la même transaction que l’action qu’elle décrit, pour que l’une n’existe jamais sans l’autre. Restent deux questions pratiques, auxquelles cet article répond aussi : comment purger les lignes anciennes sans rouvrir la porte, et comment s’assurer qu’aucune écriture n’oublie sa trace.

Pourquoi protéger le journal d’audit dans la base ?

Parce qu’un journal que le code applicatif peut réécrire ne prouve rien. Si la seule garantie est l’absence de route DELETE /audit, un bug ou une injection SQL peut effacer précisément les lignes qui auraient expliqué un incident. Le journal sert au support, au client et à l’auditeur : il doit tenir même quand le code se trompe. Sans droits UPDATE ni DELETE sur la table, une tentative de modification échoue avec permission denied, quel que soit le chemin qui l’a produite.

Comment rendre une table append-only dans PostgreSQL ?

En combinant les privilèges (GRANT) et la sécurité au niveau ligne. Les privilèges fixent les commandes autorisées ; les policies RLS bornent les lignes au tenant courant. Voici l’extrait de la migration 0003_audit_outbox.sql de saas-core :

ALTER TABLE audit_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_events FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_audit ON audit_events
  FOR SELECT
  USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);
CREATE POLICY tenant_append_audit ON audit_events
  FOR INSERT
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);
-- Append-only: SELECT + INSERT only. No UPDATE/DELETE grant, no policy either.
GRANT SELECT, INSERT ON audit_events TO saas_app;

Deux verrous se superposent. Sans privilège UPDATE ni DELETE, la commande est refusée avant même l’évaluation des policies. Et comme aucune policy UPDATE ou DELETE n’existe, un privilège accordé par erreur plus tard ne rendrait toujours aucune ligne modifiable sous RLS forcée. Cela suppose, comme pour toute table multi-tenant, que le rôle applicatif ne soit ni propriétaire de la table ni BYPASSRLS ; ce point est détaillé dans l’article sur la RLS PostgreSQL multi-tenant.

Pourquoi écrire l’audit dans la même transaction que l’action ?

Pour que l’action et sa trace committent ou échouent ensemble. Écrite après coup, dans une autre transaction, la ligne d’audit peut manquer à une action réussie, ou décrire une action annulée.

Dans saas-core, recordAudit reçoit la transaction en cours et s’appelle à l’intérieur de runInTenantTx. Le tenant de la ligne n’est pas passé en argument : il est lu dans current_setting('app.tenant_id'), la même source que les policies RLS, si bien que l’écrivain et la policy ne peuvent pas diverger. Extrait du contrôleur des rôles :

return runInTenantTx(this.db, tenantId, async (tx) => {
  // … insertion du rôle et de ses permissions …
  await recordAudit(tx, {
    actorId,
    action: "roles.create",
    resourceType: "role",
    resourceId: roleId,
    after: { slug, name, description, permissions },
  });
});

Et les effets externes : e-mails, webhooks, événements ?

Ils suivent le même principe, sous le nom de transactional outbox. Au lieu d’appeler un bus de messages ou un serveur SMTP pendant la requête, l’application insère une ligne dans outbox_events avec emitEvent, dans la même transaction que l’action et que l’audit. Un worker relit ensuite ces lignes et les publie. Le test apps/api/test/audit-outbox.spec.ts vérifie les deux faces : une transaction réussie laisse une ligne d’audit et une ligne d’outbox, une transaction qui lève une erreur n’en laisse aucune.

Que doit contenir une ligne d’audit ?

Assez pour répondre à « qui a fait quoi, sur quoi, quand, d’où, et avec quel effet ». La table de saas-core porte :

Colonne	Rôle
`actor_id`, `actor_type`	l’auteur, et sa nature dans un ensemble fermé : `user`, `platform`, `billing_webhook`, `system`
`on_behalf_of`	le membre au nom duquel agit un administrateur pendant une session déléguée
`action`, `resource_type`, `resource_id`	l’action (par exemple `roles.create`) et sa cible
`before`, `after`	l’état avant et après, en `jsonb`
`correlation_id`, `ip_address`, `user_agent`	l’origine de la requête, pour relier la ligne aux journaux techniques
`created_at`	l’horodatage posé par la base

Pendant une session déléguée, actor_id doit nommer l’administrateur réel et non le compte chargé dans le navigateur. apps/api/src/audit/audit.ts fait cette substitution une fois, pour tous les appels, plutôt que de compter sur chaque appelant.

Comment concilier ajout seul et durée de conservation ?

En séparant la fenêtre de lecture de la purge physique. Une table d’audit qui ne perd jamais une ligne grossit sans fin, et les règles de conservation des données imposent de toute façon une limite. saas-core distingue deux mécanismes :

La fenêtre de lecture, liée à l’offre du client : l’entitlement audit.retention_days (30 jours pour l’offre gratuite, 90 pour l’offre pro, migration 0026_audit_retention_entitlement.sql) borne ce que renvoie la lecture du journal (GET /audit). Elle ne supprime rien.
La purge de stockage, indépendante de l’offre : une tâche quotidienne du worker (apps/worker/src/audit-purge.ts) supprime les lignes plus anciennes que AUDIT_STORAGE_RETENTION_MONTHS, 24 mois par défaut.

La purge est la seule exception à l’ajout seul, et elle est bornée dans la base elle-même. La migration 0027_audit_storage_purge.sql accorde DELETE au seul rôle du worker, saas_relay, et seulement sur les lignes de plus de six mois :

GRANT SELECT, DELETE ON audit_events TO saas_relay;

CREATE POLICY relay_purge_audit ON audit_events
  FOR DELETE TO saas_relay
  USING (created_at < now() - interval '6 months');

CREATE POLICY relay_read_old_audit ON audit_events
  FOR SELECT TO saas_relay
  USING (created_at < now() - interval '6 months');

La seconde policy n’est pas décorative. Un DELETE … WHERE created_at < … doit lire created_at pour trouver ses lignes, et sous RLS forcée cette lecture passe par les policies SELECT. Sans policy de lecture pour saas_relay, qui tourne sans contexte tenant, la purge ne trouverait aucune ligne et ne supprimerait rien, sans erreur. Côté worker, un horizon configuré sous six mois est relevé au plancher. Même mal configurée, la tâche ne peut pas atteindre les six derniers mois, et le rôle applicatif ne peut toujours rien supprimer.

Comment garantir qu’aucune écriture n’oublie sa trace ?

Avec un méta-test qui lit le code au lieu de tester un cas. apps/api/test/audit-coverage.spec.ts parcourt les contrôleurs avec l’AST TypeScript, repère chaque handler décoré @Post, @Put, @Patch ou @Delete, et échoue si aucun n’appelle recordAudit, recordAuditMany ou recordPlatformEvent, directement ou dans une méthode de service appelée par this.<service>.<méthode>() (un niveau de délégation).

Plusieurs choix rendent ce test difficile à contourner :

la lecture se fait par l’AST et non par recherche de texte : un recordAudit( cité en commentaire ou dans une chaîne ne compte pas, et une fixture le vérifie ;
les exemptions sont nommées dans le test lui-même, chacune avec sa raison (un puits de rapports CSP, un relais de logs navigateur qui n’écrit rien) ;
une exemption qui pointe vers un handler disparu fait elle aussi échouer le test ;
une écriture sans utilisateur derrière elle (un webhook, un traitement automatique) doit déclarer son actor_type à l’endroit de l’appel, faute de quoi elle retomberait sur user.

Comment saas-core l’intègre

Table audit_events et droits SELECT, INSERT pour saas_app : apps/api/migrations/0003_audit_outbox.sql. Ensemble fermé des origines : 0051_audit_actor_origin.sql.
Écriture dans la transaction : recordAudit et recordAuditMany (apps/api/src/audit/audit.ts), appelés dans runInTenantTx (apps/api/src/db/client.ts) ; outbox par emitEvent (apps/api/src/events/outbox.ts).
Atomicité et refus de UPDATE et DELETE sous le vrai rôle applicatif : apps/api/test/audit-outbox.spec.ts.
Fenêtre de lecture par offre : 0026_audit_retention_entitlement.sql. Purge bornée : 0027_audit_storage_purge.sql et apps/worker/src/audit-purge.ts.
Couverture des handlers d’écriture : apps/api/test/audit-coverage.spec.ts, exécuté par le workflow ci. Un ci rouge bloque la livraison d’une version, pas la fusion sur main ; la chaîne complète est décrite dans l’article sur le pipeline CI/CD.

Les limites sont documentées : le journal couvre les écritures faites par l’API, pas les gestes faits directement en base (migration, intervention manuelle), et aucun export horodaté ou signé par un tiers n’existe aujourd’hui pour prouver l’inaltérabilité à l’extérieur.

Pour aller plus loin

Le journal d’audit, sa purge bornée et le méta-test de couverture font partie du socle livré avec le kit, au même titre que l’isolation des tenants. Le contenu de chaque formule est détaillé sur la page tarifs.