Inventory + lock down public function EXECUTE and sequence privileges (adjacent to MARS-416 relation-grant revoke)

Sub-tickets

Activity

• wi-cli-venus created · 2026-07-12
• wi-cli-venus assigned · 5w ago
  db-mars-cc / db
• wi-cli-venus note · 5w ago
  Live-catalog triage 2026-09-11. The broad-exposure premise is FALSIFIED, the least-privilege half is REAL. Measured on the live DB: zero public functions hold a direct anon or authenticated EXECUTE grant; zero of 14 public sequences grant USAGE to anon or authenticated; exactly one of 133 tables+views is readable by either (vProfileImageUploadEvents, authenticated:SELECT, deliberate per migration 095). Fourteen functions hold PUBLIC EXECUTE via a NULL proacl, and all fourteen return trigger, so none is invocable as an ordinary call - reachable exposure is nil. Supabase's public-schema default ACLs do grant anon/authenticated, but defaclrole is supabase_admin while all 64 Mars functions are postgres-owned, so they never fire here. Read proacl as COALESCE(proacl, acldefault('f', proowner)) or a NULL reads as clean and hides the PUBLIC grant. NEXT CONCRETE STEP: drop the anon/authenticated inventory-and-revoke scope, and instead write one assertion-only migration that pins mars_app_runtime's EXECUTE coverage (currently 19 of 64 functions) against the functions actually reached from app SQL, so the canonicalizeEmail(text) class of cutover break fails at migration time rather than at production login.
• wi-cli-venus progress · 5w ago
  Migration 139 written, verified, and committed locally for the push lane at 6218900 (Local-Author: db-mars-cc). Assertion-only - grants nothing, revokes nothing. Triage outcome first, because it changes what this WI is: the live privilege state is ALREADY fully covered. All 17 non-trigger functions reached from app SQL hold mars_app_runtime EXECUTE; both transitive arms are clean; the two deliberate negative grants are intact. So there was no lockdown to perform. What was missing was any mechanism to KEEP it that way, which is what 139 supplies. Five arms. Arm 1 enumerates the 17 app-reached signatures (no catalog query can see a call living in a TypeScript string literal) and treats a signature that stops resolving as a hard failure rather than a skip. Arm 2 asserts the negative grants - cron_auth_header stays revoked, the runtime holds no EXECUTE on drain_email_outbox_if_due, and wake_email_drain_on_enqueue stays postgres-owned SECURITY DEFINER with a pinned search_path. Arms 3 and 4 are computed rather than enumerated, so they keep covering functions added later: any INVOKER caller, ordinary or trigger, reaching an ordinary function the runtime cannot execute. Arm 5 asserts no public function grants EXECUTE to anon or authenticated. Verified to PASS and, more importantly, verified to FAIL. Every arm was negative-tested inside a rolled-back transaction: revoking canonicalizeEmail and renaming get_exam_matrix_bundle trip arm 1; granting cron_auth_header and granting the drain each trip arm 2 with its own boundary named; revoking fn_assert_admin_invariant trips arm 3 via set_user_roles_atomic; revoking fn_normalize_archive_reason(text) trips arm 4 via all ten archive triggers; granting get_exam_matrix_bundle to authenticated trips arm 5. A post-test confirmed live privileges byte-identical to before. Two bounds encoded on purpose: Postgres does not check EXECUTE on a trigger function when the trigger fires, so trigger functions need no grant of their own; and arms 3/4 test INVOKER callers only, because a SECURITY DEFINER caller breaks the chain deliberately and testing DEFINER callers would demand back the exact grant migration 138 removed.
• wi-cli-venus completed · 5w ago
  Delivered as migration 139, published by coder-mars-cc at 20a5db0, v2.79.33, live and version-confirmed against remote main. Reviewed independently by coder02-mars-cc: PASS:6218900 deep, no findings. The triage finding that reshaped this WI: the live privilege state was ALREADY fully covered, so there was no lockdown to perform. Measured on the live catalog - zero public functions grant EXECUTE to anon or authenticated, zero of 14 sequences grant them USAGE, one of 133 tables+views is readable by either (vProfileImageUploadEvents, authenticated SELECT, deliberate per migration 095), and the 14 functions holding PUBLIC EXECUTE via a NULL proacl all return trigger, so none is invocable as an ordinary call. The broad-exposure premise in the original scope was falsified. What did not exist was any mechanism to KEEP the shape correct, which is what shipped instead. 139 is assertion-only - it grants nothing and revokes nothing. Five arms: (1) the 17 non-trigger functions reached directly from app SQL, enumerated, with a signature that stops resolving treated as a hard failure rather than a skip; (2) the two deliberate negative grants - cron_auth_header stays revoked, the runtime holds no EXECUTE on drain_email_outbox_if_due, and wake_email_drain_on_enqueue stays postgres-owned SECURITY DEFINER with a pinned search_path; (3) and (4) computed rather than enumerated, so they keep covering functions added later - any INVOKER caller, ordinary or trigger, reaching an ordinary function the runtime cannot execute; (5) no public function grants EXECUTE to a PostgREST client role. The enumerated-vs-computed split is the design point worth keeping. Arm 1 must be enumerated because NO catalog query can see a call that lives in a TypeScript string literal - that is precisely how canonicalizeEmail(text) was missed at the cutover and broke production login outright. Arms 3 and 4 are computed because enumeration goes stale silently. Which is why arm 1's resolve-check matters more than its grant-check. Verified to PASS and, more importantly, verified to FAIL. Each arm was negative-tested inside a rolled-back transaction: revoking canonicalizeEmail and renaming get_exam_matrix_bundle trip arm 1; granting cron_auth_header and granting the drain each trip arm 2 naming their own boundary; revoking fn_assert_admin_invariant trips arm 3 via set_user_roles_atomic; revoking fn_normalize_archive_reason(text) trips arm 4 via all ten archive triggers; granting get_exam_matrix_bundle to authenticated trips arm 5. Post-test confirmed live privileges byte-identical. An assertion authored against an already-correct state that has only ever passed is indistinguishable from one that cannot fire - that is why the failure tests, not the green run, are the evidence here. Shape flagged and deliberately not acted on: drain_email_outbox_if_due calls cron_auth_header, so the drain is one grant away from putting the CRON_SECRET bearer inside a runtime-callable path. Arm 2 now catches that specific mistake at migration time; coder-mars-cc agreed that is the correct amount of defence and nothing further is wanted.