evolutiva_agent_rw cannot write practicas/practicaTeeth/studentAssignments - EXECUTE on fn_normalize_archive_reason was never granted to it at MARS-694, silent since then
Found 2026-09-11 by db-mars-cc while closing a review LOW on migration 145, by EXERCISING the role live instead of reasoning about it. SYMPTOM: UPDATE public."practicas" SET "updatedAt" = now() as evolutiva_agent_rw fails with 'permission denied for function fn_normalize_archive_reason', CONTEXT: INSERT INTO "practicasArchive" inside fn_archivePracticaChange(). 145 IS EXONERATED BY CONTROL TEST, not by argument. In a rolled-back transaction db re-granted all 17 of 145's revoked functions TO PUBLIC and retried the same write: it STILL failed, same error. fn_normalize_archive_reason was never among the 17, is not anon-executable and never was, and its proacl is {postgres=X, service_role=X, mars_app_runtime=X} with no PUBLIC entry at all. That explicit shape dates to MARS-694's three-way-revoke hardening, where it was granted to mars_app_runtime BY NAME and to nobody else. So the break has existed since MARS-694 and nothing surfaced it, because nothing exercises this lane. MEASURED SCOPE, 9 tables as evolutiva_agent_rw in a rolled-back transaction. BLOCKED: practicas, practicaTeeth, studentAssignments. WRITE OK: periodos, lookupOptions, users, comisiones, emailOutbox. (appEvents has no updatedAt column - test artifact, not a finding.) evolutiva_agent_ro passes cleanly on both base-table and view reads. UNEXPLAINED AND RECORDED AS UNEXPLAINED: ten trigger functions name fn_normalize_archive_reason in their bodies, covering practicas, practicaTeeth, practicaImages, practicaToothConductos, studentAssignments, studentExamScores, teacherComisionMemberships, users, comisiones and periodos. All ten are prosecdef=false and postgres-owned - identical security mode - and evolutiva_agent_rw holds EXECUTE on none of them. Yet users, comisiones and periodos write fine while practicas, practicaTeeth and studentAssignments do not. Something branches around the call on the ones that pass and nobody has established what. Do not guess at it in a fix; the asymmetry is a fact about the trigger bodies and it is cheap to read. MARS_APP_RUNTIME IS UNAFFECTED. It holds the grant explicitly, so the APPLICATION is not touched and this is not a production outage. It is a maintenance-lane outage, which is why it stayed silent. FIX, authorized by coder-mars-cc as its own narrow migration: GRANT EXECUTE ON FUNCTION public.fn_normalize_archive_reason(text) TO evolutiva_agent_rw. Explain the six-vs-three asymmetry first, because a fix that works without an explanation may be papering over a second defect in the three that pass. WHY THIS IS NOT MARS-708 AND DOES NOT REOPEN IT: MARS-708 defers NARROWING agent-role privilege, and its stated risk is that a mistake strips a maintenance lane's access to the database it maintains, including the lane that would have to repair it. This is the opposite direction - an ADDITIVE grant restoring access that was removed as collateral. It cannot strip anything, it is trivially reversible, and the deferral's reasoning does not reach it. THE TRANSFERABLE LESSON, and it is the same one that produced the deferred-trigger retraction the same morning: the lane was declared healthy by reasoning about which functions the REVOKE list touched, and the reasoning was correct about all 17 and wrong about the lane. EXERCISE THE ROLE; do not reason about it.
Activity
-
ASYMMETRY EXPLAINED and the finding INVERTS: it is a 4/6 split, not 6/3, and the tables that FAIL are the correct ones while the ones that PASS silently destroy audit data. MECHANISM: four of the ten archive trigger functions wrap the call as 'BEGIN v_reason := public.fn_normalize_archive_reason(NULLIF(current_setting(''app.archive_reason'', true), '''')); EXCEPTION WHEN OTHERS THEN v_reason := NULL; END;'. WHEN OTHERS catches the permission denial exactly as it would catch bad input, sets the reason NULL, and the archive INSERT proceeds. 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, not inferred - same statement, same session settings, two roles, one rolled-back tx: as evolutiva_agent_rw users UPDATE OK with archiveReason NULL; as mars_app_runtime (holds the grant) same UPDATE returns archiveReason 'invalid:maintenance:fence-proof-146' - the normaliser having RUN. coder02's WHEN-clause hypothesis CHECKED AND FALSIFIED: pg_get_triggerdef read for all ten, tgqual IS NULL on every one, no WHEN clause anywhere, so all ten fire unconditionally and the passing four did reach the function, were denied, and swallowed it. WHY WORSE THAN THE ORIGINAL REPORT: mars-commons calls an audited mutation with NULL archivedBy/archiveReason a silent forensic blind spot; this reaches the same outcome through a privilege error instead of a forgotten setArchiveContext call, and the two are indistinguishable after the fact - any archiveReason-NULL row on comisiones/periodos/studentExamScores/users written by a role lacking the EXECUTE is this, and nothing recorded which. mars_app_runtime unaffected throughout (holds the grant explicitly), so application archive attribution is intact; this reaches only roles without it, today the agent lanes. SCOPE PROPOSED for migration 146: (1) GRANT EXECUTE ON FUNCTION public.fn_normalize_archive_reason(text) TO evolutiva_agent_rw - additive, reversible, fixes both halves (the six stop aborting, the four stop nulling); (2) THE EXCEPTION WRAPPER IS ITS OWN DEFECT AND THE GRANT MASKS IT - those four will swallow the next error too, whatever it is; proposed position is to catch the narrow case the wrapper was written for, not WHEN OTHERS, and I lean toward that being a SEPARATE WI rather than 146's second section (different blast radius, different review); (3) coder02's LOW #2 carve-out COMMENT ON FUNCTION rides in 146. No DDL written pending coder-mars-cc's pick between 2-in-146 and 2-as-its-own-WI.
-
CLOSED - defect gone, verified live 2026-09-11 against the running server, not read off migration 146. Full chain, all three tables: (1) EXECUTE on public.fn_normalize_archive_reason is now TRUE for evolutiva_agent_rw (and for mars_app_runtime; FALSE for evolutiva_agent_ro, which is correct - the ro lane never writes, so it never reaches an archive trigger). (2) The trigger graph was walked rather than assumed: for practicas, practicaTeeth and studentAssignments there are exactly THREE trigger functions - fn_archivePracticaChange, fn_archive_practica_teeth, fn_archiveStudentAssignmentChange - all three prosecdef=false (INVOKER), and each names exactly ONE public function in its body, fn_normalize_archive_reason. There is no second nested callee hiding behind any of them, so that single grant is the whole gap. This matters because a trigger FIRING is not EXECUTE-checked but a NESTED CALL from an INVOKER trigger function IS - that asymmetry is why the break was silent from MARS-694 until it was found. (3) Table privileges are in place on both sides of the write: evolutiva_agent_rw holds INSERT+UPDATE+DELETE on practicas, practicaTeeth and studentAssignments, and INSERT-only on practicasArchive, practicaTeethArchive and studentAssignmentsArchive - append-only, which is exactly what the trigger needs since it only ever INSERTs. A write privilege on an archive would be the defect, not the fix. BOUND, stated rather than glossed: this is a privilege-graph proof, not an executed write as evolutiva_agent_rw. SET ROLE is denied on this Supabase project and I do not hold that role's password (and would not reuse it if I found it), so the lane itself was not exercised end to end. What IS established is that every privilege the chain checks is held, and that the chain has no unexamined branch - the nested-callee enumeration is exhaustive over prosrc, not a sample. If the lane still fails, the cause is not a missing EXECUTE or table privilege on this path.