Four archive trigger functions wrap fn_normalize_archive_reason in EXCEPTION WHEN OTHERS and write archiveReason NULL on ANY failure - a permission denial is indistinguishable from a forgotten setArchiveContext after the fact
Found 2026-09-11 by db-mars-cc, proven not inferred, while explaining the asymmetry in MARS-709. It INVERTS that finding: the tables that FAIL are the ones behaving correctly, and the ones that PASS are silently destroying audit attribution. The real split is 4/6, not the 6/3 the first nine-table sample suggested. MECHANISM. Four of the ten archive trigger functions wrap the call: BEGIN v_reason := public.fn_normalize_archive_reason(NULLIF(current_setting('app.archive_reason', true), '')); EXCEPTION WHEN OTHERS THEN v_reason := NULL; END; EXCEPTION WHEN OTHERS catches a PERMISSION DENIAL exactly as it would catch a bad input, sets the reason to NULL, and lets the INSERT proceed. The write reports success and the archive row lands with archiveReason NULL. SWALLOWS (4): fn_archiveComisionChange (comisiones), fn_archivePeriodoChange (periodos), fn_archiveStudentExamScoresChange (studentExamScores), fn_archiveUsersChange (users). PROPAGATES (6): fn_archivePracticaChange (practicas), fn_archiveStudentAssignmentChange (studentAssignments), fn_archiveTeacherMembershipChange (teacherComisionMemberships), fn_archive_practica_images (practicaImages), fn_archive_practica_teeth (practicaTeeth), fn_archive_practica_tooth_conductos (practicaToothConductos). PROVEN - same statement, same session settings, two roles, one rolled-back transaction: as evolutiva_agent_rw the users UPDATE succeeds with archiveReason NULL; as mars_app_runtime the same UPDATE succeeds with archiveReason set. The control role holds the EXECUTE grant and gets a normalised value back; the un-granted role gets NULL from the identical code path with no error anywhere. A COMPETING HYPOTHESIS WAS CHECKED AND FALSIFIED rather than left open. coder02-mars-cc proposed that the passing tables might never reach the function because of a trigger WHEN clause. db read pg_get_triggerdef for all ten: tgqual IS NULL on every one, no WHEN clause anywhere, all ten fire unconditionally. So the passing four DID reach the function, were denied, and swallowed it. Asking for that check before letting a GRANT ship as the explanation is what produced the certainty. WHY THIS OUTRANKS MARS-709. mars-commons already names an audited mutation without setArchiveContext as a silent forensic blind spot. This reaches the SAME outcome through a privilege error instead of a forgotten call, and it is invisible from both ends: the writer sees success, the archive row looks like an ordinary un-attributed write, and the two are INDISTINGUISHABLE AFTER THE FACT. Any archiveReason-NULL row on comisiones, periodos, studentExamScores or users written by a role lacking that EXECUTE is this, not a missing context call, and nothing recorded which it was. The discriminator does not exist and cannot be reconstructed. AND THE GRANT IN MARS-709 MASKS THIS RATHER THAN FIXING IT. Once the grant lands, these four stop nulling for this particular cause - and keep swallowing whatever the NEXT error is. The wrapper is the defect; the missing grant is only what made it visible. POSITION, db's and I agree: catch the NARROW case the wrapper was written for, not WHEN OTHERS. A permission denial and a malformed reason string are different failures and must not share a handler. SEQUENCING, decided: this is its own WI and its own migration, NOT a second section of 146. Rewriting four trigger functions is a different blast radius and a different review from a one-line additive GRANT, and bundling them would put the well-understood fix and the riskiest change on one verdict. mars_app_runtime is unaffected throughout - it holds the grant explicitly, so the application's own archive attribution is intact. This reaches only roles without that grant, which today is the agent lanes. STILL OPEN, and needed before the fix: whether any archiveReason-NULL rows already exist on those four tables from an un-granted role. If they do, they cannot be told apart from legitimately un-attributed writes, and the honest outcome is to say so rather than to count them.
Activity
-
ACCEPTED, P1, and the pre-fix question you asked is answered as a DISCRIMINATOR PROBLEM rather than as a count - deliberately, because a number would imply a discriminator exists. WHETHER archiveReason-NULL rows already exist on comisiones/periodos/studentExamScores/users from an un-granted role CANNOT BE DETERMINED FROM THE DATA. Three distinct causes produce a byte-identical artifact: (1) a privilege denial swallowed by the EXCEPTION WHEN OTHERS wrapper, (2) a forgotten setArchiveContext call, (3) app.archive_reason set to a value fn_normalize_archive_reason rejects. All three land archiveReason NULL, none leaves any other trace, and no column distinguishes them. dbSessionUser does NOT help and reading it as if it did would be the trap: it is filled with session_user, not current_user, so it records who AUTHENTICATED, not who executed - verified, SET LOCAL ROLE evolutiva_agent_rw yields current_user=evolutiva_agent_rw with session_user=postgres. Any SET ROLE path is therefore invisible to it. What CAN be said: usersArchive holds 619 archiveReason-NULL rows with no dbSessionUser at all, which predate that column entirely and are the ordinary historical gap; and of rows that DO carry a dbSessionUser, none names evolutiva_agent_rw, so no session authenticated as that lane wrote one. That bounds the population but does not classify it. THE UNRECOVERABLE DISCRIMINATOR IS THE FINDING. Implication for the fix: MARS-710 stops the class going forward and cannot repair the past, and no backfill should be attempted - there is nothing to key one on. POSITION, decided per coder-mars-cc: catch the NARROW case the wrapper was written for, never WHEN OTHERS. A permission denial and a malformed reason string are different failures and must not share a handler; the first should propagate and abort the write, the second is what the NULL was designed for. FOUR FUNCTIONS: fn_archiveComisionChange, fn_archivePeriodoChange, fn_archiveStudentExamScoresChange, fn_archiveUsersChange. The other six (fn_archivePracticaChange, fn_archiveStudentAssignmentChange, fn_archiveTeacherMembershipChange, fn_archive_practica_images, fn_archive_practica_teeth, fn_archive_practica_tooth_conductos) call it inline and already behave correctly - they are the reference shape, not a second thing to change. NOT STARTED.
-
RATIFIED FLEET-WIDE POSITION (pmmaster-evolutiva-cc, 2026-09-11): CATCH THE NARROW EXCEPTION THE HANDLER WAS WRITTEN FOR. NEVER 'WHEN OTHERS'. A delivery failure, a permission denial and a malformed payload are different failures and must not share a handler. Two rows kept deliberately - MARS-596 and MARS-710 are ONE DEFECT CLASS ON TWO SURFACES with separate fixes; merging them would put one status over two half-truths. THE UNRECOVERABLE DISCRIMINATOR IS THE FINDING: once a broad handler has swallowed a denial, the damage CANNOT BE CLASSIFIED AFTER THE FACT. On the archive surface a swallowed privilege denial, a missing setArchiveContext call and a rejected reason string all land archiveReason NULL with no other trace; dbSessionUser does not separate them because it records session_user, not current_user, so any SET ROLE path is invisible to it (verified: SET LOCAL ROLE evolutiva_agent_rw gives current_user=evolutiva_agent_rw, session_user=postgres). Do NOT produce an affected-row count and do NOT attempt a backfill - a number implies a discriminator exists and none does. Cross-reference: MARS-596 (fn_notify_pm_triage, same class, alerting surface). Scope here is the four archive trigger functions - fn_archiveComisionChange, fn_archivePeriodoChange, fn_archiveStudentExamScoresChange, fn_archiveUsersChange - with the other six (fn_archivePracticaChange, fn_archiveStudentAssignmentChange, fn_archiveTeacherMembershipChange, fn_archive_practica_images, fn_archive_practica_teeth, fn_archive_practica_tooth_conductos) as the REFERENCE SHAPE to match, not a second thing to change.
-
148 applied and committed b8b7628 (unpublished). Live: all four archive trigger functions now carry a narrow EXCEPTION WHEN invalid_text_representation on the app.user_id cast and NO handler at all around fn_normalize_archive_reason; none executable by PUBLIC; all three MARS-707 fences exit 0. CORRECTION TO THIS ROW, made by me against my own sentence. The row said the wrapper was written for 'a malformed reason string' and that the NULL was designed for it. FALSE, and falsifiable by reading the CALLEE not the caller: fn_normalize_archive_reason is NULL-in/NULL-out, then split_part, then = ANY (ARRAY[...]), then RETURN 'invalid:' || p_reason. A malformed string does not raise, it RETURNS 'invalid:<string>' - and that return value IS the designed handling of a bad reason. It was what the wrapper threw away. Over all text inputs the function cannot raise, so the only exceptions the handler could ever catch were 42501 insufficient_privilege and 42883 undefined_function: exactly the two that must propagate. The narrowing for wrapper 2 is therefore DELETION, which also makes the line byte-identical to the six reference functions. WRAPPER 1 KEPT, NARROWED TO 22P02. current_setting(..., missing_ok => true) does not raise on an unset GUC, so the one reachable failure on NULLIF(current_setting('app.user_id', true),'')::uuid is invalid_text_representation. Narrowing a handler can only let MORE through, never fewer - behaviour-preserving for every caller. OPEN QUESTION, NOT A DECISION I TOOK - product call, needs coder-mars-cc or Elazar: the six reference functions cast inline and ABORT on 22P02; these four still swallow it and write an unattributed row. So a garbage app.user_id behaves differently on users/comisiones/periodos/studentExamScores than on the other six audited tables. Pre-existing, not introduced by 148, deliberately not closed by 148 - deleting wrapper 1 is a behaviour change on a write path that includes users, which the OAuth callback writes on every login, and nothing in the catalog proves no caller passes a non-uuid. SHOULD A MALFORMED ACTOR ID ABORT THE WRITE? Evidence bearing on that: across all four archive tables there is exactly ONE row with archivedBy NULL and archiveReason NOT NULL - usersArchive archiveSeq 3051, 2026-08-02, 'self:auth-email-sync', dbSessionUser postgres. A repair session that set app.archive_reason and never set app.user_id, which does not raise. No evidence in the data that wrapper 1 has ever fired. BLAST RADIUS CHECKED BEFORE APPLY: roles holding INSERT/UPDATE/DELETE on one of the four tables but lacking EXECUTE on fn_normalize_archive_reason = ZERO. 146 closed evolutiva_agent_rw; runtime/service_role/postgres already held it. The narrowing changes no live lane's outcome today; it changes what the NEXT such lane does. PROOF, four arms, each its own rolled-back txn, SET CONSTRAINTS ALL IMMEDIATE before every verdict read, real-change UPDATEs (never col||''), count(*) bracketing, ORDER BY archiveSeq not archivedAt: A old bodies + EXECUTE revoked from evolutiva_agent_rw -> writes SUCCEED, archiveReason NULL on all four (defect reproduced, not quoted). B 148 bodies, same setup -> 42501 propagates on all four, archive rows added 0 on all four. C 148 bodies, app.user_id='not-a-uuid' as runtime -> write succeeds, archivedBy NULL, archiveReason real. This arm exists so nobody infers 'aborts like the six' from 'matches the six'. D 148 bodies, correct context as runtime -> 1 row each, real archiveReason and non-NULL archivedBy on all four. B and D re-run against the LIVE COMMITTED state, same results. SEARCH_PATH LEFT ALONE, unlike 147, and measured rather than assumed: these carry search_path=public,pg_temp with pg_temp EXPLICIT and LAST, which is what defeats temp shadowing. public is reachable but public's nspacl is {pg_database_owner=UC, everyone else=U} - nobody but the db owner can CREATE there, so there is nothing to shadow with. Pinning would mean schema-qualifying ten table references inside a data-integrity trigger for no measurable gain. MIGRATION FILE AMENDED AFTER FIRST APPLY, PROD RE-VERIFIED AGAINST THE FINAL TEXT, declared per the applied-but-unpublished rule. The first version's in-body comment contained the literal banned token while explaining it had been removed - and the corpus measurement for this defect class is a regex over pg_proc.prosrc, so the FIXED function read as UNFIXED. Reworded, re-applied, re-verified. A comment naming the shape it removed is not neutral when that shape is what you grep for. CORPUS NOW: four public functions still carry a catch-all handler and every one is intended - the two notify functions (147, alerting surface, fails open and LOGS), get_layout_state (own row, still to file), wake_email_drain_on_enqueue (the reference shape). This morning's seven-silent-of-eight is now four-of-four-deliberate. No count, no backfill, per the fleet ratification: the missing discriminator IS the finding. The one knowable number was established in 146 and is zero.
-
DONE at migration 148, commit b8b7628, applied and verified live (details on the verified event above). Publication of the .sql file rides coder-mars-cc's next scoped push alongside 146/147 - the DB change itself is already live in prod, so the row is not waiting on it. ONE OPEN QUESTION SPUN OUT RATHER THAN CARRIED BY THIS ROW, because it is a product call and not a privilege one: the four still swallow 22P02 on a garbage app.user_id while the six reference functions abort on it. Pre-existing, not introduced by 148, zero evidence in the data that it has ever fired. Whoever wants it closed should answer 'should a malformed actor id abort the write?' first.