Run your own PostgREST against a Supabase project

You’ll turn off the managed Data API on a Supabase project and run PostgREST yourself, in Docker, against the same Postgres - then front it with a db-pre-request IP filter and an nginx rate limiter. This is move 3 in Locking down Supabase: the database is the boundary: the managed HTTP tier has no network perimeter, so an IP-restricted or rate-limited REST layer has to be one you own.

Prerequisites: a Supabase project (any plan) and Docker.

What you need, and where it is

The commands reference these. Run every SQL block in the SQL Editor (Dashboard -> SQL Editor).

Value	Where to get it
$REF - the project ref (a 20-character id)	your project URL, or Settings -> API
$ANON - the anon (or publishable) API key	Settings -> API, explained in Understanding API keys
A personal access token (PAT) for the Management API calls	the dashboard, under Account -> Access Tokens
The database password	set when you created the project; reset it under Settings -> Database if you do not have it
$REGION and the session-pooler connection string	the Connect button on the dashboard, Session mode - see Connecting to Postgres

Supabase ships three Postgres roles these steps use: anon (an unauthenticated caller), authenticated (a logged-in user), and service_role (bypasses RLS). PostgREST connects as an “authenticator” role and switches to anon or authenticated per request.

Everything below was run on a throwaway micro project in ap-southeast-1 on 2026-08-28; the through-edge header behaviour behind step 5 was measured on the same shape on 2026-09-03 (security-lockdown S20, with the lab’s edge.nginx.conf in front).

Component versions

Component	Version	Note
PostgREST	postgrest/postgrest:v16.2	official image
nginx	nginx:1.31.4-trixie	the rate limiter
Supabase Postgres	17.6	reported by the PostgREST connect log

Architecture

Step 1: turn the managed Data API off

Empty the exposed schema so the managed PostgREST stops answering. Read the current value first with GET /v1/projects/{ref}/postgrest and keep it for the teardown - the body also carries the project’s jwt_secret, so do not log the response (http-tier-lockdown run 2) - then PATCH /v1/projects/{ref}/postgrest with {"db_schema": ""}. Use the API rather than the Dashboard’s Data API toggle: the toggle’s off-then-on round-trip rewrites db_schema to the constant public, dropping graphql_public and any other exposed schema, and a client asking for a dropped schema then gets 406 PGRST106 (http-tier-lockdown run 2). Confirm it wedges:

curl -s -o /dev/null -w '%{http_code}\n' \
  -H "apikey: $ANON" "https://$REF.supabase.co/rest/v1/your_table?select=id"
# -> 503 (PGRST002) within a few seconds

Auth, Storage, Realtime and Edge Functions keep answering - this lever is PostgREST-only, which is the whole reason you are replacing it.

Step 2: get the session-pooler connection

PostgREST holds a connection pool, so connect through the session pooler (Supavisor, port 5432), not the transaction pooler. Copy the exact string from the dashboard’s Connect button, Session mode - do not hand-build it, since the pooler hostname and the postgres.<ref> username format are project-specific. It has this shape:

postgres://postgres.<ref>:<db-password>@aws-0-<region>.pooler.supabase.com:5432/postgres

The direct host db.<ref>.supabase.co is IPv6-only; the pooler is IPv4, which a default Docker bridge can reach. Step 3 swaps the postgres user in this string for a dedicated role.

Step 3: create a connection role, then run PostgREST

PostgREST connects as an authenticator role and switches to anon for unauthenticated requests. Connect as a dedicated NOSUPERUSER role that can only SET ROLE to anon/authenticated - never as postgres, which is a superuser and bypasses RLS and every grant, so RLS on your own PostgREST would do nothing. Create the role first:

create role pgrst_auth noinherit login password 'a-strong-password';
grant anon, authenticated to pgrst_auth;

Then run PostgREST connecting as it (through the pooler the username is <role>.<ref>). PGRST_DB_PRE_REQUEST names the filter function you install in step 5 - set it now, since PostgREST reads it at config load.

docker run -d --name own-postgrest -p 3000:3000 \
  -e PGRST_DB_URI="postgres://pgrst_auth.$REF:a-strong-password@aws-0-$REGION.pooler.supabase.com:5432/postgres" \
  -e PGRST_DB_SCHEMAS=public \
  -e PGRST_DB_ANON_ROLE=anon \
  -e PGRST_DB_PRE_REQUEST=public.ip_filter \
  postgrest/postgrest:v16.2
docker logs own-postgrest | grep 'Successfully connected'
# -> Successfully connected to PostgreSQL 17.6

Step 4: grant anon and reload the schema cache

PostgREST cached the schema at start with anon holding no grants. Grant read on the tables you expose and reload:

grant usage on schema public to anon;
grant select on public.your_table to anon;
notify pgrst, 'reload schema';

GET http://localhost:3000/your_table?select=id now returns rows the managed endpoint no longer will.

For write endpoints the grant is the column control, not just the table. grant update on public.your_table to anon exposes every column; grant update (col1, col2) on public.your_table to anon scopes the write to those columns, and RLS still decides the rows. An UPDATE policy gates which rows change, never which columns - a table-level UPDATE grant plus a row policy leaves a caller free to overwrite any column of a row the policy admits, and a write to a withheld column then returns SQLSTATE 42501. Measured in security-lockdown S13; the reasoning is Locking down Supabase.

New tables reach your PostgREST open. pg_default_acl grants SELECT on every new table to anon and authenticated (iap-lockdown L08), so a table created after this step is readable through your instance with no deliberate grant. Run alter default privileges in schema public revoke select on tables from anon, authenticated; as postgres, then grant per table as above.

Step 5: the db-pre-request IP filter

This is the control the managed tier will not run. The function reads the forwarding header and raises with a PT-prefixed SQLSTATE, which PostgREST maps to the matching HTTP status.¹ An IP ban (block one address) keeps the filter out of the way of trusted callers:

create or replace function public.ip_filter() returns void language plpgsql as $$
declare xff text := current_setting('request.headers', true)::json ->> 'x-forwarded-for';
begin
  if xff is not null and xff like '%203.0.113.9%' then
    raise sqlstate 'PT403' using message = 'ip banned: ' || xff;
  end if;
end$$;
grant execute on function public.ip_filter() to anon;

Verify the ban and the pass:

curl -s -o /dev/null -w 'banned=%{http_code}\n' \
  -H 'x-forwarded-for: 203.0.113.9' 'http://localhost:3000/your_table?select=id'   # -> 403
curl -s -o /dev/null -w 'allowed=%{http_code}\n' 'http://localhost:3000/your_table?select=id'  # -> 200

For an allowlist instead of a ban, invert the test (raise unless xff is in your set). The function judges whatever header reaches it (S20): called direct on the container with x-forwarded-for: 198.51.100.7, an RPC returning the header saw that value, so a client that can reach port 3000 chooses its own address; through an nginx that sets proxy_set_header X-Forwarded-For $remote_addr (the lab’s edge.nginx.conf, a separate container from the step 6 rate limiter) it saw one RFC 1918 address and no client value. Keep the container reachable from the edge only, and read the header the edge sets. On the managed tier the edge appends instead, client value first, and passes cf-connecting-ip (S16) - a different header discipline, covered in Locking down Supabase. Pin the function’s search_path (alter function public.ip_filter() set search_path = '';) so the Management API security advisor does not flag it as function_search_path_mutable, and run the advisor after steps 3-5: in security-lockdown S01 it caught every seeded exposure. The pinned form was not run through S04.

Step 6: rate limit at the edge

Rate limiting lives in front of PostgREST - nginx, a Cloudflare Worker, or Upstash all work; nginx limit_req is the self-contained one. It only counts traffic through it, which is why it works now (the origin is closed) and could not work in front of the managed endpoint.

events {}
http {
  limit_req_zone $binary_remote_addr zone=rl:10m rate=2r/s;
  limit_req_status 429;
  server {
    listen 8080;
    location / { limit_req zone=rl burst=2 nodelay; proxy_pass http://host.docker.internal:3000; }
  }
}

docker run -d --name own-ratelimit --add-host=host.docker.internal:host-gateway \
  -p 8080:8080 -v "$PWD/ratelimit.nginx.conf:/etc/nginx/nginx.conf:ro" nginx:1.31.4-trixie

Two adjustments the lab’s config did not carry. Exclude any health-check path from limit_req (its own location or its own zone): in security-lockdown S12, the Worker variant of this limiter, the readiness poll spent the per-window allowance and the module had to wait a full window before the burst. And add proxy_set_header X-Forwarded-For $remote_addr; in the location block so the step 5 filter sees the peer address rather than a client-supplied header; as written, nginx passes the client’s header through. With that line in place (the lab’s edge.nginx.conf), the spoofed ban value returned 403 PT403 direct to PostgREST and 200 through nginx, and an RPC returning current_setting('request.headers', true)::json ->> 'x-forwarded-for' (S20’s sec20_xff, not part of these steps) saw the edge’s peer address instead of the client header (S20).

A 15-request burst against :8080 returns 2 served and 13 rejected (429).

Verification

Check	Expected
Managed REST	503 PGRST002
Your PostgREST direct	200 with rows
Spoofed x-forwarded-for at the filter, direct	403
Same request through the nginx edge with X-Forwarded-For $remote_addr	200
Burst through nginx	some 429, some 200

What to do about it

The measurements behind the steps imply a short list of practices. Each row names the module id or run it rests on (security-lockdown unless prefixed); a row that is a design choice rather than a result says so.

Practice	Rests on
Record db_schema with GET /v1/projects/{ref}/postgrest in step 1 and PATCH exactly that value back at teardown; the platform default is public,graphql_public, public alone drops /graphql/v1, and restore landed in 1-2s.	http-tier-lockdown run 1, run 2
Use PATCH for the off switch, never the Dashboard toggle: the toggle’s off-then-on round-trip rewrites db_schema to public alone and a client asking for a dropped schema gets 406 PGRST106.	http-tier-lockdown run 2
Keep the GET /v1/projects/{ref}/postgrest response out of logs; it carries the project’s jwt_secret, the legacy HS256 key. Projects whose Auth signs ES256 need PGRST_JWKS_URI pointed at the project JWKS instead; which applies to yours is not measured here.	http-tier-lockdown run 2
Run alter default privileges in schema public revoke select on tables from anon, authenticated; as postgres after step 4, then grant per table; pg_default_acl otherwise grants SELECT on every new table to anon and authenticated.	iap-lockdown L08
Add the PostgREST host’s egress IP to the network restriction list, or run the container in the VPC behind PrivateLink, before combining this guide with restrictions; an excluded address is refused with FATAL (EADDRNOTALLOWED) address not in tenant.	S10
Exclude the health-check path from limit_req (its own location or zone); the readiness poll spent the per-window allowance and the module waited a full window before the burst.	S12
Add proxy_set_header X-Forwarded-For $remote_addr; to the nginx location block so the step 5 filter sees the peer address; as written nginx passes a client-supplied header through. Direct to PostgREST the client header is what SQL sees (spoofed ban value 403); through an nginx with that line it is one RFC 1918 address and the same request returns 200.	S20, S04
Expose port 3000 to the edge only. The filter cannot tell a client-supplied x-forwarded-for from an edge-set one; the allowlist property comes from the container’s reachability; the function judges whatever header arrives.	S20
Pin search_path on ip_filter (alter function public.ip_filter() set search_path = '';) and run the Management API security advisor after steps 3-5; it flags function_search_path_mutable and caught every seeded exposure.	S01

Gotchas and lessons learned

• The db-pre-request function must exist for the container’s whole life. PGRST_DB_PRE_REQUEST is read at config load, and PostgREST calls the function on every request - drop it and every request errors. Create it before the container serves traffic, and do not drop it while the container runs.
• nginx limit_req rejects with 503 by default. Set limit_req_status 429 for the correct status.
• Connect as the authenticator role from step 3, never as postgres. postgres is a superuser that bypasses RLS and every grant, so an RLS-based lockdown on your own PostgREST would be silently defeated; service_role bypasses RLS too, so keep it out of the connection and the request path. Authorisation is yours to enforce now - RLS on anon/authenticated, the pre-request function, or the edge - and the managed platform’s role separation does not carry over automatically.
• A fresh table is invisible until notify pgrst, 'reload schema'. PostgREST caches the schema; grants and new tables need the reload.
• This wires anonymous reads. For authenticated user requests, point PostgREST’s JWT verification (PGRST_JWT_SECRET, or PGRST_JWKS_URI for asymmetric keys) at your token issuer’s key so it validates the bearer token and switches to authenticated - your own issuer, or the Supabase-issued tokens if you keep Supabase Auth. GET /v1/projects/{ref}/postgrest returns jwt_secret in the body, the legacy HS256 key (http-tier-lockdown run 2). Projects whose Auth signs ES256 need PGRST_JWKS_URI pointed at the project JWKS instead; which applies to yours is not measured here. Keep that response out of logs either way.
• Owning the REST layer does not cover Storage or Realtime. db-pre-request, your IP filter, and the whole PostgREST you now run sit on the REST path only; Storage (/storage/v1) and Realtime (/realtime/v1) stay the managed services against the same Postgres and never route through PostgREST - turn the Data API off and both keep answering (security-lockdown S15). An IP allowlist or owner-list for them lives in each service’s own authorisation - RLS on storage.objects, Realtime Authorization - or an edge you put in front of them too.
• Put the pooler behind PrivateLink to take the database connection itself off the public internet - then both the REST layer and the connection are private. If you apply network restrictions to the project, add the PostgREST host’s egress IP to the list first, or run the container inside the VPC: an excluded address is refused at the pooler with FATAL (EADDRNOTALLOWED) address not in tenant (security-lockdown S10), and that is what docker logs will show.

Teardown

docker rm -f own-postgrest own-ratelimit

Drop the role with drop role pgrst_auth;, and re-enable the managed Data API with PATCH .../postgrest, setting db_schema back to the value you recorded in step 1 - the platform default is public,graphql_public, and public alone drops /graphql/v1 (http-tier-lockdown run 2). Restore landed in 1-2s (http-tier-lockdown run 1).

File reference

The reproducible form is the supabase-lab security-lockdown experiment: s04-self-hosted-postgrest.ts (Data API off, container, IP filter), s05-rate-limit.ts (nginx burst), ratelimit.nginx.conf, s06-postgrest-role.ts (the connection-role check), s20-xff-trust-boundary.ts with edge.nginx.conf (the through-edge header check), and the postgrest-up / ratelimit-up / edge-up Makefile targets. The 2026-09-03 artifacts are under out/2026-09-03/.

References

1. PostgREST, “Pre-Request,” PostgREST Documentation. https://postgrest.org/en/stable/references/transactions.html#pre-request ↩