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

#architecture #database #postgres #security

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()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'audit_events is append-only; mutations are forbidden';
END;
$$;

CREATE TRIGGER audit_no_update
  BEFORE UPDATE OR DELETE ON audit_events
  FOR EACH STATEMENT
  EXECUTE FUNCTION reject_audit_mutate();

The leak: ALTER TABLE ... DISABLE TRIGGER is available to the table owner and superusers, and session_replication_role = replica skips triggers entirely in Postgres. Treat triggers as defense-in-depth, not the perimeter.

Pattern 3 — A foreign key that points backward

Make each row reference the previous row's hash, and put a FOREIGN KEY on it. Deleting any row except the newest now fails the constraint on the row that follows it.

ALTER TABLE audit_events
  ADD CONSTRAINT fk_prev
  FOREIGN KEY (prev_hash) REFERENCES audit_events (cur_hash);

The leak: the newest row has no follower, so deleting the tail still works. And healing the chain after a legitimate bulk operation is painful. Useful as one layer, not sufficient alone.

Pattern 4 — Tombstones instead of DELETE

The cleanest way to honor "right to erasure" laws without making the log mutable: you never delete. You append a redaction event.

INSERT INTO audit_events (payload, prev_hash, cur_hash)
VALUES (
  '{"type":"redaction","target_id": 42,"reason":"GDPR Art.17 request"}',
  (SELECT cur_hash FROM audit_events ORDER BY id DESC LIMIT 1),
  ... -- hash of (payload || prev_hash)
);

The original row stays intact and verifiable; the log now records that it was asked to forget. An auditor sees both the event and the request to erase it. This is the only pattern that satisfies "we must be able to delete" and "the log must stay tamper-evident" at the same time.

Pattern 5 — WORM at the storage layer

Push the table (or a time partition of it) onto write-once storage: a read-only tablespace, an object-lock bucket, or a partitioned scheme where last month's partition is ALTER TABLE ... SET (read_only = true) / moved to immutable media.

-- freeze a partition
ALTER TABLE audit_events_2026_08 SET (read_only = true);

The leak: it's coarse-grained (you freeze whole periods, not individual rows) and it's enforced by the platform, so it shares the same trust boundary as the platform admin. Good for the "we can prove we didn't touch last quarter" claim, weaker for fine-grained guarantees.

The combination that holds up

In practice you want layers, not a single trick:

App connects as an INSERT-only role (Pattern 1) — stops the common bug and the low-priv compromise.
A trigger (Pattern 2) catches the rest at the SQL level.
Tombstones (Pattern 4) replace every DELETE so erasure requests are logged, not destructive.
Period partitions go read-only (Pattern 5) for the long tail.
And underneath all of it, a hash chain (each row hashes its payload plus the previous row's hash) plus an external trusted timestamp, so that even a database superuser who can mutate a row cannot do it without breaking verification — and cannot convincingly fake when it happened.

That last point is the actual perimeter. Database constraints protect you from mistakes and from attackers who lack DB privileges. They do not protect you from someone who controls the database. For that threat, the chain + timestamp has to be verifiable by a party who doesn't trust your infrastructure at all — which is a separate post, but it's the reason none of the patterns above are sufficient on their own.

The takeaway: append-only is a property you enforce in three places — the role, the table, and the math. Skip any one and "append-only" is just a comment in your codebase that nobody obeys.

DEV Community