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

Key: SPR-00 · Type: tracking · Blocked by: none · Checked against: 0b302b2

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.

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, an 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.
  • 2026-11-07 (Ship and measure the cron-health sampling change #323 hour): 49,464 ms of DB time, list_changes 40,671 ms (82 %).
• Next partition: the monthly cron (1st of the month, 00:05 UTC) runs create_document_views_partitions. At review time, its 1 October 2026 run was due to create 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: amber. Both issues in the red list, #313 and #314, are closed. #322, #323, #324, #325 and #755 are 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. The 2026-11-10 has_function_privilege query returns anon false on all three invoker notification RPCs.

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

Benchmark ledger

Condition: Supabase production project, Postgres 15.8.1.073 until #322 lands. The maintainer reads each row in the SQL editor or Advisors > Security, with the date. An agent in the repo updates one row when a child posts a number. Quote numbers only from their #763 row, which links each child's Measured comment.

Claim (true today): "A production review of the docs.plus Supabase database closed 7 of 7 security linter errors."

Add this sentence only after the #755 after-read:

"Signed-out visitors can run 0 of 3 notification functions (was 3)."

The grant guard cannot see invoker functions, so it gives no claim about them. Mirror the claim on #763.

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.
• Revoke anon and public EXECUTE on three invoker notification RPCs #755 (AFK) Revoke anon and public EXECUTE on three invoker notification RPCs. Ruled 2026-11-10. Push it before the Find the cause of the 13-15 s realtime list_changes spikes #325 reset and the Upgrade Supabase Postgres from 15.8.1.073 to 17 #322 baseline.

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 Supabase Postgres from 15.8.1.073 to 17 #322 (HITL) Upgrade Supabase Postgres from 15.8.1.073 to 17. Rehearse first.

Performance

• Ship and measure the cron-health sampling change #323 (HITL) Ship and measure the cron-health sampling change. Hour measured 2026-11-07 (40 calls, pass). The 7-day guard passed on 2026-11-11. The Grafana state-history line is left, by 2026-11-17.
• 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

1. Push the invoker-RPC migration (Revoke anon and public EXECUTE on three invoker notification RPCs #755).
2. Wait for its clean 24 h GlitchTip read. Then, on one day, run Find the cause of the 13-15 s realtime list_changes spikes #325 step 4 and start Measure prod memory, swap and CPU for 7 days before any compute change #324.
3. On day 8 or later, run Upgrade Supabase Postgres from 15.8.1.073 to 17 #322 steps 4-7.
4. Upgrade Supabase Postgres from 15.8.1.073 to 17 #322 step 8.

#323: the guard passed on 2026-11-11. The maintainer posts the Grafana state-history line for cron-stale-frequent (2026-11-03 to 2026-11-10) by 2026-11-17, then closes it.

#322 and the #325 reset both reset pg_stat_statements. So the #325 reads from 2026-10-25 are posted first, and #322 step 1b saves its query-cost before.

Not yet owned

Green needs a ruling or an owner for each item the review found. Each item now has one:

• Three more SECURITY INVOKER RPCs with browser callers and no explicit grant: get_unread_notifications_paginated, get_channel_notif_state and get_workspace_notifications. Ruled 2026-11-10. Revoke anon and public EXECUTE on three invoker notification RPCs #755 owns the fix.

Moved out: #570 (v2.4.0) owns the pg_default_acl rows for role postgres in schema storage.

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.