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.