Make new document_views partitions start closed #315

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

Problem

New document_views partitions can start open to browser roles, and the repo cannot stop it. On prod, three partitions had RLS off, and anon held every table privilege on them. Anon could read, insert, change and delete at least 24,643 view rows. Each row holds user_id, session_id, the document slug and the device type.

The emergency fix on 2026-09-28 closed every partition that exists today. The code paths that create partitions are still unchanged:

• At HEAD, a fresh db reset creates four partitions with RLS off. This is measured on a local reset.
• The monthly cron creates each new partition with prod's default grants: anon and authenticated get ALL.
• On prod, RLS on new partitions relies on a prod-only event trigger, ensure_rls, or on a function body nobody has read. The repo contains neither the trigger nor a migration for the function.

This issue makes both creation paths close each partition themselves: RLS on, and no grants for public, anon or authenticated.

Context

The two creation paths, both in packages/supabase/scripts/09-document-views.sql:

• The cron job is defined in 09-document-views.sql ~896-906 and in migration 20260806100000_cron_schedule_upsert_preserves_jobid.sql ~61-65. On prod it is job id 9 and runs as postgres. Its average run time is 80.5 ms. It has no failures.
• The function is security definer with set search_path = public. 09-document-views.sql ~935 also pins its search_path.
• No migration has ever defined this function. grep finds its body only in scripts/. The 2026-09-28 review did not read prod's body. So nobody knows whether prod's copy enables RLS. The paired migration in this issue is the first time the repo ships this body to prod.
• Every writer goes through the parent document_views or a security definer function. enqueue_document_view writes only to pgmq. So no browser role needs a direct grant on any partition. service_role keeps its grants; do not revoke it.

Measured on prod (read-only catalog reads by the HoE, the review's lead agent, 2026-09-28):

• document_views_2026_03, _04 and _05 had relrowsecurity = false. Anon held arwdDxt (ALL) on the parent and on every partition.
• A direct query on a partition uses the partition's own RLS, not the parent's. At the grant level, anon could read 18,135 rows of _2026_03 and 6,508 rows of _2026_05.
• _2026_06 to _2026_12 had RLS on and 0 policies. Authenticated held arwdDxt on _2026_11 and _2026_12. Those two were created after the last partition revoke loop ran, in migration 20260716130500 (~27-44).
• Prod's pg_default_acl for role postgres in schema public grants anon and authenticated ALL on new tables. The cron runs as postgres, so each new partition gets these grants.
• Prod has the event trigger ensure_rls. It calls public.rls_auto_enable() on ddl_command_end. The repo contains neither object: git grep -i rls_auto_enable returns nothing.
• Access review: apart from the HoE's own grant check, no anon query ever touched a document_views partition (lifetime pg_stat_statements). No sign of outside access.

Applied to prod on 2026-09-28, about 13:38Z (emergency block A, maintainer-approved): every document_views_YYYY_MM partition got RLS on and revoke all ... from public, anon, authenticated. Emergency block E made create_document_views_partitions executable by service_role only. Prod now differs from the repo. #314 ports those statements into scripts/ and a migration.

Predicted, not measured:

• The cron run on 2026-10-01 00:05 UTC creates document_views_2027_01. It gets default grants (anon ALL). ensure_rls probably turns its RLS on. So it is probably closed by RLS but not by grants until this issue ships. Until then, the maintainer re-runs step 1 query (c) after each 1st-of-month run.
• The body and owner of rls_auto_enable are unverified. One review stream reported that a dashboard setting installs it. Nobody has confirmed that.

Measured locally at HEAD: on a fresh reset, document_views_2026_09 to _2026_12 have relrowsecurity = f. The first DO block skips RLS, and local has no ensure_rls.

False comments in the repo:

• packages/supabase/scripts/13-RLS.sql ~263-274 (section 2j. document_views) says "Existing partitions get RLS enabled by the migration". No migration did that. The comment also names a file that does not exist, 21-document-views.sql; the real file is 09-document-views.sql.
• packages/supabase/migrations/20260513130000_chat_rls_rollout_catchup.sql ~296-307 holds the same false text. That migration is applied on prod. Leave it unchanged.

Why local checks are weak here. Seed order is numeric, so 29-lint-hardening.sql runs after 09. At HEAD, its §4 loop (~122-139) revokes SELECT, INSERT, UPDATE and DELETE on partitions from authenticated. #314 changes that loop to enable RLS and REVOKE ALL on every partition. Local pg_default_acl for role postgres still grants browser roles on new tables (measured 2026-09-28; #316 lists the rows). But the 29 loop closes each partition later in the seed. So after #314, a plain reset shows closed partitions even without this issue's fix. Step 7 gives two tests that copy prod's default grants and prove each creation path on its own.

What to do

1. [maintainer] Read-only prod precheck. Post the output as a comment on this issue. It gates the push in step 9, not the agent's work.

   -- a. The function on prod: return type must be void for CREATE OR REPLACE.
   select p.oid::regprocedure as sig, pg_get_userbyid(p.proowner) as owner,
          pg_get_function_result(p.oid) as returns, p.prosecdef, p.proacl,
          pg_get_functiondef(p.oid) as body
   from pg_proc p join pg_namespace n on n.oid = p.pronamespace
   where n.nspname = 'public' and p.proname = 'create_document_views_partitions';
   
   -- b. The event trigger and its function.
   select evtname, evtevent, pg_get_userbyid(evtowner) as owner, evtenabled,
          evtfoid::regprocedure as fn, evttags
   from pg_event_trigger where evtname = 'ensure_rls';
   
   select p.oid::regprocedure as sig, pg_get_userbyid(p.proowner) as owner,
          pg_get_function_result(p.oid) as returns, p.proacl,
          pg_get_functiondef(p.oid) as body
   from pg_proc p join pg_namespace n on n.oid = p.pronamespace
   where n.nspname = 'public' and p.proname = 'rls_auto_enable';
   
   -- c. Every partition: RLS and ACL.
   select c.relname, pg_get_userbyid(c.relowner) as owner, c.relrowsecurity, c.relacl
   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}$'
   order by c.relname;
   
   -- d. The cron job.
   select jobid, jobname, schedule, username, command
   from cron.job where jobname = 'create_document_views_partitions';

   Stop rules: (a) returns no row, returns is not void, or owner is not postgres; (c) a partition owner is not postgres. No row in (a) matters because a new function would get prod's open default EXECUTE grants. If one fires, do not push. The agent reports it on the issue.

   Body check: create or replace silently replaces whatever prod runs today, and nobody has read that body. Before the push, the agent compares the posted body from (a) with the repo body. If prod's body does anything the repo body does not, stop and report. An enable row level security line does not count as a difference.

2. [agent] Close partitions in the first DO block. In packages/supabase/scripts/09-document-views.sql ~66-102, inside the if not exists (...) then branch and after the _dedup_idx index, add:

   -- A partition checks its own RLS and grants, not the parent's.
   execute format('alter table public.%I enable row level security', partition_name);
   execute format('revoke all on public.%I from public, anon, authenticated', partition_name);

3. [agent] Close partitions in the cron function. In create_document_views_partitions (~105-152), keep the existing enable row level security statement (~145-148). Add the same revoke all ... from public, anon, authenticated line after it. Replace the 4-line comment above it (~141-144) with at most 4 lines that give the reason: the default ACL grants anon ALL on new tables. Update the comment on function text (~154-155) to say "with RLS enabled and no browser-role grants". Keep security definer, set search_path = public and returns void.

4. [agent] Fix the false comment in packages/supabase/scripts/13-RLS.sql ~263-274. State the truth in at most 4 lines:

   • 09-document-views.sql enables RLS and revokes browser roles on each partition it creates (the seed DO block and create_document_views_partitions).
   • The file name is 09-document-views.sql, not 21-document-views.sql.
   • Remove "Existing partitions get RLS enabled by the migration" and the "line below ... no-op" text. No such line exists.
   • Do not edit migrations/20260513130000_chat_rls_rollout_catchup.sql.

5. [agent] Record ensure_rls as a prod-only object in packages/supabase/CLAUDE.md. Add one bullet near the grants bullet (~32). Keep it short and in Simplified English:

   • Prod has an event trigger ensure_rls that calls public.rls_auto_enable() on ddl_command_end.
   • The repo does not create it. Local resets, new projects and self-hosted installs do not have it.
   • Call it "dashboard-owned" only if the maintainer confirms this on the issue. Step 1(b) owner output alone does not prove it. Otherwise say "prod-only, origin unverified".
   • Never rely on it. Every script or migration that creates a table in public enables RLS and revokes browser roles itself.
   • Do not recreate rls_auto_enable in the repo from a guessed body. If the maintainer's step 1 output shows its body and wants it in code, open a follow-up issue.

6. [agent] Add the paired migration. Create packages/supabase/migrations/<timestamp>_close_new_document_views_partitions.sql. Use a UTC YYYYMMDDHHMMSS timestamp later than Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration and later than every file in migrations/ at that time. First find Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration in migrations/. It is the file that ports the 2026-09-28 grant and RLS fix. If it is missing, Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 has not landed: stop and report on the issue. The file has three parts:

   • Part 1. create or replace function public.create_document_views_partitions() with the exact body from step 3, then the updated comment on function. Use CREATE OR REPLACE only. Never drop function: a drop plus create resets the ACL to prod's open defaults. That would undo emergency block E, which made the function service_role only. If the push fails because prod's return type is not void, stop and report. Do not fall back to a drop.
   • Part 2. A guarded loop that closes every existing partition. After 2026-10-01 00:05 UTC, whichever of the 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 migrations runs first on prod closes document_views_2027_01. That change is expected. Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 usually lands first, so on prod this loop is often a no-op.
   • Part 3. An assert that raises if any partition is still open.

   -- Part 2. Act only when needed, so a no-op run takes no ACCESS EXCLUSIVE lock.
   do $$
   declare r record;
   begin
     for r in
       select c.oid, c.relname, c.relrowsecurity, c.relowner
       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}$'  -- anchored; LIKE would match _daily
     loop
       if not pg_has_role(current_user, r.relowner, 'MEMBER') then
         raise exception 'partition % is owned by %, not by %',
           r.relname, pg_get_userbyid(r.relowner), current_user;
       end if;
       if not r.relrowsecurity then
         execute format('alter table public.%I enable row level security', r.relname);
       end if;
       if has_table_privilege('anon', r.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER')
          or has_table_privilege('authenticated', r.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') then
         execute format('revoke all on public.%I from public, anon, authenticated', r.relname);
       end if;
     end loop;
   end $$;
   
   -- Part 3. Any failure aborts the migration.
   do $$
   declare t record; bad 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.relkind = 'r'
         and c.relname ~ '^document_views_[0-9]{4}_[0-9]{2}$'
     loop
       if not t.relrowsecurity then
         bad := bad || ('RLS off on ' || t.relname);
       end if;
       if has_table_privilege('anon', t.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') then
         bad := bad || ('anon has rights on ' || t.relname);
       end if;
       if has_table_privilege('authenticated', t.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') then
         bad := bad || ('authenticated has rights on ' || t.relname);
       end if;
     end loop;
     if cardinality(bad) > 0 then
       raise exception 'document_views partition assert failed: %', bad;
     end if;
   end $$;

   Start the file with a -- Pair: packages/supabase/scripts/09-document-views.sql line, as other migrations do. No new loop is needed in scripts/. On a fresh reset, every partition comes from the fixed DO block. Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 owns the scripts loop that ports emergency block A.

7. [agent] Rebuild locally, then run the discriminating test.

   bun run --filter @docs.plus/supabase_back reset
   docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres \
     < packages/supabase/migrations/<timestamp>_close_new_document_views_partitions.sql
   # Run it a second time: it must succeed again (idempotent).
   docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres \
     < packages/supabase/migrations/<timestamp>_close_new_document_views_partitions.sql

   reset regenerates seed.sql and skips migrations (packages/supabase/config.toml ~53-58). It also wipes all local data. So apply the migration by hand, as packages/supabase/CLAUDE.md ~25 describes.

   Then run test A. It copies prod's default grants, re-creates the +3 month partition through the function, checks it, and rolls back. Save it to a scratch file outside the repo. Pipe it through the same docker exec -i ... psql form. Do not use supabase db query -f, because it fails on multi-statement input.

   begin;
   alter default privileges for role postgres in schema public
     grant all on tables to anon, authenticated;       -- copy prod's pg_default_acl
   do $$ begin
     execute format('drop table public.%I', 'document_views_'
       || to_char(date_trunc('month', current_date + interval '3 months'), 'YYYY_MM'));
   end $$;
   select public.create_document_views_partitions();
   select c.relname, c.relrowsecurity,
          has_table_privilege('anon', c.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') as anon_any,
          has_table_privilege('authenticated', c.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') as auth_any
   from pg_class c
   where c.relname = 'document_views_'
     || to_char(date_trunc('month', current_date + interval '3 months'), 'YYYY_MM');
   rollback;

   Expected: one row, relrowsecurity = t, anon_any = f, auth_any = f.
   Baseline, measured locally at HEAD 3bb26de06 on 2026-09-28: before the fix, test A gives t / t / t on the +3 month partition. Run it first to confirm the test bites.
   Sabotage check: copy the migration to a scratch file outside the repo. Remove the revoke all line from Part 1 of the copy. Apply the copy with the same docker exec form, then re-run test A. anon_any must now be t. Re-apply the real migration, re-run test A, and confirm f again. Do not re-run the whole 09-document-views.sql script for this check.

   Then run test B for the first DO block. After Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314, the 29-lint-hardening.sql §4 loop closes every partition later in the seed. So a plain reset cannot prove the DO block fix. Test B runs the DO block alone. Copy test A to a scratch file outside the repo. In the copy, replace the select public.create_document_views_partitions(); line with the whole first DO block from 09-document-views.sql (~66-102). Run it with the same docker exec form.

   Expected: the same one row, t / f / f. Baseline at HEAD 3bb26de06, measured the same way: f / t / t. Sabotage check: remove the new revoke all line from the pasted DO block in the scratch file. anon_any must now be t. Remove the enable row level security line too, and relrowsecurity must now be f.

8. [agent] Regenerate types and check the change set.

   bun run --filter @docs.plus/supabase_back types

   The signature does not change, so apps/webapp/src/types/supabase.ts should show no diff. Include it in the change set anyway. The change set is: scripts/09-document-views.sql, scripts/13-RLS.sql, packages/supabase/CLAUDE.md, the new migration, the regenerated packages/supabase/seed.sql, and apps/webapp/src/types/supabase.ts. Do not commit. Stop at a review checkpoint and hand the diff to the maintainer.

9. [maintainer] Commit with /commit-review, then push with the house procedure. Run bunx supabase migration list first. Set [db.migrations] enabled = true only while pushing, then set it back to false. Never migration repair --status applied a new version. This migration goes out with, or after, Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration. Then run the prod checks in Verification.

Out of scope

• Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314: the parent document_views, document_views_daily, document_view_stats and email_bounces grants. Also function EXECUTE revokes, including create_document_views_partitions. Also 29-lint-hardening.sql §3/§4 REVOKE ALL and porting emergency block A into scripts/.
• Stop new tables and functions from opening by default #316: prod's pg_default_acl. That is the root cause that opens every new table and function.
• Add a read-only grant guard to run after each db push #317: the read-only grant guard that runs after each db push.
• Close the remaining 47 linter warnings on functions #318: the lint 0028 row for rls_auto_enable and the trigger-function revokes.
• Ship the three migrations that prod's history lacks #313: the push procedure and the three migrations that prod's history lacks.
• Recreating ensure_rls or rls_auto_enable in the repo.
• Editing any applied migration.

Acceptance criteria

• scripts/09-document-views.sql: the first DO block enables RLS and runs revoke all ... from public, anon, authenticated on each partition it creates.
• create_document_views_partitions keeps enable row level security and adds the same revoke all line. It stays security definer, set search_path = public, returns void.
• scripts/13-RLS.sql §2j no longer claims a migration enabled partition RLS and no longer names 21-document-views.sql.
• migrations/20260513130000_chat_rls_rollout_catchup.sql is unchanged (git diff shows nothing for it).
• packages/supabase/CLAUDE.md has one bullet that names ensure_rls / rls_auto_enable as prod-only and says never to rely on it. It says "dashboard-owned" only if the maintainer confirmed that on the issue.
• A new migration, timestamped after Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration, uses create or replace (no drop function). It contains the guarded loop and the assert from step 6.
• The migration applies twice in a row on a fresh local reset with no error.
• After a fresh reset, every document_views_YYYY_MM partition has relrowsecurity = t.
• Step 7 test A (the function) returns t / f / f. With the revoke line removed from the migration copy, it returns anon_any = t.
• Step 7 test B (the first DO block) returns t / f / f. With the revoke line removed from the scratch copy, it returns anon_any = t.
• seed.sql is regenerated, not hand-edited. supabase.ts is regenerated by the command.
• No commit, push or prod write was made by the agent.

Verification

Local, after step 7:

select c.relname, c.relrowsecurity,
       has_table_privilege('anon', c.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') as anon_any,
       has_table_privilege('authenticated', c.oid, 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') as auth_any
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}$'
order by 1;

Expected: four rows (the current month and the next 3), each t / f / f.

grep -n "revoke all on public.%I from public, anon, authenticated" packages/supabase/scripts/09-document-views.sql
# Expected: exactly 2 matches, one in the first DO block and one in the function.
# #314 ports emergency block A into 29-lint-hardening.sql, not into this file.
grep -n "21-document-views" packages/supabase/scripts/13-RLS.sql
# Expected: no output.
grep -n "drop function" packages/supabase/migrations/*_close_new_document_views_partitions.sql
# Expected: no output.

Prod, [maintainer], read-only, after the push:

• Re-run step 1 query (c). Expected: every partition shows relrowsecurity = t, and relacl has no anon= or authenticated= entry.
• Re-run step 1 query (a). Expected: body contains revoke all on public.%I from public, anon, authenticated. proacl still lists no anon or authenticated EXECUTE, and no =X/ PUBLIC entry.
• After the next cron run (1st of the month, 00:05 UTC), re-run query (c). Expected: the new partition is closed, with no manual step.
• Security linter: no rls_disabled_in_public row for any document_views_* table.

House rules

• Scripts first, then the paired migration. Every change in migrations/ is mirrored in scripts/*.sql, then seed.sql is regenerated (packages/supabase/CLAUDE.md ~27).
• Never edit an applied migration. Never hand-edit packages/supabase/seed.sql or apps/webapp/src/types/supabase.ts. After any SQL change, run bun run --filter @docs.plus/supabase_back types and include the file.
• Bun only. Never run npm, yarn, pnpm or npx.
• No commits, pushes or stashes unless the maintainer asks. End at a review checkpoint.
• Tests are opt-in. Do not add a test file for this change. The step 7 SQL tests A and B are enough.