SaaS Development
8 min read

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.

PostgreSQL multi-tenant row level security architecture diagram

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.