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.