Tenant isolation with PostgreSQL row security

Database-Layer Isolation Fundamentals

In a multi-tenant application, relying solely on application-layer filtering (e.g., appending WHERE tenant_id = X to every query) introduces systemic fragility. A single missed filter can result in cross-tenant data exposure. PostgreSQL Row-Level Security (RLS) mitigates this by enforcing access control deep within the database engine.

When RLS is enabled on a table using ALTER TABLE ... ENABLE ROW LEVEL SECURITY, all normal access must be explicitly permitted by a policy. If no policy exists, the table defaults to a deny-all posture for standard roles. Policies evaluate a boolean expression for each row before query conditions are applied, filtering out unauthorized data. Operations that apply to the whole table, such as TRUNCATE, bypass these checks.

It is critical to note that superusers, table owners, and roles granted the BYPASSRLS attribute are exempt from RLS by default. Table owners can optionally enforce RLS on themselves using ALTER TABLE ... FORCE ROW LEVEL SECURITY. Policies are evaluated using the privileges of the user executing the query, and they can be scoped to specific commands like SELECT, INSERT, UPDATE, or DELETE.

Concrete Worked Example: Multi-Tenant Architecture

Implementing tenant isolation requires defining both the visibility of existing rows and the constraints on creating or modifying rows. Consider a hypothetical metrics table where each row belongs to a specific tenant. The application connects to PostgreSQL using a dedicated role, and the current tenant ID is passed via a runtime configuration parameter.

First, enable RLS on the target table: ALTER TABLE tenant_metrics ENABLE ROW LEVEL SECURITY. Next, define a policy that restricts access. A comprehensive policy uses both the USING clause to filter visible rows and the WITH CHECK clause to validate new or modified rows.

CREATE POLICY tenant_isolation_policy ON tenant_metrics TO app_user USING (tenant_id = current_setting('app.current_tenant')::uuid) WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

By relying on current_setting, the application avoids maintaining separate database roles for thousands of tenants. The connection pooler or application framework must strictly set this parameter at the start of every transaction and clear it afterward to prevent connection-bleeding.

Safe Test Matrix and Expected Outcomes

Validating RLS implementation requires a deterministic test matrix executed under an unprivileged role. Testing must cover boundary conditions, unauthorized modifications, and cross-tenant access attempts. The following synthetic fixtures demonstrate expected database responses when app_user assumes the identity of Tenant A.

• SELECT query without tenant context: Expected outcome is an error from current_setting if it is strictly evaluated, or an empty result set if missing_ok is true.
• SELECT query with Tenant A context: Expected outcome is retrieving only records where tenant_id matches Tenant A. Tenant B records must be silently omitted.
• INSERT record for Tenant A: Expected outcome is successful insertion.
• INSERT record for Tenant B while in Tenant A context: Expected outcome is an error: new row violates WITH CHECK OPTION for relation.
• UPDATE Tenant B record to belong to Tenant A: Expected outcome is UPDATE 0, as the USING clause filters the target row out of visibility before the update is evaluated.
• Superuser SELECT without context: Expected outcome is returning all rows for all tenants, demonstrating that BYPASSRLS circumvents the policies.

Implementation Tradeoffs and Performance

While RLS centralizes authorization, it introduces specific tradeoffs. The policy expressions are evaluated for every row processed by the query executor. Simple policies that compare column values against connection variables, as shown in the example, carry negligible overhead. However, complex policies relying on sub-SELECTs or pl/pgsql functions can severely degrade performance.

Database optimizers generally push RLS filters down efficiently, but developers must ensure that columns used in RLS policies (like tenant_id) are properly indexed. Without indexes, full table scans evaluating the policy condition across millions of rows will cause latency spikes.

Another tradeoff involves backup and maintenance operations. A standard pg_dump executed by an unprivileged role subject to RLS will silently omit restricted rows, leading to incomplete backups. The row_security configuration parameter can be set to off to throw an error if a policy would filter results, but backups should always run under roles with BYPASSRLS.

Failure Modes, Race Conditions, and Covert Channels

RLS implementation is prone to subtle failure modes. Referential integrity checks, such as unique constraints and foreign keys, always bypass row security. If a multi-tenant table enforces a global UNIQUE(email) constraint, Tenant A can deduce that an email belongs to Tenant B if their INSERT statement fails with a constraint violation. This constitutes a covert channel.

More severe vulnerabilities arise when policy expressions utilize sub-SELECTs against heavily updated permission tables. As documented by the PostgreSQL project, concurrent transactions can exploit race conditions in READ COMMITTED mode. If a policy checks a user's privilege level via a subquery without FOR SHARE, an attacker might query a row just as their permissions are being revoked.

The attacker's query blocks waiting for the permission update to commit. Once committed, the FOR UPDATE clause fetches the target row, but the sub-SELECT reads the permission table using the transaction's initial snapshot. The outdated policy allows access to the data. Mitigation requires using SELECT ... FOR SHARE in the subquery or taking ACCESS EXCLUSIVE locks on permission tables during updates, which heavily impacts concurrency.

Sources and Evidence Limits

The factual claims regarding PostgreSQL Row-Level Security, including the BYPASSRLS attribute, the behavior of referential integrity constraints, and the specific race condition involving sub-SELECTs in READ COMMITTED isolation mode, are grounded in the official PostgreSQL 18 documentation. The information reflects the capabilities of PostgreSQL as of 2026-11-12.

Drafted with Gemini and checked for source links, advisory identifiers, structure and unsupported product claims. Examples are synthetic and are not scan results.

Drafted with Gemini and checked for source links, advisory identifiers, structure and unsupported product claims. Examples are synthetic and are not scan results.

• PostgreSQL 18 Documentation: Row Security Policies