Postgres Row-Level Security: When the Database Enforces What the Application Forgets

Multi-tenant SaaS applications enforce tenant isolation through application code. Every query includes a WHERE clause that scopes the results to the current tenant. Every write includes the tenant ID in the new row. Every API request authenticates the caller and resolves their tenant context. The pattern works when every developer remembers every isolation rule on every code path. The failure modes are familiar: a join that crosses tenant boundaries because someone forgot to filter the joined table, a background job that processes records across tenants because the job runner does not have tenant context, a debug endpoint that returns data from any tenant because it bypasses the normal authorization layer. Each of these has been a real customer-facing security incident at companies you have heard of.

Postgres row-level security (RLS) addresses the failure mode at its root. The database enforces tenant isolation regardless of what the application code does. A query missing a WHERE clause returns zero rows instead of all rows. A join across tenants returns nothing. A debug endpoint sees only what the current role is permitted to see. The application becomes a softer kind of dependency on doing the right thing, because the database stops trusting it to.

The trade-offs are real and the implementation has subtleties that have caught experienced teams. We have evaluated RLS for the multi-tenant portions of DocuMint, CronPing, FlagBit, and WebhookVault, and the analysis is the same for almost any SaaS at our scale.

What RLS actually does

An RLS policy is a SQL predicate attached to a table that is automatically applied to every query against that table. The policy can reference session-local settings, current_user, or arbitrary SQL expressions. When RLS is enabled on a table and a query is run by a non-bypass user, Postgres rewrites the query to include the policy predicate as an additional filter.

The mechanics are at the planner level. The policy becomes part of the query plan, gets joined with the user's WHERE clause via AND, and is optimized along with the rest of the query. The application sees the filtered results without modifying the query. Crucially, the policy applies to JOINs, subqueries, CTEs, and views — anywhere the table is touched, the policy fires.

The basic multi-tenant pattern

The standard setup for a multi-tenant application is to have a tenant_id column on every tenant-scoped table, an application-defined session variable that holds the current tenant's ID, and an RLS policy that scopes each table to the current tenant. The application sets the session variable at the start of every request, and every query against any tenant-scoped table is automatically filtered.

The session variable approach uses Postgres's SET command with a custom namespace. The application calls SET LOCAL app.current_tenant_id = 'tenant-abc' at the start of each transaction, and the RLS policy reads current_setting('app.current_tenant_id') to filter rows. The SET LOCAL scope means the setting is rolled back if the transaction is rolled back, which prevents leaking tenant context between sessions on a shared connection.

The policy itself is simple: USING (tenant_id = current_setting('app.current_tenant_id')::uuid). The policy applies to SELECT, UPDATE, DELETE, and INSERT operations. For INSERT, there is a separate WITH CHECK clause that validates new rows match the policy, preventing a tenant from inserting rows attributed to a different tenant.

The role-and-bypass question

RLS does not apply to superusers and does not apply to roles with the BYPASSRLS attribute. This is essential for migrations, backups, and operational tasks that genuinely need to see all rows. The discipline is to have a dedicated application role that does NOT have BYPASSRLS, used by the application's connection pool, and a separate admin role with BYPASSRLS used only for migrations and operational tasks.

The failure mode here is using a superuser role for the application connection. RLS does not fire for superusers, the policies become decorative, and the application can do anything the database can do. This is more common than it should be, because the development environment often uses a superuser for convenience and the production environment inherits the configuration. Every RLS deployment should include a check that the application role is not a superuser and does not have BYPASSRLS.

The connection-pool problem

RLS works on session-local settings. Connection pools share connections across requests. The combination is dangerous if the session variable is not reset between requests. A request for tenant A sets app.current_tenant_id to A, the connection returns to the pool, the next request for tenant B reuses the same connection without setting the variable, and the request sees tenant A's data.

The fix is mechanical: set the session variable at the start of every transaction with SET LOCAL, not SET. SET LOCAL is scoped to the current transaction and is automatically cleared at COMMIT or ROLLBACK. The pool returns the connection in a clean state, and the next request must set the variable before any query runs.

The discipline is to wrap every database access in a transaction even for single-statement reads, with the session variable set as the first statement of the transaction. The middleware that runs before request handlers should establish the tenant context, and the data access layer should refuse to issue queries without an active tenant context. The defensive check is cheap and the cost of getting it wrong is severe.

The performance question

The performance cost of RLS is real but bounded. The policy predicate becomes part of every query plan, which means the planner must consider it during planning. For simple predicates on indexed columns (tenant_id = current_setting(...)), the cost is essentially zero — the query plan already filters on tenant_id and the policy is redundant with the existing WHERE clause.

For more complex policies (joins to other tables, function calls, recursive checks), the cost can be substantial. The pattern is to keep policies as simple as possible: a single equality check against an indexed column is the gold standard. Complex authorization logic should run in the application, with the database policy enforcing only the gross structural rule.

The planner sometimes has trouble with policies that reference session settings. The standard workaround is to wrap the current_setting() call in a function marked STABLE, which lets the planner cache the value within a query and use it for index lookups. The function is small and the impact on plans is measurable.

The migration problem

Enabling RLS on existing tables is non-trivial. The policy applies immediately upon ENABLE ROW LEVEL SECURITY, which means existing queries that did not include tenant filtering will start returning zero rows. Production traffic will fail, customer-facing operations will break, and the deployment will be a fire.

The migration pattern is the standard expand-contract pattern adapted to RLS. Phase 1: enable RLS in audit-only mode by creating a permissive policy that always returns true but logs violations to a separate audit table. Run for one week and verify zero violations in the audit. Phase 2: replace the permissive policy with a restrictive one in dev and staging. Validate that all code paths still work. Phase 3: deploy the restrictive policy to production during a low-traffic window with rollback procedures ready. Phase 4: monitor for the next 24 hours and roll back if any customer-visible issue appears.

The audit-only phase is the load-bearing part. It catches code paths that you did not know existed, debug endpoints that nobody documented, background jobs that operate without tenant context, and operational scripts that bypass the normal request path. These are precisely the code paths that RLS will break when deployed restrictively, and the audit lets you fix them before they cause an incident.

What RLS does not protect against

RLS protects against bugs in application code that fail to filter by tenant. It does not protect against bugs in the policy itself, against compromised application credentials with BYPASSRLS, against SQL injection that bypasses parameterization and inserts SET commands, or against the application maliciously setting the wrong tenant ID. RLS is a defense-in-depth layer, not a replacement for application-layer authorization.

The deepest failure mode is forgetting which tenant is the current tenant. If the authentication layer resolves the wrong tenant from a session, RLS will faithfully filter to the wrong tenant's data. The data isolation is correct in a narrow sense (no data leakage across tenants) and disastrously wrong in a broader sense (the wrong tenant is being accessed). The application layer remains responsible for authentication.

The deeper observation

RLS moves the responsibility for tenant isolation from "every developer at all times" to "the database, always". The first model fails when a developer forgets, which is eventually. The second model fails when the policy is wrong, which is much less often. The trade-off is that the second model is harder to set up, harder to migrate to, and creates new failure modes (forgetting to set the session variable, using the wrong role, complex policies that confuse the planner) that did not exist before. For mature multi-tenant applications with real security stakes, the trade-off is usually worth it. For early-stage applications still figuring out the data model, the application-layer pattern is probably sufficient, and the migration to RLS is a deliberate choice for a later stage. The right time to enable RLS is when the application has stabilized enough that the tenant boundary is clear and the operational complexity of the migration is justified by the protection it provides.