Leer hoe je in PostgreSQL manipulatieresistente audittrails maakt met append-only tabellen en hash‑ketting, zodat bewerkingen tijdens reviews en onderzoeken makkelijk detecteerbaar zijn.
Een audittrail is het bewijs waar je op terugvalt als er iets mis lijkt: een vreemde refund, een permissiewijziging die niemand zich herinnert, of een klantrecord dat “verdween”. Als de audittrail bewerkt kan worden, stopt het met bewijs te zijn en wordt het gewoon weer een stuk data dat iemand kan herschrijven.
Veel “auditlogs” zijn gewoon normale tabellen. Als rijen geüpdatet of verwijderd kunnen worden, kan het verhaal ook aangepast of verwijderd worden.
Een belangrijk onderscheid: wijzigingen blokkeren is niet hetzelfde als wijzigingen detecteerbaar maken. Je kunt veranderingen beperken met permissies, maar iedereen met genoeg toegang (of een gestolen admin‑credential) kan nog steeds de geschiedenis aanpassen. Tamper‑evidence accepteert die realiteit. Je voorkomt misschien niet elke wijziging, maar je laat wijzigingen een duidelijke vingerafdruk achter.
Normale auditlogs worden om voorspelbare redenen betwist. Geprivilegieerde gebruikers kunnen het log later “corrigeren”. Een gecompromitteerd app‑account kan geloofwaardige entries schrijven die eruit zien als normaal verkeer. Timestamps kunnen worden teruggevuld om een late wijziging te verbergen. Of iemand verwijdert alleen de meest schadelijke regels.
“Tamper‑evident” betekent dat je de audittrail zo ontwerpt dat zelfs een kleine wijziging (een veld veranderen, een rij verwijderen, events herordenen) later detecteerbaar wordt. Je belooft geen magie. Je belooft dat wanneer iemand vraagt “Hoe weten we dat dit log echt is?”, je controles kunt uitvoeren die aantonen of het log is aangeraakt.
Een manipulatieresistente audittrail is alleen nuttig als die de vragen beantwoordt die je later zult krijgen: wie deed wat, wanneer deden ze het, en wat veranderde.
Begin met de gebeurtenissen die voor je business belangrijk zijn. Dataveranderingen (create, update, delete) zijn de basis, maar onderzoeken draaien vaak ook om beveiliging en toegang: logins, wachtwoordresets, permissiewijzigingen en accountlockouts. Als je betalingen, refunds, credits of uitbetalingen verwerkt, behandel geldbewegingen als eersteklas gebeurtenissen, niet als bijeffect van een geüpdatete rij.
Bepaal vervolgens wat een event geloofwaardig maakt. Auditors verwachten meestal een actor (gebruiker of service), een server‑zijde timestamp, de uitgevoerde actie en het object dat werd geraakt. Voor updates: sla before en after waarden op (of ten minste de gevoelige velden), plus een request id of correlatie id zodat je veel kleine databasewijzigingen kunt teruglinken naar één gebruikersactie.
Wees tenslotte expliciet over wat “immutable” betekent in jouw systeem. De eenvoudigste regel is: update of verwijder nooit auditrijen, insert alleen. Als er iets mis is, schrijf een nieuw event dat het oude corrigeert of vervangt, en houd het origineel zichtbaar.
Houd auditdata gescheiden van je normale tabellen. Een dedicated audit schema verkleint het risico op per ongeluk bewerken en maakt permissies makkelijker te beheren.
Het doel is simpel: rijen mogen toegevoegd worden, maar nooit veranderd of verwijderd. In PostgreSQL dwing je dat af met privileges (wie wat mag) en een paar veiligheidsrails in je tabelontwerp.
Hier is een praktisch start‑tabel
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
);
Een paar velden zijn tijdens onderzoeken bijzonder nuttig:
occurred_at met DEFAULT now() zodat tijd door de database wordt gestempeld, niet door de client.entity_type en entity_id zodat je één record over tijd kunt volgen.request_id zodat één gebruikersactie over meerdere rijen te traceren is.Vergrendel het met rollen. Je applicatierol zou INSERT en SELECT op audit.events moeten kunnen, maar niet UPDATE of DELETE. Houd schema‑wijzigingen en sterkere permissies voor een adminrol die niet door de app gebruikt wordt.
Als je een manipulatieresistente audittrail wilt, is de meest betrouwbare plaats om wijzigingen vast te leggen de database. Applicatielogs kunnen worden overgeslagen, gefilterd of herschreven. Een trigger vuurt ongeacht welke app, script of admin‑tool de tabel aanraakt.
Hou triggers saai. Hun taak moet één ding zijn: voor elke INSERT, UPDATE en DELETE op relevante tabellen precies één auditevent toevoegen.
Een praktisch auditrecord bevat meestal de tabelnaam, type operatie, primaire sleutel, before en after waarden, een timestamp en identifiers die je in staat stellen gerelateerde wijzigingen te groeperen (transactie id en correlatie id).
Correlatie‑id's zijn het verschil tussen “20 rijen geüpdatet” en “dit was één druk op een knop”. Je app kan per request een correlatie‑id instellen (bijvoorbeeld in een DB‑sessie‑instelling), en de trigger kan die lezen. Sla ook txid_current() op, zodat je nog steeds wijzigingen kunt groeperen wanneer de correlatie‑id ontbreekt.
Hier is een simpel trigger‑patroon dat voorspelbaar blijft omdat het alleen in de audittabel insert (pas namen aan naar je eigen 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;
Weersta de drang om meer in triggers te doen. Vermijd extra queries, netwerkcalls of complexe branching. Kleine triggers zijn makkelijker te testen, sneller en lastiger om tijdens een review tegenin te gaan.
Een append‑only tabel helpt, maar iemand met genoeg toegang kan nog steeds eerdere rijen herschrijven. Hash‑ketting maakt dat soort manipulatie zichtbaar.
Voeg twee kolommen toe aan elke auditrij: prev_hash en row_hash (soms chain_hash genoemd). prev_hash slaat de hash van de vorige rij in dezelfde keten op. row_hash slaat de hash van de huidige rij op, berekend uit de rijgegevens plus prev_hash.
Wat je hasht is belangrijk. Je wilt een stabiele, reproduceerbare input zodat dezelfde rij altijd dezelfde hash geeft.
Een praktische aanpak is het hashen van een canonieke string opgebouwd uit vaste kolommen (timestamp, actor, actie, entity id), een canonieke payload (vaak jsonb, omdat keys consistent worden opgeslagen) en de prev_hash.
Wees voorzichtig met details die zonder betekenis kunnen veranderen, zoals whitespace, JSON‑volgorde in plaintext of locale‑specifieke formatting. Houd types consistent en serialiseer op één voorspelbare manier.
Als je ieder auditevent in één globale sequentie ketent, kunnen writes een bottleneck worden. Veel systemen ketenen binnen een “stream”, zoals per tenant, per entity type of per businessobject.
Elke nieuwe rij zoekt de laatste row_hash voor zijn stream op, slaat die op als prev_hash en berekent vervolgens zijn eigen 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'
);
Voor snellere reviews sla je periodiek de laatste row_hash (de “chain head”) op, bijvoorbeeld dagelijks per stream, in een kleine snapshottabel. Tijdens een onderzoek kun je de keten verifiëren tot elke snapshot in plaats van de hele geschiedenis in één keer te scannen. Snapshots maken het ook makkelijker exports te vergelijken en verdachte gaten op te merken.
Hash‑ketting wordt lastiger bij echt verkeer. Als twee transacties tegelijk auditrijen schrijven en beide dezelfde prev_hash gebruiken, kun je forks krijgen. Dat verzwakt je vermogen om één schone volgorde te bewijzen.
Bepaal eerst wat je keten representeert. Eén globale keten is het makkelijkst uit te leggen maar heeft de meeste contention. Meerdere ketens verminderen contention, maar je moet duidelijk zijn over wat elke keten bewijst.
Welke model je ook kiest, definieer een strikte volgorde met een monotone event‑id (meestal een sequence‑backed id). Timestamps alleen zijn niet genoeg omdat ze kunnen colliden en gemanipuleerd kunnen worden.
Om race‑conditions te vermijden bij het berekenen van prev_hash, serialiseer “read last hash + insert next row” per stream. Gebruik vaak het vergrendelen van een enkele rij die de streamhead representeert, of een advisory lock ge‑keyed op de stream id. Het doel is dat twee schrijvers naar dezelfde stream niet allebei dezelfde last hash kunnen lezen.
Partitionering en sharding beïnvloeden waar “de laatste rij” woont. Als je verwacht auditdata te partitioneren, houd elke keten volledig binnen één partition door dezelfde partitionkey als streamkey te gebruiken (bijvoorbeeld tenant id). Zo blijven tenantketens verifieerbaar zelfs als tenants later over servers verplaatst worden.
Hash‑ketting helpt alleen als je kunt aantonen dat de keten nog klopt wanneer iemand erom vraagt. De veiligste aanpak is een read‑only verificatiequery (of job) die elke rij opnieuw hasht op basis van de opgeslagen data en vergelijkt met wat is geregistreerd.
Een verifier zou moeten: de verwachte hash per rij herbouwen, bevestigen dat elke rij naar de vorige linkt, en alles flaggen dat afwijkt.
Hier is een veelgebruikt patroon met window‑functies. Pas kolomnamen aan naar je eigen tabel.
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;
Naast “kapot of niet”, is het de moeite waard te controleren op gaten (missende ids in een range), links die uit volgorde zijn, en verdachte duplicaten die niet passen bij echte workflows.
Draai geen query en verberg de output in een ticket. Sla verificatie‑uitkomsten op in een aparte append‑only tabel (bijvoorbeeld audit_verification_runs) met runtime, verifier‑versie, wie het triggerde, het gecontroleerde bereik en counts voor broken links en hash mismatches.
Dat geeft je een tweede trail: niet alleen is het auditlog intact, je kunt ook aantonen dat je het hebt gecontroleerd.
Een praktische cadence is: run na elke deploy die auditlogica raakt, nightly voor actieve systemen, en altijd vóór een geplande audit.
De meeste fouten hebben niets met het hashalgoritme te maken. Het gaat om uitzonderingen en gaten die mensen ruimte geven om te betwisten.
De snelste manier om vertrouwen te verliezen is updates op auditrijen toestaan. Zelfs als het “maar één keer” is, creëer je zowel een precedent als een werkende weg om geschiedenis te herschrijven. Als je iets moet corrigeren, voeg een nieuw auditevent toe dat de correctie uitlegt en houd het origineel.
Hash‑ketting faalt ook als je onstabiele data hasht. JSON is een veelvoorkomende valkuil. Als je een JSON‑string hasht, kunnen onschuldige verschillen (keyvolgorde, whitespace, nummerformattering) de hash veranderen en verificatie luidruchtig maken. Geef de voorkeur aan een canonieke vorm: genormaliseerde velden, jsonb, of een andere consistente serialisatie.
Andere patronen die een verdedigbare trail ondermijnen:
prev_hash in een keten toestaan zonder een duidelijke, gedocumenteerde regel.Scheiding van taken is belangrijk. Als dezelfde rol auditevents kan inserten en ook kan wijzigen, wordt tamper‑evidence een belofte in plaats van een controle.
Een verdedigbare audittrail moet moeilijk te veranderen en makkelijk te verifiëren zijn.
Begin met access control: de audittabel moet in de praktijk append‑only zijn. De applicatierol mag insertten (en meestal lezen), maar niet updaten of verwijderen. Schema‑wijzigingen moeten strak beperkt zijn.
Zorg dat elke rij de vragen van een onderzoeker beantwoordt: wie deed het, wanneer gebeurde het (server‑zijde), wat gebeurde er (duidelijke eventnaam plus operatie), wat raakte het (entity‑naam en id) en hoe het verbonden is (request/correlatie id en transactie id).
Valideer dan de integriteitslaag. Een snelle test is een segment replayen en bevestigen dat elke prev_hash overeenkomt met de hash van de voorgaande rij, en dat elke opgeslagen hash overeenkomt met de herberekende hash.
Operationeel: behandel verificatie als een normale job:
Een veelvoorkomend testgeval is een refunddispuut. Een klant beweert dat er een goedkeuring was voor $250 refund, maar het systeem toont nu $25. Support zegt dat de goedkeuring correct was en compliance wil antwoord.
Begin met het verkleinen van de zoekruimte met een correlatie id (order id, ticket id of refund_request_id) en een tijdsvenster. Haal de auditrijen op voor die correlatie id en bracket ze rond de goedkeuringstijd.
Je zoekt naar het volledige set events: request aangemaakt, refund goedgekeurd, refundbedrag ingesteld en eventuele latere updates. Met een tamper‑evident ontwerp controleer je ook of de sequentie intact bleef.
Een simpel onderzoeksflow:
prev_hash).Als iemand één auditrij bewerkte (bijvoorbeeld het bedrag van 250 naar 25 veranderde), zal die rij’s hash niet meer matchen. Omdat de volgende rij de vorige hash bevat, veroorzaakt de mismatch meestal een cascade vooruit. Die cascade is de vingerafdruk: het toont dat het auditrecord achteraf is aangepast.
Wat de keten je kan vertellen: er is een wijziging geweest, waar de keten voor het eerst brak en de scope van getroffen rijen. Wat het niet alleen kan vertellen: wie de wijziging maakte, wat de originele waarde was als die overschreven is, of andere tabellen ook werden aangepast.
Behandel je audittrail als elke andere security‑control. Rol het in kleine stappen uit, bewijs dat het werkt en breid uit.
Begin met acties die je het meest zouden schaden als ze betwist worden: permissiewijzigingen, uitbetalingen, refunds, data‑exports en handmatige overrides. Zodra die gedekt zijn, voeg laagrisico‑events toe zonder het kernontwerp te veranderen.
Leg het contract voor je auditevents vast: welke velden worden opgeslagen, wat elk eventtype betekent, hoe de hash wordt berekend en hoe je verificatie draait. Bewaar die documentatie naast je database‑migraties en houd de verificatieprocedure reproduceerbaar.
Herstel‑drills zijn belangrijk omdat onderzoeken vaak vanaf backups starten, niet vanaf het live systeem. Restore regelmatig naar een testdatabase en verifieer de keten end‑to‑end. Als je na een restore niet hetzelfde verificatieresultaat kunt reproduceren, is je tamper‑evidence moeilijk te verdedigen.
Als je interne tools en adminworkflows bouwt met AppMaster (appmaster.io), helpt het standaardiseren van audit‑eventwrites via consistente server‑zijde processen om het eventschema en correlatie‑ids uniform te houden over features, wat verificatie en onderzoeken veel eenvoudiger maakt.
Plan onderhoudstijd voor dit systeem. Audittrails falen meestal stilletjes wanneer teams nieuwe features uitrollen maar vergeten events toe te voegen, de hashinputs bij te werken, of verificatiejobs en restore‑drills draaiende te houden.
21 jan 2026
8 min
20 jan 20268 min
20 jan 20267 minExperimenteer met AppMaster met gratis abonnement.
Als je er klaar voor bent, kun je het juiste abonnement kiezen.
