The Practical Guide to PostgreSQL Row Level Security for Multi-Tenant SaaS

Relying on developers to remember a manual tenant filter on every SQL query is a data breach waiting to happen unless your database enforces isolation at the storage engine layer.

VW
Viery Wahyu•Author
•9 min read

Every multi-tenant software architecture eventually faces a sobering security question. When hundreds of competing merchants, travel agencies, or enterprise teams store their financial ledgers and customer records inside the exact same PostgreSQL tables, what actually stops Tenant A from reading Tenant B's private rows? In ninety percent of early-stage SaaS codebases, the honest answer is nothing more than developer memory. Every time an engineer writes a new Drizzle or Prisma query, builds a CSV export route, or joins three tables for an analytics widget, they have to manually append a WHERE tenant_id = ? clause. Relying on human beings to never forget an application-level filter across five hundred endpoints is not a security architecture; it is a countdown timer to a cross-tenant data leak. When we consolidated our multi-branch booking and settlement engines onto a shared PostgreSQL cluster, we decided that application-level filters were no longer enough and pushed mandatory tenant isolation straight down into the database kernel using PostgreSQL Row Level Security.

Why Shared Tables With Row Level Security Beat Schema Sprawl

When teams first worry about tenant isolation, their initial impulse is often creating a separate physical database or a separate PostgreSQL schema namespace for every single customer sign-up. That silo model feels safe when you only have ten enterprise clients, but it turns into an operational nightmare the moment you scale past a few hundred active tenants. Running a simple column migration requires iterating DDL statements across thousands of isolated schemas, connection pools run out of file descriptors trying to keep warm sockets open across fragmented databases, and PgBouncer configurations buckle under metadata overhead.

PostgreSQL Row Level Security, commonly known as RLS, gives you the operational simplicity of a single unified schema alongside the mathematical isolation of separate databases. Instead of trusting your TypeScript application code to filter rows correctly, you attach a declarative security policy directly to the PostgreSQL table. Whenever any SQL query touches that table—whether it is a simple primary key lookup, a complex multi-table JOIN, an UPDATE, or a bulk DELETE—the PostgreSQL query planner automatically rewrites the query execution plan behind the scenes and injects your tenant boundary predicate before reading or mutating a single disk page.

Writing Bulletproof Policies That Enforce Both Reads and Writes

Configuring Row Level Security in production requires avoiding three classic traps that catch almost every engineer on their first attempt. First, PostgreSQL policies only govern non-owner database roles by default. If your Node.js backend connects to the database using the exact same postgres superuser or table-owner role that ran your schema migrations, PostgreSQL silently bypasses every single RLS policy on the table and returns all rows across all tenants. You must always create a dedicated, least-privilege application role without the BYPASSRLS attribute and run ALTER TABLE ... FORCE ROW LEVEL SECURITY so even table owners remain bound by the policy.

Second, a production policy must define both a USING clause and a WITH CHECK clause. The USING expression filters existing table rows during SELECT, UPDATE, and DELETE operations, making rows belonging to other tenants completely invisible. However, without an explicit WITH CHECK expression, a buggy API endpoint authenticated as Tenant A could still execute an INSERT or UPDATE statement carrying Tenant B's identifier and corrupt another customer's workspace.

Third, you need a safe mechanism to pass the active tenant identifier from your stateless backend into the PostgreSQL policy evaluator without causing type-cast crashes when a background health check runs without a tenant context. We solve this by reading a custom runtime configuration parameter via current_setting('app.current_tenant_id', true) wrapped inside NULLIF so an unset empty string resolves cleanly to NULL rather than throwing an invalid UUID syntax exception.

typescript
1-- Create a dedicated non-superuser role for the Node.js application pool
2CREATE ROLE app_runtime LOGIN PASSWORD 'strict-production-secret' NOSUPERUSER NOBYPASSRLS;
3GRANT USAGE ON SCHEMA public TO app_runtime;
4GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_runtime;
5
6-- Multi-tenant table storing sensitive merchant invoices
7CREATE TABLE public.merchant_invoices (
8 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
9 tenant_id UUID NOT NULL,
10 customer_email TEXT NOT NULL,
11 amount_cents BIGINT NOT NULL,
12 status TEXT NOT NULL DEFAULT 'DRAFT',
13 created_at TIMESTAMPTZ NOT NULL DEFAULT now()
14);
15
16-- Composite index with tenant_id leading for fast index-only scans under RLS
17CREATE INDEX idx_invoices_tenant_created ON public.merchant_invoices (tenant_id, created_at DESC);
18
19-- Enable and strictly force Row Level Security even for the table owner
20ALTER TABLE public.merchant_invoices ENABLE ROW LEVEL SECURITY;
21ALTER TABLE public.merchant_invoices FORCE ROW LEVEL SECURITY;
22
23-- Enforce tenant isolation on both read visibility (USING) and write mutations (WITH CHECK)
24CREATE POLICY tenant_isolation_policy ON public.merchant_invoices
25 AS RESTRICTIVE
26 FOR ALL
27 TO app_runtime
28 USING (
29 tenant_id = (SELECT NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
30 )
31 WITH CHECK (
32 tenant_id = (SELECT NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
33 );

Look closely at the subtle (SELECT NULLIF(...)::uuid) wrapper inside the USING and WITH CHECK clauses above, because that single SELECT subquery is the secret to keeping PostgreSQL RLS blazing fast on million-row tables. If you write tenant_id = current_setting('app.current_tenant_id', true)::uuid directly without the surrounding scalar subquery, PostgreSQL treats current_setting as a volatile function that must be re-evaluated for every single row scanned in the table. Wrapping the call inside (SELECT ...) tricks the PostgreSQL optimizer into creating an InitPlan node, evaluating the session variable exactly once at the start of the query, caching the resulting UUID constant in memory, and driving a clean B-Tree index scan against idx_invoices_tenant_created.

Eliminating Connection Pool Context Bleed With Scoped Transactions

Once the database schema enforces RLS via app.current_tenant_id, the most dangerous architectural mistake you can make is setting that variable at the session level over a shared connection pool. In a high-concurrency Node.js backend using pg-pool or an upstream PgBouncer instance in transaction pooling mode, database connections are constantly borrowed and returned by different HTTP requests. If Request One runs a standard SET app.current_tenant_id = 'tenant-alpha' statement and returns the socket to the pool, that session variable remains active on that underlying TCP socket. If Request Two borrows that same socket for an unauthenticated route or fails to overwrite the variable before running a query, it will silently inherit Tenant Alpha's security context.

Preventing connection pool bleed requires scoping the tenant variable strictly to the lifespan of an atomic database transaction using SET LOCAL or the built-in set_config(setting_name, new_value, is_local) function with the third parameter set to true. When is_local is true, PostgreSQL guarantees that the configuration setting only exists inside the current transaction block and is automatically wiped the exact millisecond the transaction commits or rolls back, making cross-request context pollution impossible.

In our TypeScript and Drizzle ORM services, we enforce this boundary by hiding the raw database client behind a mandatory withTenantContext transaction wrapper that every domain service and route handler must call.

typescript
1import { db } from "@/db"
2import { sql } from "drizzle-orm"
3
4export async function withTenantContext<T>(
5 tenantId: string,
6 operation: (tx: typeof db) => Promise<T>
7): Promise<T> {
8 if (!tenantId || !/^[0-9a-fA-F-]{36}$/.test(tenantId)) {
9 throw new Error("Valid tenant UUID is required to open an isolated database context")
10 }
11
12 return await db.transaction(async (tx) => {
13 // Bind tenant_id strictly to this transaction scope (third argument = true)
14 await tx.execute(
15 sql`SELECT set_config('app.current_tenant_id', ${tenantId}, true)`
16 )
17
18 // Execute all downstream Drizzle queries inside the isolated RLS transaction
19 return await operation(tx as unknown as typeof db)
20 })
21}

Wrapping our data access layer inside withTenantContext makes secure multi-tenancy effortless for product engineers. When a developer needs to fetch all draft invoices inside an API handler, they simply run withTenantContext(session.tenantId, (tx) => tx.select().from(merchantInvoices)). Even if the developer completely forgets to write a .where(eq(merchantInvoices.tenantId, session.tenantId)) filter in TypeScript, the set_config statement at the top of the transaction arms the PostgreSQL policy, and the database engine refuses to return or modify a single row outside that tenant's boundary. Conversely, if a developer accidentally calls db.select().from(merchantInvoices) outside of withTenantContext, app.current_tenant_id evaluates to NULL, the restrictive RLS policy fails closed, and PostgreSQL safely returns zero rows instead of leaking the entire table.

Handling Super-Admin Operations and Foreign Key Cascades

Deploying RLS across a real production SaaS does require planning for background system jobs and internal support dashboards that legitimately need to query across multiple tenants—such as a nightly billing cron job or an internal fraud detection worker. Rather than disabling RLS on the tables or littering policies with complex conditional OR branches that confuse the query planner, we provision a second database role named app_system_worker granted the BYPASSRLS privilege, or create a separate permissive policy scoped specifically to an internal admin session flag verified by cryptographic HMAC tokens. Keeping your customer-facing connection pool strictly locked to the restricted app_runtime role while isolating cross-tenant maintenance jobs on a dedicated worker pool preserves clean separation of concerns.

You also want to pay close attention to foreign key indexes and cascade rules on RLS-protected tables. While PostgreSQL checks foreign key referential integrity beneath the RLS layer so a tenant cannot infer the existence of another tenant's parent ID safely if you composite-key your references (tenant_id, id), failing to include tenant_id as the leading column in your composite primary keys and foreign keys forces the planner to perform extra heap fetches during joins. Aligning your physical indexes and foreign keys around (tenant_id, record_id) ensures that security enforcement and query performance pull in the exact same direction.

Defense in Depth That Lets Engineers Ship Fearlessly

Implementing PostgreSQL Row Level Security across our multi-tenant platforms did not mean instructing our engineers to stop writing explicit WHERE clauses in their application code. Explicit query filters in TypeScript still make code self-documenting and catch logical bugs early in unit tests. What RLS provides is an unbreakable last line of defense right inside the storage engine. When you combine a non-superuser runtime role, FORCE ROW LEVEL SECURITY, InitPlan-optimized policy expressions, and transaction-local set_config wrappers in Drizzle ORM, a forgotten filter in a rushed Friday pull request simply returns an empty array instead of becoming a company-ending security headline.

VW

Written by

Viery Wahyu

📍 Bali, Indonesia

Software Engineer

Software engineer based in Bali with over a decade of experience building fast web applications

The Journal

View all →