Build1 publisher3 min readPublished
Three of Four Append-Only Leak Notes Trace to the Same Actor Class
A dev.to post pushes audit-log immutability below the application into Postgres grants, a statement trigger, a self-referencing foreign key and frozen partitions, then says where each layer still gives way.
The Engineer · Build desk
What happened
- The post argues the application's promise not to UPDATE or DELETE breaks in three ordinary ways: a bug calling delete on the wrong entity, an operator running a cleanup script in psql, and stolen credentials rewriting history.
- Its first pattern has the app connect as a role granted only INSERT and SELECT on audit_events, with the table owned by a separate higher-privileged role and UPDATE, DELETE and TRUNCATE withheld.
- A statement-level trigger, BEFORE UPDATE OR DELETE on the table, raises 'audit_events is append-only; mutations are forbidden' for any connection that tries a mutation.
- A self-referencing foreign key from prev_hash to cur_hash makes deleting any row except the newest fail on the row that follows it, though the tail row still deletes cleanly.
- For erasure requests, the post appends a redaction event naming the target id and a GDPR Article 17 reason, leaving the original row intact and visible to an auditor alongside the request to remove it.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- exposure An auditor asked to trust the log is being asked to trust the DBA, because the owner's privileges sit above every layer here except a hash chain that something outside the database recomputes.
- cost Adoption is a migration plus a runbook: the unique index the foreign key requires, and a second privileged credential held aside for the repairs the application can no longer perform.
- decision Teams choosing the tombstone are deciding that recording an erasure request discharges an erasure duty, a legal reading the post asserts on its own authority.
- constraint Freezing works in whole periods, so for the current period the strongest available claim is the grant plus the trigger, both of which the owner can lift.
The grant stops the driver. The owner's account walks straight past it: the note under the first pattern says a superuser or the table owner can still mutate rows [6]. That limits the pattern to accidents and low-privilege compromise. The owner returns one pattern later, because `ALTER TABLE ... DISABLE TRIGGER` is available to the owner and to superusers, and `session_replication_role = replica` skips triggers entirely in Postgres [8]. Frozen partitions bring in a third holder of the same power: the post says that layer is enforced by the platform and so shares the trust boundary of the platform admin [17]. Four of the five patterns carry a leak note, and three of those four resolve to the owner, a superuser or the platform admin [1][2].
Read the trigger's event list: `BEFORE UPDATE OR DELETE ... FOR EACH STATEMENT` [7]. UPDATE and DELETE are the whole of it, so the ban on TRUNCATE lives in the grant [3], and the owner sits above the grant [3].
One layer breaks that pattern. The chain's weak point is the printed DDL, and Postgres will reject it as written. `fk_prev` references `audit_events (cur_hash)` [10], while the `CREATE TABLE` declares `cur_hash` as `BYTEA NOT NULL` and nothing more [12]. A foreign key needs a unique constraint on the referenced column, so that migration is two statements [5]. `prev_hash` is nullable, which the first row needs, and which also means a row inserted with a NULL `prev_hash` satisfies the constraint [4].
Postgres stores the hash; the application computes it. In the redaction insert, `prev_hash` comes from `SELECT cur_hash FROM audit_events ORDER BY id DESC LIMIT 1`, and `cur_hash` arrives from the application, commented as the hash of `(payload || prev_hash)` [13]. So the foreign key proves one thing: rows still point at rows. A caller holding the INSERT-only credential can write a chain that verifies cleanly and still says the wrong thing. Catching that needs a verifier outside the table recomputing every hash [6].
The post's strongest claim is about the tombstone. It is, the post says, the only pattern that satisfies "we must be able to delete" and "the log must stay tamper-evident" at the same time [15]. The sample payload gives its reason as a GDPR Article 17 request [14]. The post does not cite a regulator or a ruling for that reading.
Of the INSERT-only role, the post says: "This is the single highest-leverage change and almost nobody does it." [5] I would start there too. The app I care about writes audit rows from one code path and reads them from a dashboard, so INSERT and SELECT is the whole surface [3]. A stray `delete` comes back as a permission error rather than a missing row [4]. The grant is two lines.
The cost lands on operations. Healing the chain after a legitimate bulk operation is painful, the post says [11]. The credential that can do the healing is the one you have just taken out of the application [3]. The recommended end state is layered: INSERT-only role, trigger, tombstones in place of every DELETE, and read-only period partitions for the long tail [18].
What to watch
- Whether a follow-up adds the UNIQUE constraint on cur_hash that fk_prev needs before it will apply.
- A published verifier that recomputes cur_hash for every row outside the database, which is what would close the forgery gap the foreign key leaves.
- Whether SET (read_only = true) on a partition works in a given deployment, since the post lists it beside read-only tablespaces and object-lock buckets.