Add a read-only grant guard to run after each db push #317

Key: SPR-06 · Type: AFK · Part of: #327 · Blocked by: #314 · Checked against: 3bb26de

Problem

Nothing checks grants or RLS after a db push, so the next migration can reopen prod without anyone noticing. On 2026-09-28 the Supabase security linter found 7 ERROR items on prod. The cause was a hardening script that never reached prod, and no check caught the gap. This issue adds one read-only SQL guard to the repo. The maintainer runs it against prod after every db push. Zero rows means pass.

Context

Why prod drifted. Prod builds only from packages/supabase/migrations/. A local reset builds from packages/supabase/scripts/ through seed.sql. The grant sweeps in scripts/29-lint-hardening.sql §3, §4, §5 and §8 have no paired migration, so prod never ran them. Local looked clean while prod stayed open.

Why a new object starts open on prod (measured on prod, 2026-09-28, read-only catalog):

• pg_default_acl for role postgres in schema public opens new objects to anon and authenticated. They get ALL on tables (r), EXECUTE on functions (f) and USAGE on sequences (S).
• CREATE FUNCTION also grants the built-in PUBLIC EXECUTE. REVOKE ... FROM anon leaves PUBLIC in place, and anon inherits it.
• A DROP plus CREATE of a function resets its ACL to these defaults. CREATE OR REPLACE keeps the ACL.

What was applied to prod on 2026-09-28, about 13:38Z (maintainer-approved emergency fix, grants and RLS only):

• RLS on, and no browser rights, for every document_views_YYYY_MM partition, the parent document_views, document_views_daily, document_view_stats, email_bounces and "PushSentLog".
• anon lost all table-level rights on public.users. It kept a column grant without email.
• anon lost all rights on public.admin_users.
• No other table changed. No migration has ever run the 29 §3 ON ALL TABLES revoke on prod.
• 27 functions became service_role only. is_admin was revoked from anon.
• Measured after the fix: linter ERROR 7 to 0; lint 0028 (anon can run a definer function) 72 to 47; has_column_privilege('anon','public.users','email','SELECT') = false.

Where the guard can run. Only two places test the right thing:

No workflow in .github/workflows/ runs Supabase against a real database, and CI has no prod credentials. So this issue does not add a CI job.

What the guard checks. One SELECT returns one row per problem, as kind | obj:

Design notes:

• The anon read allowlist has 7 table-level grants plus users. The 7 come from scripts/29-lint-hardening.sql §3 (~85-89, ~102-103). users is a column grant (~92-95). The guard uses has_any_column_privilege, which also sees column grants, so users needs its own entry. anon_email guards email on its own.
• The authenticated allowlist is a verbatim copy of the §6 user_facing_names array (scripts/29-lint-hardening.sql ~202-236), minus the two names Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 removes (create_direct_message_channel, get_bookmark_count). It adds the three internal RLS primitives granted at scripts/13-RLS.sql ~19-22. Invoker names in the array, and the trigger update_user_online_at (scripts/10-1-func-users.sql ~140-141), do no harm: the filter skips them.
• PUBLIC is caught. has_function_privilege counts the built-in PUBLIC EXECUTE. Stop new tables and functions from opening by default #316's per-schema default revoke cannot remove that grant. So this guard is the "per-migration revoke plus guard" option in Stop new tables and functions from opening by default #316's ruling.
• An internal function counts for a role only if that role has USAGE on internal. authenticated has it (scripts/13-RLS.sql ~19). anon does not, so anon rows appear only if someone grants anon that USAGE.
• Invoker functions are out of scope on purpose. refuse_connected_app_request (scripts/31-connected-app-token-gate.sql ~10-33) and hook_block_password_tokens (scripts/32-password-sign-in-hook.sql ~5-25) are security invoker and keep explicit grants. If anon loses EXECUTE on the first one, every Data API request fails. Never extend this guard, or any sweep, to invoker functions.

Measured on the maintainer's local database, 2026-09-28 (read-only, default_transaction_read_only=on; the local database may lag the tree). 35 rows:

A reviewer re-ran the guard below on the local database at 3bb26de (2026-09-28, read-only). It exits 0 and returns these same 35 rows.

A sabotage run proved the guard fails. Inside a rolled-back local transaction, one EXECUTE grant and one SELECT grant to anon each produced a row.

Expected on prod on the first run, after #314 lands. Nobody has run the guard on prod yet, so each cell says measured or predicted.

While rls_off is 0, RLS policies still decide which rows anon can read or change on those tables. The Data API exposes no TRUNCATE. The grant layer is the second wall, and the guard reports where it is missing.

Anon can still call these 17 self-gated definer functions on prod: admin_get_failed_push_subs, admin_get_recent_push_activity, admin_get_user_notification_subs, archive_bookmark, get_bookmark_count, get_bookmark_stats, get_document_follow_state, get_document_member_previews, get_document_members, get_user_bookmarks, join_workspace, mark_bookmark_as_read, register_push_subscription, set_document_follow, toggle_message_bookmark, unregister_push_subscription, update_notification_preferences.

What to do

1. [agent] Confirm Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 has landed. Read the §6 user_facing_names array in packages/supabase/scripts/29-lint-hardening.sql (~202-236). It must no longer hold create_direct_message_channel or get_bookmark_count. If it still does, stop: this issue is blocked.

2. [agent] Add packages/supabase/scripts/check-grants.sql.

   • The name follows .cursor/docs/scripts-naming-convention.md ~92-96: kebab-case, verb-first, check-* (not the banned validate-*).
   • It has no numeric prefix, so packages/supabase/generate-seed.ts (isNumberedScript, ~21-23) never puts it in seed.sql. scripts/cleanup-orphan-chat-media.sql is the same kind of unnumbered ops file.
   • Keep it one SELECT statement, so it runs unchanged in the Dashboard SQL Editor and in psql. It calls only catalog and has_*_privilege functions, so it writes nothing.
   • Copy the public part of authenticated_definer_ok from the §6 array at HEAD. The list below assumes Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 removed exactly the two names in step 1, and Close the remaining 47 linter warnings on functions #318 has not landed. If Close the remaining 47 linter warnings on functions #318 has landed, update_user_online_at is gone from §6; drop it here too.
   • Add no package.json script. @docs.plus/supabase_back is a tool-wrapper workspace, and its bare verbs match Supabase CLI subcommands.

   -- Grant and RLS guard. Read-only; run against prod after every db push. Zero rows = pass.
   -- The public names in authenticated_definer_ok copy 29-lint-hardening.sql §6 user_facing_names.
   -- Change both in the same commit.
   with anon_read_ok(t) as (values
       ('channels'), ('messages'), ('channel_message_counts'), ('pinned_messages'),
       ('workspaces'), ('channel_members'), ('message_bookmarks'),
       ('users')), -- column grant without email; anon_email checks email
   authenticated_definer_ok(schema_name, fn) as (
     select 'public', unnest(array[
       'get_channel_aggregate_data', 'get_channel_members_by_last_read_update',
       'advance_read_cursor', 'add_reaction', 'remove_reaction',
       'toggle_message_bookmark', 'archive_bookmark', 'mark_bookmark_as_read',
       'get_bookmark_stats', 'get_user_bookmarks',
       'notifications_summary', 'get_unread_notif_count',
       'get_unread_notifications_paginated', 'get_channel_notif_state',
       'get_workspace_notifications', 'update_notification_preferences',
       'fetch_mentioned_users',
       'join_workspace', 'update_user_online_at',
       'get_document_member_previews', 'get_document_members',
       'set_document_follow', 'get_document_follow_state',
       'register_push_subscription', 'unregister_push_subscription',
       'admin_get_failed_push_subs', 'admin_get_recent_push_activity',
       'is_admin'])
     union all
     select 'internal', unnest(array['is_workspace_member', 'is_channel_member', 'can_read_channel'])),
   rels as (
     select c.oid, c.relname::text as relname, c.relkind, c.relrowsecurity
     from pg_class c join pg_namespace n on n.oid = c.relnamespace
     where n.nspname = 'public' and c.relkind in ('r', 'p', 'v', 'm', 'f'))
   select 'rls_off' as kind, relname as obj from rels
   where relkind in ('r', 'p') and not relrowsecurity
   union all
   select 'anon_write', relname from rels
   where has_table_privilege('anon', oid, 'INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER')
      or has_any_column_privilege('anon', oid, 'INSERT,UPDATE,REFERENCES')
   union all
   select 'anon_read_not_allowed', relname from rels
   where has_any_column_privilege('anon', oid, 'SELECT')
     and relname not in (select t from anon_read_ok)
   union all
   select 'anon_email', 'users.email'
   where has_column_privilege('anon', 'public.users', 'email', 'SELECT')
   union all
   select 'definer_exec', p.oid::regprocedure::text
   from pg_proc p join pg_namespace n on n.oid = p.pronamespace
   where n.nspname in ('public', 'internal') and p.prosecdef
     and p.prorettype not in ('trigger'::regtype, 'event_trigger'::regtype)
     and ((has_schema_privilege('anon', n.oid, 'USAGE')
           and has_function_privilege('anon', p.oid, 'EXECUTE'))
       or (has_schema_privilege('authenticated', n.oid, 'USAGE')
           and has_function_privilege('authenticated', p.oid, 'EXECUTE')
           and (n.nspname::text, p.proname::text) not in (select schema_name, fn from authenticated_definer_ok)))
   union all
   select 'default_acl_anon', d.defaclobjtype::text || ':' || string_agg(a.privilege_type, ',' order by a.privilege_type)
   from pg_default_acl d, aclexplode(d.defaclacl) a
   where d.defaclrole = 'postgres'::regrole and d.defaclnamespace = 'public'::regnamespace
     and a.grantee = 'anon'::regrole
   group by d.defaclobjtype
   union all
   select 'default_acl_authenticated_exec', 'f:EXECUTE'
   where exists (
     select 1 from pg_default_acl d, aclexplode(d.defaclacl) a
     where d.defaclrole = 'postgres'::regrole and d.defaclnamespace = 'public'::regnamespace
       and d.defaclobjtype = 'f' and a.grantee = 'authenticated'::regrole)
   order by 1, 2;

   The guard checks pg_default_acl for role postgres only. The supabase_admin defaults grant the same rights, but role postgres cannot change them.

3. [agent] Run it on the local database as a smoke test. Local is not the prod build path, so a local pass proves syntax and logic only. Run the command from the repo root.

   docker exec -i -e PGOPTIONS='-c default_transaction_read_only=on' supabase_db_docsplus_supabase \
     psql -U postgres -d postgres -X -v ON_ERROR_STOP=1 < packages/supabase/scripts/check-grants.sql

   Compare the rows with the local table in Context. Do not run db reset for this; it wipes shared local state.

4. [agent] Prove it fails (local only, rolled back). Never run this step against prod. From the repo root, run the guard inside a transaction that grants anon two rights and then rolls back:

   { echo 'begin;'
     echo 'grant execute on function public.consume_push_queue(integer, integer) to anon;'
     echo 'grant select on public.admin_users to anon;'
     cat packages/supabase/scripts/check-grants.sql
     echo 'rollback;'
   } | docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres -X -v ON_ERROR_STOP=1

   • Both objects are owned by postgres locally, so the grants apply. consume_push_queue is a service-only definer function. admin_users is outside the allowlist.
   • Expect definer_exec | consume_push_queue(integer,integer) and anon_read_not_allowed | admin_users. admin_users may also show anon_write if Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 has not reached the local database.
   • The output must end with ROLLBACK. Then re-run step 3; neither row may appear.

5. [agent] Document when and how to run it. Add one bullet to packages/supabase/CLAUDE.md, next to the db push bullets (~29-30). It must say:

   • After every db push, the maintainer runs scripts/check-grants.sql read-only against prod. Paths: Dashboard → SQL Editor, or psql with the prod connection string and PGOPTIONS='-c default_transaction_read_only=on'.
   • Zero rows = pass. Any row blocks the next push until it is fixed or ruled.
   • Every edit to 29-lint-hardening.sql §6 user_facing_names updates the guard in the same change.
   • The guard never covers invoker functions. Name refuse_connected_app_request and hook_block_password_tokens as the reason.

6. [maintainer] Rule on the four internal rows. internal.check_email_rate_limit, internal.cleanup_push_subscriptions, internal.clear_email_bounce_info and internal.is_email_suppressed are SECURITY DEFINER. They keep PUBLIC EXECUTE, and authenticated has USAGE on internal.

   • Callers found in the repo: scripts/07-5-email-notifications-pgmq.sql ~477 and ~613 (is_email_suppressed), ~487 (check_email_rate_limit), and the cron command at scripts/16-cron-jobs.sql ~23 (cleanup_push_subscriptions). clear_email_bounce_info has no caller. No browser caller was found.
   • They are predicted not reachable through the Data API. config.toml ~13 exposes only public and graphql_public. Prod's exposed schemas are a dashboard setting nobody has read.
   • Option A (recommended): revoke PUBLIC, keep service_role. Close the remaining 47 linter warnings on functions #318 carries it, if the ruling is on [Tracking] Supabase prod security and performance review, 2026-09-28 #327 before Close the remaining 47 linter warnings on functions #318 starts. If Close the remaining 47 linter warnings on functions #318 has already started, a follow-up issue carries it.
   • Option B: add them to authenticated_definer_ok with a one-line reason.
   • Record the ruling on [Tracking] Supabase prod security and performance review, 2026-09-28 #327. An agent then edits the allowlist or leaves it, as ruled.

7. [maintainer] First prod run. After Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's db push, run the guard read-only on prod. Paste the row list, with personal data removed, into a comment on [Tracking] Supabase prod security and performance review, 2026-09-28 #327. Compare it with the prod table in Context. A row that the table does not predict is a new finding: report it on [Tracking] Supabase prod security and performance review, 2026-09-28 #327.

   • The anon_write and anon_read_not_allowed rows have no owning issue yet. List them on [Tracking] Supabase prod security and performance review, 2026-09-28 #327 and rule who revokes them on prod. A follow-up issue then carries a named-table revoke in a migration.

Out of scope

• The prod grant and RLS port, and the 29 §3/§4 REVOKE ALL fix in scripts/: Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314.
• Revoking the existing anon table rights on prod. This issue only reports them (step 7).
• Partition creation paths and rls_auto_enable: Make new document_views partitions start closed #315.
• Default privileges on prod: Stop new tables and functions from opening by default #316.
• The 17 self-gated functions and the trigger-function revokes: Close the remaining 47 linter warnings on functions #318.
• Dropping create_direct_message_channel: Drop the dead create_direct_message_channel function #319. Prod-only drift functions and "PushSentLog": Remove prod-only drift: four functions and the PushSentLog table #320.
• A CI job, a scheduled prod run, or the Supabase advisors API as a second signal.
• Any sweep of pgmq, storage or other schemas.

Acceptance criteria

• packages/supabase/scripts/check-grants.sql exists, holds one SELECT statement, and has no numeric prefix.
• After bun run --filter @docs.plus/supabase_back seed, packages/supabase/seed.sql does not contain the string authenticated_definer_ok.
• The public names in authenticated_definer_ok equal the §6 user_facing_names array in scripts/29-lint-hardening.sql, name for name.
• The internal names are exactly is_workspace_member, is_channel_member and can_read_channel, unless the step 6 ruling adds more.
• The anon read allowlist holds the 7 §3 table-level tables plus users.
• The local run with default_transaction_read_only=on exits 0.
• The rolled-back local sabotage run (step 4) returns definer_exec for consume_push_queue(integer,integer) and anon_read_not_allowed for admin_users, and ends with ROLLBACK.
• packages/supabase/CLAUDE.md has the bullet from step 5.
• The step 6 ruling is recorded on [Tracking] Supabase prod security and performance review, 2026-09-28 #327.
• The first prod run's row list is posted on [Tracking] Supabase prod security and performance review, 2026-09-28 #327 (maintainer).

Verification

• Local smoke run (step 3): exit code 0. The rows match the local table in Context, minus any rows that Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 and Make new document_views partitions start closed #315 already cleared.

• Sabotage run (step 4): the two granted objects appear. After rollback, a fresh run no longer lists them.

• Seed check:

  bun run --filter @docs.plus/supabase_back seed
  grep -c authenticated_definer_ok packages/supabase/seed.sql   # expect 0

• Allowlist drift check: compare the public names in check-grants.sql with the §6 array by eye or with a short diff. They must be identical.

• Prod (maintainer, read-only): the first run lists only rows that the prod table in Context predicts. The guard returns 0 rows only after three things: Stop new tables and functions from opening by default #316 and Close the remaining 47 linter warnings on functions #318 land, the step 6 ruling is carried out, and the step 7 anon table rights are revoked.

House rules

• Scripts-first and paired. This issue changes no schema, so it needs no migration, and it leaves seed.sql byte-identical. Never hand-edit packages/supabase/seed.sql or apps/webapp/src/types/supabase.ts.
• Agents have no prod access. Every prod step is [maintainer], and the guard is read-only. Never set role, and never run a sabotage step on prod.
• No commits unless asked. No git add, git commit or git push. The maintainer commits through /commit-review.
• Bun only. Never npm, yarn, pnpm or npx.
• Tests are opt-in. The guard is an ops check, not a test suite. Do not add Jest or Cypress specs for it.