Managed Supabase: safe deployment of db_pre_request before exposing custom API schema

[dbu-maker]

Hi Supabase community,

I'm preparing to enable a custom Data API schema on a managed Supabase project (PostgreSQL 17) and would appreciate guidance on the safest supported deployment procedure.

Our architecture

We use a Cloudflare Worker as our application gateway.

The Worker sends an X-Company-Gateway header to PostgREST. A private PostgreSQL function, private.require_gateway(), validates the header against SHA-256 verifier material stored in a restricted private table.

The verifier is accessed through a minimal-privilege SECURITY DEFINER helper. The main pre-request function remains SECURITY INVOKER.

The intended configuration is:

• pgrst.db_pre_request = 'private.require_gateway'
• Exposed Data API schemas: api only
• private and auth remain unexposed
• Both anon and authenticated Data API requests require the gateway header

The implementation has passed local PostgreSQL and actual PostgREST 14.5/16.3 HTTP/JWT tests.

Our main concern is safe deployment on managed Supabase without temporarily exposing the API before gateway enforcement is active.

Questions

1. Hook registration authority

The official documentation shows:

ALTER ROLE authenticator SET pgrst.db_pre_request = 'private.require_gateway';

followed by:

NOTIFY pgrst, 'reload config';

Is this procedure supported when executed from the SQL Editor as the project's postgres role on managed Supabase, including Free-plan projects?

2. Safe deployment / maintenance mode

Can we temporarily disable the Data API through the Supabase Dashboard, install our private gateway verifier and pre-request hook, change Exposed schemas to api only, and then re-enable the Data API?

Is disabling the Data API sufficient to prevent an unprotected access window? Would you recommend temporarily withholding EXECUTE permissions as an additional safeguard?

3. Configuration propagation

How can we verify that db_pre_request and the exposed-schema configuration have been applied to all relevant PostgREST serving instances?

Is NOTIFY pgrst, 'reload config' sufficient on managed Supabase, or are additional steps needed when replicas or pooled connections are involved?

4. Effective enforcement testing

What is the recommended way to verify that the pre-request hook is actually running before allowing normal application traffic?

We want to verify missing, incorrect and correct gateway credentials through real HTTP requests, rather than relying only on stored database settings.

Does a global pre-request hook require special handling for service_role requests or Supabase-managed Data API operations?

5. Failure and recovery

If hook registration succeeds in PostgreSQL but effective HTTP enforcement is not confirmed, what is the safest supported way to keep the Data API inaccessible while correcting the configuration?

Our account functionality is currently disabled, and we have not applied these hosted gateway changes.

I'm looking for a supported, fail-closed procedure for managed Supabase rather than a custom privilege workaround.

Any guidance or references to existing implementations would be greatly appreciated.

Thank you!

[cekuu35]

Great architecture — the header-gate-via-pre_request pattern is sound, and you've already got the two pieces that matter right (SECURITY DEFINER only for the minimal verifier read, the check itself as SECURITY INVOKER). Since both pgrst.db_pre_request and pgrst.db_schemas are reloadable config on managed Supabase (PostgREST v12), the safe procedure is order + proof at every step, because the two settings cannot be flipped atomically. Here's the sequence I'd use:

The principle

Activate and prove the gate while there is nothing to steal. The dangerous window is "schema exposed, gate not enforced" — so you never allow that state to exist: the pre_request goes live first, gets verified against a probe, and only then does api get exposed.

Step-by-step

1. Ship the function while the schema is dark (one migration, all in private):

• require_gateway() + the SECURITY DEFINER verifier helper
• GRANT EXECUTE on both to authenticator, anon, authenticated (pre_request runs for every request, including anon — and it must not depend on auth.uid())
• REVOKE ALL ON SCHEMA private FROM anon, authenticated stays in force; make sure private is not on the Data API's exposed list and is not in db-extra-search-path (it only needs USAGE for the function lookup, not exposure)

2. Enable the pre_request and reload config:

alter role authenticator set pgrst.db_pre_request = 'private.require_gateway';
notify pgrst, 'reload config';

(The alter role authenticator route is the one that works on managed Supabase today; PostgREST v12 marks it deprecated in favor of a pre_config function, which you can migrate to later without changing this sequence.)

3. Prove the gate before anything valuable is reachable. Put a throwaway probe in the already exposed schema:

create or replace function public.api_gate_probe() returns int
language sql security definer set search_path = public as $$ select 1 $$;
grant execute on function public.api_gate_probe() to anon, authenticated;

Then, against the live Data API:

• GET /rest/v1/api_gate_probe without the gateway header → must fail with your gate's rejection (403), not 200
• Same call with the correct header → 200
• Same call with a wrong header value → rejected

That probe is the load-bearing step: it proves the pre_request actually runs for anonymous Data API traffic on your project's PostgREST build before api exists. Delete the probe afterwards (or keep it as a canary — it's cheap and useful).

4. Only after the probe proves the gate, expose the schema:

alter role authenticator set pgrst.db_schemas = 'public, api';   -- keep whatever you already expose
notify pgrst, 'reload config';
notify pgrst, 'reload schema';   -- refresh the endpoint cache

Verify again with the same three calls against a real api object.

5. Document the rollback as the mirror image (unset db_schemas → reload → unset db_pre_request → reload), and test it once in staging so the incident path is known.

Failure modes worth knowing before you flip it

• A throwing require_gateway() produces 5xx for every Data API request — fail-closed, which is what you want for a gate, but it means a broken verifier lookup (schema drift on the verifier table, a revoked grant on the SECURITY DEFINER helper) is a full Data API outage, not a partial one. Deliberately break the helper in staging once to confirm the failure is "block everything" and not "bypass silently".
• Reload timing: notify pgrst, 'reload config' applies server-side promptly but not instantly — give it a few seconds before trusting the probe result, and re-run the probe after any later config change.
• Two reloads, two jobs: 'reload config' re-reads the GUCs; 'reload schema' refreshes the endpoint cache. Exposing a schema without the schema-cache reload can leave the API serving stale 404s — annoying, but the dangerous direction (config exposed before gate) is the one this sequence eliminates.
• Header access inside the check: read it via current_setting('request.headers', true)::json ->> 'x-company-gateway' — and keep treating the verifier table as the only secret; the header value is a shared credential, so rotate it with the same discipline you'd rotate an API key.

One extra hardening idea since you're already in this territory: give the gate a statement_timeout (or run the verifier read with one) so a slow lookup can't become a cheap DoS on every request.

I do Supabase/PostgREST security audits for production apps, so if you'd like a second pair of eyes on the final migration before it ships, happy to take a look — but the sequence above should stand on its own either way.

[BryceEWatson]

@cekuu35's order (gate first, prove it with a probe, then expose api) is the right idea. A few corrections to it, then the parts of your questions it doesn't cover.

Fixes to that sequence:

• Functions live under rpc/, so the probe is GET /rest/v1/rpc/api_gate_probe. Without rpc/, PostgREST looks for a table and returns 404 before your hook runs, header or not.
• anon, authenticated and service_role all need USAGE on private to call private.require_gateway(), so a REVOKE ALL ON SCHEMA private FROM anon, authenticated would fail every request, correct header included.
• A plain raise in the hook gives a 400, not a 5xx, and a missing verifier table gives a 404, which looks just like "not exposed yet". Raise the rejection with sqlstate 'PGRST', your own code and 'status', 403 (the error responses example shows the shape), so a rejection from the hook can't be mistaken for anything else. A bare 403 isn't enough, since a missing grant also returns 403 for signed-in users.
• Don't set the exposed schemas with ALTER ROLE authenticator SET pgrst.db_schemas. It overrides the dashboard setting, so the dashboard would show one list while PostgREST serves another, and 'public, api' keeps public exposed when you want api only.

The Data API toggle isn't a clean maintenance mode (Q2). Supabase's Securing your API guide says that with it off, "none of the auto-generated REST endpoints respond, regardless of grants or RLS." But in the dashboard's code, turning it off clears the exposed schemas list, and turning it back on restores only public. While it's off, the dashboard doesn't show the exposed schemas setting at all, and nothing answers, so the probe can't run either.

Use the exposed schemas list as your staging step and stop button instead (Q2 and Q5). With the Management API (PATCH /v1/projects/{ref}/postgrest with db_schema), point the Data API at a small schema that holds only the probe (with USAGE on that schema and EXECUTE on the probe granted to the three API roles), run the three header tests there, then set it to api. That works even if the Data API is off right now, since any non-empty list turns it back on with just that schema. If enforcement ever looks wrong, set it back to the probe schema. Unlike the toggle, that doesn't bring public back. Either way, confirm with a real request, and check that authenticator has no pgrst.db_schemas of its own, since that would win over both. This lists its settings for every database, including database-specific ones:

select coalesce(d.datname, 'all databases') as applies_to, s.setconfig
from pg_db_role_setting s
left join pg_database d on d.oid = s.setdatabase
where s.setrole = 'authenticator'::regrole;

service_role goes through the hook too (Q4). It's a global pre-request, so besides USAGE on private, service_role needs EXECUTE on private.require_gateway() and on the SECURITY DEFINER helper, like anon and authenticated. Any Edge Function, cron job or server code that calls the REST API with the secret key will be rejected unless it sends the header, or the function lets that role through on purpose. The same guide says pgrst.db_pre_request only covers the Data API, not Realtime or Storage, so those still rest on RLS and Storage policies.

Withholding EXECUTE (Q2) is a kill switch, not a staging step. It does fail closed, but every request fails with a permission error, correct header included.

Replicas and pooling (Q3). NOTIFY makes PostgREST reread its config, and the hook is PostgREST's own setting, so pooled database connections don't change anything. Replicas are different: Postgres doesn't allow LISTEN or NOTIFY on a replica, so that reload can't reach a replica's API that way. If you add replicas (Pro plan and up), each has its own API endpoint, so run the same header tests against each one and the load balancer.

[aniketatgithub]

Your order is the right one, with one change in emphasis: prove the gate on a probe first, and treat the exposed schema list as the switch, not the on and off toggle.

On Q1, yes. The Securing your API guide documents this exact registration as the normal procedure: alter role authenticator set pgrst.db_pre_request, then notify pgrst, 'reload config'. The guide gives no plan split for it. Build the functions first while api is not exposed.

One grant detail matters before you test. The hook runs on every Data API request, so anon, authenticated and service_role each need usage on schema private and execute on private.require_gateway() and on the SECURITY DEFINER helper. If any one of them misses usage or execute, that role fails even with the correct header.

Prove it before api exists. Put one probe function in a schema that is already exposed, then call it over HTTP at /rest/v1/rpc/probe_name (functions live under rpc, tables do not). Test three cases: no header rejected, wrong header rejected, correct header passes. Raise the rejection with sqlstate PGRST and status 403 as shown in the guide error responses, so a gate rejection is easy to tell apart from a missing grant or a missing table. Only after those three pass should you set the exposed schemas to api only in the Data API settings, then repeat the same three tests on a real api object.

On Q2, a disabled Data API does block REST calls while it is off; the guide says none of the auto generated REST endpoints respond then. But do not lean on the toggle to keep your final list. In Studio, turning it off writes an empty exposed list, and turning it back on falls back to public when the stored list is empty. So the toggle can quietly bring public back. The exposed list itself is the safer stop switch: point it at the probe schema while you work, and point it back there if enforcement ever looks wrong. Withholding execute is a valid emergency stop, but it fails good requests too, with a permission error, so use it as a kill switch and not as a staging step.

On Q4, service_role goes through the hook too, because the hook is global to the Data API. Server code, Edge Functions and cron jobs that call REST with the secret key will be rejected unless they send the header or your function lets that role through on purpose. The hook only covers the Data API, not Realtime or Storage, so those still rest on RLS and Storage policies.

On Q3 and Q5: a stored setting proves nothing by itself. NOTIFY is the documented reload step, and only the HTTP probes prove enforcement. If probes fail, set the exposed list back to the probe schema and fix it there. If you add read replicas later, repeat the three probes on each endpoint you expose, and do not assume a pass on one endpoint covers another.