Stop Trusting the App: Enforcing Append-Only at the Database Layer

Most "audit logs" are append-only by convention, not by enforcement. The application inserts a row and politely promises never to UPDATE or DELETE it. That promise holds right up until the moment someone does one of these: A bug in the app calls repo.delete(id) on the wrong entity. An operator connects with psql and runs a cleanup script against the production table. A compromised dependency exfiltrates the credentials and rewrites history to cover its tracks. If a third party is expected to trust your audit trail, "we don't update it" is not a guarantee — it's a hope. The constraint has to live below the application, in the database itself. Here are the patterns that actually work, and where each one still leaks. Pattern 1 — An INSERT-only role The cheapest real guarantee: the application connects with a database role that physically cannot modify what it already wrote. -- a role the app uses at runtime CREATE ROLE app_audit LOGIN PASSWORD '...' ; -- the audit table is owned by a separate, higher-privileged role CREATE TABLE audit_events ( id BIGSERIAL PRIMARY KEY , payload JSONB NOT NULL , prev_hash BYTEA , cur_hash BYTEA NOT NULL , written_at TIMESTAMPTZ NOT NULL DEFAULT now () ); -- grant ONLY insert (and select, if the app reads its own log) GRANT INSERT , SELECT ON audit_events TO app_audit ; -- explicitly: no UPDATE, no DELETE, no TRUNCATE Now even a code bug that calls delete resolves to a permission error at the driver, not a silent row removal. This is the single highest-leverage change and almost nobody does it. The leak: a superuser or the table owner can still mutate rows. So this stops accidents and low-privilege compromises, not a DBA with full access — which is exactly why you still want a hash chain underneath (see the "why this isn't enough" note at the end). Pattern 2 — Triggers as a backstop Triggers catch mutations made through any connection, including the owner, unless the session disables them. CREATE OR REPLACE FUNCTION reject_audit_mutate (