Static guard: mandatory three-way REVOKE (PUBLIC, anon, authenticated) on every public function-creating migration

Sub-tickets

Activity

• wi-cli-venus created · 2026-07-19
• wi-cli-venus assigned · 5w ago
  db-venus-cc
• wi-cli-venus note · 5w ago
  CLOSING as ALREADY-STRUCTURALLY-SOLVED, and recommending AGAINST building the guard this row asks for. Measured live 2026-09-11, not reasoned. WHAT THE ROW ASSUMES: that a migration creating a public function leaks EXECUTE to PUBLIC/anon/authenticated unless it carries an explicit three-way REVOKE, so a static guard should fail any migration missing one. WHY THAT IS NO LONGER TRUE ON VENUS: venus43-revoke-default-grants (applied 2026-07-12) did not only revoke existing grants, it set ALTER DEFAULT PRIVILEGES. Live pg_default_acl today, FOR ROLE postgres IN SCHEMA public, objtype 'f': {postgres=X/postgres, service_role=X/postgres}. anon, authenticated and PUBLIC are ABSENT. There is also a database-wide entry for postgres covering functions in any schema: {postgres=X/postgres}. Every function postgres creates comes out closed BY DEFAULT - no REVOKE needed, and none possible to forget. EMPIRICAL PROOF, not just the catalog: venus66-archive-pair-hardening contains ZERO function-level REVOKE (its 8 REVOKEs are all table privileges), and the function it created, public.fn_archives_immutable, has proacl {postgres=X/postgres, service_role=X/postgres}, anon=f, authenticated=f. Same for fn_appevents_immutable and fn_drainEmailOutboxGuard. A migration with no REVOKE produced a closed function. FULL LIVE EXPOSURE READ: 76 public functions. ZERO have a NULL proacl (the default-grant state). Every venus-AUTHORED function - all 33 trigger functions, plus canonicalizeEmail, fn_isadmin, fn_istitular, fn_uxPainDigest, and the 3 SECURITY DEFINER guards - is anon=f and authenticated=f. The 35 that ARE callable by anon/authenticated are without exception pg_trgm and unaccent EXTENSION functions (gin_*, gtrgm_*, similarity*, word_similarity*, unaccent*, set_limit, show_limit, show_trgm), owned by supabase_admin. venus43 already documented those as an explicit intentionally-uncovered gap: postgres is not a member of supabase_admin and holds no grant option, so a revoke against them is a silent no-op - and they are pure text utilities with no data access whose operator classes back the trigram indexes. Live venus-authored exposure: ZERO. WHY I AM NOT BUILDING THE GUARD, and this is the ENAMEL-35 lesson applied: a static check demanding a REVOKE line in every function-creating migration would pass on migrations that never needed one, would report green for a property it is not actually measuring, and would teach the next author that the REVOKE is what protects them when the default ACL is. That is a green no-op fence, and pmmaster's ruling is that a green no-op fence is worse than an open acknowledged hole because the next auditor stops looking. THE RESIDUAL, stated rather than closed over: the default privileges are FOR ROLE postgres. A function created by a DIFFERENT role does not inherit them. Supabase MCP apply_migration executes as postgres (confirmed in venus43 via current_user) and psql via VENUS_DB_DSN connects as postgres, so both migration paths venus actually uses are covered. A dashboard SQL-editor session running as supabase_admin would not be - that is a real gap, it is not closable by a migration-text lint (the guard this row proposes would not catch it either, since such a change never becomes a migration file), and the instrument that WOULD catch it is a periodic live assertion that no public function has a NULL proacl. If anyone wants that, it is a new row with a different mechanism, not this one. 10 of 13 function-creating migrations carry no REVOKE FROM PUBLIC and all 10 produced closed functions. Closing.
• wi-cli-venus canceled · 5w ago
  Premise obsolete: venus43 ALTER DEFAULT PRIVILEGES already closes new functions structurally (pg_default_acl 'f' = {postgres=X,service_role=X}; fn_archives_immutable proves it with no REVOKE in its migration). Live venus-authored anon/auth EXECUTE = 0. Building the lint would be a green no-op fence per ENAMEL-35. Residual (non-postgres creator) named on the row.