Today's red-team run aimed at the PostgreSQL migration lock checker. It reads a migration file and points out the statements that can lock a table while it runs. The run seeded 38 small faults into its engine, its statement splitter and its CLI, and ran the tool's own tests after each one. 29 broke a test. Of the 9 that got through, 4 now have a test that catches them. The other 5 change nothing a user can see. The run also found five real bugs while reading the code as an attacker. All five are fixed.
The worst one: two pairs of quotes
The splitter blanks out comments and string literals so that a CREATE INDEX inside a comment can't produce a finding. It also keeps quoted identifiers such as "users" as code, and replaces the quote marks with spaces. That is fine for "users". A schema-qualified name breaks it:
ALTER TABLE "public"."users" ALTER COLUMN "email" TYPE bigint;
After masking, that reads public . users. That's three tokens, and every check's name pattern expects one. The column-type check never matched, so the tool reported nothing for a statement that rewrites every row under an exclusive lock. Without the quotes, public.users, the same statement was flagged. SET NOT NULL on the quoted form was still flagged, but it named the table public.
pg_dump writes names in this quoted, schema-qualified form, and so does any tool that quotes every identifier. The fix rejoins a dot between two names in the masked text, so every check sees public.users.
Four smaller ones
DROP INDEX CONCURRENTLYgot a false warning. The README says it is one of the safe forms and isn't flagged. The engine still called it an exclusive lock and asked for alock_timeout. It takes SHARE UPDATE EXCLUSIVE.- Three ACCESS EXCLUSIVE statements went unflagged.
REFRESH MATERIALIZED VIEW(withoutCONCURRENTLY),DROP MATERIALIZED VIEWandLOCK TABLE(whose default mode is ACCESS EXCLUSIVE) were never flagged for a missinglock_timeoutand never counted toward the "several locks in one transaction" check. - Plain
DROP INDEXskipped the lock accounting. It had its own finding, but that branch returned early, soBEGIN; DROP INDEX i; TRUNCATE b; COMMIT;didn't count as two exclusive locks held together. public.tandtwere different tables. The tool stays quiet about indexes on a table the same file just created.CREATE TABLE public.tfollowed byCREATE INDEX ... ON tstill got a warning.ON ONLY talso read the table name asONLY.
The faults that got through
- VACUUM FULL. A test checked that
VACUUM FULLgets the full-rewrite finding, but none checked that it also needs alock_timeout. One now does. - CLI output. The heading's 88-column wrap and the two-space JSON indent had no exact test, so changing either went unnoticed. Two tests now pin them.
- Five equivalent faults. Five survivors in the splitter and the timeout tracker can't change the output. Examples are an off-by-one past the end of the text that is clamped one line later, and a field nothing reads. The record says why for each.
The catch rate went from 76% to 87% of the faults seeded. The remaining 13% are the five equivalent faults.