public-schema ALTER DEFAULT PRIVILEGES grants mars_app_runtime=arwd on every new table - narrow grant lists in migrations are decorative

Sub-tickets

Activity

• wi-cli-venus created · 5w ago
• wi-cli-venus spec · 5w ago
  FENCE DESIGN, drafted not applied - coder-mars-cc ruled NARROW THE DEFAULT, with the fence built first so the instrument exists before the thing it must catch. Numbers below are measured live 2026-09-11, not estimated. THE FOOT-GUN IS WORSE THAN 'a migration forgets to grant', AND IT IS NOT DETECTABLE BY GREP. All 10 fn_archive*Change trigger functions are SECURITY INVOKER (prosecdef=false, postgres-owned). An INVOKER trigger fires as whoever did the DML, so mars_app_runtime needs INSERT on all 10 archive tables - and it holds that today ONLY because the blanket default ACL gave it arwd. Of those 10 archive tables, SIX appear NOWHERE in src: practicasArchive, practicaTeethArchive, practicaImagesArchive, practicaToothConductosArchive, studentExamScoresArchive, and (as a table reference) the rest reached only through triggers. A manifest derived from application SQL would omit them, narrowing would strip INSERT, and then EVERY audited mutation in the app - practicas, users, comisiones, assignments, memberships - would begin failing at the trigger, not at the statement the developer wrote. That is the single most important constraint on this work. MEASURED BASELINE: 44 tables in public. 34 are referenced by name in src .ts/.tsx. 27 are written (INSERT/UPDATE/DELETE) in src. 10 are NOT referenced at all: accountSwitchLinks, appEventActionClassifications, dbperfSnapshots, matviewRefreshRuns, practicasIntegrityInventory, and the five archive tables above. Non-reference does NOT imply non-need - the archive tables prove it, and dbperfSnapshots/matviewRefreshRuns are written by pg_cron jobs rather than by app code. PROPOSED FENCE - a MANIFEST plus a two-directional assertion, modelled on migration 139 because every arm of that was negative-tested in rolled-back transactions. The manifest is an explicit table-to-privileges map in the migration body, assembled from THREE sources, because no single one is sufficient: (a) direct app SQL references, enumerated by grep - the only instrument that can see a call inside a TypeScript string literal; (b) THE TRIGGER GRAPH - for every INVOKER trigger on a table the app writes, every table that trigger's function writes into, derived from pg_trigger joined to pg_proc.prosrc rather than from any source file; (c) the function graph already established in 139 - SECURITY DEFINER callees are excluded, since inside a DEFINER function the current user is the owner and the runtime's own privileges are not what is checked. The assertion runs BOTH directions, and that is the point. Missing: a manifest table where mars_app_runtime lacks a listed privilege - catches the forgotten grant, loudly, in the migration that created the table. Excess: any public table where the runtime holds a privilege the manifest does not list - catches the default ACL silently re-granting arwd on a future table, which is the actual defect this WI is about. An assert-missing-only fence would pass forever under the current default and prove nothing. SEQUENCING, per coder-mars-cc: fence first as its own migration, verified green and negative-tested per arm. Then, separately, ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE the blanket table grants from mars_app_runtime, with the fence already in place to catch what that breaks. OPEN QUESTION I am NOT deciding alone: evolutiva_agent_rw also receives arwd from the same default entry, and evolutiva_agent_ro receives r. Narrowing only the runtime role leaves the identical silent-grant mechanism live for the agent roles. Narrowing all three is the coherent position but widens blast radius to the agent lanes. Flagged to coder-mars-cc for the Elazar call. NOT APPLIED. Nothing in this event has been executed against prod.
• wi-cli-venus decision · 5w ago
  Sequence CORRECTED before any DDL, on db-mars-cc's gate. My original split (fence first, then the narrowing) cannot work: the fence has an excess arm and mars_app_runtime live holds DELETE,INSERT,SELECT,UPDATE on 43 of 44 public tables, so a fence applied before the REVOKEs fails the day it lands - and a fence weakened until it passes is the decorative-GRANT failure in a fence's clothing. Approved split: migration 143 = manifest + REVOKE the excess on EXISTING tables + both-directions fence (this is the narrowing). Migration 144 = ALTER DEFAULT PRIVILEGES so FUTURE tables stop arriving pre-granted. Still inside 'narrow the runtime first', so no new authorization. Scope approved: narrow INSERT/UPDATE/DELETE only; SELECT stays on all 44 as an EXPLICIT manifest row, never an omission. Reason, db's and it is the deciding one: migration 141 flipped 64 views to security_invoker eight hours ago, which deliberately widened the runtime's required base-SELECT set to the recursive pg_depend closure of every view the app reads. SELECT is therefore the least-measured half and does not belong on the same blast radius as the well-measured half - the same argument that split agent roles off to MARS-708. DELETE is the clean win: exactly one DELETE FROM in src against a public table (supportTicketNotifySubscriptions), zero in the trigger closure, zero in the app-function closure, and fn_blockpracticasharddelete blocks it on practicas outright. Two method corrections db is making to its own manifest build, both of which would otherwise have shipped a manifest that breaks production: (1) a verb-grep misses privilege-bearing constructs that are not the verb - ON CONFLICT DO UPDATE needs UPDATE on a table that greps insert-only, FOR UPDATE/FOR SHARE need UPDATE on a table that greps read-only; (2) both arms must use has_table_privilege, not aclexplode filtered to grantee=mars_app_runtime, because a REVOKE cannot subtract from a PUBLIC grant and an ACL-by-grantee read would call a table clean while the role still holds the privilege through PUBLIC. Fence form: a persistent postgres-owned function called at the foot of 143, not a one-shot DO block - a DO block passes once and never runs again, the same passes-forever failure in a different coat. pg_cron: NO, my call. The fence is a migration-time instrument; a cron call turns it into an unowned monitor and a failing cron fence pages nobody. Open question back to db: whether the live-tables-vs-manifest direction is an explicit arm. If it is, forgetting a manifest row fails LOUD at migration time and the re-call obligation is not the invisible kind this WI exists to eliminate. Proof before apply: one superuser transaction doing the REVOKEs, SET LOCAL ROLE mars_app_runtime, a real write against every audited table so the SECURITY INVOKER archive triggers actually execute, then ROLLBACK; each arm negative-tested separately in its own rolled-back transaction. Nothing applies until all of it is green.
• wi-cli-venus note · 5w ago
  Correction owed in the FLEET commons, recorded so it is not lost. I pushed fabf902 to pensanta-websites before db's gate arrived; its new evolutiva-db.md section ends 'a coverage fence ... built FIRST, so the instrument exists before the thing it has to catch.' That is the sequence db falsified an hour later and it is now wrong in a doc four projects read - a pluto or venus reader following it attempts fence-before-REVOKE, fails on day one, and weakens the fence to make it pass. Not re-pushing for it alone. The fix ships with migration 143 and cites that SHA, so the correction arrives with the measurement rather than as a second unsourced assertion. Queued in my commons pending queue as item 16.
• wi-cli-venus note · 5w ago
  Migration 143 applied and verified live; local handoff commit bdda605, awaiting publication by coder-mars-cc. 71 of 174 table privileges revoked for mars_app_runtime: DELETE 43 tables -> 1, UPDATE 43 -> 21, INSERT 43 -> 37, SELECT untouched at 44. Manifest (fn_runtime_privilege_manifest, 44 rows / 103 declared privileges) built from four sources: INVOKER trigger-function callee closure, the closure of the 15 INVOKER functions the app calls directly, grep over src kept only for SQL in TypeScript string literals, and 26h of pg_stat_statements filtered to the runtime role. Fence (fn_assert_runtime_privilege_manifest) has five arms - stale manifest row, unlisted public table, missing privilege, excess privilege, and a write path around the manifest via an owner-rights auto-updatable view - each negative-tested in its own rolled-back transaction and confirmed able to fail, plus a control and a GRANT-TO-PUBLIC case. Both privilege arms use has_table_privilege, not aclexplode by grantee, because a REVOKE cannot subtract from a PUBLIC grant. Pre-apply proof fired real writes as the runtime role against every audited table so all ten INVOKER archive triggers, both admin-invariant triggers, the unread-count trigger, four ON CONFLICT upserts and three FOR UPDATE locks executed, then rolled back. NOT done: SELECT is not narrowed (141 widened the required base-SELECT closure hours earlier); evolutiva_agent_rw/_ro untouched, deferred to MARS-708; future tables not covered until migration 144 does the ALTER DEFAULT PRIVILEGES, and until then assertion 2 is what catches the next CREATE TABLE. Fence is a migration-time instrument, deliberately not scheduled in pg_cron.
• wi-cli-venus verified · 5w ago
  PTD on c376f52 (publishes db-mars-cc's bdda605, migration 143). Live /api/health reports 2.79.41, deploymentId dpl_9hszjSU1CiJXKpX69Jfkg6KXHmm7; REMOTE package.json on main read at check time is 2.79.41. Match, and this one is about MY push - nothing landed between the push and the read, unlike the 8593821 confirm which matched against coder02's concurrent deploy. Version behaviour re-derived for THIS push rather than carried forward: --only named the three paths already committed in bdda605, so nothing was left to stage from them and the commit gitpush.sh created contains package.json alone - but the path list classifies CODE on the .sql extension, so the Markdown-Only test did not fire and the version bumped. That is the correct instrument here: the deploy carries a real version number the ahead-range can be pinned to. Review depth, both ahead SHAs classified because the publishing commit alone misreads: bdda605 DEPTH=deep CHECK=2a on the known-deep migration/schema paths; c376f52 DEPTH=shallow, FILES=package.json - true about its own contents and silent about what it published. coder02-mars-cc cold-briefed with both. Pre-publish checks I ran rather than delegated: all seven newly read-only tables greped against src for INSERT/UPDATE/DELETE - zero hits, accountSwitchLinks has no reference in src at all, systemSettings is read-only through settings.ts and email-domains.ts. Read the five fence arms and the explicit-rather-than-computed REVOKE rationale. Still open: coder02's verdict on bdda605, then 144 (the ALTER DEFAULT PRIVILEGES narrowing) is GO.
• wi-cli-venus note · 5w ago
  TWO CORRECTIONS to the record of c376f52, both raised by db-mars-cc AFTER I published. The commit body is immutable and carries both errors, so this event is where they live. Neither changes the migration; both change what the record claims. My GO for 144 crossed these in flight and stands - the re-run came back green. 1. EVIDENCE RETRACTION, and it is the kind that matters because it was load-bearing in three places at once (the published commit body, both DMs, and my earlier WI note). The pre-apply proof did NOT fire 'both admin-invariant triggers'. trg_users_admin_invariant and trg_userroles_admin_invariant are DEFERRABLE INITIALLY DEFERRED (tgdeferrable=t, tginitdeferred=t in pg_trigger); a deferred constraint trigger's check is queued to COMMIT, and a proof that ends in ROLLBACK discards the queue unexecuted. The proof's other claims stand - all ten SECURITY INVOKER archive triggers, the unread-count trigger, the four ON CONFLICT upserts and the three FOR UPDATE locks are immediate and did run. Re-run against the LIVE COMMITTED state, so it tests production rather than a re-applied body: transactional GRANT, SET LOCAL ROLE mars_app_runtime, UPDATE users, SET CONSTRAINTS ALL IMMEDIATE - both invariant triggers executed. The re-run also caught something the original missed: a no-op updatedAt UPDATE never reaches the deletedAt transition, so fn_cascadeSoftDeleteUserRoles's own UPDATE on userRoles had never been exercised at all. A genuine soft-delete of a non-admin user cascaded one userRoles row and then fired trg_userroles_admin_invariant. Fence still passes, rolled back, no privilege gap. Green, but green for a reason that is not the test: the invariant chain is EXECUTE-only (fn_lock_admin_invariant is an advisory lock, fn_admin_invariant_ok is SECURITY DEFINER) and SELECT was never revoked, so 143's table REVOKEs could not have broken it. That is why it stayed green - not evidence the test was unnecessary. A deferred trigger inside a rolled-back proof is a general trap: it reads as exercised and is structurally incapable of running. 2. THE ROLLBACK RECIPE IN THE COMMIT BODY OVER-RESTORES. 'GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO mars_app_runtime' does not return to the pre-143 state - it also hands back UPDATE and DELETE on supportActionIdempotency, which 143 never took, because migration 140 made that table an append-only ledger (SELECT,INSERT). Blanket-restoring would silently undo 140. CORRECTED RECIPE: run the blanket GRANT, then immediately REVOKE UPDATE, DELETE ON public."supportActionIdempotency" FROM mars_app_runtime. It is the same defect class 143 exists to fix - a blanket grant wrong in a direction nobody looks at. 3. The seven read-only tables closed with pg_stat_user_tables rather than my src grep alone: accountSwitchLinks, appEventActionClassifications, lookupOptions, systemSettings all 0/0/0 ins/upd/del. matviewRefreshRuns 8 ins and matviewRefreshLog 8 upd, both pg_cron writing as postgres, consistent. dbperfSnapshots has exactly one INSERT with no visible writer anywhere - no cron job, no function, no src reference - named rather than rounded to zero. Nothing contradicts the manifest. 4. Runtime-error window after the REVOKEs went live: zero clusters project-wide in 2h (Vercel get_runtime_errors). Bound, because absence of exercise is not verification: 2h of organic traffic exercises the hot paths and says nothing about cold ones, which is exactly where a missing manifest privilege would hide.
• wi-cli-venus decision · 5w ago
  144 scope SETTLED before DDL, and my spec was wrong again in the direction that matters. I wrote 'a new table now lands with NO runtime grant'. Taken literally that revokes SELECT from the default too, and db-mars-cc measured what that costs. ALTER DEFAULT PRIVILEGES objtype 'r' COVERS VIEWS, NOT TABLES ALONE. public holds 44 tables and 91 views/matviews. All 91 views have a non-NULL relacl granting mars_app_runtime SELECT, and not one was granted by hand - every one is migration 127's default ACL materialized into relacl at CREATE VIEW time (vRecurringErrors and vProfileImageUploadEvents read arwd, vAuthUserIdentity reads r). The fence filters relkind='r', so it cannot see a view at all. That makes the failure shape asymmetric. A new TABLE with no manifest row is caught by assertion 2 at MIGRATION time, loudly, before anything ships. A new VIEW created without an explicit grant arrives with no runtime SELECT and fails as 'permission denied for view X' in PRODUCTION, on whatever route reads it, possibly weeks later on a cold path - in the one direction the instrument is blind. Mars creates views constantly; 141 touched 64 of them the same day. Trading a migration-time failure for a production-time one is the exact inverse of what this pair of migrations is for. DECIDED: 144 revokes INSERT, UPDATE, DELETE from the default and LEAVES SELECT. A new table then arrives readable and unwritable, and detection is unchanged - because THE LOUDNESS COMES FROM THE MANIFEST, NOT FROM THE ABSENCE OF THE GRANT. Assertion 2 raises on a missing manifest row whatever privileges the table holds. I had the mechanism wrong, not merely the scope. All of migration 127's actual damage - a new table arriving silently WRITABLE - is closed either way. Residue accepted and named rather than hidden: a new table still arrives with SELECT it may not need. It is a read privilege on an empty new table, visible to assertion 2 the same day, and narrowing it belongs with the SELECT work that 141 made unmeasurable this week. TWO SCOPE EXCLUSIONS, both approved. SEQUENCES untouched: the objtype 'S' default gives rU and USAGE is what nextval needs - the archive tables' archiveSeq columns and accessRequests.requestNumber are real INSERT paths the runtime holds INSERT on, so revoking sequence USAGE would break exactly the writes 143 just proved. FUNCTIONS untouched: nothing to narrow. ONE CORRECTION to the function rationale, mine, and it must not go into the body the way it was proposed - do NOT write that the objtype 'f' default is why functions are clean. Mars REPRODUCES the supautils PUBLIC-EXECUTE force-grant (fleet probe 2026-07-19, Mars YES), which re-grants at CREATE regardless of default privileges and is unchangeable by any DB role including postgres. ALTER DEFAULT PRIVILEGES is not a control on that surface at all; the standing per-migration explicit REVOKE is. 'Nothing to narrow' is right; 'the default keeps them clean' would make a future reader trust an instrument that does not exist. 144 also gets its own both-directions fence asserting the postgres-owned default ACL for objtype 'r' contains EXACTLY the expected privilege set for mars_app_runtime, so a future migration silently re-widening the default is caught - that absence of assertion is why 127 stayed invisible as long as it did. Both arms negative-tested.
• wi-cli-venus note · 5w ago
  144 applied + committed befedd7 (unpublished handoff). postgres-owned default ACL for public objtype 'r' now mars_app_runtime=r (was arwd): relations created from here arrive readable, not writable. Existing tables unchanged (DEL 1 / UPD 21 / INS 37 / SEL 44 of 44). SELECT deliberately LEFT in the default, narrower than the original spec: objtype 'r' covers views, all 91 Mars views hold SELECT from migration 127's default rather than a hand-written grant, and the 143 fence filters relkind='r' so it sees none of them - revoking SELECT would have converted a migration-time failure into a production-time 'permission denied for view X' on cold routes. Decisive mechanism (coder-mars-cc): the loudness comes from the manifest, not from the absence of the grant - assertion 2 raises on a missing manifest row whatever privileges the table holds. Proven, not asserted: new table and new view both arrive SELECT-only and the manifest fence still rejects the new table by name. New fence fn_assert_runtime_default_privileges() asserts the default ACL in both directions; 5 negative tests all fired, incl. the pg_default_acl ROW being deleted outright (an absent row must read as MISSING, not nothing-to-check) and a derived privilege universe (ALL re-grant reports MAINTAIN/REFERENCES/TRIGGER/TRUNCATE without a hardcoded list). Sequences untouched with a reason: mars_app_runtime holds rU on objtype 'S' and USAGE is what nextval needs for the 10 archiveSeq columns + accessRequests.requestNumber. FUNCTIONS: ALTER DEFAULT PRIVILEGES is NOT a control there at all - measured, a new public function arrives with proacl {=X/postgres,...}, PUBLIC EXECUTE force-granted at CREATE by supautils regardless of the objtype 'f' default, undisableable by any DB role. My earlier 'the default has no PUBLIC entry so the functions are clean' was wrong about the mechanism; the clean ones are clean because their migrations explicitly REVOKEd. NEW FINDING, not fixed here: 16 public functions still hold PUBLIC EXECUTE, two of them created by migration 143 itself (fn_runtime_privilege_manifest, fn_assert_runtime_privilege_manifest); all 16 SECURITY INVOKER; anon+authenticated hold EXECUTE on all. NOT yet established whether PostgREST exposes them as RPC - anon holding EXECUTE is not anon being able to call it. Proposed as its own migration after that measurement, not folded into 144.
• wi-cli-venus note · 5w ago
  PostgREST exposure MEASURED live 2026-09-11 12:07-12:20 UTC (not inherited from the 2026-07-19 fleet claim). PostgREST's own logs report db-schemas=pg_pgrst_no_exposed_schemas, db-extra-search-path=public,extensions - Supabase's disabled-Data-API sentinel - and it consequently fails schema-cache load with 3F000 'schema pg_pgrst_no_exposed_schemas does not exist'. Four real anon GETs with the publishable key: /rest/v1/rpc/fn_runtime_privilege_manifest 503, /rpc/fn_assert_runtime_privilege_manifest 503, /rpc/fn_set_updatedAt 503, /rest/v1/lookupOptions?select=value&limit=1 503, all {"code":"PGRST002"}, all logged by PostgREST as user anon. Flat ~555 log lines/hour across 2026-09-10T12:30Z-2026-09-11T12:20Z with no step change at 143 or 144, so it predates both. Corroborating DB-side: anon and authenticated hold SELECT on 0 of 44 public tables; authenticator rolconfig has no pgrst.db_schemas, so the setting is project config with NO SQL read path. VERDICT: the PUBLIC EXECUTE grants are hygiene, not a live hole - BOUND: the only thing making them hygiene is a dashboard toggle that leaves no DB-side trace when flipped; no migration, fence or catalog read can detect the change. Unverifiable-state class, belongs in mars-unverifiable-state.md with owner+date+'not queryable over MCP'. Also: PGRST002 / 'Attempting to reconnect' in Mars postgrest logs is the EXPECTED steady state of a disabled Data API, not an outage. COUNT CORRECTION: 17 PUBLIC-executable public functions, not 16 - the 17th is fn_assert_runtime_default_privileges, created by migration 144 itself, whose own header states that the only control there is an explicit REVOKE ... FROM PUBLIC. coder02 HIGH on befedd7 CONFIRMED LIVE and one case wider than reported: the fence's WHERE a.grantee = v_role filter is defeated by (a) ALTER DEFAULT PRIVILEGES ... GRANT ... TO PUBLIC and (b) GRANT <other_role> TO mars_app_runtime (membership inheritance) - both proven in rolled-back transactions, new table shows has_table_privilege UPDATE=true, fence did not fire in either. has_table_privilege cannot rescue this as it did in 143 because there is no relation to ask about yet. Fix for 145: CASE WHEN a.grantee = 0 THEN true ELSE pg_has_role(v_role, a.grantee, 'USAGE') END - CASE not OR, since SQL does not guarantee short-circuit and pg_has_role on oid 0 must not be evaluated. Note the 143 manifest fence DID catch variant (b) via its unlisted-table arm - a second arm keyed on a different property covering the first arm's blind spot. 144 rollback recipe (exact): ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO mars_app_runtime; DROP FUNCTION public.fn_assert_runtime_default_privileges(); nothing else in 144 touched state. 145 proposed, awaiting GO: (a) fence-fix CREATE OR REPLACE + 2 new negative tests, (b) 17 REVOKE EXECUTE FROM PUBLIC each with its own proof - a trigger FIRING is not EXECUTE-checked but a NESTED CALL from an INVOKER function IS, so each needs its nested-caller question answered separately, (c) both new fence functions get their own REVOKE FROM PUBLIC in the same migration.
• wi-cli-venus verified · 5w ago
  PTD on a285c3e (publishes db-mars-cc's befedd7, migration 144) and the record entry that followed it. a285c3e: live /api/health 2.79.43, deploymentId dpl_3PBUmPYYU8uPHusmdAv1qAfRMTc4, REMOTE package.json on main 2.79.43 read at check time. Match, about my own push. coder02 returned PASS:befedd7 deep, PASS:a285c3e shallow. MY DEFECT ON THIS PUSH, recorded because the branch carries the evidence permanently. main holds TWO commits with identical bodies: 69ef947 (2.79.41 to .42) and a285c3e (.42 to .43). The first publishes nothing. gitpush.sh now gates publishing another lane's ahead-range commit behind GITPUSH_AHEAD_ACK; my first run hit that gate and printed 'Nothing was pushed. The commit above remains local.' That sentence is TRUE ABOUT THE PUSH LEG AND MISLEADING ABOUT THE STATE - the run had already bumped package.json and COMMITTED it. I re-ran with the ack, which bumped and committed again, and the push published both. The generalisable form, sharper than the failed-push-burns-a-version rule I already hold: A BLOCKED PUSH LEAVES A COMMIT, NOT ONLY A CONSUMED VERSION NUMBER, and the abort message describes only the leg that failed. Read git log after any aborted gitpush before re-running. Compounded by a second error: I told coder02 that 2.79.42 was theirs rather than checking, and had to correct it on-channel - coder02 has not pushed this session at all. coder02's HIGH on 144's fence, confirmed live by db and WIDER than first stated: fn_assert_runtime_default_privileges's excess arm filters pg_default_acl by named grantee, so a future ALTER DEFAULT PRIVILEGES ... TO PUBLIC would give every new table INSERT/UPDATE/DELETE while v_actual stays {SELECT} and the fence never raises. db proved both that variant AND a role-membership variant (GRANT evolutiva_agent_rw TO mars_app_runtime) in rolled-back transactions; neither fired the fence. Fix approved for 145: CASE WHEN a.grantee = 0 THEN true ELSE pg_has_role(v_role, a.grantee, 'USAGE') END - CASE not OR, because SQL does not guarantee short-circuit and pg_has_role on oid 0 is the evaluation to avoid. Why the same defect class reappeared one migration after 143 closed it, and this is the sentence to keep: HAS_TABLE_PRIVILEGE CANNOT RESCUE A DEFAULT-ACL CHECK THE WAY IT RESCUED 143, BECAUSE THERE IS NO RELATION TO ASK ABOUT YET. The fence must read defaclacl directly, so the by-grantee shape is forced back on the author and the grantee filter has to do the inheritance reasoning itself. The design half that paid out: 143's manifest fence DID catch the role-membership variant, through its unlisted-table arm. A second arm keyed on a DIFFERENT PROPERTY covered the first arm's blind spot - the both-directions argument working rather than being asserted. Count correction from db: SEVENTEEN functions hold PUBLIC EXECUTE, not sixteen. The seventeenth is fn_assert_runtime_default_privileges, which migration 144 itself created - in a migration whose own header states that the only control on function EXECUTE is an explicit per-migration REVOKE FROM PUBLIC, because supautils force-grants at CREATE. Writing a rule into a file is not applying it.
• wi-cli-venus note · 5w ago
  145 applied + committed 3e2e2ed (unpublished handoff). Closes MARS-707 as scoped. (a) fn_assert_runtime_default_privileges rewritten: grantee filter replaced with CASE WHEN a.grantee=0 THEN true ELSE pg_has_role(v_role,a.grantee,'USAGE') END - CASE not OR (no guaranteed short-circuit; grantee 0 is PUBLIC, not a role oid). Closes both defeats measured against the 144 version: ALTER DEFAULT PRIVILEGES ... GRANT ... TO PUBLIC (found by coder02 in review of befedd7) and GRANT evolutiva_agent_rw TO mars_app_runtime (found while proving the first). General lesson in the body verbatim: has_table_privilege cannot rescue a default-ACL check the way it rescued 143 BECAUSE THERE IS NO RELATION TO ASK ABOUT YET. (b) 17 REVOKE EXECUTE ... FROM PUBLIC in two argued groups - 14 trigger-only (>=1 live trigger, 0 nested callers in prosrc, 0 viewdef refs, 0 src hits) and 3 migration-time instruments called only as postgres. (c)/(d) new fence fn_assert_no_public_execute() raises if ANY non-C function in public is PUBLIC-executable, keyed on PUBLIC not a named role; all four of this lane's functions are in the REVOKE list. LIVE AFTER APPLY: 0 of 68 public functions PUBLIC-executable, 0 anon, 19 mars_app_runtime (was 35); default ACL unchanged at mars_app_runtime=r; all three fences exit 0. PROOF: all 17 exercised live pre-commit - 3 lookupOptions guards as postgres (runtime holds SELECT only there, stated not glossed), 11 as mars_app_runtime with SET CONSTRAINTS ALL IMMEDIATE for the two deferred admin-invariant triggers, 3 instruments as postgres. 6 negative tests + controls all fired. NEW MEASUREMENT that bounds the recurring defect: the supautils force-grant fires on CREATE, NOT on CREATE OR REPLACE - a replaced function keeps its proacl (verified rolled back), so a function revoked once stays revoked and only genuinely NEW functions are at risk. THREE OWN ERRORS caught by the proof, not by review: guard-trigger proof written against category 'endo1Tratamiento' taken from the function NAME (real category is 'tratamientoTipo' - it raised rather than passing vacuously only because a missing row was a hard EXCEPTION, not an IF NULL skip); accessRequests clone reused the sequence-backed UNIQUE requestNumber; userRoles has no updatedAt column. NOT VERIFIED: evolutiva_agent_rw/_ro also lose inherited EXECUTE on all 17 and neither lane was exercised - the argument is trigger-invocation-is-not-checked plus zero direct callers, which is reasoning not a live test. ROLLBACK: GRANT EXECUTE on each of the 17 individually + DROP fn_assert_no_public_execute() + restore 144's fence text. NEVER 'GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO PUBLIC' - that hands PUBLIC EXECUTE to the 51 functions that never had it, including those migration 128/MARS-380 revoked deliberately.
• wi-cli-venus completed · 5w ago
  CLOSED - the defect is gone, verified live today on a table created after the fix, not inferred from the migration text. The title's premise ('grants mars_app_runtime=arwd on every new table, narrow grant lists are decorative') was TRUE when filed and is now FALSE: migration 144 (befedd7) narrowed the postgres-owned default ACL for schema public objtype 'r' to mars_app_runtime=r, and migration 145 (3e2e2ed) fixed the fence that guards it plus revoked PUBLIC EXECUTE from 17 functions. PROOF INDEPENDENT OF THOSE MIGRATIONS: migration 154 created public.practicaDeletionRequests today and its post-check read the live relacl as postgres=arwdDxtm/service_role=arwdDxtm/evolutiva_agent_ro=r/evolutiva_agent_rw=arwd/mars_app_runtime=arw - the runtime's a+w came from 154's own GRANT INSERT, UPDATE line and the absent 'd' is the default no longer supplying it. So a new-table grant list is LOAD-BEARING again: what the migration writes is what the runtime gets. DECISION AS RESOLVED: the body asked narrow-the-default vs require-a-REVOKE-per-table. Answer was narrow the default for INSERT/UPDATE/DELETE and LEAVE SELECT, decided by coder-mars-cc on this row 11:58 with the mechanism correction that matters - objtype 'r' covers VIEWS, all 91 Mars views hold their SELECT from the default rather than a hand-written grant, and the 143 fence filters relkind='r' so it cannot see one; revoking SELECT would have traded a loud migration-time failure for a silent production-time 'permission denied for view X' on a cold route. Loudness comes from the 143 manifest fence, not from the absence of the grant. RESIDUE NAMED, NOT HIDDEN: a new table still arrives with a SELECT it may not need; sequences untouched deliberately (rU, USAGE is what nextval needs for the 10 archiveSeq columns and accessRequests.requestNumber); and ALTER DEFAULT PRIVILEGES is not a control on FUNCTIONS at all - supautils force-grants PUBLIC EXECUTE at CREATE, which is why 145 had to REVOKE 17 of them by hand. The forward-looking exposure the WI was actually filed on (an append-only ledger silently writable) is closed in both directions: the default no longer grants write, and fn_assert_runtime_default_privileges() raises on a future re-widening. Append-only tables still carry their own REVOKE + assertion, per migration 140.