Stop new tables and functions from opening by default #316

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

Problem

On prod, every new table, sequence and function in schema public starts open to anon. Each new object stays exposed until a later statement closes it. This default is the root cause of the 7 ERROR items that the Supabase security linter flagged on 2026-09-28. The emergency fix closed the existing objects only. The next migration that adds a table or drops and re-creates a function opens it again.

The obvious fix does not work for functions. A per-schema ALTER DEFAULT PRIVILEGES ... REVOKE EXECUTE ON FUNCTIONS FROM anon, authenticated leaves new functions callable by anon. The reason is the built-in PUBLIC EXECUTE grant (measured below). Only the global form removes that grant, and the global form reaches beyond public. The maintainer must choose between the two options before an agent writes the function part.

Context

Where the defaults come from. pg_default_acl holds the grants that Postgres adds to each new object. On prod, for role postgres in schema public, these grants are:

Role supabase_admin has the same rows in public. A local supabase start shows the same open rows for both roles, with rwU on sequences. Locally, role postgres also has the same open rows for schema storage. Nobody has read the prod storage rows yet.

PUBLIC EXECUTE is separate. CREATE FUNCTION also gives EXECUTE to PUBLIC, which every role inherits. This grant is built into Postgres. It does not appear in pg_default_acl unless a global row overrides it. So REVOKE ... FROM anon leaves PUBLIC, and anon still inherits it. REVOKE ... FROM public leaves the explicit anon=X from the default ACL. Only a revoke from both closes a function.

Other facts from the 2026-09-28 review:

• A DROP plus CREATE of a function resets its ACL to these defaults. CREATE OR REPLACE keeps the old ACL.
• packages/supabase/scripts/29-lint-hardening.sql has the right table and function sweeps (§3 to §8), but no migration carries them. So prod never ran them.
• git grep -i "default privileges" packages/supabase finds only prose at packages/supabase/CLAUDE.md ~line 32. No script or migration changes pg_default_acl today.
• The emergency fix on 2026-09-28 (about 13:38Z) changed grants on existing objects only. It did not change pg_default_acl.

Local probe (measured 2026-09-28 on the local stack, inside BEGIN ... ROLLBACK, run as postgres):

Each statement is idempotent: a second run is a silent no-op. A matching GRANT restores the old rows exactly. Both points were checked in the same probe.

What the global form reaches. It applies to functions that role postgres creates, in every schema. The facts below were checked on the local stack:

• Public functions keep service_role, because the per-schema public row still grants it.
• New internal.* functions get postgres only. Locally, 11 of the 20 existing internal.* functions have a NULL ACL. Today some internal.* helpers have explicit grants, for example packages/supabase/scripts/13-RLS.sql ~lines 20-22 grant is_workspace_member, is_channel_member and can_read_channel to authenticated, service_role. Others have a NULL ACL and work only through PUBLIC.
• Storage's own functions are owned by supabase_storage_admin, so the global form does not touch them.
• The pgmq extension is owned by postgres locally. A later ALTER EXTENSION pgmq UPDATE would add any new pgmq.* functions with no PUBLIC grant. No repo code calls pgmq.* as anon, authenticated or service_role. Every pgmq.send caller is a SECURITY DEFINER function owned by postgres (compile_digest_emails, enqueue_document_view, enqueue_push_notification, on_message_*_queue, process_email_queue). The worker calls only definer wrappers through client.rpc(...) (apps/hocuspocus.server/src/lib/pgmqConsumer.ts ~lines 118 and 144). The prod owner of pgmq is not read yet.

The two options.

HoE recommendation (the review's lead agent; not a ruling): Option A. This incident was a fail-open default. Option A turns a forgotten grant into a visible error instead of a silent exposure. Its cost is the house rule below, which authors must follow anyway.

Known limitation: the seed-order trap. A local reset builds from scripts/ through seed.sql, with [db.migrations] enabled = false (packages/supabase/config.toml ~lines 53-58). The seed runs 29-lint-hardening.sql after scripts 01 to 28. Scripts 31-connected-app-token-gate.sql and 32-password-sign-in-hook.sql run after 29, so they get the new defaults. Both already grant their functions explicitly (31 ~lines 32-33, 32 ~lines 23-25). So on a local reset, objects in those scripts get the old open defaults. On prod, a new migration gets the new closed defaults. A missing grant therefore passes locally and returns 42501 on prod. This issue does not remove the trap. It adds three guards: the house rule, the #317 guard after each push, and a local psql run of each new migration after the reset. The reset already ran 29, so that run gets the new defaults. Moving the statements into scripts/00-bootstrap.sql would remove the trap. It is a later follow-up, because every browser-facing object in scripts 01 to 28 must first have an explicit grant.

Accepted residual. authenticated keeps arwdDxt on new tables and rwU on new sequences (measured above). Sequences need it: public.messages.seq defaults to nextval('public.messages_seq_seq') (packages/supabase/scripts/05-0-message.sql ~lines 7 and 11), so a signed-in insert needs USAGE. A new table with RLS off would still be open to signed-in users. #317's rls_off row and prod's ensure_rls event trigger cover that case.

Why #314 comes first. Five SECURITY INVOKER RPCs work today only through the default grants: get_channel_aggregate_data, notifications_summary, get_channel_members_by_last_read_update, get_unread_notif_count and fetch_mentioned_users. This change does not touch existing objects, so they keep working. But a later migration may DROP and CREATE one of them, which resets its ACL. #314 puts an explicit grant next to each body in scripts/10-functions.sql. A migration that copies the body then copies the grant too. Without it, the new defaults would close the RPC and break chat for guests and signed-in users. Three more invoker RPCs lack an explicit grant; #327 lists them under "Not yet owned".

What to do

1. [maintainer] Rule on Option A or Option B. Record the ruling as a comment on this issue. Steps 5a and 7 depend on it.

2. [maintainer] Run read-only prod checks and paste the output into this issue. Run them in Dashboard → SQL Editor.

   -- 2a. Every default-ACL row for role postgres, in every schema.
   select case when d.defaclnamespace = 0 then '<all schemas>'
               else d.defaclnamespace::regnamespace::text end as schema,
          d.defaclobjtype as objtype,
          case when a.grantee = 0 then 'PUBLIC' else pg_get_userbyid(a.grantee) end as grantee,
          a.privilege_type
   from pg_default_acl d, aclexplode(d.defaclacl) a
   where d.defaclrole = 'postgres'::regrole
   order by 1, 2, 3, 4;
   
   -- 2b. Owner of pgmq, and of the extensions postgres may update.
   select extname, pg_get_userbyid(extowner) as owner, extnamespace::regnamespace as schema
   from pg_extension order by 1;
   
   -- 2c. The five invoker RPCs are callable today (expect 5 rows, all true).
   select p.oid::regprocedure,
          has_function_privilege('anon', p.oid, 'EXECUTE') as anon_exec,
          has_function_privilege('authenticated', p.oid, 'EXECUTE') as auth_exec,
          p.proacl
   from pg_proc p join pg_namespace n on n.oid = p.pronamespace
   where n.nspname = 'public' and p.proname in ('get_channel_aggregate_data',
     'notifications_summary', 'get_channel_members_by_last_read_update',
     'get_unread_notif_count', 'fetch_mentioned_users');
   
   -- 2d. #314's migration is in prod history (expect at least one row).
   select version
   from supabase_migrations.schema_migrations
   where version > '20260928130000'
   order by version;

   2c cannot prove that Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 is live. The default ACL already put anon=X and authenticated=X on these functions, and an explicit grant adds the same entries. 2d is the liveness check. Stop if 2d does not list Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration. In 2a, note any storage rows that grant anon. This issue does not change them; report them in [Tracking] Supabase prod security and performance review, 2026-09-28 #327.

3. [agent] Confirm Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 has landed in the repo. The five RPCs live in packages/supabase/scripts/10-functions.sql (get_channel_aggregate_data ~line 25, notifications_summary ~529, fetch_mentioned_users ~707, get_unread_notif_count ~780, get_channel_members_by_last_read_update ~929). Run the grep below. Expect five matches, each a grant to anon, authenticated, service_role.

   grep -n -A1 -E "grant execute on function public\.(get_channel_aggregate_data|notifications_summary|get_channel_members_by_last_read_update|get_unread_notif_count|fetch_mentioned_users)\(" packages/supabase/scripts/10-functions.sql

   Also confirm that Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration exists after 20260928130000. Stop if either check fails.

4. [agent] Probe locally before editing. Start the stack with bun run --filter @docs.plus/supabase_back start if it is not running. Run the probe in "Verification" step V1 and confirm the table above. The probe ends in ROLLBACK, so it leaves local state unchanged.

5. [agent] Edit the script first: packages/supabase/scripts/29-lint-hardening.sql. Add a new section §9. Default privileges for role postgres between the end of §8 (the DO block that ends ~line 322) and COMMIT; (~line 325). Also add one line to the header list (~lines 5-11).

   5a. Always add these statements:

   -- Never name supabase_admin: postgres cannot change that role's defaults
   -- ("must be member of role supabase_admin"), and the transaction aborts.
   alter default privileges for role postgres in schema public
     revoke execute on functions from anon, authenticated;
   alter default privileges for role postgres in schema public
     revoke all on tables from anon;
   alter default privileges for role postgres in schema public
     revoke all on sequences from anon;
   -- authenticated keeps sequence USAGE: messages.seq uses nextval().

   5b. Option A only. Add the global statement after 5a:

   -- Built-in PUBLIC EXECUTE is global; a per-schema row cannot remove it.
   -- New internal.* functions then need an explicit grant to every caller role:
   -- authenticated for RLS policy helpers, service_role for the worker.
   alter default privileges for role postgres
     revoke execute on functions from public;

   5c. In the "Post-apply verification" comment block (~lines 328-360), add query (e). Copy the query from V2 below as a comment.

6. [agent] Write the paired migration. From packages/supabase, run bunx supabase migration new default_privileges_close_new_objects. Its timestamp must sort after Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration and after every other file in migrations/. Copy the same statements as step 5, and add no others. Follow recent migrations such as 20260928130000_reject_password_sign_in_hook.sql: no explicit begin or commit, and never a mid-file COMMIT. Every statement is idempotent, so a re-run is safe.

7. [agent] Add the house rule to packages/supabase/CLAUDE.md. Insert a new bullet right after the bullet at ~line 32 ("Explicit table GRANTs for authenticated ..."). Use this text, and keep the bracketed sentence only under Option A:

   • New objects start closed on prod, so grant them in the same migration. pg_default_acl for role postgres gives anon nothing on new tables, sequences or functions in public (issue #). [Option A: new functions in any schema also get no PUBLIC EXECUTE. So a new internal.* function needs an explicit grant to every role that calls it: authenticated for RLS policy helpers, service_role for the worker.] A migration that creates a browser-facing table, sequence or function must grant it in the same file. The same holds when it drops and re-creates one. The local seed creates objects in scripts numbered below 29 before 29-lint-hardening.sql closes the defaults. So a missing grant on such an object passes a local reset and fails with 42501 on prod. Run the Add a read-only grant guard to run after each db push #317 guard after each push.

   Replace #<this issue> with the real number. Replace "Add a read-only grant guard to run after each db push #317 guard" with the guard's file path once Add a read-only grant guard to run after each db push #317 has landed.

8. [agent] Regenerate the seed and the types. Run bun run --filter @docs.plus/supabase_back seed now. Run bun run --filter @docs.plus/supabase_back types after the reset in step 9, so it reads the new schema. Include apps/webapp/src/types/supabase.ts in the change even if it has no diff. Never edit either file by hand.

9. [agent] Verify locally. Run bun run --filter @docs.plus/supabase_back reset. It wipes local data. The reset runs §9 through the seed. Then run bun run --filter @docs.plus/supabase_back types. Then apply the new migration on top. This proves it is a silent no-op. Command: docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres -v ON_ERROR_STOP=1 < packages/supabase/migrations/<file>.sql. Run V2 and V3. Then run the chatroom browser suite: bash scripts/test-chatroom.sh (license and port: apps/webapp/cypress/e2e/chatroom/README.md). It intercepts every Supabase call, so it is a UI smoke check only. The SQL checks are the proof. Stop here: do not commit. The maintainer reviews the diff.

10. [maintainer] Push to prod with the house procedure in packages/supabase/CLAUDE.md ~lines 29-30. Run bunx supabase migration list from packages/supabase first. Set [db.migrations] enabled = true only while pushing, and never commit it. Never repair-apply a new version. Then run V2 on prod, and run the Add a read-only grant guard to run after each db push #317 guard.

Rollback [maintainer]. First choice: add the missing grant to the object that fails. Use the statements below only if a 42501 outage is worse than the exposure. They restored the old rows exactly in a local test. On prod, compare the result with the step 2a output and match that privilege list.

begin;
alter default privileges for role postgres in schema public grant execute on functions to anon, authenticated;
alter default privileges for role postgres in schema public grant all on tables to anon;
alter default privileges for role postgres in schema public grant all on sequences to anon;
alter default privileges for role postgres grant execute on functions to public;  -- Option A only
commit;

Out of scope

• Grants and RLS on existing objects, the 29-lint-hardening.sql §3, §4 and §6 fixes, and the five explicit invoker grants: Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314.
• Closing new document_views partitions, and recording ensure_rls / rls_auto_enable: Make new document_views partitions start closed #315.
• The guard SQL, its file and how it runs: Add a read-only grant guard to run after each db push #317.
• Revokes on self-gated definer functions and trigger functions: Close the remaining 47 linter warnings on functions #318.
• Shipping the three migrations that prod history lacks: Ship the three migrations that prod's history lacks #313.
• Default ACLs of role supabase_admin. Role postgres cannot change them. Objects that role creates stay open by default, and the Add a read-only grant guard to run after each db push #317 guard must catch them.
• Default ACLs of role postgres in schema storage. Step 2a reports them; a later issue decides.
• Moving the statements into scripts/00-bootstrap.sql (see "Known limitation").

Acceptance criteria

• The maintainer's ruling (Option A or B) is recorded as a comment on this issue.
• Step 2 prod output is pasted into this issue. 2c shows true for anon and authenticated on all five RPCs. 2d lists Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration.
• packages/supabase/scripts/29-lint-hardening.sql has §9 with the 5a statements, plus 5b under Option A, before COMMIT;.
• No new SQL statement outside comments names supabase_admin.
• One new migration, sorted after Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration, holds exactly the §9 statements and no mid-file COMMIT.
• packages/supabase/CLAUDE.md has the new house-rule bullet after the ~line 32 bullet.
• packages/supabase/seed.sql and apps/webapp/src/types/supabase.ts are regenerated by the commands, not by hand.
• After a local reset plus the migration, V2 matches the expected local rows.
• V3 returns false for anon on the new table and sequence, and on the new function under Option A.
• Applying the new migration after the local reset prints only ALTER DEFAULT PRIVILEGES lines and no error.
• bash scripts/test-chatroom.sh passes.
• After the prod push, V2 on prod meets the pass rule under V2.
• After the prod push, the Add a read-only grant guard to run after each db push #317 guard returns 0 default_acl_anon rows.

Verification

V1. Local probe before the change (as postgres; ends in ROLLBACK):

begin;
alter default privileges for role postgres in schema public revoke execute on functions from anon, authenticated;
alter default privileges for role postgres in schema public revoke all on tables from anon;
alter default privileges for role postgres in schema public revoke all on sequences from anon;
create function public.zz_probe_a() returns int language sql as 'select 1';
select has_function_privilege('anon', 'public.zz_probe_a()', 'EXECUTE');  -- expect true: still open
alter default privileges for role postgres revoke execute on functions from public;
create function public.zz_probe_b() returns int language sql as 'select 1';
select has_function_privilege('anon', 'public.zz_probe_b()', 'EXECUTE');  -- expect false
rollback;

V2. Default ACL rows (read-only; local after step 9, prod after step 10):

select case when d.defaclnamespace = 0 then '<all schemas>'
            else d.defaclnamespace::regnamespace::text end as schema,
       d.defaclobjtype as objtype,
       case when a.grantee = 0 then 'PUBLIC' else pg_get_userbyid(a.grantee) end as grantee,
       a.privilege_type
from pg_default_acl d, aclexplode(d.defaclacl) a
where d.defaclrole = 'postgres'::regrole
  and (d.defaclnamespace = 0 or d.defaclnamespace = 'public'::regnamespace)
order by 1, 2, 3, 4;

Expected locally for schema public (measured in the probe):

• r: grantees authenticated, postgres, service_role. No anon.
• S: grantees authenticated, postgres, service_role. No anon.
• f: grantees postgres, service_role. No anon, no authenticated.

Expected locally for <all schemas>:

• Option A: one f row with grantee postgres only. No PUBLIC row.
• Option B: no <all schemas> row.

On prod the privilege lists may differ slightly from local (see Context). The pass rule is the same: no anon grantee in any public row, and no authenticated in the public f row. Under Option A, the <all schemas> f row has no PUBLIC grantee.

V3. New objects start closed (local only; ends in ROLLBACK). On prod this probe would be a write, so it is optional and [maintainer] only:

begin;
create function public.zz_probe() returns int language sql as 'select 1';
create table public.zz_probe_t (id int);
create sequence public.zz_probe_s;
select has_function_privilege('anon', 'public.zz_probe()', 'EXECUTE') as fn_anon,  -- A: false; B: true
       has_table_privilege('anon', 'public.zz_probe_t', 'SELECT') as tbl_anon,     -- false
       has_sequence_privilege('anon', 'public.zz_probe_s', 'USAGE') as seq_anon,   -- false
       has_sequence_privilege('authenticated', 'public.zz_probe_s', 'USAGE') as seq_auth; -- true
rollback;

V4. The #317 guard returns no default_acl_anon row after the prod push.

House rules

• Scripts first, then one paired migration. Regenerate seed.sql and run bun run --filter @docs.plus/supabase_back types in the same change (packages/supabase/CLAUDE.md ~lines 15 and 27).
• Never edit applied migrations. Never hand-edit packages/supabase/seed.sql or apps/webapp/src/types/supabase.ts.
• Bun only. Never run npm, yarn, pnpm or npx.
• No commits unless the maintainer asks. The maintainer reviews the diff and commits through /commit-review.
• Tests are opt-in. This change adds no new test files; the checks above are enough.