Enforcing Tenant Isolation With Row-Level Security

Application-level tenant filtering works until one query forgets, and the query that forgets is never the one you reviewed. Row-level security moves the predicate into the database, where a missing WHERE clause returns nothing instead of returning another customer’s data. This page is part of the multi-tenant authorization and data isolation guide.

The Policy, and the Two Clauses That Matter

A policy has two halves, and using only the first is the most common way a policy ends up decorative.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE  ROW LEVEL SECURITY;   -- applies to the table owner as well

CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = current_setting('app.tenant_id', true)::uuid)   -- governs reads
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);  -- governs writes

USING filters what a query can see. WITH CHECK constrains what it can write. With only USING, a user reads exclusively their own rows and can still insert or update rows carrying another tenant’s identifier — which is a data-poisoning path, and one that surfaces later as mysteriously misfiled records rather than as an obvious breach.

FORCE matters just as much. By default a policy does not apply to the table’s owner, and most applications connect as the role that owns their tables, so a policy without FORCE silently does nothing in exactly the environment you care about.

What each part of the policy controls USING filters reads, WITH CHECK constrains writes, and FORCE makes the policy apply to the table owner, which is the role most applications connect as. USING filters what is visible reads only WITH CHECK constrains what is written inserts and updates FORCE applies to the owner too without it: no effect
Three keywords, three distinct failures if omitted — and the third one fails completely silently.

Setting the Tenant Inside the Transaction

The policy reads a setting, and where that setting lives decides whether the whole scheme is safe under concurrency.

export async function withTenant<T>(tenantId: string, fn: (tx: Tx) => Promise<T>) {
  return db.transaction(async (tx) => {
    // SET LOCAL is scoped to this transaction and reset when it ends.
    await tx.raw("SET LOCAL app.tenant_id = ?", [tenantId]);
    return fn(tx);
  });
}

SET LOCAL is scoped to the transaction; plain SET persists on the connection. Connection pools hand the same physical connection to unrelated requests, so a connection-level setting leaks one tenant’s context into the next request that borrows it. The resulting bug appears only under concurrency, produces correct results in every test, and is exactly the failure this whole mechanism exists to prevent.

Pass the tenant from the verified session, never from a header or a body field, and make the tenant-scoped transaction helper the only way to reach the database. If some code path can obtain a raw connection, that path will eventually run a query with no tenant set — which, with current_setting(..., true), returns null and matches nothing, so it fails closed rather than leaking. Confirm that behaviour deliberately rather than assuming it.

Testing It Under a Pool

Three tests that prove the policy works A two-tenant read test proves filtering, a cross-tenant write test proves the check clause, and a concurrent pooled test proves the setting does not leak between requests. Read isolation two tenants, same table each sees only its rows Write isolation insert a foreign tenant id must be rejected Pool safety concurrent, one connection no context bleed
The third test is the one that must run with real concurrency and a pool of size one; without those two conditions it passes trivially and proves nothing.

Add a scheduled invariant check on top of the tests: for each child table, count rows whose tenant identifier differs from their parent’s. A non-zero result means something wrote across the boundary, and discovering that from a query beats discovering it from a customer.

Performance and Indexing

A policy adds a predicate to every query, so the tenant column needs to be in your indexes — usually as the leading column of composite indexes, because almost every query is scoped by it. A table with an index on created_at alone will scan far more rows than necessary once every query also filters on tenant.

Watch query plans after enabling policies rather than assuming they are unchanged. The planner generally handles the added predicate well, but a policy that calls a function rather than comparing a column can prevent index use entirely, turning a fast lookup into a sequential scan on a large table.

Keep the policy expression simple — a column compared to a setting — and put any complexity in the application. A policy containing a subquery runs that subquery for every row considered, which is the kind of change that looks harmless in review and shows up as a production incident under load.

Rolling It Out on an Existing Database

Enabling policies on a live system is a change that can break every query at once, so stage it in three steps rather than one migration.

First, add the tenant column everywhere it is missing and backfill it, verifying that no row is left null. A policy over a column with nulls silently hides those rows, which looks like data loss to whoever owned them. Backfilling is usually the longest part of the work and it is entirely reversible, so do it well before anything else changes.

Second, enable the policy on one low-traffic table with USING only, and watch. This is where you discover which code paths reach the database without setting a tenant — they will start returning empty results rather than errors, so instrument the “no tenant set” case explicitly and log it loudly during the rollout. Every hit on that log is a code path to fix before you proceed.

Third, add WITH CHECK and FORCE, and expand table by table. Adding the write constraint last means you have already fixed the read paths, so failures are limited to writes that were genuinely mislabelled — which is a much shorter list and a much clearer signal.

Staged rollout of row-level security Backfill the tenant column first, enable read filtering on one table and fix the code paths it exposes, then add the write constraint and the force option before expanding to the rest of the schema. 1 · backfill no null tenants 2 · USING only one table, watch logs 3 · CHECK + FORCE then expand 4 · assert every table covered
The middle step is where the real work is: it converts "we think every query is scoped" into a list of the ones that are not.

Keeping New Tables Covered

The protection decays through growth rather than through change: someone adds a table, forgets the policy, and nothing complains. Close that with a test rather than a convention.

Query the catalogue for tables carrying a tenant column, compare against the tables with an enabled and forced policy, and fail the build on any difference. It is a few lines of SQL, it runs in seconds, and it is the only thing that reliably catches the table added in a hurry on a Friday.

Extend the same test to views and materialised views, which do not inherit policies from their underlying tables in the way people expect and are a recurring source of surprise. A reporting view built over three tenant-scoped tables is not itself tenant-scoped unless you say so, and the person who wrote it almost certainly assumed otherwise.

Pair it with a migration helper that creates the table, the column, the index and the policy together, so the easy path is also the correct one. When the correct path requires remembering four separate steps, it will eventually be done in three.

Frequently Asked Questions

Does row-level security replace application-level filtering?

No — keep both. The application filter makes queries efficient and makes intent visible in the code; the policy makes a forgotten filter harmless rather than catastrophic. Belt and braces is the right posture for the one boundary whose failure is a breach notification rather than a bug report.

What happens if the tenant setting is missing?

With current_setting('app.tenant_id', true) the value is null, the comparison is not true, and the query matches nothing — a fail-closed outcome. Without the second argument, the call raises an error instead, which is also acceptable and arguably clearer. What you must avoid is a policy that treats a missing setting as permission to see everything, which is what happens if you write the comparison defensively with a null fallback.

Do superusers bypass the policy?

Yes, and roles with the BYPASSRLS attribute do too. That is fine for administrative access and dangerous for application connections, so make sure the role your application uses has neither. Audit it explicitly: a migration that grants an application role broader privileges for convenience can silently disable every policy in the database.

How does this work with background jobs?

The same way, but the tenant has to come from the job payload rather than from a session. Carry it explicitly in the message, set it inside the job’s transaction, and treat a job without a tenant as a bug rather than as a job that may see everything. Workers are the most common place where isolation quietly stops applying, because there is no request to inherit context from.

Can I use one policy for every table?

You write one per table, but they can be identical, and generating them from a migration helper keeps them consistent. What matters is that every table containing tenant data has one — so add a test that lists tables with a tenant column and asserts each has an enabled, forced policy. New tables are exactly where this protection gets forgotten.

The result is a boundary that holds even when a query is wrong, which is the only kind of boundary worth relying on in a shared database.