Postgres RLS for Multi-Tenant SaaS, the Production Pattern

The Supabase tenant_id = auth.uid() policy falls apart the moment you leave Supabase. Postgres row level security for a multi-tenant SaaS on self-hosted Postgres is one transaction, one set_config('app.tenant_id', $1, true), and one CREATE POLICY against that GUC. This article walks the pattern on a Node app, the connection pool trap, the Drizzle and Prisma wiring, and the negative tests that prove cross-tenant reads are denied.

TL;DR, the four-line RLS pattern that survives an audit

Row level security for a multi-tenant SaaS is four lines of SQL wrapped around every request. Open a transaction, set a GUC (grand unified configuration parameter) called app.tenant_id with set_config(..., true) so the value is scoped to the transaction, run the actual query, then commit. The policy on every tenant-scoped table reads current_setting('app.tenant_id', true)::uuid and matches it against the row's tenant_id column. Pooled connections that leak the GUC across requests are the single highest-impact risk, and the true argument is what prevents it.

The four lines, in order:

1. BEGIN;
2. SELECT set_config('app.tenant_id', $1, true);
3. The actual query.
4. COMMIT;

Four lines. One audit-safe boundary.

Why tenant_id columns alone are not enough

Every multi-tenant app starts the same way. Each table gets a tenant_id column, every query gets a WHERE tenant_id = $1, and code review is supposed to catch the one place a junior developer forgets the predicate. The problem is that code review does not catch every one. A single SELECT * FROM invoices WHERE id = $1 in a debug script, a forgotten join in an admin endpoint, or an aggregate over an internal report is enough to cross-tenant a row into the wrong customer's response. The pain compounds in audits: a SOC 2 reviewer asks "what control prevents tenant A from reading tenant B's data" and the honest answer is "the developers remembered to add a WHERE clause", which is not a control.

RLS moves that control into the database. The policy runs on every read, every write, every join, even queries the application layer never wrote, and the database refuses to return rows that do not match. As a canonical reference, the Supabase row level security guide is excellent at the basics. The pattern it teaches relies on Supabase-managed JWT claims through auth.uid(), which works inside Supabase and does not exist on a self-hosted Postgres. Crunchy Data's tenant RLS post covers the same idea on plain Postgres and is the closest second source to the pattern this article extends. The argument for RLS is not that it replaces application-layer checks; it is that it makes a forgotten check non-fatal. Defense in depth, one layer down.

A WHERE clause is a convention. A policy is a contract.

The production RLS pattern, step by step

The pattern has four pieces and they all matter together: enable RLS on the table, set the tenant inside a transaction, write the policy against the GUC, and respect the connection pool. Each piece is one line of code or one SQL statement. Skip any one and the others stop protecting you.

Enabling row level security on the table

Two ALTER TABLE statements turn the feature on. The first enables RLS, the second forces it for table owners.

src/db/schema.sql

CREATE TABLE invoices (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL,
  customer text NOT NULL,
  total numeric(12, 2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX invoices_tenant_id_idx ON invoices (tenant_id);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

The ENABLE line activates policies for normal roles. FORCE is the line most tutorials skip, and it is the one an auditor will ask about. By default, the owner of a table bypasses RLS, which means the role that ran your migrations can read every row and policies do not apply. The Postgres docs on row security call out this carve-out explicitly. FORCE ROW LEVEL SECURITY removes it. The index on tenant_id is not optional either; the policy will reference that column on every query, so the planner needs an index to keep the cost down.

Owners bypass policies until you force them not to.

Setting the tenant with set_config inside a transaction

The GUC is how the request hands the tenant identity to the database. Postgres ships a function called set_config that writes to a session variable; with the third argument set to true, the write is scoped to the surrounding transaction and reverts on commit or rollback. The Postgres SET reference describes the same semantics under the LOCAL keyword, and custom GUC variables explain why a two-part name like app.tenant_id is allowed at all (Postgres reserves single-word GUCs for itself and accepts namespaced ones for applications).

src/db/with-tenant.ts

import type { PoolClient } from 'pg'

export async function withTenant<T>(
  client: PoolClient,
  tenantId: string,
  work: (client: PoolClient) => Promise<T>,
): Promise<T> {
  await client.query('BEGIN')
  try {
    await client.query('SELECT set_config($1, $2, true)', [
      'app.tenant_id',
      tenantId,
    ])
    const result = await work(client)
    await client.query('COMMIT')
    return result
  } catch (error) {
    await client.query('ROLLBACK')
    throw error
  }
}

The wrapper does three things you cannot afford to do by hand on every endpoint. It begins a transaction so the set_config write has a scope, it sets the GUC as the first statement so the policy has something to read, and it rolls back on any thrown error so a half-committed update never lands. The true argument is the line that matters most. Without it, the GUC stays on the session, the connection returns to the pool with a tenant id baked in, and the next request can run a query with the previous tenant's permissions. With true, the value evaporates on commit. Same connection, clean slate.

Writing the CREATE POLICY against a GUC, not auth.uid()

The policy is the boundary. It reads the GUC, compares it to the row's tenant_id, and Postgres skips any row that does not match without ever telling the application the row existed. The CREATE POLICY reference documents the four commands you can attach a policy to and the USING and WITH CHECK clauses that gate reads and writes.

src/db/policies.sql

CREATE POLICY tenant_isolation_select ON invoices
  FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON invoices
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON invoices
  FOR UPDATE
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON invoices
  FOR DELETE
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

The true second argument to current_setting is the one new readers always miss. By default, current_setting('app.tenant_id') raises an error if the GUC was never set. The true flag tells Postgres to return an empty string instead, which then casts to a null uuid, which then fails the equality check, which denies every row. This is what makes a forgotten middleware safe: the failure mode is zero rows, not a leaked row from the previous request. Four policies cover the four DML commands; a single FOR ALL policy would work if read and write rules are identical, but splitting them keeps the audit trail readable when you eventually need a different rule for inserts. The WITH CHECK clause on insert and update is what stops a tenant from writing a row tagged as another tenant.

The GUC is the API between your application and the policy engine.

Connection pooling and the reset-on-checkout problem

The pool is where the pattern usually breaks. A naive implementation sets the GUC with SET app.tenant_id = ... outside a transaction, the connection returns to the pool, and the next checkout inherits the value. The node-postgres pooling docs explain that pooled connections are reused across requests, which is the whole point of pooling and the whole risk for RLS. Two mitigations work together.

First, always set the GUC with set_config(..., true) inside a transaction. The transaction-scoped write reverts on commit or rollback, so the connection returns to the pool with the GUC cleared. Wrapping every code path that uses the pool in withTenant enforces this.

Second, if you sit behind PgBouncer, run it in transaction mode, not session mode. Transaction mode binds a pooled connection to a single transaction at a time, which matches the lifetime of the GUC and gives you back the per-request safety the application code already assumes. Session mode binds the connection for the whole client session, which is the opposite of what you want here. That is fine for a worker that owns a connection for its entire lifetime; it is wrong for a web tier where one request hands the connection to the next.

A shared pool is only safe when every tenant-scoped query runs inside its own transaction.

Wiring RLS through Drizzle or Prisma without escape hatches

The ORM should not let you forget the GUC. Every tenant-scoped query in the application has to flow through a transaction that sets app.tenant_id before any other statement, and the way to enforce that is to put the transaction inside a middleware the route handler cannot bypass. Both Drizzle and Prisma expose the hooks you need; the wiring differs.

Drizzle transactions and the tenant middleware

Drizzle ships a transactions API that maps directly onto the withTenant pattern, and an RLS helper module for declaring policies alongside the schema. The middleware shape below works for any framework that exposes a request lifecycle.

src/db/drizzle-tenant.ts

import { drizzle } from 'drizzle-orm/node-postgres'
import { sql } from 'drizzle-orm'
import { Pool } from 'pg'

const pool = new Pool({ connectionString: process.env.DATABASE_URL })
export const db = drizzle(pool)

export async function withTenantTx<T>(
  tenantId: string,
  work: (tx: Parameters<Parameters<typeof db.transaction>[0]>[0]) => Promise<T>,
): Promise<T> {
  return db.transaction(async (tx) => {
    await tx.execute(
      sql`SELECT set_config('app.tenant_id', ${tenantId}, true)`,
    )
    return work(tx)
  })
}

The exported withTenantTx is the only function any route handler is allowed to call. It opens a Drizzle transaction, runs set_config as the first statement inside that transaction, and hands the transaction object to the caller. Any select, insert, update, or delete the handler issues runs inside the same transaction, so the policy fires with the right GUC every time. The escape hatch a careless reviewer might miss is a direct call to db.select() outside the wrapper; the way to close it is to mark db as internal and only export withTenantTx from the module, so the type system itself stops the bypass.

src/routes/invoices.ts

import { withTenantTx } from '@/db/drizzle-tenant'
import { invoices } from '@/db/schema'

export async function listInvoices(tenantId: string) {
  return withTenantTx(tenantId, async (tx) => {
    return tx.select().from(invoices)
  })
}

The handler reads as if no policy existed at all. There is no WHERE tenant_id = in sight, because the policy adds one to the plan automatically, and that is the point. Application code stays clean, the boundary stays in the database, and a forgotten predicate is a non-event because the policy filled it in. The trade-off is that you cannot use Drizzle's logger to spot a missing tenant filter; you need a negative test instead, which the next section covers.

Prisma $extends and the same idea

Prisma's hook surface is different but the pattern is identical. The Prisma client extensions API lets you wrap every query with custom logic, and interactive transactions give you the same kind of begin-set-run-commit lifecycle Drizzle does.

src/db/prisma-tenant.ts

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

export async function withTenantTx<T>(
  tenantId: string,
  work: (tx: Parameters<Parameters<typeof prisma.$transaction>[0]>[0]) => Promise<T>,
): Promise<T> {
  return prisma.$transaction(async (tx) => {
    await tx.$executeRaw`SELECT set_config('app.tenant_id', ${tenantId}, true)`
    return work(tx)
  })
}

The mechanics match. An interactive transaction opens, the first statement is a raw set_config, and the work callback runs against the transaction-bound client. Raw SQL is unavoidable for the GUC write because the value Prisma sets through its query API would be parameterized differently and would not satisfy set_config's function signature. Everything else stays Prisma-native: relations, includes, generated types, the lot.

src/routes/invoices-prisma.ts

import { withTenantTx } from '@/db/prisma-tenant'

export async function listInvoicesPrisma(tenantId: string) {
  return withTenantTx(tenantId, (tx) => tx.invoice.findMany())
}

Note findMany() with no where. The policy supplies the predicate, and any code reviewer who sees a Prisma call inside this wrapper can trust the database to filter. Guard the same seam as Drizzle: do not export the raw PrismaClient from this module, only withTenantTx, so a handler cannot reach prisma.invoice.findMany() directly and bypass the policy.

Same pattern. Same trade-off. Different surface.

Negative tests, the part everyone skips

A policy is only as good as the test that proves it denies. The positive test (tenant A sees tenant A's invoices) is the one every team writes; the negative test (tenant A sees zero of tenant B's invoices) is the one that catches the regression where someone disabled RLS on a migration and nobody noticed. A companion piece on an AI code review checklist for React and Vue covers the wider habit of writing reviewer-grade tests; here the same principle lands on the data layer.

src/db/policies.test.ts

import { afterAll, beforeAll, describe, expect, it } from 'vitest'
import { Pool } from 'pg'
import { withTenant } from '@/db/with-tenant'

const pool = new Pool({ connectionString: process.env.DATABASE_URL })

const tenantA = '11111111-1111-1111-1111-111111111111'
const tenantB = '22222222-2222-2222-2222-222222222222'

beforeAll(async () => {
  const client = await pool.connect()
  try {
    await client.query('TRUNCATE invoices')
    await client.query(
      `INSERT INTO invoices (tenant_id, customer, total) VALUES
       ($1, 'Acme', 100.00),
       ($2, 'Globex', 200.00)`,
      [tenantA, tenantB],
    )
  } finally {
    client.release()
  }
})

afterAll(async () => {
  await pool.end()
})

describe('invoices RLS policy', () => {
  it('returns only tenant A rows when tenant A is set', async () => {
    const client = await pool.connect()
    try {
      const rows = await withTenant(client, tenantA, async (c) => {
        const { rows } = await c.query('SELECT customer FROM invoices')
        return rows
      })
      expect(rows).toEqual([{ customer: 'Acme' }])
    } finally {
      client.release()
    }
  })

  it('returns zero rows when no tenant is set', async () => {
    const client = await pool.connect()
    try {
      const { rows } = await client.query('SELECT customer FROM invoices')
      expect(rows).toEqual([])
    } finally {
      client.release()
    }
  })

  it('denies cross-tenant insert via WITH CHECK', async () => {
    const client = await pool.connect()
    try {
      await expect(
        withTenant(client, tenantA, async (c) => {
          await c.query(
            'INSERT INTO invoices (tenant_id, customer, total) VALUES ($1, $2, $3)',
            [tenantB, 'Initech', 999],
          )
        }),
      ).rejects.toThrow(/row-level security/i)
    } finally {
      client.release()
    }
  })
})

Three assertions cover the failure modes that matter. Tenant A reads tenant A's row and nothing else. A request with no tenant set reads nothing, which is the safe failure mode. An attempt to insert a row tagged as another tenant gets rejected by the WITH CHECK clause and surfaces as a Postgres error. The seed data is intentionally minimal so the test is fast and so a future change to the policy is the only reason any of these assertions can flip. I have shipped a trick on more than one project: run this test against the migrated schema in CI before any application code runs, so a missing RLS migration fails the build before the deploy.

Tests that prove denials are the only tests that prove a boundary.

Performance, indexes, and the EXPLAIN plan with RLS on

RLS is not free. The policy adds a predicate to every plan, and Postgres has to evaluate it on every row the planner considers. With the right index, the predicate folds into the index scan and the overhead is invisible. Without the index, the same query degrades to a sequential scan and the overhead is enormous. The Postgres EXPLAIN docs describe how to read the plan and confirm which scan node is responsible.

explain.sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM invoices;

Run that query twice on the same table, once with invoices_tenant_id_idx present and once after dropping it. With the index, the plan reports an Index Scan using invoices_tenant_id_idx and execution time stays in the same order of magnitude as the same query without RLS enabled, often within a few percent on tables under a million rows. Without the index, the plan reports a Seq Scan on invoices with a Filter: (tenant_id = ...) line, and execution time on a multi-million-row table can climb by an order of magnitude or more depending on the row width and the shared buffer hit rate.

The takeaway is a single rule: every column referenced by a policy needs an index. For the canonical tenant_id pattern, that is one index per tenant-scoped table. Composite indexes work too when the same query filters on tenant plus another column (a common shape for "the most recent invoices for this tenant"): CREATE INDEX invoices_tenant_id_created_at_idx ON invoices (tenant_id, created_at DESC) keeps both the policy and the order-by inside one index scan. The cost is the index maintenance on writes, which is usually a fair trade for the read path.

Add the index, never weaken the policy.

When to use SECURITY DEFINER and how to review one safely

SECURITY DEFINER is a Postgres function attribute that runs the function as the user who defined it, not the user who called it. In the RLS context, that means a definer function can read rows the calling tenant's policies would deny, which is occasionally what you want (a billing aggregate across all tenants, a platform-admin tool) and very often what an attacker would want. Every definer function is a documented policy bypass, and every one should be a review item the same way a sudo line in a shell script is.

The safe pattern is to make the bypass explicit and the scope tight. A definer function should accept the tenant id it intends to scope to as a parameter, re-set the GUC inside the function body so any policy that reads it still fires, and document the audit trail in the function comment.

src/db/definer.sql

CREATE OR REPLACE FUNCTION billing_summary(tenant uuid)
RETURNS TABLE (total numeric, count bigint)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  PERFORM set_config('app.tenant_id', tenant::text, true);

  RETURN QUERY
  SELECT SUM(total) AS total, COUNT(*) AS count
  FROM invoices;
END;
$$;

COMMENT ON FUNCTION billing_summary(uuid) IS
  'SECURITY DEFINER: runs under the function owner. Caller must pass the tenant_id and the function re-applies the GUC so the same RLS policy still fires inside the body.';

Four lines do the audit work. SECURITY DEFINER declares the bypass. SET search_path = public stops a hostile schema on the calling session from shadowing the function's table references, which is the standard injection vector for definer functions. The PERFORM set_config(...) inside the body re-applies the GUC so the same RLS policy you wrote for application traffic still constrains what the function can read. An auditor reads the COMMENT ON FUNCTION first; treat it as a required field.

The review checklist for any new definer function is short. Does it take the tenant scope as an explicit parameter? Does it re-apply the GUC inside the body? Does it set search_path? Is the comment honest about what it bypasses? If any answer is no, the function is the bug.

Every definer function is a tracked exception, not a shortcut to skip RLS.

A reviewable RLS checklist for your next PR

The pattern fits on a PR template. Paste this into the description of any PR that touches a tenant-scoped table, and any reviewer can validate the diff in under five minutes. The same posture lives on the application side in the hardening AI-generated React apps for production audit; this is the database-layer companion to that pass.

1. ALTER TABLE <table> ENABLE ROW LEVEL SECURITY; is in the migration.
2. ALTER TABLE <table> FORCE ROW LEVEL SECURITY; is in the migration so owner roles do not bypass.
3. A CREATE POLICY exists for every command the application uses (SELECT, INSERT, UPDATE, DELETE), each referencing current_setting('app.tenant_id', true)::uuid and using WITH CHECK for writes.
4. Every column referenced by a policy has an index, and the index is part of the same migration.
5. The application path goes through a single withTenantTx wrapper that begins a transaction, calls set_config(..., true) as the first statement, and runs the work inside the same transaction.
6. The raw db or prisma client is not exported from the tenant module; only the wrapper is.
7. A negative test asserts that a request with no tenant set returns zero rows, and that a write tagged as another tenant raises a policy error.
8. Any new SECURITY DEFINER function takes the tenant id as a parameter, re-applies the GUC inside the body, sets search_path = public, and has a COMMENT ON FUNCTION that explains the bypass.
9. If the deployment uses PgBouncer, the pool runs in transaction mode, not session mode.
10. The CI pipeline runs the negative test against the migrated schema before the application image is built.

Ten items. Two minutes per item. One audit-safe boundary at the end.

That is the full pattern: enable, force, set, check, test, index, review. RLS is the cheapest hard boundary you can put on a multi-tenant Postgres, and the pattern stays the same whether you reach for Drizzle, Prisma, or raw node-postgres. The work is in the wiring, not in the feature.