Upgrade prod Postgres from 15.8.1.073 #322

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

Problem

Prod Postgres runs image supabase-postgres-15.8.1.073, and the Supabase security linter flags it as vulnerable_postgres_version (WARN). The image misses later Supabase security patches. Severity is MEDIUM (OWASP A06:2021, vulnerable and outdated components).

This is patch debt, not a known open path. Most Postgres CVEs need a SQL session. The browser roles reach SQL only through PostgREST, which lowers the risk. The grant and RLS gaps in #314 and the pg_net drop in #321 come first.

The upgrade takes the whole Supabase project offline, including Auth. During that time, sign-in, chat, push, email and document-view processing stop. So it needs a rehearsal, a planned window, and a fixed list of checks before and after.

Context

Labels: [prod] = read on prod, read-only, by the HoE (the review's lead agent), 2026-09-28. [local] = local Docker stack, same image. [repo] = this tree. [docs] = Supabase upgrade page, read 2026-09-28. [pred] = predicted, not measured.

Prod facts

Extension versions. These are the local versions: pg_cron 1.6, pgmq 1.4.4, pg_net 0.14.0, pgjwt 0.2.0, pg_graphql 1.5.11, supabase_vault 0.3.1 [local]. Prod runs the same image, but nobody has read prod's pg_extension yet. Step 1 does that.

The 12 cron jobs (all ran with no failures, 2026-09-21..28) [prod]:

The four pgmq queues [repo]: push_notifications (07-4-push-notifications-pgmq.sql ~36), email_notifications_queue (07-5-email-notifications-pgmq.sql ~83, non-partitioned), message_counter (09-message_counter.sql ~71), document_views (09-document-views.sql ~252). The repo calls only pgmq.send, read, archive, delete, create, create_non_partitioned and metrics_all. public.get_pgmq_queue_metrics (07-4-push-notifications-pgmq.sql ~606) selects metrics_all() columns by name, so a new column in a newer pgmq does not break it [repo]. The scripts also touch pgmq internals. They declare pgmq.message_record variables (09-message_counter.sql ~167, 09-document-views.sql ~355). They read queue tables directly: pgmq.q_push_notifications and pgmq.a_push_notifications (07-4-push-notifications-pgmq.sql ~433-439), and pgmq.q_email_notifications_queue (07-5-email-notifications-pgmq.sql ~874). A newer pgmq may change those shapes [pred]. Step 3 rebuilds them on the new image.

What the Supabase upgrade page says [docs] (https://supabase.com/docs/guides/platform/upgrading):

• Start it with the "Upgrade project" button on the project's General settings page. The security report named Settings > Infrastructure. Use whichever page shows the button. That page also shows a time estimate.
• The upgrade uses pg_upgrade. "Your project is taken offline", and a new instance is created on the new version. The default GP3 disk gives about 100 Mbps.
• "In projects using Postgres 17, the following extensions are deprecated: plcoffee, plls, plv8, timescaledb, pgjwt." On pgjwt: "If you weren't explicitly using pgjwt in your project, it's most likely safe to disable."
• "Logical replication slots must be dropped." The page does not say whether the two Supabase-managed Realtime slots count.
• During the upgrade, pg_cron is dropped and recreated, and cron.job_run_details is duplicated first. A very large table can fail the upgrade. Here it is 18 MB, because job 37 prunes it daily since 2026-09-21.
• The page does not say that the upgrade runs ANALYZE. Step 7 checks the table statistics.
• pg_upgrade refuses reg* columns that reference system OIDs. Custom roles with md5 passwords need manual migration. The repo creates no custom roles [repo: no create role in packages/supabase/].
• "If the upgrade should fail, your original database would be brought back up online." The page describes no downgrade after a successful upgrade.
• pg_graphql 1.6.0 turns off introspection by default. config.toml ~13 exposes graphql_public, but no app code calls GraphQL. Only the generated apps/webapp/src/types/supabase.ts mentions it [repo].

Target version. Confirm it in the dashboard. It is likely a Postgres 17 image [pred]. If the dashboard offers only a newer 15.x image, skip the Postgres 17 extension step. Keep every other check.

Downtime. 62 MB is small, so fixed platform overhead will dominate. The security report says "plan for tens of minutes" [pred]. Use the dashboard's estimate if it shows one. Book a 60-minute window.

No downgrade. If the upgrade fails, the old database comes back [docs]. After a successful upgrade, the only way back is a restore from a backup [pred]. That loses every write made after the backup.

Alerts will page during the window [repo]. sampleCronJobHealth in apps/hocuspocus.server/src/lib/workerMetricsSampler.ts ~96 calls cronJobLastSuccess.reset() when get_cron_job_health fails. The worker samples cron health every 60 s at HEAD, or every 180 s once #323 ships. The cron-stale-frequent, cron-stale-daily, cron-stale-weekly and cron-stale-monthly rules in scripts/observability/grafana/provisioning/alerting/rules-supabase-jobs.yml ~86-214 use noDataState: Alerting with for: 5m. So an outage longer than about 5 to 8 minutes pages the ops channel (Telegram). pgmq-message-age (>15 m) and pgmq-poll-stalled (>2 m) in the same file may fire just after the database returns.

Risk: new cron job ids [pred]. pg_cron is dropped and recreated, so the job ids may change. get_cron_job_health joins cron.job_run_details on jobid, with no foreign key. If an id changes, that job's old run history is orphaned, and last_success_at reads NULL until its next successful run. The worker then emits 0, and the matching cron-stale-* rule pages. The daily jobs recover within a day. cleanup_old_document_views waits for Sunday 03:00 UTC. create_document_views_partitions waits for the 1st of the month at 00:05 UTC. Step 7 compares the ids.

Why it waits for #314 and #321.

• Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 puts the 2026-09-28 prod grant and RLS fix into scripts/ and a migration. Until then, a fresh local reset does not equal prod, so a local rehearsal proves less.
• Remove pg_net from prod #321 drops pg_net. It is not relocatable and sits in the linter's extension_in_public list. Drop it before the upgrade, so the upgrade carries one less extension.

What to do

1. [maintainer] Record the baseline on prod (read-only). Run this in the SQL editor. Save the output; step 7 compares against it.

   show server_version;
   select e.extname, e.extversion, n.nspname as schema
   from pg_extension e join pg_namespace n on n.oid = e.extnamespace
   order by e.extname;
   select jobid, jobname, schedule, active, username from cron.job order by jobname;
   select count(*) as run_details_rows,
          pg_size_pretty(pg_total_relation_size('cron.job_run_details')) as size
   from cron.job_run_details;
   select slot_name, plugin, slot_type, active from pg_replication_slots;
   select tablename from pg_publication_tables where pubname = 'supabase_realtime' order by 1;
   select evtname, evtevent, evtfoid::regproc from pg_event_trigger order by 1;
   select queue_name, queue_length, oldest_msg_age_sec from pgmq.metrics_all();

   Expected: 12 cron jobs, all active. Two Realtime slots. Seven publication tables. The ensure_rls event trigger. pg_net absent if Remove pg_net from prod #321 landed. Keep the jobid of each job; step 7 compares them.

2. [maintainer] Run the upgrade blockers check on prod (read-only).

   -- a) Any user function that may call pgjwt (sign, verify, url_encode ...).
   --    Review each hit by hand; math "sign(" is a false positive.
   select n.nspname, p.proname
   from pg_proc p join pg_namespace n on n.oid = p.pronamespace
   where n.nspname not in ('pg_catalog', 'information_schema', 'extensions')
     and p.prosrc ~* '\m(sign|verify|url_encode|url_decode|algorithm_sign)\s*\(';
   
   -- b) Objects outside pgjwt that depend on a pgjwt function (views, defaults).
   select d.classid::regclass, d.objid, d.deptype
   from pg_depend d
   where d.refclassid = 'pg_proc'::regclass
     and d.refobjid in (
       select m.objid from pg_depend m
       where m.classid = 'pg_proc'::regclass
         and m.refclassid = 'pg_extension'::regclass
         and m.refobjid = (select oid from pg_extension where extname = 'pgjwt')
         and m.deptype = 'e')
     and not exists (
       select 1 from pg_depend x
       where x.classid = d.classid and x.objid = d.objid
         and x.refclassid = 'pg_extension'::regclass and x.deptype = 'e');
   
   -- c) reg* columns that pg_upgrade refuses (regclass, regtype, regrole are fine).
   select c.oid::regclass as table_name, a.attname, a.atttypid::regtype
   from pg_attribute a
   join pg_class c on c.oid = a.attrelid
   join pg_namespace n on n.oid = c.relnamespace
   where a.attnum > 0 and not a.attisdropped
     and n.nspname not in ('pg_catalog', 'information_schema')
     and a.atttypid in ('regproc'::regtype, 'regprocedure'::regtype, 'regoper'::regtype,
                        'regoperator'::regtype, 'regconfig'::regtype,
                        'regdictionary'::regtype, 'regnamespace'::regtype,
                        'regcollation'::regtype);
   
   -- d) Logical slots that are not Supabase Realtime's own.
   select slot_name from pg_replication_slots
   where slot_type = 'logical' and slot_name not like 'supabase_realtime%';

   Expected: (a) no hit that uses pgjwt, (b) 0 rows, (c) 0 rows, (d) 0 rows. The repo has no SQL use of pgjwt [repo]. If (a) or (b) shows a real user, stop and file a follow-up issue.

3. [agent] Rehearse the schema build on the target major, locally. This proves scripts/*.sql and the seed build on the new image. It does not prove prod data.

   • This wipes the local Supabase volume, which other worktrees share. Get the maintainer's OK first. The local stack is a weaker copy of prod: its publication has five tables, not seven, and it has no ensure_rls event trigger.
   • In packages/supabase/config.toml ~36, set major_version to the target major from the dashboard (for example 17). Do not commit it yet.
   • From packages/supabase, run bunx dotenv -e ../../.env.local -- bunx supabase stop --no-backup to drop the old volume. This mirrors the package stop script. Then, from the repo root, run bun run --filter @docs.plus/supabase_back start and bun run --filter @docs.plus/supabase_back reset.
   • Run the step 1 SQL on the local database: docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres. Expect 12 cron jobs and four queues. Local job ids differ from prod.
   • Prove each pgmq call on a throwaway queue, not a live one. The cron jobs and the worker drain the live queues. Run select pgmq.create('upgrade_probe'); and select pgmq.send('upgrade_probe', '{}'::jsonb); twice. Then run select * from pgmq.read('upgrade_probe', 30, 2);, select pgmq.archive('upgrade_probe', <first msg_id>); and select pgmq.delete('upgrade_probe', <second msg_id>);. End with select pgmq.drop_queue('upgrade_probe');.
   • Run bun run --filter @docs.plus/supabase_back types. Keep the regenerated apps/webapp/src/types/supabase.ts if it changed.
   • Do not use the chatroom Cypress specs as proof. stubChatroom in apps/webapp/cypress/support/chatroomFixtures.ts ~52 intercepts every Auth and REST call, so those specs never reach the database.
   • Smoke-test chat by hand against the local stack instead. The fresh reset has no pad, so sign in first. From the repo root, run bun run dev. Sign in with an email link; the local mail UI is http://localhost:54324 (config.toml ~100-103). Create a pad and send a chat message. Open the pad as a guest in a second browser, and see the next message arrive live.
   • If any step fails on the new image, stop and report the error. Do not work around it in prod.
   • If the prod upgrade will not follow within a few days, set major_version back to 15 and repeat the stop, start and reset.

4. [maintainer] Rehearse on a copy with prod data, if the plan allows. Prefer a new project restored from a prod backup, then upgrade that copy with the same button. A Supabase branch is a weak rehearsal here. It builds from migrations/, and those do not build from empty (config.toml ~53-58). Record the copy's upgrade duration. Delete the copy afterwards, because compute is billed while it exists.

5. [maintainer] Prepare the window.

   • Stop unless the saved reads are done. Find the cause of the 13-15 s realtime list_changes spikes #325 steps 1-2 must be pasted, and the maintainer must confirm that no other saved read is still owed. The upgrade resets those counters.
   • Pick a low-traffic hour. Avoid the 1st of the month at 00:05 UTC and Sunday at 03:00 UTC, when the monthly and weekly jobs run. Tell active users if any are known.
   • Confirm a fresh backup exists in the dashboard (Database > Backups). Optionally take logical dumps from packages/supabase: bunx supabase db dump --linked -f <path outside the repo> for the schema, then the same with --data-only.
   • Silence the six rules of the Grafana supabase-jobs group for 90 minutes: pgmq-poll-stalled, pgmq-message-age and cron-stale-frequent, -daily, -weekly, -monthly.
   • If step 2 (a) and (b) were clean and the target is Postgres 17, disable pgjwt in Database > Extensions.
   • Do not drop the two supabase_realtime* slots by hand. If the dashboard's pre-check refuses because of them, stop and ask Supabase support.

6. [maintainer] Run the upgrade. Use "Upgrade project" on the General settings page (or Settings > Infrastructure). Note the start and end time in UTC. If it fails, the docs say the old database comes back online. Then re-run step 1 and record what failed on this issue.

7. [maintainer] Post-checks on prod, in this order.

   • Re-run the step 1 SQL. Compare: new server_version; same 12 jobs, all active; both Realtime slots active = true; same seven publication tables; ensure_rls, pgrst_ddl_watch and pgrst_drop_watch still present. Other Supabase-managed event triggers may differ on the new image.

   • Compare each job's jobid with the baseline. If any id changed, expect the matching cron-stale-daily, -weekly or -monthly rule to page until that job's next success. Keep a silence only for that job: match its jobname label, not the whole rule. Note the ids on this issue.

   • Refresh planner statistics. Before Postgres 18, pg_upgrade does not carry them, and it resets the counters in pg_stat_user_tables. Run select relname, last_analyze, last_autoanalyze from pg_stat_user_tables where schemaname = 'public' order by relname;. Unless every busy table shows a time after the upgrade, run analyze;. At 62 MB it is short. It is a maintenance write, not a schema change.

   • After 30 minutes, check the cron runs:

     select j.jobname,
            max(d.start_time) as last_start,
            (array_agg(d.status order by d.start_time desc))[1] as last_status
     from cron.job j
     left join cron.job_run_details d
       on d.jobid = j.jobid and d.start_time > now() - interval '1 hour'
     group by j.jobname
     order by j.jobname;

     Every job on a 1, 2, 5, 10 or 15-minute schedule must show succeeded. Daily, weekly and monthly jobs show NULL until their slot; check them the next day.

   • Re-run select queue_name, queue_length, oldest_msg_age_sec from pgmq.metrics_all();. All four queues must be present. oldest_msg_age_sec must be NULL (empty queue) or below 300 within 15 minutes.

   • App checks: send a chat message signed in and see it arrive live in a second browser. Get a push notification on a second device. See one notification email leave email_queue. Open a document and see its view count rise after the next process_document_views_queue run.

   • Open Advisors > Security. vulnerable_postgres_version must be gone. The ERROR count must stay 0. If Add a read-only grant guard to run after each db push #317 has landed, run its guard and compare with its expected rows.

   • Remove the Grafana silences, except any kept for changed job ids. The other supabase-jobs rules must be Normal.

8. [agent] Bring the repo in line, after step 7 passes.

   • Set major_version in packages/supabase/config.toml ~36 to the prod major from show server_version.
   • If pgjwt was disabled, fix packages/supabase/CLAUDE.md ~33. It says pgjwt lives in extensions; that is no longer true. Remove pg_net from prod #321 edits the same bullet, so edit its current text.
   • Run bun run --filter @docs.plus/supabase_back reset and bun run --filter @docs.plus/supabase_back types. Include apps/webapp/src/types/supabase.ts if it changed.
   • Leave the change uncommitted for the maintainer's /commit-review.

Out of scope

• Porting the 2026-09-28 grant and RLS fix into the repo: Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314.
• Dropping pg_net and fixing its "relocatable" claim: Remove pg_net from prod #321.
• The read-only grant guard: Add a read-only grant guard to run after each db push #317.
• Any compute size change: Measure prod memory, swap and CPU for 7 days before any compute change #324.
• The 13-15 s Realtime list_changes spikes: Find the cause of the 13-15 s realtime list_changes spikes #325.
• Any SQL change beyond config.toml and the one CLAUDE.md line.

Acceptance criteria

• The step 1 baseline and the step 2 blocker results are recorded in a comment on this issue.
• Step 2 shows no pgjwt user, no refused reg* column, and no non-Realtime logical slot.
• A local reset on the target major succeeds, with 12 cron jobs and four pgmq queues.
• On the local target-major stack, a guest reads a pad's chat, and a signed-in message arrives live in a second browser.
• Prod show server_version reports a newer version than 15.8, and the linter no longer lists vulnerable_postgres_version.
• The linter ERROR count is still 0 after the upgrade.
• All 12 cron jobs are active. Every sub-hourly job shows succeeded within one hour. Daily, weekly and monthly jobs succeed on their next slot.
• Any change of cron job id is recorded on this issue.
• Both supabase_realtime* slots are active, and the publication still lists the same seven tables.
• All four pgmq queues are present and draining, with oldest_msg_age_sec NULL or below 300.
• Chat, push, email and document views each work once end to end on prod.
• packages/supabase/config.toml major_version matches prod, and a local reset passes with it.

Verification

• Prod version: show server_version; returns a version newer than 15.8. The dashboard shows the new image, and the linter no longer names 15.8.1.073.
• Linter: Advisors > Security shows no vulnerable_postgres_version row and 0 ERROR rows.
• Cron: within one hour, the step 7 cron query shows succeeded for update-user-status, aggregate_document_view_stats, cleanup-push-subscriptions, process_email_queue, compile_digest_emails, message_counter_batch_job and process_document_views_queue. Use names, because ids may change.
• Queues: select queue_name, oldest_msg_age_sec from pgmq.metrics_all(); returns four rows, each NULL or below 300.
• Slots: select slot_name, active from pg_replication_slots; returns both Realtime slots with active = t.
• Grafana: the supabase-jobs rules are Normal 30 minutes after the silences end. A rule kept silent for a changed job id is Normal after that job's next run.
• Repo: grep -n major_version packages/supabase/config.toml shows the prod major. bun run --filter @docs.plus/supabase_back reset exits 0.

House rules

• Bun only. Never run npm, yarn, pnpm or npx. Use bunx supabase for the CLI.
• Agents have no prod access. Every prod read or write in this issue is a [maintainer] step.
• Never hand-edit packages/supabase/seed.sql or apps/webapp/src/types/supabase.ts. Regenerate them with the reset and types scripts.
• Keep [db.migrations] enabled = false in config.toml. Never edit an applied migration.
• No commits unless the maintainer asks. Leave repo changes for /commit-review.