Remove pg_net from prod #321

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

Problem

Prod has the pg_net extension registered in schema public, and nothing in the repo uses it. On 2026-09-28 the Supabase security linter flagged it as WARN 0014_extension_in_public.

pg_net gives the database a server-side HTTP client (net.http_post, net.http_get). An unused HTTP client inside the database is attack surface with no benefit. The fix is to drop it. It cannot be moved, because it is not relocatable.

The repo also carries two false claims that pg_net "is relocatable". It still installs pg_net on every local reset. The Postgres upgrade (#322) waits for this drop.

Context

Labels: [prod] = read on prod on 2026-09-28. [local] = measured in the local container supabase_db_docsplus_supabase, not prod. It runs image supabase/postgres:15.8.1.073, the same version as prod. [repo] = read at HEAD 3bb26de06.

Why the prechecks matter: a plpgsql body that names net.* is not a tracked dependency. It survives the drop and fails only when it runs. A cron job command is plain text, so it fails the same way.

What a drop loses: rows in net.http_request_queue and net._http_response. Both hold transient request data only.

The false claims in the repo:

• packages/supabase/scripts/00-bootstrap.sql ~9-13: a comment that says "pg_net is relocatable", then create extension if not exists pg_net with schema extensions;.
• packages/supabase/CLAUDE.md ~33 (the rule "The seed batch runs with an empty search_path..."): "pg_net is relocatable and does not".
• packages/supabase/README.md ~24 lists pg_net among the extensions that 00-bootstrap.sql creates.

The real cause of the old 3F000 reset failure is still true. pg_net's control file names no schema. So a bare create extension pg_net under the seed's empty search_path raises 3F000: no schema has been selected to create in. Relocatability has nothing to do with it.

00-bootstrap.sql is not part of seed.sql. packages/supabase/generate-seed.ts ~11-18 (excludePatterns) excludes it. config.toml ~70 ([db.seed].sql_paths) runs it before seed.sql. So this change does not alter seed.sql.

What to do

1. [maintainer] Run three read-only prechecks on prod. Use the Supabase SQL editor. Post the results of all five queries on this issue. Every query only reads.

   -- Check 1: no cron job calls net.*  (expect 0 rows)
   select jobid, jobname from cron.job where command ~ '\mnet\.';
   -- Cross-check: expect 12. Fewer means this role cannot see every job.
   select count(*) from cron.job;
   
   -- Check 2: no function body outside schema net calls net.*  (expect 0 rows)
   select n.nspname, p.proname
   from pg_proc p
   join pg_namespace n on n.oid = p.pronamespace
   where n.nspname <> 'net'
     and p.prosrc ~ '\mnet\.'
     and (n.nspname, p.proname) not in (
       ('extensions', 'grant_pg_net_access'),
       ('supabase_functions', 'http_request')
     );
   
   -- Check 3: no Database Webhooks  (expect 0; "relation does not exist" also passes)
   select count(*) from supabase_functions.hooks;
   
   -- Record for rollback: where pg_net lives and its version
   select extname, extnamespace::regnamespace as schema, extversion
   from pg_extension where extname = 'pg_net';

   If any check fails, stop. Name the caller on this issue. Do not drop pg_net. The fallback is drop and re-create with schema extensions, after the caller is ruled on.

2. [agent] Remove the install line from bootstrap. In packages/supabase/scripts/00-bootstrap.sql, delete lines ~9-13. That is the four-line comment starting "pg_net is relocatable" and the create extension if not exists pg_net with schema extensions; line. Keep pg_cron, pgmq and schema internal.

3. [agent] Fix the rule in packages/supabase/CLAUDE.md ~33. Replace only the pg_net clause. Keep the rest of the bullet. Suggested text, with this issue's number in place of #NNN:

   An extension whose control file names no schema fails: pg_cron and pgmq name theirs. pg_net named none. So a bare create extension pg_net raised 3F000: no schema has been selected to create in and killed the whole reset on 00-bootstrap.sql. The repo no longer installs pg_net (#NNN).

   Keep the next sentence, "Give any such extension with schema extensions...".

4. [agent] Fix packages/supabase/README.md ~24. Change "(pg_cron, pgmq, pg_net, schema internal)" to "(pg_cron, pgmq, schema internal)".

5. [agent] Add one paired migration. Name it packages/supabase/migrations/<YYYYMMDDHHMMSS>_drop_pg_net.sql. Its timestamp must sort after every file in packages/supabase/migrations/, including the one Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 adds. At HEAD the newest file is 20260928130000_reject_password_sign_in_hook.sql. Body:

   -- Drops pg_net. The repo moved push and email to pgmq and no longer calls
   -- net.*. pg_net is not relocatable, so it cannot move out of public.
   -- Paired with scripts/00-bootstrap.sql. The guard refuses the drop if a caller exists.
   
   do $$
   begin
     if exists (select 1 from cron.job where command ~ '\mnet\.') then
       raise exception 'pg_net drop refused: a cron job calls net.*';
     end if;
     if exists (
       select 1
       from pg_proc p
       join pg_namespace n on n.oid = p.pronamespace
       where n.nspname <> 'net'
         and p.prosrc ~ '\mnet\.'
         and (n.nspname, p.proname) not in (
           ('extensions', 'grant_pg_net_access'),
           ('supabase_functions', 'http_request')
         )
     ) then
       raise exception 'pg_net drop refused: a function body calls net.*';
     end if;
     if to_regclass('supabase_functions.hooks') is not null then
       if exists (select 1 from supabase_functions.hooks) then
         raise exception 'pg_net drop refused: a Database Webhook exists';
       end if;
     end if;
   end;
   $$;
   
   drop extension if exists pg_net;

   Do not add cascade. A tracked dependency must stop the drop loudly. The guard and the drop were run on local, inside a rolled-back transaction: the guard passed and the drop succeeded.

6. [agent] Verify locally. From the repo root, first apply the migration to the running local database:
   docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres < packages/supabase/migrations/<file>.sql
   Then prove a fresh reset works without pg_net: bun run --filter @docs.plus/supabase_back reset. A reset wipes local data for every worktree, so tell the maintainer first. Then run the queries in Verification.

7. [agent] Check the generated files. Run bun run --filter @docs.plus/supabase_back seed and bun run --filter @docs.plus/supabase_back types. Expect no diff in packages/supabase/seed.sql or apps/webapp/src/types/supabase.ts. Schema net is not in the generated types. If either file changes, include the regenerated file and say why.

8. [maintainer] Ship to prod. Use the house push procedure that Ship the three migrations that prod's history lacks #313 uses. Run the CLI from packages/supabase. Run bunx supabase migration list first. Set [db.migrations] enabled = true only while pushing, then set it back to false. Push only after Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration is on prod.

   • If the push fails with a permission error on drop extension, turn pg_net off in the dashboard (Database > Extensions). Then push again. if exists makes the migration a no-op.
   • If the guard raises, the push stops. Post the message here and go back to step 1.

9. [maintainer] Post-checks on prod. Run the prod queries in Verification. Re-run the Security Advisor (Advisors > Security Advisor).

10. [agent] Commit only when the maintainer asks, through /commit-review.

Rollback, if a hidden caller shows up later: turn pg_net on again in Database > Extensions. The SQL form is create extension if not exists pg_net with schema extensions;. Old queue and response rows do not come back.

Out of scope

• The grant and RLS fix port and its migration: Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314.
• The push procedure and the three missing migrations: Ship the three migrations that prod's history lacks #313.
• Default privileges for new objects: Stop new tables and functions from opening by default #316. The post-push grant guard: Add a read-only grant guard to run after each db push #317.
• The Postgres upgrade, which waits for this drop: Upgrade prod Postgres from 15.8.1.073 #322.
• The pgjwt check before the upgrade: Upgrade prod Postgres from 15.8.1.073 #322.
• Historical pg_net comments in scripts/07-4-push-notifications-pgmq.sql (~9, ~17, ~26, ~182, ~268, ~293, ~672) and scripts/07-5-email-notifications-pgmq.sql ~8. They describe the move to pgmq. Leave them.
• The same comments in applied migrations such as 20260519200000_scripts_functions_triggers_parity.sql. Never edit an applied migration.

Acceptance criteria

• The maintainer posted the results of prod checks 1-3 on this issue, and all three passed.
• packages/supabase/scripts/00-bootstrap.sql has no pg_net line and no "relocatable" comment.
• packages/supabase/CLAUDE.md and packages/supabase/README.md no longer say pg_net is relocatable or installed.
• One new migration *_drop_pg_net.sql exists. It sorts last in packages/supabase/migrations/. It has the guard block and drop extension if exists pg_net; without cascade.
• A fresh local reset succeeds, and local pg_extension has no pg_net row.
• seed.sql and supabase.ts are unchanged, or the regenerated files are in the change with a reason.
• After the push, prod pg_extension has no pg_net row.
• After the push, the Security Advisor shows no extension_in_public row.
• After the push, every cron job run in the next hour has status succeeded.

Verification

Repo, from the repo root, after steps 2-4. Both commands print nothing.

grep -n -i -E "relocatable|pg_net" packages/supabase/scripts/00-bootstrap.sql packages/supabase/README.md
grep -rn -E "net\.(http_|_http)" packages/supabase/scripts packages/supabase/migrations

grep -n "pg_net" packages/supabase/CLAUDE.md prints only the rewritten sentence, with "named none" and no "relocatable".

Local, after step 6:

select count(*) from pg_extension where extname = 'pg_net';  -- expect 0
select count(*) from pg_namespace where nspname = 'net';     -- expect 0

Prod, after step 8 (read-only):

select count(*) from pg_extension where extname = 'pg_net';  -- expect 0
select j.jobname, d.status, count(*)
from cron.job_run_details d
join cron.job j on j.jobid = d.jobid
where d.start_time > now() - interval '1 hour'
group by 1, 2
order by 1, 2;                                               -- expect status succeeded only

House rules

• Scripts and migrations are paired. Edit scripts/00-bootstrap.sql and add the migration in the same change. Never edit an applied migration (packages/supabase/CLAUDE.md).
• Never hand-edit packages/supabase/seed.sql or apps/webapp/src/types/supabase.ts. Regenerate them with the workspace scripts and include any diff.
• [db.migrations] enabled stays false in git. Set it to true only while pushing. Run bunx supabase migration list before any push or repair.
• Bun only. Never run npm, yarn, pnpm, or npx.
• No commits unless asked. No git add, git commit, or git push. The maintainer commits through /commit-review.