Skip to content

postgres.md

Latest commit

 

History

History
353 lines (240 loc) · 25.1 KB

postgres.md

File metadata and controls

353 lines (240 loc) · 25.1 KB
Raw

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

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.

Dates come back as Date

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

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.

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 { 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:

  • 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 options:

withPostgresClient({
  connectionString: 'postgresql://...',
  pool: { max: 4, checkoutTimeoutMs: 10000 },
})
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.

pool.max is how many connections this process opens at most on that connection string: a positive integer, 4 by default. pool.checkoutTimeoutMs is how long a query waits for a free connection before failing with POSTGRES_POOL_BUSY, and how long a new connection may take to come up: a positive number of milliseconds, 10000 by default. Both are checked when the middleware is built. An invalid value throws a RangeError at startup, not on the first request. Two entries on one connection string share a pool only when their options resolve to the same values, so set pool on one entry or set it identically on both.

errors: { detailed: false } trims that response, and withPostgresClient's UNSUPPORTED_ROLE refusal, to code and message; see docs/error-handling.md.

Connection pooling

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:

  • Each process opens at most pool.max connections per connection string, 4 by default. At that default, a stack on 10 isolates holds up to 40 database connections.
  • A request that finds every connection busy waits up to pool.checkoutTimeoutMs for one, 10 seconds by default, then fails with a PostgresPoolError, code POSTGRES_POOL_BUSY (all 4 connections stayed busy for 10000ms). Under saturation, latency rises to that ceiling and then requests fail. Opening a new connection has the same limit, so a request that waits for a slot and then has to reconnect can take up to twice that to fail.
  • A scoped query holds its connection for five round trips: begin, set_config, set local role, the query, and commit. An admin query holds it for one.
  • After a connection attempt fails, the pool pauses new attempts until one succeeds: between half a second and one second the first time, doubling each failing round up to between 15 and 30 seconds. A request that would open a connection during the pause fails immediately with a PostgresPoolError, code POSTGRES_CONNECT_PAUSED (new connections paused for <n>ms after a connection failure: <reason>). A request that can reuse an idle connection goes through, and a request waiting for a busy connection is checked again when one frees up.

Both pool errors carry status: 503, a code, and details. They are thrown from the query call, not returned as a response, so catch them in the handler or map them in the host's error handler. details.retryAfterMs on the paused error fits a Retry-After header. See PostgresPoolError codes.

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.

Which connection string to use

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.

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

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.

Troubleshooting

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
POSTGRES_POOL_BUSY: all 4 connections stayed busy for 10000ms Every pooled connection stayed busy for the whole checkout wait 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
POSTGRES_CONNECT_PAUSED: new connections paused for <n>ms after a connection failure: <reason> A connection attempt failed and the pool is backing off Wrong password
Every client of the project gets ECIRCUITBREAKER: too many authentication failures Something is retrying a bad credential 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

Zero rows

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`

Slow under load

The throughput ceiling is pool.max concurrent queries per process, 4 by default. Above it, requests wait up to pool.checkoutTimeoutMs for a connection and then fail with POSTGRES_POOL_BUSY (all 4 connections stayed busy for 10000ms), so a saturated pool shows up first as rising latency and then as that error. Four levers:

  • Fewer queries per request. Put multi-statement logic in a database function and call it once.
  • Route heavy reads through ctx.supabase, which talks HTTP to PostgREST and does not use the pool.
  • A larger pool.max on a long-lived server. Keep it at one to four on serverless, where every isolate multiplies it.
  • More processes or isolates. Each adds pool.max connections on the database side, so check the connection cap first.

Wrong password

A failed connection attempt (a wrong password, a refused or unreachable host, a connect that hangs past 10 seconds) makes the pool pause new connection attempts until a connection succeeds: between half a second and one second the first time, doubling on each failing round up to between 15 and 30 seconds. Host logs show [@supabase/server] postgres pool: connection failed, pausing new connections for <n>ms: <reason> once per pause. A request that would open a connection during the pause fails immediately with POSTGRES_CONNECT_PAUSED (new connections paused for <n>ms after a connection failure: <reason>), carrying the original error as cause and the time left as details.retryAfterMs. A request that can reuse an idle connection goes through. Requests already waiting for a busy connection are checked when one frees up, so a burst that arrives before the first failure lands produces at most pool.max attempts, one per connection slot. Usual triggers: a database password reset, a restored or resumed project, or a connection string copied from another project.

The pause is what protects the rest of the project. Left to itself, pg retries a failed connection as fast as the failure comes back, dozens of attempts per second per process, and within seconds the pooler's authentication circuit breaker rejects new connections for the whole project, including ones with the correct password:

ECIRCUITBREAKER: too many authentication failures, new connections are temporarily blocked

That block lifts about a minute after the bad traffic stops, and trips again while it continues. Seeing it means something is retrying without pause: another client, or an older version of this package. Fix the credential or stop that deployment, then wait a minute.

Dropped connections

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.

Roles in pooler logs

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.

What those logs can tell apart is the application. Every connection carries application_name supabase-server, or supabase-server:<function slug> on Supabase Edge Functions, where the runtime sets SUPABASE_FUNCTION_SLUG. An application_name in the connection string itself takes precedence.

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