ctx.postgres)Alpha. Composing
withSupabaseas apipelineentry and the@supabase/server/middleware/*subpaths track@supabase/middleware0.x — entry shapes and context keys may change between 0.x releases. ThewithSupabase(config, handler)form is stable.
Two middleware give you a direct Postgres connection, mirroring the ctx.supabase / ctx.supabaseAdmin pair:
| Middleware | Subpath | Contributes | RLS |
|---|---|---|---|
withPostgresClient |
./middleware/postgres |
ctx.postgres |
Enforced, scoped to the caller |
withPostgresAdminClient |
./middleware/postgres-admin |
ctx.postgresAdmin |
Bypassed |
Reach for the scoped one by default. The admin one is a deliberate opt-out, covered below.
withPostgresClient puts a direct Postgres connection on ctx.postgres, scoped to the calling user by RLS. It is the safe version of "authenticate, then query as the user": you write plain SQL, and Postgres — not your application code — decides which rows the caller may see.
import { pipeline } from '@supabase/middleware'
import { withSupabase } from '@supabase/server'
import { withPostgresClient } from '@supabase/server/middleware/postgres'
export default {
fetch: pipeline(
[withSupabase({ auth: 'user' }), withPostgresClient()],
async (_req, ctx) => {
// No WHERE clause — RLS scopes the rows to the caller.
const notes = await ctx.postgres.query`select id, body from notes`
return Response.json(notes)
},
),
}
Use this when PostgREST is not the right tool: multi-table joins, window functions, CTEs, insert ... returning with computed columns, or any query that is simply easier to express in SQL. For ordinary CRUD, ctx.supabase is still the better choice.
Every query takes a connection from the pool and runs your SQL inside its own transaction, injecting the caller's claims exactly the way PostgREST does:
begin;
select set_config('request.jwt.claims', $claims, true); -- auth.uid() resolves
set local role "authenticated"; -- RLS now enforces
-- your query
commit;
Both set_config's third argument and set local are transaction-local, so nothing leaks onto the pooled connection when it goes back to the pool.
query is a tagged template. Every interpolation becomes a bind parameter, so an interpolated value is never SQL text and cannot change the shape of the statement:
const rows = await ctx.postgres
.query`select id, body from notes where id = ${id}`
// -> select id, body from notes where id = $1 with values [id]
That holds no matter what id contains. A value like '; drop table notes; -- is sent as a parameter and compared as a string.
Tagged templates cannot carry type arguments, so annotate the binding rather than writing query<NoteRow>:
const rows: NoteRow[] = await ctx.postgres.query`select id, body from notes`
Passing a plain string to query throws. The two calls differ only in their brackets, so refusing is safer than reinterpreting one as the other.
Datepg returns date, timestamp, and timestamptz columns as JavaScript Date objects, not strings. Declare those fields as Date in your row type. When a value feeds a PostgREST filter through ctx.supabase, or goes straight into a JSON body, cast it in SQL instead:
const rows: NoteWithDay[] = await ctx.postgres
.query`select day::text as day, body from notes`
::text makes the column a string on the wire, so the row type and the consumer agree without a conversion step.
queryRaw for text you buildUse queryRaw(text, params) when the SQL cannot be a literal — a query builder or codegen emitting { sql, parameters }, or a statement held in a constant:
const rows = await ctx.postgres.queryRaw(
'select id, body from notes where id = $1',
[id],
)
It is fully safe as long as caller-supplied values travel in params; that is exactly what query compiles down to. What it cannot do is stop you concatenating a value into text. The name is the warning, and it greps.
ident for identifiersTable names, column names and order by direction can never be bind parameters — select $1 from notes selects a literal, not a column. Those have to reach the server as SQL text, so check them against a set you control and quote them:
import { ident } from '@supabase/server/middleware/postgres'
const SORTABLE = new Set(['created_at', 'title'])
if (!SORTABLE.has(column)) throw new Error('unsupported sort column')
const rows = await ctx.postgres.queryRaw(
`select id, title from notes order by ${ident(column)} desc`,
)
ident stops injection; it does not authorize. Quoting a caller-supplied name yields a valid identifier, not a permitted one — it cannot break out of the statement, but it can still name a column the caller was never meant to read. The allowlist is what prevents that.
Only authenticated and anon. A verified token naming any other role is refused — a 500 with code: 'UNSUPPORTED_ROLE' and a message naming the role — rather than quietly downgraded:
role claim |
Result |
|---|---|
| absent, or no token at all | anon |
anon |
anon |
authenticated |
authenticated |
service_role |
Refused, pointing at withPostgresAdminClient |
| anything else (custom roles) | Refused, naming the role |
Refusing rather than downgrading is deliberate. Running someone's query under the wrong identity returns zero rows instead of an error, which is close to undebuggable — you see an empty array and no indication that the role was the problem.
Supabase lets you define custom Postgres roles and put them in the role claim, with RLS policies written to manager. That is a legitimate pattern and RLS still applies — custom roles are a dimension of RLS, not a way around it.
They are not supported here yet, and the reason is worth knowing. PostgREST connects as the unprivileged authenticator role, so grant manager to authenticator is the authorization — Postgres itself decides which roles are reachable. This middleware connects with SUPABASE_DB_URL, which on Supabase is postgres: a role that already bypasses RLS and can SET ROLE into almost anything. With no equivalent boundary to lean on, v1 assumes a fixed pair of roles instead of trusting the claim.
Until custom-role support lands, issue tokens with authenticated or anon, or use withPostgresAdminClient and do the scoping in your own where clause.
auth.* helpersThe single request.jwt.claims setting is the whole claim payload as JSON, and it is the only one this middleware sets. auth.uid(), auth.role(), and auth.jwt() all read it, so policies written the normal way work unchanged:
create policy "users read their own notes"
on public.notes for select to authenticated
using ((select auth.uid()) = user_id);
Reach for those helpers rather than reading settings by hand. In particular, the older singular GUCs — current_setting('request.jwt.claim.sub') and friends — are not set here; they are a legacy PostgREST convention that PostgREST itself has since removed. A policy that reads them directly sees NULL and quietly matches nothing.
withPostgresClient needs the caller's verified claims at ctx.jwtClaims. That prerequisite is enforced at compile time, so there are exactly two ways to satisfy it.
After withSupabase — the context already carries jwtClaims, so place it next in the array:
pipeline([withSupabase({ auth: 'user' }), withPostgresClient()], handler)
Standalone — in a Supabase-agnostic pipeline, pair it with withClaims, which verifies the Bearer token against the project JWKS:
import { pipeline } from '@supabase/middleware'
import { withClaims } from '@supabase/server/middleware/claims'
import { withPostgresClient } from '@supabase/server/middleware/postgres'
export default {
fetch: pipeline([withClaims(), withPostgresClient()], async (_req, ctx) => {
const rows = await ctx.postgres.query`select id, title from posts`
return Response.json({ rows, caller: ctx.jwtClaims?.sub ?? 'anon' })
}),
}
Order matters. withPostgresClient before withClaims is a compile-time error:
middleware-prereq: key 'jwtClaims' is not yet on the context (check ordering)
withClaims is not an auth gate. It contributes claims when a token is present, and null when one is not. The standalone pipeline above therefore also serves anonymous callers, whose queries run as anon. To require an authenticated caller, swap in withRequiredClaims: it rejects token-less requests with a 401 before the handler runs and contributes non-null jwtClaims, so the handler reads ctx.jwtClaims.sub directly. Inside withSupabase, auth: 'user' provides the same gate.
Queries run as authenticated or anon, and on current Supabase projects new tables grant those roles nothing. RLS policies are not enough on their own — a policy filters rows the role is already allowed to touch.
grant select, insert on public.notes to authenticated;
Without the grant the query fails with permission denied (SQLSTATE 42501) before RLS is consulted. withPostgresClient recognizes that code and appends the role and the missing-grant hint to the error message, so the fix is in the error you actually see.
When a handler legitimately needs to cross user boundaries — an admin dashboard, a cron aggregate, a background job — compose withPostgresAdminClient instead. It contributes ctx.postgresAdmin, which runs queries as-is under the connection-string role: no claim injection, no role switch, no wrapping transaction.
import { pipeline } from '@supabase/middleware'
import { withSupabase } from '@supabase/server'
import { withPostgresAdminClient } from '@supabase/server/middleware/postgres-admin'
export default {
fetch: pipeline(
[withSupabase({ auth: 'secret' }), withPostgresAdminClient()],
async (_req, ctx) => {
const rows = await ctx.postgresAdmin
.query`select user_id, count(*) from notes group by user_id`
return Response.json(rows)
},
),
}
Unlike the scoped half it declares no upstream prerequisite — it never reads ctx.jwtClaims, so it works under auth: 'secret' and auth: 'none' where there is no caller identity at all.
Compose both when a handler needs each in turn. They share one pool, and ctx.postgres stays RLS-scoped regardless:
pipeline(
[
withSupabase({ auth: 'user' }),
withPostgresClient(),
withPostgresAdminClient(),
],
handler,
)
Two things worth being deliberate about:
where clause you write. The failure mode is silent — a forgotten clause returns every row rather than raising an error..admin property so that bypassing RLS is visible at the composition site. You can grep a codebase for withPostgresAdminClient and find every handler that can cross user boundaries.Both middleware take the same option:
withPostgresClient({ connectionString: 'postgresql://...' })
withPostgresAdminClient({ connectionString: 'postgresql://...' })
connectionString defaults to the SUPABASE_DB_URL environment variable, which Supabase Edge Functions provide automatically. If neither is set the middleware short-circuits with a 500 and code MISSING_CONNECTION_STRING, whose hint names the option to pass.
errors: { detailed: false } trims that response, and withPostgresClient's UNSUPPORTED_ROLE refusal, to code and message; see docs/error-handling.md.
Connections are pooled per process, lazily, one pool per connection string. The pool outlives individual requests, which is what makes this viable on a per-request runtime. Three facts about the pool decide how a deployment behaves under load:
begin, set_config, set local role, the query, and commit. An admin query holds it for one.Both middleware share the pool for the same connection string, so composing the pair opens one pool, not two. Sharing is safe because everything the scoped half sets is transaction-local: a connection always returns to the pool clean, and an admin query can never inherit a previous caller's claims or role.
SUPABASE_DB_URL on Edge Functions is the direct connection, db.<ref>.supabase.co:5432. Every pooled connection counts against the database's max_connections, which depends on compute size, so a deployment with many isolates can exhaust it.
For those deployments, use the shared pooler in transaction mode instead. Copy the transaction-mode string from Project Settings, Database, Connection string (port 6543), store it in a secret of your own, and pass it as connectionString. Secret names starting with SUPABASE_ are reserved on Edge Functions, so the default variable cannot be overridden there.
Transaction mode fits this middleware: nothing it sets outlives the transaction, and it sends no named prepared statements, so no driver setting is needed.
pg opens a raw TCP socket, so both middleware run on Node, Deno, Bun, and the Supabase Edge runtime — but not on Workers-style isolates, which have no TCP. On those, use ctx.supabase, which talks HTTP to PostgREST.
pg is an optional peer dependency. Install it alongside the package when you use this middleware:
npm install pg
On Deno, including Edge Functions, an optional peer resolves only when your own code imports it. Pin the version in deno.json and add the import once, at the top of your entry module:
import 'pg'
import { withPostgresClient } from '@supabase/server/middleware/postgres'
Without it, deno check passes and the function fails at startup with Could not find package 'pg'. deno info lists npm:/pg@... once the import is in place.
| Symptom | Cause | Fix |
|---|---|---|
500 with code MISSING_CONNECTION_STRING |
No SUPABASE_DB_URL and no connectionString |
Configuration |
500 with code UNSUPPORTED_ROLE |
The token's role claim is service_role or a custom role |
Which roles are assumed |
permission denied for table (SQLSTATE 42501) |
The role has no grant on the table | Table grants |
| The query succeeds and returns zero rows | The query ran as anon, or a policy reads a setting that is never set |
Zero rows |
| Latency rises under load and nothing errors | All 4 connections are busy and requests are queuing | Slow under load |
Max client connections reached or remaining connection slots are reserved |
Total connections exceed the pooler or database cap | Which connection string to use |
Every client of the project gets ECIRCUITBREAKER: too many authentication failures |
A deployment with a wrong password is retrying without pause | Wrong password |
Occasional 500 with terminating connection due to administrator command |
A pooled connection was closed by the pooler or the database | Dropped connections |
Pooler logs show postgres, never authenticated |
Expected. The role is set inside the transaction | Roles in pooler logs |
Could not find package 'pg' on Deno |
The optional peer is not in the module graph | Runtime support |
The query ran as the wrong identity. withSupabase in publishable, secret, or none mode contributes jwtClaims: null, and withClaims contributes null when no token arrives. Both make withPostgresClient run as anon. Place withSupabase({ auth: 'user' }) or withRequiredClaims() ahead of it.
If the identity is right, check the policy. Only request.jwt.claims is set, and the auth.* helpers read it. A policy that reads current_setting('request.jwt.claim.sub') sees NULL and matches nothing. See Write policies with the auth.* helpers.
This query returns the identity the database saw:
const [who] = await ctx.postgres
.query`select current_setting('request.jwt.claims', true) as claims, current_user as role`
The throughput ceiling is 4 concurrent queries per process. Above it, requests wait for a connection with no timeout, so a saturated pool shows up as rising latency rather than errors. Three levers:
ctx.supabase, which talks HTTP to PostgREST and does not use the pool.pg retries a failed connection with no pause, so a single process attempts dozens of connections per second. Within seconds the pooler's authentication circuit breaker trips and rejects new connections for the whole project, including ones with the correct password:
ECIRCUITBREAKER: too many authentication failures, new connections are temporarily blocked
The block lifts about a minute after the bad traffic stops, and trips again while it continues. Fix the credential or stop the deployment, then wait a minute. Usual triggers: a database password reset, a restored or resumed project, or a connection string copied from another project.
A pooled connection can be closed underneath the middleware by a pooler restart, a failover, or an idle reap. The query in flight fails, the pool discards the dead connection, and the next request gets a fresh one. Host logs show [@supabase/server] postgres pool: connection lost: <reason> for a client mid-query, or idle connection lost (discarded): <reason> for one sitting idle in the pool when it dropped. Claims never leak across the event: a connection whose transaction cannot be rolled back is discarded rather than returned to the pool.
The pooler records the role a client connects with, which is postgres for SUPABASE_DB_URL. set local role runs inside the transaction and is invisible to it, so pooler logs, metrics, and pg_stat_activity show postgres for every query, scoped or admin. To confirm the identity a query ran under, use the query in Zero rows.
query() call. There is no multi-statement transaction API, so you cannot yet span several query() calls in one atomic unit. Put multi-statement logic in a database function and call it in a single query.withPostgres() that gives you both clients at once; list the two entries you want. The name is reserved in case that changes.application_name.docs/api-reference.md — withPostgresClient, withPostgresAdminClient, PostgresApi, config typesdocs/security.md — how RLS fits the rest of the auth model