Multi-Tenant SaaS with PostgreSQL Row-Level Security

Multi-tenant SaaS has a deceptively simple data isolation requirement: one customer must never see another customer's data. It sounds like a basic constraint. In practice, it is the kind of requirement that gets violated quietly, not through malice, but through a missing filter clause, an abstraction that forgot to thread tenant context through, or a new query written by someone who didn't know the convention.

Application-layer isolation works until it doesn't. The failure mode is silent, the blast radius is a data leak, and the usual response is a post-incident review that concludes someone should have been more careful. PostgreSQL's Row-Level Security offers a different approach: move the isolation boundary into the database, where it operates below the application and cannot be bypassed by any query the application sends.

What Row-Level Security Actually Does

RLS lets you attach policy expressions to a table. When enabled, PostgreSQL rewrites every query against that table to include the policy predicate, injected at the query planning stage, before execution. This applies regardless of how the query reaches the database: through your ORM, through a raw query, through a background job, through a new engineer's first PR. The application never receives rows it isn't entitled to see.

PostgreSQL separates policies into two types: USING policies filter which existing rows are visible (reads and the "before" state of updates/deletes), and WITH CHECK policies validate which rows can be written (inserts and the "after" state of updates). They can differ, you might allow a tenant to read all their rows but restrict which rows they can create based on additional conditions.

The standard pattern is straightforward: each tenant-scoped table carries a tenant_id column, and the USING policy specifies that a row is visible only when its tenant_id matches the current session's tenant context.

Setting the Tenant Context

The tenant context is a session-level variable, set at the beginning of each request. The critical detail is how you set it:

-- Inside a transaction: scoped to this transaction only
SET LOCAL app.current_tenant = 'tenant-uuid-here';
 
-- Outside a transaction: persists for the entire connection lifetime
SET app.current_tenant = 'tenant-uuid-here';

SET LOCAL is what you want. It's scoped to the current transaction and cleared automatically when the transaction ends. SET without LOCAL persists for the connection's lifetime, which becomes a data leak when you're using connection pooling. If two requests share a pooled connection and the second request doesn't set the variable, it inherits the first request's tenant context. Every test passes; the bug surfaces under load in production.

The safe pattern is: acquire a connection, begin a transaction, SET LOCAL the tenant context, run queries, commit. The middleware does this once; every query inside the transaction operates as if the database only contains that tenant's rows.

The Isolation Guarantee

The meaningful property here is what happens when something goes wrong. In application-layer isolation, a missing WHERE clause is the entire failure, rows from other tenants are returned, and the application has no way to know. With RLS, a missing WHERE clause returns only the current tenant's rows. The policy predicate is appended by the query planner regardless.

This doesn't make application bugs impossible, but it changes their blast radius. The worst-case failure from an application bug is a user seeing wrong data within their own tenant. Cross-tenant exposure requires bypassing the database policy, which requires a different class of failure entirely.

For products where tenant isolation is a compliance requirement, healthcare, finance, legal, this is not a subtle distinction. The difference between "we rely on application code to enforce isolation" and "isolation is enforced at the database layer" is material in security reviews and audit conversations.

Fail-Closed by Default

What happens if the session variable isn't set at all? If you reference current_setting('app.current_tenant') and the variable doesn't exist, PostgreSQL raises an error, the query fails, no rows are returned. This is fail-closed behaviour, and it's the right default. A misconfigured request path that forgets to set the tenant context will surface as an error, not as a silent data leak.

You can also call current_setting('app.current_tenant', true), which returns NULL instead of erroring when the variable is missing. Since tenant_id = NULL evaluates to NULL (not TRUE), no rows match, also fail-closed, but silently. Both are safe. I prefer the error: explicit failures are easier to debug than empty result sets.

Ownership and FORCE

A subtlety that's easy to miss: by default, table owners bypass RLS entirely. If your application connects as the role that owns the tables, RLS policies are silently ignored. You need both:

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE accounts FORCE ROW LEVEL SECURITY;

FORCE ensures the policy applies even to the table owner. Without it, your RLS setup has a silent hole that passes every integration test, because your test database role probably owns the tables too. Superusers bypass RLS even with FORCE, which matters for migration scripts and database maintenance, but those shouldn't be running through your application's connection pool.

Cross-Tenant Operations

Enabling RLS creates an immediate question: what about operations that legitimately need to span tenants? Billing aggregation, platform analytics, support tooling, and data migrations all need a view of data that crosses tenant boundaries.

The clean solution is a separate database role with explicit BYPASSRLS privileges, used only for these operations. The standard application role is subject to all RLS policies. The admin role bypasses them. They have separate credentials, separate connection pools, and separate operational contexts. An application server running user-facing requests cannot accidentally use admin-level access, the credentials aren't there.

This enforces a discipline that most multi-tenant systems need anyway: cross-tenant operations should be explicit and intentional, not something that happens because a filter was forgotten.

The Operational Cost

RLS has real tradeoffs. Policies live in the database schema, not in application code, which means they need to be versioned, reviewed in migrations, and understood by the team as part of the data model. A developer who doesn't know to look for RLS policies when debugging unexpected query behaviour will spend time confused. Migration ordering matters too, if you enable RLS on a table before creating its policy, there's a window where all rows become invisible. Enable and create the policy in the same migration, or create the policy first.

Performance is generally not a concern for the common case. The policy predicate is an equality check on an indexed tenant_id column, the same cost as writing the WHERE clause yourself, which you would have done anyway. More complex policies involving subqueries or joins require more care with query planning, but they're also uncommon.

Connection pooling is the area that requires the most operational attention. With PgBouncer in transaction mode, SET LOCAL works correctly, the variable is scoped to the transaction and cleared when the connection is returned to the pool. In statement mode, SET LOCAL has no effect because there's no transaction scope. If you're using statement-level pooling, RLS tenant context doesn't work without restructuring your pool configuration.

What It Doesn't Replace

RLS handles the outer boundary: ensuring one tenant's data is invisible to another. It doesn't handle authorisation within a tenant, who within an organisation can approve a transaction, who can export data, what operations require elevated confirmation. Those remain application concerns.

The correct mental model is layered: RLS is the guarantee that no bug in the application layer can produce cross-tenant exposure. RBAC and application-level checks provide finer-grained control within that boundary. Each layer does what it is suited for.

A multi-tenant architecture with both is one where the worst-case failure of any single application bug is scoped. That's the right goal, not preventing all bugs, but ensuring that bugs have bounded consequences.