Refutation and fix review: H-L10-05 (a write in flight during an account erasure)
From mnema-talk v3.3.13, rendered on this site.
What I reviewed:
- The finding: H-L10-05.md.
- The hunt test: msch128/hunt-lane10-b at c7ffee5b9.
- The fix: msch128/fix-h-l10-05, commit 01423ef35. Its parent is c7ffee5b9, which already contains the H-L10-01 fix 425838109.
The review was read-only. I added nothing to the repository and pushed nothing. I used two temporary worktrees and one throwaway local postgres:18-alpine container; all three are removed. The probes were scratch test files and were never committed.
| Notes | Verdict |
|---|---|
| Phase 2 (refuter) | STANDS, S4 (S3 argued and rejected) |
| Fix 01423ef35 | MERGE AFTER FIXES (one documentation correction; one merge conflict to resolve) |
Phase 2: is the finding right?
The mechanism holds. I proposed it as a suspicion in the H-L10-01..04 review; here I checked it against the real routes.
- The erasure takes
SELECT ... FOR UPDATEon the users row first (internal/auth/account/account_erase.go). - A write that needs the same row waits for that lock. It waits either in the foreign key check (FOR KEY SHARE) or in its own
UPDATE users. - Once the erasure commits, the write runs against the tombstone. The tombstone keeps its id, and nothing in between looks at
deleted_atagain. - The tombstone trigger (0043, 0057) allowed
display_name,bioandstatus_text.
I ran the revert check for the fix (below). With the migration removed, 19 of the 20 routes in the hunt test leave rows behind. These include a message with its text, the display name, bio and status, uploads together with their stored files, reactions, RSVPs and scheduled messages. "direct message" holds for the reason the finding gives.
- Documented? No. SECURITY.md (account deletion) says the erasure removes everything that refers to the person, in one transaction. It names no exception for a request that is still running, and no time window.
- Intended? No. No test asserts this behaviour. The tombstone trigger shows the opposite intent: the database already refused names, passwords and avatars on a tombstone, and the profile text fields were simply missing from that list.
- Reachable? Yes, by any member, against their own account, with no special rights. Examples: a second tab or device that keeps sending, a client outbox retry, the desktop client. The window is one request's latency while the erasure holds its lock, and the erasure holds it longest for the most active members.
- Duplicate? No.
Severity. The case for S3: the display name, bio or status written back onto the tombstone is visible to every member. The message list joins users for the author's display name, so all of the person's emptied messages would show the real name again. A message written in flight shows its full text as "Deleted member". This is wrong behaviour that users can see, and it breaks the erasure promise.
The case against, which decides it: it needs a write from the person's own session to race their own erasure, which is an unusual timing condition. That is the S4 definition. No other party can trigger it or choose its content. The person no longer exists in the app, and other members only see what the person themselves wrote a moment earlier. I rate it S4, at the top of the band. If the owner weighs a visible privacy promise above the rarity of the condition, S3 is defensible.
Fix review: migration 0103_erased_person_guard.sql
1. Does it remove the cause? The lock argument
The guard (erased_person_guard) is one BEFORE INSERT OR UPDATE OF SELECT deleted_at FROM users WHERE id = v FOR KEY SHARE. That is the lock the foreign key check would take anyway. The old check took it slightly later, in the AFTER trigger. In READ COMMITTED:
- If the erasure holds FOR UPDATE, the guard waits. When it gets the lock it reads the newest row version and sees
deleted_at. - If the writer takes its lock first, the erasure's FOR UPDATE waits until the writer commits. Each erase statement then sees the committed row.
No interleaving is left in between. An UPDATE checks a column only when its value changes, which matches when PostgreSQL itself checks a foreign key. The tombstone's own messages therefore stay writable for pins, unpins and reactions.
Deadlocks. The guard adds no new lock type and no new lock order, because every guarded column already had a foreign key check taking the same lock. The one change is that the lock now comes earlier in the statement, before the heap and index insert. So an insert that has to wait on a unique-index conflict now waits on the users row first. Both waits are on the erasure, so this creates no cycle.
Tables with two person columns, such as user_blocks, lock two users rows FOR KEY SHARE. Those locks do not conflict with each other. A cycle would need an erasure that waits on the writer while the writer waits on a second erasure, and that second erasure never waits on the writer. I found none.
The audit trigger is a new lock on audit inserts that name a person who has no foreign key row in the same transaction. Moderator actions on that person update the users row first, which conflicts with the erasure anyway. Writes after an action's commit hold nothing else.
erased_message_stays_empty takes no users lock, and the argument holds:
- An edit that waited for the erasure's lock on that message row re-reads the row after the commit. Its EXISTS query runs in a new snapshot and sees
deleted_at. - An edit that came first commits before the erasure's
UPDATE messages, which then empties it.
I checked one interaction with another branch. H-L5-04 (fix-h-l5-04) adds a channel row lock in the send path, plus UPDATE channels SET last_message_number. That UPDATE does not fire the channels guard, which fires on UPDATE OF dm_created_by, leader_id only. One deadlock can still happen: a send holds a channel row and waits on the users lock, while the erasure later needs that channel row (the dm_created_by and leader_id steps). That happens with or without this fix, because the foreign key check waited at the same point. PostgreSQL resolves it by aborting one side. NOTE.
Person columns without a foreign key. The lane's schema oracle lists audit_log, moderation_notes and login_unlock_requests, and there are also JSON copies.
audit_log: handled. Entries by or about an erased account are written scrubbed. Remaining gap, which the author names: details written by others after the erasure that mention the id.moderation_notes: author_id and subject_id are not guarded. By design, notes stay attached to a tombstone (erasureKeeps), so a note in flight is consistent with what is already kept. NOTE.login_unlock_requests: keyed by username, written without authentication. Not the person's session. NOTE.message_reports.snapshot: still leaks (SHOULD-FIX). The report route builds its snapshot from a read before its transaction (internal/chat/reports/reports.go:166-174) and inserts afterwards. I ran two probes, each with a real interleaving through the routes: the erasure held right before its anonymise statement, as the hunt test does.- Bob reports alice's message while her erasure is held. Answer: 201 after 512 ms.
reported_user_idis NULL ("clear" mode), but the stored copy is{"message": {"content": "<alice's text>", "user_id": "<her id>", "username": "alice", "display_name": "alice", ...}}. - Bob reports a neighbour's message, with alice's message in the context. Answer: 201 after 13 ms, no wait at all. Alice's text stays in the context.
The erasure's
scrubReportCopiesran before either insert, so it never saw these rows. This has nothing to do with the person's own writes, it existed before the fix, and it is outside the 17 routes of the finding. But the fix's new SECURITY.md text says "a report of their message names no account", and the moderators' copy still names her and holds her words.- Bob reports alice's message while her erasure is held. Answer: 201 after 512 ms.
outgoing_queuepayloads written by others after the erasure: not probed.
2. What breaks
Full integration suite of every package (go test -p 1 -tags=integration ./..., nice -n 10): 85 packages ok, no failure, exit 0. No port-exhaustion failures occurred.
Writes that legitimately name a tombstone afterwards, checked by reading the code:
- Pass the guard: reply, reaction, pin and unpin of a deleted member's message.
- Reactions and pins name the actor.
pinned_bychanges, butuser_idstays the same and is skipped.- A reply's thread follow for the root's author is dropped. The author's TestOtherMembersStillWriteAboutAnErasedAccount covers this.
- Report: goes through with
reported_user_idcleared (but see the snapshot gap above). - Forward: names the forwarder. No media row is copied: the only INSERT INTO media statements are uploads, covers, wiki images, webhook files and profile pictures.
- Link-card unfurl of a deleted member's message: now refused by
erased_message_stays_empty. That is the intent. - Channel copy and category sync (channel_layout.go:253, channels_categories.go:38): copy permission_overwrites rows. A row naming a tombstone is dropped, and the erasure has deleted those rows anyway.
- Pruners, the auto-delete sweep and the outbox: delete, or touch no guarded column with a new value.
- ON DELETE SET NULL actions: write NULL, which is skipped.
"drop" mode returns 2xx for a write that did not happen. This affects:
- an admin assigning a role (user_roles) or setting a channel overwrite for a member, in flight while that member erases the account;
- another member adding them to a group conversation (dm_members);
- another member blocking them (user_blocks.blocked_id).
The admin routes and the group conversation do not read the row back. The block route uses a plain Exec, and the role insert uses ON CONFLICT DO NOTHING without RETURNING. After the erasure, the person is 404 to every administrative route. So the outcome is the same as if the request had come a moment later. NOTE.
The 500 for the in-flight request. The member's web client outbox treats 5xx as retryable (web/src/stores/chat/outbox.ts:46). The retry carries a session that the erasure revoked (token_version + 1) and gets 401, and the 401 listener signs the client out. The user briefly sees the message as unsent, then the signed-out screen. That is acceptable for a request from an account that no longer exists. Answering 401 directly would be nicer. It is logged with table and column, no personal data.
3. Dump and restore
The documented path is scripts/restore.sh: dropdb, createdb, then psql of a plain pg_dump --no-owner (backup.sh:112, backup-online.sh:65). The PostgreSQL major upgrade script uses the same plain dump. I did not run the scripts. Instead I reproduced their form by hand on a throwaway postgres:18-alpine container:
- I built a world with a test: populate an erased member's full footprint, then erase them. Before the erasure, bob had replied in her thread and reported her message. After it, bob reacted to her emptied message and replied in her thread again. The result has 3 tombstone messages, a report with its account cleared, and audit entries.
pg_dump --no-ownerof that database, loaded into an empty database withpsql -v ON_ERROR_STOP=1.- Outcome: exit 0, no error. For all 75 tables, the row count and an md5 over every row in sorted text form are identical between source and restore.
In the plain dump, CREATE FUNCTION comes before the data and every CREATE TRIGGER erased_person_guard comes after it (dump lines 4172ff against COPY public.messages at 2129). So the triggers do not fire during a restore. Confirmed.
A data-only load into an already migrated schema (pg_dump --data-only piped into psql) fails. That would also happen without the fix:
- pg_dump itself warns about circular foreign keys (users and invites, channels, messages, wiki_pages);
- the load stops on
users_invite_id_fkey; - under the dump's empty
search_path, the trigger functions fail with "relation users does not exist". That includes the older counter triggers, which also nameuserswithout a schema.
No documented path uses a data-only load. NOTE: if one is ever added, these functions need SET search_path or schema-qualified names.
4. The migration
Edits: a new file only. No applied migration changed. The checksum gate passes in the full suite.
Duration: I applied the migration file in one transaction to a database where its objects had been dropped. The whole run took under 2 ms, and the triggers do not scan any rows, so it does not grow with the data. Inside the transaction it held ShareRowExclusive on 49 tables and AccessShare on 14 until commit. ShareRowExclusive blocks writes to those tables for that moment. It does not conflict with the AccessShare a running pg_dump holds, so an online backup does not block it.
Upgrade with the previous binary still serving: a compose upgrade, and the updater sidecar, recreate the app container, so the old binary is stopped before the new one migrates. Migrations are serialised by an advisory lock, one transaction per migration.
Upgrade drill: not run. It pulls released images from GHCR, which is too heavy for this machine under the hunt rules.
H-L5-04 also ships a 0103 file (0103_channel_last_message_number.sql). Migrations are keyed by file name, so both would apply and they do not interact:
- the guard does not fire on
UPDATE channels SET last_message_number(UPDATE OF lists onlydm_created_byandleader_id); - its new column changes nothing for the triggers;
- the two branches merge without conflicts.
The renumbering must happen before either is released. Order does not matter.
- the guard does not fire on
Future-proofing test (TestEveryForeignKeyToUsersRefusesAnErasedAccount): it reads pg_trigger and fails for any foreign key to users that is not guarded with the expected mode, or not covered by
UPDATE OF. NOTE: its hint says to addCREATE TRIGGER erased_person_guard. For a new column on a table that already has the trigger, that fails on the existing name; the migration needsCREATE OR REPLACE TRIGGERlisting all columns. Worth one line in the hint.
5. Cost
I measured the latency of a single real POST /api/channels/{id}/messages (a mention of the Owner, so two guarded rows), 300 posts per round, with and without the guard, alternating three times on the same database:
| Round | Without guard | With guard |
|---|---|---|
| 1 | median 5.59 ms, p90 9.5 ms | median 5.45 ms, p90 7.3 ms |
| 2 | median 4.28 ms, p90 8.9 ms | median 4.24 ms, p90 5.1 ms |
| 3 | median 4.39 ms, p90 5.2 ms | median 4.49 ms, p90 5.3 ms |
The difference is below the noise at load 9-11. The author's bulk figure of about 9 µs per row is plausible: to_jsonb(NEW) plus one primary-key lookup. The 1000-session load test was not run.
6. Documentation and gates
- SECURITY.md, doc/operations.md, doc/upgrade.md (section and table row) and the erasure comment match the behaviour, except the report sentence above.
- doc/upgrade.md places the 0103 row after the 0102 row, before the separately placed 0101 row; the 0101 placement was already there. NOTE.
- Gates, all clean:
- gofmt;
- go vet, with and without
-tags=integration; - complexity check;
- internal/repo tests;
- English-only check;
- doc link check;
- staticcheck with
-tags=integrationon internal/server.
- No allowlist entry, no raised limit, no skipped test. The known-failure list
inFlightLeaksis gone, which is a list shrinking. Commit typefix.
Revert check. With 0103_erased_person_guard.sql removed, three tests fail:
- TestAWriteInFlightDuringTheErasureLeavesNothingOfThePerson: 19 subtests (every site but "direct message");
- TestEveryForeignKeyToUsersRefusesAnErasedAccount;
- TestOtherMembersStillWriteAboutAnErasedAccount.
All pass with the fix.
Merge. Against fix-h-l10-03 there is a textual conflict in SECURITY.md: both change the account-deletion paragraph around "Export is built in the ... (not touched by the orphan cleanup)". Fix 05 carries the old parenthesis. Resolve it by keeping both texts. It merges without conflicts with fix-h-l10-01 (already its ancestor), fix-h-l10-02 and fix-h-l5-04.
Verdict on the fix: MERGE AFTER FIXES
- Required: SECURITY.md must not claim "a report of their message names no account" while a report written in flight keeps the person's text, username, display name and id in its snapshot (probe above). Either:
- correct the sentence and record the snapshot gap as a follow-up finding (recommended for this patch release); or
- extend the guard: a BEFORE INSERT trigger on message_reports that blanks snapshot entries of erased accounts, with the same KEY SHARE wait on each id it finds.
- Required at merge: resolve the SECURITY.md conflict with fix-h-l10-03, and renumber one of the two 0103 migrations.
- NOTE: the hint text of the schema test (
CREATE OR REPLACE TRIGGER).
Would a narrower fix be better for a patch release?
Not in my judgement. A narrow version would be the users-trigger tightening plus re-checks in the message, upload and profile routes. It would cover the visible part: text, name, files. But it would leave the 14 other routes and every future write. There is no single place in the request path to put it; the author counts 85 transactions and about 112 pool-level writes.
The trigger is uniform. It takes no lock the foreign key check did not already take, and the full suite of every package passes with it. The restore path and the hot-path latency are unaffected, and a schema test keeps it complete. The real risks are small:
- a silent "drop" in admin writes racing an erasure;
- the 500, which the client turns into a sign-out.
If the owner wants the smallest possible change for 3.0.x, the fallback is users_deleted_is_final alone plus the messages and media guard. That fallback would leave the reaction, RSVP, comment and scheduled-message leaks open. I do not recommend it.
What I did not check
- The upgrade drill; the 1000-session load test.
outgoing_queuepayloads written by others after an erasure.- The real scripts/backup.sh and restore.sh: not run, per the rules. I reproduced their pg_dump/psql form by hand.
- A multi-instance deployment: not supported by the app.
- LastModified and other object-store behaviour: not relevant here.
- The desktop client.
Round 2: commit 056dffc62 (on top of 01423ef35)
What changed: a new BEFORE INSERT trigger message_report_erased_people on message_reports, plus erased_report_entry(). The unreleased migration 0103 was extended in place. Also: the SECURITY.md correction, the drop mode named in the docs, the schema-test hint, and the migration number moved out of comments.
Checked on a fresh worktree of 056dffc62 (removed afterwards). Probes were scratch files only; nothing pushed.
My two probes again (erasure held before its anonymise statement, real routes)
- Bob reports alice's message: 201 after 510 ms. Snapshot:
"content": "","username": "deleted-<id>","display_name": "Deleted member".reported_user_idis NULL. No row in any table holds her word. - Bob reports carol's message, with alice's message in the context: 201 after 510 ms (it now waits for the erasure). Alice's context entry is scrubbed the same way, carol's entry is unchanged, and no row holds alice's word.
Is the replacement byte-identical to scrubReportCopies?
- A report taken before the erasure (alice's message reported, two alice entries and one of carol's in the context) was scrubbed by the erasure.
- The original copy, saved before the erasure, was inserted again by SQL after it, so the trigger rewrote it.
snapshot::textof the two rows is identical byte for byte, and differs from the original.- Both paths write the tombstone username from the same value:
users.usernameis set by anonymise toDeletedUsernamePrefix + shortID. - One difference in principle: the scrub rebuilds the object from
messageandcontextonly, while the trigger usesjsonb_setand keeps any other top-level key. Today's copies (member, automod and raid) have only those two keys. NOTE.
Automod and raid copies
- Raid alert shape (
snapshotMessagewith a zero user id): stored unchanged, compared against its canonical jsonb text. The zero UUID passes the pattern, and the users lookup finds no row. - Automod-style copy of the erased member, inserted after the erasure: scrubbed to
"content": "", the tombstone name and "Deleted member". - automod hits use
INSERT ... ON CONFLICT DO UPDATE. The BEFORE INSERT trigger also runs when the row then turns into an update; it only rewrites the proposed row, so the update ofhit_countis unaffected.
The new lock and deadlocks
A member's copy names at most 21 messages: the reported one, 15 before and 5 after (ReportContextBefore and ReportContextAfter). So at most 21 distinct users rows are locked FOR KEY SHARE. They are locked in id order: ORDER BY u.id FOR KEY SHARE, where the lock step sits above the sort.
Other writers:
- Another report: KEY SHARE does not conflict with KEY SHARE, and the reporter's own FOR NO KEY UPDATE does not conflict with KEY SHARE either.
- Bans and timeouts: UPDATE users with non-key columns takes FOR NO KEY UPDATE, which is compatible with KEY SHARE.
- Role changes: insert into user_roles, which takes KEY SHARE.
- A username change: a unique key column, so FOR UPDATE. It makes a report wait briefly, but it needs nothing the report holds, so there is no cycle.
The cycle the author names, constructed. Alice had timed out bob: timeout_by = alice, set by SQL with the timeout already over, so bob may still report. The cycle:
- bob's report transaction takes
SELECT 1 FROM users WHERE id = bob FOR NO KEY UPDATE, then waits for alice's row; - alice's erasure holds alice FOR UPDATE and reaches
UPDATE users SET timeout_by = NULL, which needs bob's row.
I ran both orders:
| Order | Erasure | Report | Left behind |
|---|---|---|---|
Erasure held before its timeout_by step; bob reports during the hold, so the report waits first | 204 after 1.05 s, account erased | deadlock detected (40P01), so 500 | none of her text in any row |
| Report held after its reporter lock, before its insert; the erasure waits for bob's row first, then the report waits for alice | 204, account erased | 40P01, so 500 | none |
In both orders PostgreSQL aborts the report. The deadlock check that finds the cycle belongs to the transaction that closes it, and that is always the report's insert. The erasure goes through. The reporter gets a 500 and can report again, after which the copy is scrubbed.
Pre-existing? With the whole migration removed, both orders deadlock the same way and with the same outcome when the reported message is alice's: the foreign key check of reported_user_id takes the same KEY SHARE. What is new in this round is only the case where alice appears in the context alone. Acceptable: rare, nothing leaks, the erasure always wins (when the report closes the cycle; when the erasure is the one aborted, it is retried since the H-L10-06 fix). NOTE.
Cost of a report insert
Bulk insert of 2000 reports, each copy naming 21 distinct members, run three times alternating:
| Notes | Per row |
|---|---|
| With the trigger | 133-185 µs |
| With the trigger disabled | 41-47 µs |
So about 0.1 ms more per report, for a rare action that a member starts. Not on a hot path.
Dump and restore again
On a throwaway postgres:18-alpine container:
- The world: an erased member's full footprint; a report of her message taken before the erasure (scrubbed by it); and, after the erasure, a report of her message and one of bob's message with her in the context (scrubbed by the trigger).
- Dump and restore: plain
pg_dump --no-ownerinto an empty database withpsql -v ON_ERROR_STOP=1. Exit 0, no error. All 75 tables are identical by row count and md5, includingmessage_reports(3 rows). - Trigger order:
CREATE TRIGGER message_report_erased_peopleis at dump line 4574, afterCOPY public.message_reportsat line 2161, so it never fires during a restore.
Suites and gates, by exit code
- Full internal/server integration suite: ok, 247 s, exit 0. internal/db integration suite: ok.
- Gates: each returned 0:
- gofmt;
- go vet, with and without
-tags=integration(server and db); make complexity-check;- internal/repo tests;
node scripts/check-english-only.mjs;node scripts/check-doc-links.mjs;- staticcheck v0.8.2 with
-tags=integration(server and db).
The other answers
- SECURITY.md: the report sentence is now true. Checked by the probes, and the scrub of existing copies covers context entries (byte compare above).
- The drop mode is named, with its 2xx.
- The schema-test hint says CREATE OR REPLACE TRIGGER with all of the table's guarded columns.
- "0103" occurs only in the file name and in doc/upgrade.md (the section title, its text and the table row):
git grepover *.go, *.md and *.sql. - Editing the migration in place: acceptable. The file is unreleased, and the checksum gate only protects applied migrations.
Still open at merge (no change needed on the branch):
- a textual conflict in SECURITY.md with fix-h-l10-03 (still present per
git merge-tree); - renumbering one of the two 0103 migrations (with fix-h-l5-04, which merges without conflicts and does not interact).
Round 2 verdict: MERGE
No new blocker. The open items are merge work (the SECURITY.md conflict, the renumbering), not branch work.
Not checked in round 2: the 1000-session load test and the upgrade drill (as in round 1); a report by a moderator through the admin queue (the same insert path, so no difference expected).