postgres.md

Postgres (ctx.postgres)

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 { withSupabase } from '@supabase/server'
import { withPostgresClient } from '@supabase/server/middleware/postgres'

export default {
  fetch: withSupabase(
    { auth: 'user', middleware: [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.

What each query runs

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.

Writing queries safely

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.

queryRaw for text you build

Use 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 identifiers

Table 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.

Which roles are assumed

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.

Custom roles are not supported yet

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.

Write policies with the auth.* helpers

The 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.

Composition

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.

Inside withSupabase — the context already carries jwtClaims, so compose it directly:

withSupabase({ auth: 'user', middleware: [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.

Table grants

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.

Bypassing RLS

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 { withSupabase } from '@supabase/server'
import { withPostgresAdminClient } from '@supabase/server/middleware/postgres-admin'

export default {
  fetch: withSupabase(
    { auth: 'secret', middleware: [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:

middleware: [withPostgresClient(), withPostgresAdminClient()]

Two things worth being deliberate about:

• Authorization becomes yours. RLS is not consulted, so any per-user scoping has to be a where clause you write. The failure mode is silent — a forgotten clause returns every row rather than raising an error.
• The split is the safety feature. These are two middleware rather than one object with an .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.

Configuration

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 { message, code: 'ENV_ERROR' }.

Connections are pooled per process, lazily, one pool per connection string (max 4 connections). The pool outlives individual requests — that is what makes this viable on a per-request runtime.

Both middleware share that cache, 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.

Runtime support

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

Limits in this version

• One transaction per 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.
• No read-replica routing and no trace propagation — both are tracked separately.
• No composing wrapper. There is no withPostgres() that gives you both clients at once; list the two entries you want. The name is reserved in case that changes.

See also

• docs/api-reference.md — withPostgresClient, withPostgresAdminClient, PostgresApi, config types
• docs/security.md — how RLS fits the rest of the auth model