Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314

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

Problem

Prod has a grant and RLS fix that the repo does not have. A future migration can undo it, and a fresh db reset does not reproduce it.

On 2026-09-28 at about 13:38Z, the maintainer approved an emergency fix on prod tglymsfloxmouzjuoycu. It ran as two SQL transactions through the SQL connector. The Supabase security linter fell from 7 ERROR items to 0, and lint 0028 fell from 72 rows to 47.

No script and no migration holds that SQL today. Two risks follow:

• A migration that drops and re-creates one of these functions resets its ACL to the open defaults. CREATE OR REPLACE keeps the ACL; DROP plus CREATE does not.
• A fresh local reset or a self-hosted install does not match prod. For example, document_views_2026_09 to _12 get relrowsecurity = f on a fresh reset.

This issue writes the prod fix into packages/supabase/scripts/ first, then into one idempotent paired migration. On prod the migration must change nothing, with one exception. After 2026-10-01 00:05 UTC, whichever of the #314 and #315 migrations runs first on prod closes document_views_2027_01. That change is expected.

Context

Why prod was open (root cause)

• A local reset builds from scripts/*.sql through the generated seed.sql. [db.migrations] enabled = false (packages/supabase/config.toml ~53-58). Prod builds only from migrations/.
• scripts/29-lint-hardening.sql §3, §4, §5 and §8 have no paired migration, so prod never ran them. Local looked clean; prod kept the defaults.
• Prod pg_default_acl (role postgres, schema public) gives anon and authenticated ALL on new tables and EXECUTE on new functions.
• CREATE FUNCTION also grants EXECUTE to PUBLIC. REVOKE ... FROM anon leaves PUBLIC, and anon inherits it. REVOKE ... FROM public leaves the explicit anon=X from the default ACL. A function is closed only when it is revoked from public and anon (and authenticated where needed).
• The scripts use one half only. 10-4-func-chat-media-features.sql revokes the media stats functions from public only. 10-8-func-workspace_members.sql revokes admin_get_document_member_counts from anon only.
• Some scripts revoke nothing. 07-4-push-notifications-pgmq.sql only grants consume_push_queue and ack_push_message to service_role. 28-ghost-accounts-audit.sql only grants to service_role.

What prod received (measured, 2026-09-28)

Transaction 1 changed tables (blocks A, B, C, D, G). Transaction 2 changed functions (blocks E, F). Both committed, and their built-in asserts passed.

The block D column list is id, username, full_name, display_name, avatar_url, avatar_updated_at, profile_data, status, online_at, created_at, updated_at, deleted_at. It matches the authenticated grant in scripts/13-RLS.sql ~67-71.

The 27 block E functions:

• purge_document_footprint
• consume_push_queue, ack_push_message
• disable_failed_subscriptions, get_failed_push_subscriptions, get_push_failure_summary, get_email_failure_summary, get_email_bounces, get_notification_health
• get_inactive_users, get_user_deletion_impact, get_ghost_summary_public
• get_all_workspace_media_storage_stats, get_workspace_media_storage_stats, get_workspace_media_storage_summary
• admin_get_document_member_counts
• create_direct_message_channel
• enqueue_document_view, update_view_duration, aggregate_document_view_stats, process_document_views_queue, cleanup_old_document_views, create_document_views_partitions
• Prod-only drift, not in the repo: cleanup_push_subscriptions (the public one), get_push_notification_stats, get_push_subscriptions, reset_workspace_unread_counts

Checks after the commit (measured on prod):

• has_column_privilege('anon','public.users','email','SELECT') = false. Guests can still read username.
• purge_document_footprint: authenticated can no longer call it. is_admin: anon no, authenticated yes.
• get_channel_aggregate_data: anon yes. Guest chat still works.
• Cron jobs 2, 8, 17, 25 and 28 ran and succeeded after the change. All 12 cron jobs run as postgres, which owns every target function.
• A prod precheck found one invoker function that reads users columns: fetch_message_window. It reads only granted columns, so block D did not break guest chat.

Every caller of the closed functions uses service_role (verified in the repo)

• purge_document_footprint: apps/hocuspocus.server/src/api/services/documentPurge.service.ts ~49.
• enqueue_document_view, update_view_duration: apps/hocuspocus.server/src/extensions/document-views.extension.ts ~31, ~56 (getServiceRoleClient). The four cron functions have no app caller.
• consume_push_queue, ack_push_message: apps/hocuspocus.server/src/lib/push/pgmqConsumer.ts ~138-139.
• get_ghost_summary_public, get_user_deletion_impact: apps/hocuspocus.server/src/api/services/adminGhostAccounts.service.ts ~182, ~253.
• get_all_workspace_media_storage_stats, get_workspace_media_storage_summary: apps/hocuspocus.server/src/api/services/adminMediaStorage.service.ts ~48, ~130. get_workspace_media_storage_stats has no app caller.
• admin_get_document_member_counts: apps/hocuspocus.server/src/api/services/adminStats.service.ts ~170.
• create_direct_message_channel: only apps/webapp/src/api/rpc/createDirectMessageChannel.ts, and nothing imports it except the barrel. Drop the dead create_direct_message_channel function #319 drops it.
• get_bookmark_count: no caller outside the generated types.
• is_admin: the admin dashboard calls it signed in (apps/admin-dashboard/src/hooks/useAdminAuth.ts ~49, ~89). It keeps authenticated.

Five invoker RPCs live on default grants only

These are SECURITY INVOKER. No script names them in a GRANT statement. Only 29 §6 grants authenticated through its name loop, and prod never ran 29. On prod they work only through the default =X, anon=X and authenticated=X entries. A later default-privilege change (#316) or a DROP plus CREATE would break them. Four migrations already dropped and re-created get_channel_aggregate_data with no grant: 20260504212243, 20260513142000, 20260519053356, 20260519200000.

Grant each one to anon, authenticated, service_role. That writes down today's effective access and changes no behaviour. Narrowing any of them is a separate decision.

Three more invoker RPCs are in the same state: get_unread_notifications_paginated, get_channel_notif_state and get_workspace_notifications. They are out of scope here; #327 lists them under "Not yet owned".

Three defects in scripts/29-lint-hardening.sql

• §3 (~79-81) and §4 (~112-117, ~134, ~149) revoke only SELECT, INSERT, UPDATE, DELETE. TRUNCATE, REFERENCES and TRIGGER stay. Use REVOKE ALL.
• §6 (user_facing_names, ~202-236) re-grants authenticated on create_direct_message_channel (~216) and get_bookmark_count (~210). That undoes §5 and prod block E. Remove both names.
• Query (c) (~352-354) reads information_schema.role_table_grants and says "expect 0". It can never return 0, because §3 grants SELECT back on seven tables. The users grant is column-level, so that view does not list it. Rewrite it (step 3).

The anon read allowlist is seven tables: channels, messages, channel_message_counts, pinned_messages, workspaces, channel_members, message_bookmarks. users is readable only through the column grant.

Why the migration must not copy the 29 sweeps

• 29 stays local-seed only. Its §3 ON ALL TABLES revoke and its §5 definer sweep would also hit prod-only tables and supabase_admin grants.
• refuse_connected_app_request() (migration 20260928120000) is SECURITY INVOKER and must keep its anon grant. A hook that anon cannot run fails every Data API request. Name functions in lists; never sweep.
• The §1 search_path sweep can break prod drift functions that call extensions without a schema prefix.

Build facts a cold agent needs

• Seed order is numeric (packages/supabase/generate-seed.ts). 29 runs after scripts 01 to 28; only 31 and 32 run after it. So its §5 already closes those definer functions locally. Per-file revokes still matter: migrations copy script bodies, and prod never runs 29.
• The chatroom Cypress suite (apps/webapp/cypress/e2e/chatroom/) intercepts every Supabase HTTP call. Its README says it does "not verify database policies". It is a UI regression gate only. Grants are proven by SQL (see Verification).
• Prod migration history ends at 20260921120000. Ship the three migrations that prod's history lacks #313 pushes 20260922120000, 20260928120000 and 20260928130000. This migration must sort after 20260928130000.
• Local baseline, measured 2026-09-28 on a running local stack, read-only:
  • V1 below returns 33 rows. They include rls_off on document_views_2026_09 to _12, and TRUNCATE-class rights left by the partial revokes.
  • V3 shows authenticated can still call get_bookmark_count and create_direct_message_channel, through 29 §6.
  • A rolled-back run of the step 3 table changes brought V1 to 0 rows.
  • A rolled-back run of the step 5 migration SQL passed all its asserts. It raised four "not present" notices for the drift names.
• On 2026-10-01 at 00:05 UTC the monthly cron ('5 0 1 * *') creates document_views_2027_01 with default grants. On prod, the ensure_rls event trigger turns its RLS on. Block A of this migration then revokes its grants. Make new document_views partitions start closed #315 owns the creation path.

What to do

All steps are local. Agents have no prod access. Do not call any Supabase MCP tool.

1. [agent] Start local Supabase from the repo root: bun run --filter @docs.plus/supabase_back start. It needs the root .env.local.

2. [agent] Edit the per-file script homes. Put each revoke right after the function's existing grant, or add one where none exists. Change no function body.

   Prod statement	File (~line, symbol)	Change
   E purge_document_footprint	scripts/09-document-views.sql ~996-997	Make the revoke from public, anon, authenticated. Keep the service_role grant (~998).
   E enqueue_document_view	09-document-views.sql ~339	Add revoke all on function public.enqueue_document_view(text, text, uuid, boolean, text) from public, anon, authenticated;
   E update_view_duration	09-document-views.sql ~487	Same, for (uuid, integer).
   E four cron functions	09-document-views.sql §11 (~923-929)	Add revoke all ... from public, anon, authenticated and grant execute ... to service_role for create_document_views_partitions() (~105), process_document_views_queue() (~348), aggregate_document_view_stats() (~497), cleanup_old_document_views() (~647).
   E consume_push_queue	scripts/07-4-push-notifications-pgmq.sql ~338	Add revoke all on function public.consume_push_queue(int, int) from public, anon, authenticated;
   E ack_push_message	07-4-push-notifications-pgmq.sql ~355	Same, for (bigint).
   E six audit functions	scripts/27-failed-notifications-audit.sql ~335-351	No change. The revokes are already correct. Only the migration is missing.
   E three ghost functions	scripts/28-ghost-accounts-audit.sql ~112-114	Before each grant, add revoke all ... from public, anon, authenticated for get_inactive_users(integer), get_user_deletion_impact(uuid), get_ghost_summary_public().
   E three media stats	scripts/10-4-func-chat-media-features.sql ~204, ~246, ~280	Change each from public to from public, anon, authenticated.
   E admin_get_document_member_counts	scripts/10-8-func-workspace_members.sql ~131-133	Revoke from public, anon, authenticated. Delete the authenticated grant (~132). Keep service_role. Update the comment at ~91-92 to say "service_role only".
   E create_direct_message_channel	scripts/10-functions.sql ~426 (body ends ~525)	After the body, add revoke all ... (character varying, uuid) from public, anon, authenticated; and grant execute ... to service_role;. Drop the dead create_direct_message_channel function #319 drops the function later.
   F is_admin	scripts/02-z-admin-users.sql ~54	Add revoke all on function public.is_admin(uuid) from public, anon; and grant to authenticated, service_role.
   Five invoker RPCs	scripts/10-functions.sql, after the search_path block (~1001-1010)	Add one grant execute on function public.<name>(<identity signature>) to anon, authenticated, service_role; per row of the table in Context.
   A, C, D tables	scripts/29-lint-hardening.sql §3, §4	See step 3.
   B PushSentLog, E four drift functions	none	Not in the repo. Keep them only in the migration, guarded. Remove prod-only drift: four functions and the PushSentLog table #320 rules on dropping them.

3. [agent] Fix scripts/29-lint-hardening.sql.

   • §3 (~79-81): REVOKE ALL ON ALL TABLES IN SCHEMA public FROM anon;. The re-grants below it stay as they are. Table-level REVOKE ALL also clears column grants, so the users column grant (~92-95) must stay after it.

   • §4 (~112-117 and ~149): REVOKE ALL ON public.<table> FROM authenticated;.

   • §4 partition loop (~122-139): for each partition, run enable row level security, then REVOKE ALL ... FROM public, anon, authenticated. This makes a fresh reset match prod block A. Make new document_views partitions start closed #315 fixes the creation paths.

   • §6 (~202-236): remove 'get_bookmark_count' (~210) and 'create_direct_message_channel' (~216). Fix the comment line -- Mentions / DMs to match.

   • Replace query (c) (~352-354) with:

     -- (c) anon table rights outside the read allowlist (expect 0 rows)
     --   SELECT c.relname 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')
     --     AND (has_table_privilege('anon', c.oid, 'INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER')
     --          OR (has_table_privilege('anon', c.oid, 'SELECT')
     --              AND c.relname NOT IN ('channels','messages','channel_message_counts',
     --                  'pinned_messages','workspaces','channel_members','message_bookmarks')));
     --   SELECT has_column_privilege('anon','public.users','email','SELECT');  -- expect false

4. [agent] Reset and check locally before writing the migration (scripts first). bun run --filter @docs.plus/supabase_back reset regenerates packages/supabase/seed.sql and resets the local database. It wipes local data. Then run the SQL in Verification V1 to V5 with docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres. Fix the scripts until every check passes.

5. [agent] Write the paired migration. Create it from packages/supabase: bunx supabase migration new port_prod_grant_rls_fix. Check that its timestamp sorts after 20260928130000 and after every other file in migrations/. Rules:

   • Mirror blocks A to G of the prod fix, plus the five invoker grants. It must be a no-op on prod, except for document_views_2027_01 (see Problem).
   • Idempotent: re-running it must succeed.
   • Name functions in lists. Never loop over all definer functions. Never touch refuse_connected_app_request.
   • Do not copy 29 §1, §3, §5 or §6, and do not use ON ALL TABLES.
   • Do not add begin or commit; keep the file one unit. Start with set local lock_timeout = '5s';, because enable row level security takes an ACCESS EXCLUSIVE lock. A lock timeout rolls back cleanly and is safe to retry.
   • Raise on an owner mismatch; a non-owner REVOKE only warns.
   • Name the paired scripts in the header comment.

   Skeleton (fill in, keep the asserts):

   -- Port of the 2026-09-28 prod grant and RLS fix. No-op on prod, which ran it by hand.
   -- Paired with scripts 02-z, 07-4, 09, 10-4, 10-8, 10-functions, 27, 28 and 29.
   set local lock_timeout = '5s';
   
   -- A. Partitions: RLS on, no browser roles.
   do $$
   declare r record;
   begin
     for r in
       select c.relname, pg_get_userbyid(c.relowner) as owner
       from pg_class c join pg_namespace n on n.oid = c.relnamespace
       where n.nspname = 'public' and c.relkind = 'r'
         and c.relname ~ '^document_views_[0-9]{4}_[0-9]{2}$'
     loop
       if r.owner <> current_user then
         raise exception 'partition % is owned by %, not %', r.relname, r.owner, current_user;
       end if;
       execute format('alter table public.%I enable row level security', r.relname);
       execute format('revoke all on public.%I from public, anon, authenticated', r.relname);
     end loop;
   end $$;
   
   -- B. PushSentLog exists only on prod. #320 rules on dropping it.
   do $$ begin
     if to_regclass('public."PushSentLog"') is not null then
       alter table public."PushSentLog" enable row level security;
       revoke all on public."PushSentLog" from public, anon, authenticated;
     end if;
   end $$;
   
   -- C. Analytics tables.
   revoke all on public.document_views, public.document_views_daily,
                 public.document_view_stats, public.email_bounces
     from public, anon, authenticated;
   revoke all on public.admin_users from anon;
   
   -- D. users: anon reads a column list without email.
   revoke all on public.users from anon;
   grant select (id, username, full_name, display_name, avatar_url, avatar_updated_at,
                 profile_data, status, online_at, created_at, updated_at, deleted_at)
     on public.users to anon;
   
   -- E. service_role only, every overload (this is the prod block E, verbatim in logic).
   do $$
   declare
     names text[] := array[
       'purge_document_footprint','consume_push_queue','ack_push_message',
       'disable_failed_subscriptions','get_failed_push_subscriptions',
       'get_push_failure_summary','get_email_failure_summary',
       'get_email_bounces','get_notification_health',
       'get_inactive_users','get_user_deletion_impact','get_ghost_summary_public',
       'get_all_workspace_media_storage_stats','get_workspace_media_storage_stats',
       'get_workspace_media_storage_summary','admin_get_document_member_counts',
       'create_direct_message_channel',
       'enqueue_document_view','update_view_duration',
       'aggregate_document_view_stats','process_document_views_queue',
       'cleanup_old_document_views','create_document_views_partitions',
       -- Prod-only drift; absent from the repo. #320 rules on dropping them.
       'cleanup_push_subscriptions','get_push_notification_stats',
       'get_push_subscriptions','reset_workspace_unread_counts'];
     f record; nm text; bad text[] := '{}';
   begin
     foreach nm in array names loop
       if not exists (select 1 from pg_proc p join pg_namespace n on n.oid = p.pronamespace
                      where n.nspname = 'public' and p.proname = nm) then
         raise notice 'not present on this database: %', nm;
       end if;
     end loop;
   
     for f in
       select p.oid, p.oid::regprocedure as sig, pg_get_userbyid(p.proowner) as owner,
              p.prorettype::regtype::text as rettype
       from pg_proc p join pg_namespace n on n.oid = p.pronamespace
       where n.nspname = 'public' and p.proname = any (names)
     loop
       if f.rettype in ('trigger','event_trigger') then
         raise exception 'unexpected trigger function in list: %', f.sig;
       end if;
       if f.owner <> current_user then
         raise exception '% is owned by %, not %', f.sig, f.owner, current_user;
       end if;
       execute format('grant execute on function %s to service_role', f.sig);
       execute format('revoke all on function %s from public, anon, authenticated', f.sig);
     end loop;
   
     for f in
       select p.oid, p.oid::regprocedure as sig
       from pg_proc p join pg_namespace n on n.oid = p.pronamespace
       where n.nspname = 'public' and p.proname = any (names)
     loop
       if has_function_privilege('anon', f.oid, 'EXECUTE')
          or has_function_privilege('authenticated', f.oid, 'EXECUTE') then
         bad := bad || ('still callable: ' || f.sig::text);
       end if;
       if not has_function_privilege('service_role', f.oid, 'EXECUTE') then
         bad := bad || ('service_role lost: ' || f.sig::text);
       end if;
     end loop;
     if cardinality(bad) > 0 then raise exception 'function assert failed: %', bad; end if;
   end $$;
   
   -- E2. Five invoker RPCs: explicit grants that match today's effective access.
   grant execute on function public.get_channel_aggregate_data(character varying, integer, uuid)
     to anon, authenticated, service_role;
   grant execute on function public.notifications_summary(character varying)
     to anon, authenticated, service_role;
   grant execute on function public.get_channel_members_by_last_read_update(character varying, timestamp with time zone)
     to anon, authenticated, service_role;
   grant execute on function public.get_unread_notif_count(character varying)
     to anon, authenticated, service_role;
   grant execute on function public.fetch_mentioned_users(character varying, text)
     to anon, authenticated, service_role;
   
   -- F. is_admin (prod block F). Prod found five policies that call it: admin_users (3),
   --    document_view_stats and document_views_daily. Skip if any other policy calls it.
   do $$
   declare n int;
   begin
     select count(*) into n from pg_policies
     where coalesce(qual,'') || ' ' || coalesce(with_check,'') ilike '%is_admin%'
       and not (schemaname = 'public'
                and tablename in ('admin_users','document_view_stats','document_views_daily'));
     if n > 0 then
       raise notice 'is_admin left unchanged: % other policies call it', n;
     else
       grant execute on function public.is_admin(uuid) to authenticated, service_role;
       revoke execute on function public.is_admin(uuid) from public, anon;
       if has_function_privilege('anon', 'public.is_admin(uuid)', 'EXECUTE') then
         raise exception 'is_admin revoke did not apply (owner is not %)', current_user;
       end if;
     end if;
   end $$;
   
   -- G. Assert (prod block G, plus the invoker grants).
   do $$
   declare t record; bad text[] := '{}'; fn text;
   begin
     for t in
       select c.oid, c.relname, c.relrowsecurity
       from pg_class c join pg_namespace n on n.oid = c.relnamespace
       where n.nspname = 'public'
         and (c.relname ~ '^document_views_[0-9]{4}_[0-9]{2}$'
              or c.relname in ('document_views','document_views_daily','document_view_stats',
                               'email_bounces','admin_users','PushSentLog'))
     loop
       if has_table_privilege('anon', t.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE') then
         bad := bad || ('anon still has rights on ' || t.relname);
       end if;
       if t.relname <> 'admin_users'
          and has_table_privilege('authenticated', t.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE') then
         bad := bad || ('authenticated still has rights on ' || t.relname);
       end if;
       if t.relname ~ '^document_views_[0-9]{4}_[0-9]{2}$' and not t.relrowsecurity then
         bad := bad || ('RLS off on ' || t.relname);
       end if;
     end loop;
     if has_column_privilege('anon', 'public.users', 'email', 'SELECT') then
       bad := bad || 'anon can read users.email'::text;
     end if;
     if has_table_privilege('anon', 'public.users', 'INSERT,UPDATE,DELETE,TRUNCATE') then
       bad := bad || 'anon can write users'::text;
     end if;
     if not has_column_privilege('anon', 'public.users', 'username', 'SELECT') then
       bad := bad || 'guest profile read broken'::text;
     end if;
     foreach fn in array array[
       'public.get_channel_aggregate_data(character varying, integer, uuid)',
       'public.notifications_summary(character varying)',
       'public.get_channel_members_by_last_read_update(character varying, timestamp with time zone)',
       'public.get_unread_notif_count(character varying)',
       'public.fetch_mentioned_users(character varying, text)'] loop
       if not has_function_privilege('anon', fn, 'EXECUTE')
          or not has_function_privilege('authenticated', fn, 'EXECUTE') then
         bad := bad || ('invoker RPC not callable: ' || fn);
       end if;
     end loop;
     if cardinality(bad) > 0 then raise exception 'assert failed: %', bad; end if;
   end $$;

   Two prod facts shaped this SQL. Service_role is granted before each revoke, because prod may give it EXECUTE only through PUBLIC. The owner check raises, because a REVOKE by a non-owner only warns and changes nothing.

6. [agent] Apply the migration on the reset database, twice. Run docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres -v ON_ERROR_STOP=1 -1 < packages/supabase/migrations/<new file>.sql. The -1 flag runs the file in one transaction, so set local applies. Run it a second time to prove idempotence. Expect notices only for the four drift names and for PushSentLog.

7. [agent] Regenerate generated files. Run bun run --filter @docs.plus/supabase_back seed if you edited scripts after step 4. Then run bun run --filter @docs.plus/supabase_back types from the repo root. Include apps/webapp/src/types/supabase.ts in the change even if it has no diff. Never hand-edit either file.

8. [agent] Check the UI. Run the chatroom browser suite: bash scripts/test-chatroom.sh (see apps/webapp/cypress/e2e/chatroom/README.md for the license and port). Then run the webapp against local Supabase. Open a pad as a guest: the chat panel loads messages. Sign in with an email link and open the same pad: chat loads, the @mention list fills, and the notification count loads.

9. [agent] Stop at a review checkpoint. List the changed files and paste the V1 to V5 output. Do not commit.

10. [maintainer] Push to prod after Ship the three migrations that prod's history lacks #313. Follow the house procedure in packages/supabase/CLAUDE.md. Run bunx supabase migration list first. Set [db.migrations] enabled = true only while pushing, then set it back to false. Before the push, note the lint 0028 count. After the push, run V6 read-only on prod.

Out of scope

• Ship the three migrations that prod's history lacks #313: pushing 20260922120000, 20260928120000 and 20260928130000.
• Make new document_views partitions start closed #315: RLS and revokes inside the partition creation paths (09-document-views.sql first DO block ~66-102 and the body of create_document_views_partitions ~105-152). Also the false comment in scripts/13-RLS.sql ~271, and recording rls_auto_enable.
• Stop new tables and functions from opening by default #316: ALTER DEFAULT PRIVILEGES and the house-rule line.
• Add a read-only grant guard to run after each db push #317: the read-only grant guard.
• Close the remaining 47 linter warnings on functions #318: the 17 self-gated definer functions still callable by anon, the trigger functions, and the useHyperlinkSuggestions.ts guest gate. On prod, get_bookmark_count stays callable until Close the remaining 47 linter warnings on functions #318.
• Drop the dead create_direct_message_channel function #319: dropping create_direct_message_channel and its webapp wrapper.
• Remove prod-only drift: four functions and the PushSentLog table #320: dropping PushSentLog and the four drift functions.
• No new tests. No function body changes.

Acceptance criteria

• Every script home in step 2 revokes from public and anon, and from authenticated where the prod fix did.
• scripts/29-lint-hardening.sql §3 and §4 use REVOKE ALL, and the §4 partition loop enables RLS.
• 29 §6 no longer lists get_bookmark_count or create_direct_message_channel, and query (c) is replaced.
• The five invoker RPCs have explicit grants in scripts/10-functions.sql and in the migration (the V4 grep prints 10 lines).
• One new migration exists, sorts after 20260928130000, names its paired scripts, and uses name lists only.
• The migration applies twice on a fresh local reset with no error.
• The migration does not mention refuse_connected_app_request, ON ALL TABLES, or a prosecdef loop.
• V1 to V5 return the expected results after bun run --filter @docs.plus/supabase_back reset, both before and after the migration is applied.
• packages/supabase/seed.sql and apps/webapp/src/types/supabase.ts were regenerated by the scripts, not by hand.
• The chatroom Cypress suite passes. The manual guest and signed-in chat checks in step 8 pass.
• No applied migration file was edited. Nothing was committed.
• [maintainer] After the push, V6 on prod returns the expected results. The linter shows 0 ERROR, and the lint 0028 count equals the count before the push (47 on 2026-09-28).

Verification

Run V1 to V5 on the local database after step 4, and again after step 6.

V1. Tables: expect 0 rows.

select c.relname, 'rls_off' as why from pg_class c join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relname ~ '^document_views_[0-9]{4}_[0-9]{2}$' and not c.relrowsecurity
union all
select c.relname, 'anon_right' 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')
  and (has_table_privilege('anon', c.oid, 'INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER')
       or (has_table_privilege('anon', c.oid, 'SELECT')
           and c.relname not in ('channels','messages','channel_message_counts','pinned_messages',
                                 'workspaces','channel_members','message_bookmarks')))
union all
select c.relname, 'authenticated_right' from pg_class c join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
  and (c.relname ~ '^document_views(_[0-9]{4}_[0-9]{2}|_daily)?$'
       or c.relname in ('document_view_stats','email_bounces'))
  and has_table_privilege('authenticated', c.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE');

V2. users: expect f, t.

select has_column_privilege('anon','public.users','email','SELECT'),
       has_column_privilege('anon','public.users','username','SELECT');

V3. Service-role-only functions: expect every row f, f, t.

select p.oid::regprocedure,
       has_function_privilege('anon', p.oid, 'EXECUTE') as anon,
       has_function_privilege('authenticated', p.oid, 'EXECUTE') as authed,
       has_function_privilege('service_role', p.oid, 'EXECUTE') as service
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.proname = any (array[
  'purge_document_footprint','consume_push_queue','ack_push_message',
  'disable_failed_subscriptions','get_failed_push_subscriptions','get_push_failure_summary',
  'get_email_failure_summary','get_email_bounces','get_notification_health',
  'get_inactive_users','get_user_deletion_impact','get_ghost_summary_public',
  'get_all_workspace_media_storage_stats','get_workspace_media_storage_stats',
  'get_workspace_media_storage_summary','admin_get_document_member_counts',
  'create_direct_message_channel','get_bookmark_count','enqueue_document_view',
  'update_view_duration','aggregate_document_view_stats','process_document_views_queue',
  'cleanup_old_document_views','create_document_views_partitions'])
order by 1;

Locally, expect 24 rows. get_bookmark_count is in this list for the local check only; on prod it stays open until #318.

V4. Invoker grants and is_admin: expect anon and authenticated in explicit for the five RPCs, and is_admin with anon = f, authed = t.

select p.oid::regprocedure,
       has_function_privilege('anon', p.oid, 'EXECUTE') as anon,
       has_function_privilege('authenticated', p.oid, 'EXECUTE') as authed,
       (select string_agg(case when a.grantee = 0 then 'PUBLIC' else a.grantee::regrole::text end, ',')
          from aclexplode(p.proacl) a where a.privilege_type = 'EXECUTE') as explicit
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','is_admin');

V4 cannot prove the new script grants on its own. The local default ACL already adds anon and authenticated entries, so V4 passes before the change too. Prove the change with text instead: each of the five names must appear in a grant execute line in both files.

grep -n -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 packages/supabase/migrations/<new file>.sql

Expect 10 lines: five per file.

V5. The Data API hook keeps anon: expect t.

select has_function_privilege('anon', 'public.refuse_connected_app_request()', 'EXECUTE');

If refuse_connected_app_request() does not exist locally, the reset did not include scripts/31-connected-app-token-gate.sql. Stop and report it.

V6. [maintainer] Prod, read-only, after the push. Do not run V1 on prod. Prod never ran 29 §3, so anon still holds default rights on other tables. V1 lists them, and they are out of scope here. Run this scoped table check instead. Expect 0 rows.

select c.relname, 'rls_off' as why from pg_class c join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and not c.relrowsecurity
  and (c.relname ~ '^document_views_[0-9]{4}_[0-9]{2}$' or c.relname = 'PushSentLog')
union all
select c.relname, 'browser_right' from pg_class c join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
  and (c.relname ~ '^document_views(_[0-9]{4}_[0-9]{2}|_daily)?$'
       or c.relname in ('document_view_stats','email_bounces','PushSentLog'))
  and (has_table_privilege('anon', c.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE')
       or has_table_privilege('authenticated', c.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE'))
union all
select 'admin_users', 'anon_right'
where has_table_privilege('anon', 'public.admin_users', 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE');

Then run V2, V4 and V5 as written. Run V3 with get_bookmark_count removed from the list. Expect one row per overload, 23 if prod has no extra overloads, each f, f, t.

Check the history: select version from supabase_migrations.schema_migrations where version > '20260928130000'; returns the new version. Re-run the security linter. Expect 0 ERROR, and the lint 0028 count noted before the push. A different count means the migration changed prod. Stop and compare.

House rules

• Scripts first, then one paired migration. Every change lives in both packages/supabase/scripts/*.sql and packages/supabase/migrations/ (packages/supabase/CLAUDE.md).
• Never edit an applied migration (for example 20260513130000, 20260708144905, 20260715082400). Add a new one.
• Never hand-edit packages/supabase/seed.sql or apps/webapp/src/types/supabase.ts. Regenerate them with bun run --filter @docs.plus/supabase_back seed and ... types, and include the result.
• Bun only. Never run npm, yarn, pnpm or npx.
• No commits, pushes or branches unless the maintainer asks. Tests are opt-in: add none here.