Day 143: The Hole in Append-Only
Pinchy’s audit log is the part of the product I most want to be boring. It records who did what, and it’s built to be tamper-evident: each row’s signature includes its predecessor’s, so deleting, truncating, or reordering history breaks the chain and a verifier can see the break. Tamper-evident, not tamper-proof. The distinction is the whole honest pitch. It cannot stop a determined superuser, but it can make sure that if history is altered, the alteration shows. PR #691 hardened three things, and two of them taught me something.
An append-only table that TRUNCATE could still empty
The log is append-only, enforced by Postgres triggers that reject UPDATE and DELETE on the audit table. I’d been treating that as airtight. It wasn’t. TRUNCATE in Postgres is a statement-level operation, not a row-level one, and row-level triggers simply never fire for it. So the exact triggers standing guard over “you cannot delete audit rows” would sit and watch a single TRUNCATE empty the entire table without a peep. The append-only guarantee was only ever as strong as its least-covered operation, and I’d left the biggest one uncovered. The fix is small once you see it: a BEFORE TRUNCATE statement-level trigger that closes the gap. But the lesson is the size of the blind spot, not the size of the patch. A guarantee you state confidently (“this table is append-only”) can have a whole category of exception you never enumerated.
A verifier that could cry wolf
The other feature is a background cron that re-verifies the chain incrementally, so tampering gets caught on its own, not only when someone remembers to run a manual check. Writing it, I nearly handed it a way to raise a false alarm, which for a tamper detector is almost the worst possible bug. Here’s the trap: Postgres serial sequences are not gapless. A transaction that rolls back still consumes its sequence value, and no row is ever written with that id. So if the verifier did the obvious arithmetic, counting rows and assuming the next id follows the last, it could seed the next sweep from an id that never existed, read a null where it expected a hash, and conclude the chain was broken when nothing was wrong. The fix is to stop assuming and start looking: snapshot toId = MAX(id) from the actual table and derive the checkpoint from the real highest row, never from counting. A tamper alarm that fires on a normal rolled-back transaction is worse than no alarm, because the first false one teaches you to ignore the next real one. That’s the same thing yesterday’s probe was about, wearing different clothes: a check you can’t trust when it’s wrong is a check nobody heeds when it’s right.
Crypto-shredding, so deletion doesn’t mean tampering
The third piece is the one that reconciles two things that look opposed: an immutable audit trail and a person’s right to be forgotten. Each user gets a random audit_pseudonym, and audit rows reference that, not the raw identity. Delete the user and the pseudonym’s link is destroyed, so historical rows become unlinkable to a real person while the chain over them stays intact and verifiable. You erase the who without erasing the record. Lawful deletion stops being a reason to break the log.
Day 143
Every one of these was a case where a guarantee I’d have stated out loud was quietly narrower than the words. “The table is append-only” had an unlisted exception. “The verifier detects tampering” could have meant “the verifier sometimes invents tampering.” The honest version of a security claim isn’t the confident sentence. It’s the sentence with its exceptions enumerated, which is why the audit trail’s own page says tamper-evident and not tamper-proof. A guarantee is only worth as much as the edge cases you actually went and checked, and the ones you’re most sure about are the ones worth checking first.