[Tracking] Supabase prod security and performance review, 2026-09-28 #327

Key: SPR-00 · Type: tracking · Blocked by: none · Checked against: 3bb26de

Purpose

This issue tracks the review of the Supabase prod project on 2026-09-28. The review covered security and performance. It lists the public issues that the review filed, and holds one flag.

On 2026-09-28 at 09:29Z, the Supabase security linter flagged 7 ERROR items on prod (Postgres 15.8.1.073). The root cause is in the repo. The sweeps in packages/supabase/scripts/29-lint-hardening.sql §3, §4, §5 and §8 have no paired migration, so prod never ran them.

Two defaults keep each new prod object open:

• 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. A REVOKE ... FROM anon does not remove that grant, and anon inherits it.

The source reports are the maintainer's local reports. Each child issue copies the facts it needs, so an agent can work from the repo and the issue alone.

What was applied to prod on 2026-09-28

At about 13:38Z, the HoE (the Head of Engineering agent) applied an emergency fix to prod through the SQL connector. The maintainer approved it. It changed grants and RLS only. It changed no function body.

• Transaction 1 (tables):
  • RLS was off on document_views_2026_03, _04, _05 and PushSentLog. Now RLS is on for every document_views partition, the parent, document_view_stats, document_views_daily, email_bounces and PushSentLog.
  • anon and authenticated lost every grant on those tables. service_role keeps its access.
  • anon lost every grant on admin_users, so the is_admin revoke below breaks no guest read.
  • anon lost table-level SELECT on public.users. It got a column grant back without email, so guests can still read username.
• Transaction 2 (functions):
  • 27 functions became service_role only. Examples: purge_document_footprint, consume_push_queue, get_email_bounces, and the four prod-only functions.
  • is_admin was revoked from anon. authenticated keeps it.

Results after the change:

• Security linter: 0 ERROR (was 7). Lint 0028 fell from 72 to 47 rows.
• The built-in asserts in both transactions passed. Cron jobs 2, 8, 17, 25 and 28 ran and succeeded afterwards.
• get_channel_aggregate_data was not touched, so guest chat still works.

Prod now differs from the repo. A fresh db reset or a self-hosted install still starts open. Any later migration that drops and re-creates one of these functions resets its grants to the open defaults. #314 ports the slice into the repo.

Other facts at the time of the review:

• Migration history: prod history ends at 20260921120000. The repo has three later migrations: 20260922120000 (commit f6ed0a22c), 20260928120000 (commit e4113fd9d) and 20260928130000 (commit 3ef541d8a). Ship the three migrations that prod's history lacks #313 ships them.
• Performance: all SQL together uses 35.0 s of DB time per hour, 0.97% of one core, in a 69.8-minute prod window. Realtime list_changes takes 78% of that. The database is 62 MB. Supabase bills compute by instance size, so query work saves $0.
• Next partition: the monthly cron (1st of the month, 00:05 UTC) runs create_document_views_partitions. On 1 October it creates document_views_2027_01 with the open default grants.
  • On prod, the ensure_rls event trigger turns RLS on, so the new partition stays closed. The repo has no such trigger.
  • In the repo, create_document_views_partitions in packages/supabase/scripts/09-document-views.sql (~105) turns RLS on but revokes no grant. The first DO block (~66) does neither. Make new document_views partitions start closed #315 owns both gaps.

The flag

Flag: red. Every issue in the red list is still open.

The red list is #313 and #314. #314 covers a prod state the repo cannot rebuild, and #313 must land before it.

• Red: an issue in the red list is still open.
• Amber: every issue in the red list is closed, but another issue below is still open. Parked Supabase query changes and when to reopen them #326 does not count.
• Green: every issue below is closed, except Parked Supabase query changes and when to reopen them #326, which stays open as a record. The grant guard from Add a read-only grant guard to run after each db push #317 runs clean on prod, and the security linter shows 0 ERROR and no lint 0028 row that lacks a ruling.

Update the flag when an issue closes. Move to amber only when every issue in the red list is closed.

The issues

Urgent decisions

• Ship the three migrations that prod's history lacks #313 (HITL) Ship the three migrations that prod's history lacks. They must land before Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314.

Security repo fix

• Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 (AFK) Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration. A fresh reset must equal prod.
• Make new document_views partitions start closed #315 (AFK) Make new document_views partitions start closed. Each new partition gets RLS on and no browser-role grants.
• Add a read-only grant guard to run after each db push #317 (AFK) Add a read-only grant guard to run after each db push.
• Close the remaining 47 linter warnings on functions #318 (AFK) Close the remaining 47 linter warnings on functions. They cover self-gated definer functions and trigger functions.
• Drop the dead create_direct_message_channel function #319 (AFK) Drop the dead create_direct_message_channel function. Its unused webapp wrapper goes too.

Security rulings

• Stop new tables and functions from opening by default #316 (HITL) Stop new tables and functions from opening by default. The ruling: a global default revoke, or per-migration revokes plus the guard.
• Remove prod-only drift: four functions and the PushSentLog table #320 (HITL) Remove prod-only drift: four functions and the PushSentLog table. Read the bodies and dependencies first.

Platform

• Remove pg_net from prod #321 (HITL) Remove pg_net from prod. The repo no longer uses it, and it cannot be moved out of public.
• Upgrade prod Postgres from 15.8.1.073 #322 (HITL) Upgrade prod Postgres from 15.8.1.073. Rehearse first.

Performance

• Ship and measure the cron-health sampling change #323 (HITL) Ship and measure the cron-health sampling change. It moves sampling from 60 s to 180 s and is in the working tree now.
• Measure prod memory, swap and CPU for 7 days before any compute change #324 (HITL) Measure prod memory, swap and CPU for 7 days before any compute change.
• Find the cause of the 13-15 s realtime list_changes spikes #325 (HITL) Find the cause of the 13-15 s realtime list_changes spikes.

Records

• Parked Supabase query changes and when to reopen them #326 (record) Parked Supabase query changes and when to reopen them. It also holds the do-not-do list, and it stays open as a record.

Tracked privately

Five further security items (SPR-01, SPR-08, SPR-11, SPR-12, SPR-13) follow SECURITY.md. Each is filed here after it is fixed on prod.

Order and dependencies

Dated checks. Each is a read-only [maintainer] step:

• Before 2026-09-29 04:15 UTC: Find the cause of the 13-15 s realtime list_changes spikes #325 step 1. The row for job 37's first run may expire as early as then.
• After 2026-10-01 00:05 UTC: Make new document_views partitions start closed #315 step 1 query (c). Repeat after each monthly cron run until Make new document_views partitions start closed #315 ships.
  • relrowsecurity = f on the new partition means it is open to anon. Treat that as urgent, and post it on Make new document_views partitions start closed #315.
  • anon= or authenticated= grants on it are expected until Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 or Make new document_views partitions start closed #315 ships. Re-running emergency block A is a prod write, so it is the maintainer's call.

Order:

• Find the cause of the 13-15 s realtime list_changes spikes #325 steps 1-2 come before any pg_stat_statements reset (Find the cause of the 13-15 s realtime list_changes spikes #325 step 4) and before Upgrade prod Postgres from 15.8.1.073 #322. Both reset those counters.
• Ship the three migrations that prod's history lacks #313 comes before Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314. The new paired migration must sort after 20260928130000.
• Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 blocks Make new document_views partitions start closed #315, Stop new tables and functions from opening by default #316, Add a read-only grant guard to run after each db push #317, Close the remaining 47 linter warnings on functions #318, Drop the dead create_direct_message_channel function #319, Remove prod-only drift: four functions and the PushSentLog table #320 and Remove pg_net from prod #321.
• The guard from Add a read-only grant guard to run after each db push #317 reports failures until Stop new tables and functions from opening by default #316 and Close the remaining 47 linter warnings on functions #318 land. That issue lists the rows to expect until then.
• Upgrade prod Postgres from 15.8.1.073 #322 waits for Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 and Remove pg_net from prod #321.
• Ship the three migrations that prod's history lacks #313, Ship and measure the cron-health sampling change #323, Measure prod memory, swap and CPU for 7 days before any compute change #324, Find the cause of the 13-15 s realtime list_changes spikes #325 and Parked Supabase query changes and when to reopen them #326 have no blockers. They can run at any time.

Not yet owned

No issue owns these yet. Green needs a ruling or an owner for each:

• Existing anon table rights on prod outside the read allowlist. The Add a read-only grant guard to run after each db push #317 guard lists them on its first prod run (step 7).
• Three more SECURITY INVOKER RPCs with browser callers and no explicit grant: get_unread_notifications_paginated, get_channel_notif_state and get_workspace_notifications. Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 grants only its five.
• Dropping get_bookmark_count and admin_get_user_notification_subs. Close the remaining 47 linter warnings on functions #318 makes both service_role only.
• pg_default_acl rows for role postgres in schema storage. Stop new tables and functions from opening by default #316 step 2a reads them.

What we decided not to do

The review measured and rejected 14 query ideas and parked 4 more. Examples: moving chat postgres_changes to broadcast, the advisor's channel_members (channel_id) index, and a Micro downgrade before memory data exists. The numbers and the reopen triggers are in #326. Do not re-propose one of these ideas without new numbers.