Multi-tenant SaaS on Postgres · the RLS-first playbook

Multi-tenant SaaS on Postgres in 2026 means RLS. Application-layer tenant filtering is not enough · one forgotten WHERE clause or LLM-generated SQL query is a cross-tenant data breach. This is our current playbook, refined across 12 multi-tenant SaaS projects.

The non-negotiable setup

1. Every tenant-aware table has a `tenant_id UUID NOT NULL` column.
2. `ENABLE ROW LEVEL SECURITY` + `FORCE ROW LEVEL SECURITY` on every such table.
3. Policies check `tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid`.
4. Application code uses `SET LOCAL app.tenant_id = $1` inside every transaction.
5. PgBouncer in transaction-pooling mode · compatible with `SET LOCAL` but not `SET`.

Tenant context helper

import { sql } from 'drizzle-orm';

export async function withTenant<T>(tenantId: string, fn: (tx: Transaction) => Promise<T>): Promise<T> {
  return db.transaction(async (tx) => {
    await tx.execute(sql`SET LOCAL app.tenant_id = ${tenantId}`);
    return fn(tx);
  });
}

5 RLS bypasses we guard against

• Forgetting `FORCE` · table owner bypasses policies
• Unset `app.tenant_id` · fails open if policy doesn't use NULLIF
• Superuser connection · production should never use postgres superuser
• SECURITY DEFINER function · bypasses RLS unless explicitly marked INVOKER
• `RETURNING *` without a SELECT policy · silent failures

CI-testing tenant isolation

For every migration, CI runs a 'tenant leak' test: create a row as tenant A, switch context to tenant B, try to read · must return zero rows. Do this for every new table. Our base test file is 40 lines and catches 90% of policy bugs.