Tenant Isolation Should Not Depend on Every WHERE Clause

In a shared-schema SaaS database, the most expensive missing predicate is usually tenant_id = ?.

Application reviews, repository abstractions, and integration tests reduce the risk, but they all rely on every future query preserving the same invariant. PostgreSQL row-level security (RLS) moves that invariant closer to the data: normal access is permitted only when a policy allows the row.

RLS is not a complete tenancy strategy. It is a database-enforced backstop for one of its most important rules.

Start with the invariant #

For an invoice table, state the policy before writing SQL:

A request may read or modify an invoice only when the request tenant equals the row tenant. New rows must belong to the request tenant.

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

CREATE POLICY invoice_tenant_select
ON invoice FOR SELECT
USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE POLICY invoice_tenant_write
ON invoice FOR ALL
USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

USING filters existing rows. WITH CHECK rejects rows whose new values violate the policy. Both matter: protecting reads while permitting a tenant to insert another tenant’s ID is not isolation.

PostgreSQL applies default deny when RLS is enabled and no applicable policy exists. Treat that as a deployment advantage: new tables should fail closed until their access model is explicit.

Request context must survive pooling #

The policy is only as correct as app.tenant_id. With transaction pooling, session-level state can leak across requests. Set context inside the transaction and make it local:

BEGIN;
SELECT set_config('app.tenant_id', $1, true); -- true: transaction-local
-- application queries
COMMIT;

Never accept the tenant identifier from an unverified request field. Derive it from authenticated identity and authorised membership. Resetting state is not an adequate substitute for transaction-local context because exceptions and pool behaviour create leak paths.

Background workers need the same contract. A job payload should carry a tenant identity or intentionally use a separate privileged workflow with bounded scope and an audit trail.

Understand bypass paths #

Table owners normally bypass row security. Superusers and roles with BYPASSRLS do too. FORCE ROW LEVEL SECURITY makes the owner subject to policies in ordinary operation, but privileged roles remain privileged.

Therefore:

• the application must not connect as a superuser;
• the migration owner should be different from the runtime role;
• administrative jobs need narrowly scoped roles;
• connection strings and role grants belong in security review;
• tests must run under the real runtime role, not the table owner.

An RLS test executed as a bypassing role proves nothing.

Index for the policy #

RLS adds policy expressions to query planning. Tenant-scoped access should usually begin with tenant identity in the index:

CREATE INDEX invoice_tenant_created_idx
ON invoice (tenant_id, created_at DESC);

CREATE UNIQUE INDEX invoice_tenant_number_key
ON invoice (tenant_id, invoice_number);

Global uniqueness is often a hidden multi-tenant bug. If invoice numbers need only be unique within a tenant, encode that fact in the constraint.

Inspect representative plans with the runtime role and tenant context. Policies containing subqueries, volatile functions, or joins can create surprising cost and locking behaviour. Keep the hot-path predicate simple.

Test isolation as a matrix #

Do not stop at “tenant A cannot select tenant B.” Test every command and bypass boundary:

Add property-style tests that generate tenants and operations. Include prepared statements and the production pooler mode. Isolation failures are data breaches, so this suite deserves the same release authority as payment correctness.

Operations still need tenant-aware design #

RLS does not isolate CPU, connections, storage growth, locks, or noisy queries. It does not solve per-tenant backup and restore. It does not automatically scope caches, object storage, search indexes, events, or analytics.

Log the authenticated tenant separately from any tenant ID supplied in data. Alert on policy violations without logging sensitive row content. Include tenant scope in reconciliation and deletion workflows.

For large or regulated tenants, pooled RLS may eventually give way to schemas, databases, or cells. That is not a failure. Isolation is a spectrum, and the correct boundary follows risk, scale, and recovery requirements.

The architecture decision #

Use RLS when many tenants share tables and the cost of a missed application predicate is unacceptable. Keep application predicates anyway: they communicate intent, improve portability, and can help query plans. The database policy is defense in depth, not permission to make the application tenant-unaware.

The strongest test is simple: can one ordinary application query accidentally cross the tenant boundary? With correctly operated RLS, the database should make the answer no.

References #

• PostgreSQL: Row Security Policies
• PostgreSQL: CREATE POLICY
• Related: Multi-Tenant SaaS Isolation
• Related: PostgreSQL Connections Are a Capacity Budget