PostgreSQL Row-Level Security: Making the Database Enforce Multi-Tenant Isolation

Infrastructure PostgreSQL Database
4 min read

The worst accident in multi-tenant SaaS is not performance — it’s another company’s data becoming visible. The cause is usually one line: somewhere, some query is missing its WHERE tenant_id = ?. Code review and discipline only go so far — there are hundreds of queries, new teammates keep joining, and one omission is one incident. Row-Level Security (RLS) moves that filter from application discipline into database enforcement. It’s the access-control edition of the principle from basics chapter 9: integrity lives in the database layer.

The basic form: start with one policy #

Basic RLS setup 
-- 1) enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- 2) policy: only the session's configured tenant's rows are visible — and creatable
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.tenant_id')::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

From here, every SELECT, UPDATE, and DELETE on this table gets the USING condition, and the new values of INSERT and UPDATE get the WITH CHECK condition, applied automatically. Even if a query omits the WHERE, other tenants’ rows appear not to exist, and attempts to create rows under another tenant error out. USING is “what is visible”; WITH CHECK is “what may be written.”

Miss two premises and RLS becomes decoration. First, policies do not apply to the table owner or superusers by default. If the application connects as the owner, RLS is skipped wholesale — the least-privilege, non-owner role structure built in basics chapter 9 is a precondition (to enforce even against the owner, FORCE ROW LEVEL SECURITY). Second, an RLS-enabled table with zero policies defaults to denying everything. “I turned it on and nothing is visible” is correct behavior.

Selecting the tenant: the current_setting pattern and connection pools #

The current_setting('app.tenant_id') value referenced by the policy is a session (or transaction) variable. Planting the authenticated tenant at the start of request handling is the application’s job.

Select the tenant 
BEGIN;
SET LOCAL app.tenant_id = '42';   -- valid only for this transaction
-- ... every query of this request ...
COMMIT;

The crux is not SET but SET LOCAL. With a connection pool, connections are shared property rotated between requests, and a session-level SET leaks one request’s tenant into the next. SET LOCAL evaporates with the transaction, which also makes it compatible with PgBouncer’s transaction mode. In most frameworks the standard shape is “open a transaction in request-start middleware and SET LOCAL.”

Performance: keep conditions simple, make the index mandatory #

RLS is, in substance, “a WHERE condition automatically attached to every query,” so its performance principles match ordinary queries. With an index on tenant_id, the policy filter is a plain index condition and the overhead is negligible. Without it, every query becomes a tenant-filtered full scan. For composite indexes, putting tenant_id as the leading column is where this meets the index ground rules. And a policy whose condition runs subqueries or touches other tables multiplies that cost into every query — keep policies at the level of “current row’s column = session variable.” Verification is, as always, EXPLAIN; the policy condition shows up as a filter in the plan.

When RLS is not the answer #

  • When access rules are complex business logic (document sharing tangled with roles, states, and ownership). A policy that becomes a logic blob is painful to debug and to run. Use RLS for simple boundaries like tenant_id; complex authorization belongs in the application layer.
  • When tenant counts are small and isolation demands are extreme, schema-per-tenant or database-per-tenant are the candidates. RLS is the middle-ground answer of “shared tables + row isolation.”
  • For paths that sweep all tenants — analytics, batch jobs — an explicit policy-bypassing role (BYPASSRLS) is cleaner than branching exceptions into the policy and complicating the normal path.

Summary #

  • RLS turns the “missing WHERE tenant_id” accident from a code-discipline problem into database enforcement. USING is visibility; WITH CHECK is write validation.
  • Owners and superusers are exempt by default. Connecting via a non-owner least-privilege role is the precondition.
  • Tenant selection standardizes on transaction + SET LOCAL. Session-level SET under a connection pool is the seed of contamination incidents.
  • The performance conditions are a tenant_id index (leading column if composite) and simple policy conditions. Hold those and overhead is negligible.
  • Complex authorization logic, extreme isolation demands, and all-tenant batch paths belong outside RLS (application layer, schema separation, a BYPASSRLS role).
X