Menu

A tamper-evident audit log in PostgreSQL with hash chains and HMAC
📰

A tamper-evident audit log in PostgreSQL with hash chains and HMAC

DEV Community: postgres·Mike Vu·22 days ago
#CuRIJ854
#devtopostgresql#devto#dev#chain#database
Long article (1355 words) — no AI summary yet.
Reading 0:00
15s threshold

In an app where data is edited from several devices, sooner or later you'll need to answer "who changed this, when, and from where?". An audit_log table answers that. But there's a harder question behind it: how do you know the audit log itself hasn't been edited?

This post walks through building a tamper-evident audit log in PostgreSQL, layer by layer, each one closing the gap left by the previous. The examples come from Financial Plan , my budgeting app (its sync is covered in local-first PWA sync ), where every record a device pushes is logged with its before and after versions, the device, the time and the outcome.

Layer 1: append only

The first step is to have the database itself refuse any update or delete on the log table:

CREATE FUNCTION audit_log_append_only () RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'audit_log is append-only' ; END $$ ;

CREATE TRIGGER audit_log_no_change BEFORE UPDATE OR DELETE ON audit_log FOR EACH ROW EXECUTE FUNCTION audit_log_append_only (); CREATE…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.