Back to Knowledge Hub

Architecting Multi-Tenant SaaS Databases with Row-Level Security in PostgreSQL

A hands-on engineering guide to enforcing tenant isolation at the database layer using PostgreSQL Row-Level Security (RLS), connection pooling via PgBouncer, and automated Prisma migrations.

1. The Tenant Isolation Problem in SaaS

When engineering multi-tenant B2B SaaS applications, software architects face a fundamental choice between three isolation strategies:

1. **Database-per-tenant**: Cleanest physical separation, but exorbitant operational overhead and connection pool exhaustion at 500+ tenants. 2. **Schema-per-tenant**: Moderate separation, but database schema migrations (e.g. running 2,000 DDL scripts simultaneously) become a maintenance nightmare during continuous deployment. 3. **Shared-database, shared-schema with Row-Level Security (RLS)**: High resource efficiency, single migration path, and strict cryptographic/policy isolation enforced directly in the database kernel.

For 95% of modern SaaS products, the shared-schema model with PostgreSQL Row-Level Security delivers the optimal balance of engineering velocity, cost efficiency, and rigorous security guarantees.

---

2. Why Application-Layer WHERE Clauses Fail

In junior engineering codebases, tenant isolation is frequently implemented by manually appending `WHERE tenant_id = current_tenant` to every database query.

This approach is inherently fragile: - A single omitted WHERE clause in an admin endpoint, cron job, or search route instantly leaks confidential customer data across tenant boundaries. - Refactoring queries or adding complex JOINs dramatically increases the surface area for data cross-contamination bugs. - Application-level bugs can bypass business logic filters, exposing data at the raw database boundary.

With PostgreSQL RLS, the database engine **refuses to return rows belonging to another organization**, regardless of what SQL query the application service executes.

---

3. Implementing Postgres Row-Level Security

Let's inspect the canonical DDL implementation:

-- Enable Row-Level Security on the core table
ALTER TABLE workspaces ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Define a policy that restricts all CRUD operations to the session's active tenant CREATE POLICY tenant_isolation_policy ON documents FOR ALL USING ( tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::UUID ) WITH CHECK ( tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::UUID );

When a database connection executes queries, the application middleware injects the authenticated tenant ID into the transaction context:

// In your transaction wrapper or Prisma middleware:
await prisma.$executeRawUnsafe(
  `SET LOCAL app.current_tenant_id = '${tenantId}';`
);

// Any subsequent query is automatically filtered by Postgres kernel! const documents = await prisma.document.findMany();

---

4. Connection Pools & Transaction-Local Settings

A critical pitfall in production environments is using `SET app.current_tenant_id` across pooled connections (such as PgBouncer or serverless connection pools).

If `SET` is executed globally on a pooled connection, the setting persists for the next request that reuses the connection, leading to catastrophic cross-tenant leakage.

**The Solution:** Always use **`SET LOCAL`** inside an explicit transaction block:
BEGIN;
SET LOCAL app.current_tenant_id = 'c8b21c4e-5412-421f-829d-4395679901ef';
SELECT * FROM documents;
COMMIT;
The `LOCAL` modifier guarantees that the setting is discarded immediately when the transaction commits or aborts.

---

5. Compound Indexing & Benchmark Results

Enabling RLS introduces a WHERE condition evaluated against every row candidate. To maintain fast query performance at scale, every tenant-partitioned table must have a compound index starting with `tenant_id`:

-- Critical compound index for multi-tenant temporal queries
CREATE INDEX idx_docs_tenant_created 
ON documents (tenant_id, created_at DESC)
WHERE is_deleted = FALSE;

PostgreSQL query plans utilizing this compound index evaluate tenant partitions efficiently using indexed scans rather than full sequential table scans.