Multi-Tenant Postgres RLS: Policies That Hold

If you build B2B software, you build a multi-tenant system whether you call it that or not. Many companies use the same application, and each one's data has to stay completely invisible to the others. That isolation is not a feature you add later. A single cross-tenant leak is the kind of incident that ends contracts, triggers breach notifications, and follows a company into every security questionnaire it fills out afterward. This article is the pattern we trust most for keeping tenants apart, Postgres Row-Level Security, written for the engineers who will implement it and the founders who need to know what they are paying for. It is the same model behind our own multi-tenant products, from Vexolis, a logistics platform where every carrier sees only its own fleet, to DibsHub, where every service provider owns a private calendar.

Why “just add WHERE tenant_id” eventually fails

The obvious design puts a tenant_id column on every table and appends WHERE tenant_id = ? to every query. It works, right up until someone forgets. A new report endpoint, a background job written at midnight, an ORM relation loaded through a path nobody reviewed, a raw SQL string in an admin tool. Each one is a query that has to be perfect forever, and the failure mode is silent: nothing crashes, Company A simply sees Company B's invoices.

The deeper problem is where the guarantee lives. When isolation depends on application code, the guarantee is distributed across thousands of lines, dozens of contributors, and every future refactor. Security properties that are distributed decay. The fix is to move the guarantee to the one place every query has to pass through: the database engine itself.

What Row-Level Security actually does

Postgres Row-Level Security (RLS) lets you attach a policy to a table. Once the table has RLS enabled, every statement against it, selects, updates, deletes, inserts, is filtered by that policy before any rows are returned or touched. The application no longer has to remember the filter; the engine applies it to the query plan itself. A forgotten WHERE clause stops being a security hole and becomes just a slightly less efficient query.

The policy needs to know which tenant is asking. The conventional approach is a session variable that your application sets at the start of each request and the policy reads through current_setting(). Here is the complete shape for one table:

-- 1. Every tenant-owned table carries the tenant id, non-null.
ALTER TABLE invoices
  ADD COLUMN tenant_id uuid NOT NULL REFERENCES tenants(id);

-- 2. Turn RLS on, and FORCE it so even the table owner is filtered.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

-- 3. One policy that covers reads and writes for the current tenant.
--    USING filters rows that already exist; WITH CHECK validates rows
--    being inserted or updated, so a tenant cannot write into another.
CREATE POLICY tenant_isolation ON invoices
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- 4. Optional but useful: default the column so inserts cannot forget it.
ALTER TABLE invoices
  ALTER COLUMN tenant_id
  SET DEFAULT current_setting('app.tenant_id', true)::uuid;

Three details in that snippet matter more than they look. FORCE ROW LEVEL SECURITY closes the gap where the table's owner role bypasses policies by default; if your application connects as the role that ran the migrations, you need this line or RLS silently does nothing. The second argument to current_setting, the true, tells Postgres to return null instead of raising an error when the variable is unset. That sounds like a convenience, and it is, but it also means an unset tenant produces tenant_id = NULL, which matches nothing, so a request that forgot to set its tenant sees an empty table rather than everyone's data. And WITH CHECK is the half most tutorials skip: without it, a tenant can read only its own rows but can still insert a row stamped with somebody else's id.

The goal is to make a cross-tenant leak impossible by construction, not merely unlikely if every developer stays careful for the life of the product.

Separate policies when reads and writes differ

A single FOR ALL policy is fine for most tables, but real products often want asymmetric rules. Reference data shared by every tenant should be readable by all and writable by none. Audit logs should be append-only. Support staff may need read access across tenants, under a different role, with every access recorded. Postgres lets you stack policies by command:

-- Shared catalogue: everyone reads, nobody writes through the app role.
ALTER TABLE plan_catalog ENABLE ROW LEVEL SECURITY;
CREATE POLICY read_all ON plan_catalog FOR SELECT USING (true);

-- Append-only audit trail scoped to the tenant.
ALTER TABLE audit_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_events FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_read   ON audit_events FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);
CREATE POLICY tenant_append ON audit_events FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);
-- No UPDATE or DELETE policy exists, so those statements affect zero rows.

-- Support role: cross-tenant reads only, and only when a ticket id is set,
-- which your application logs alongside every query it runs.
CREATE POLICY support_read ON invoices FOR SELECT TO support_role
  USING (current_setting('app.support_ticket', true) IS NOT NULL);

Policies on the same table combine with OR for permissive policies, so the support role's cross-tenant read does not weaken the tenant policy for ordinary application connections; it only applies to sessions running as support_role. Keep the number of roles small and the rules readable. A policy nobody on the team can explain in one sentence is a policy that will be “temporarily” disabled during an incident and never re-enabled.

Propagating the tenant id from request to policy

RLS is exactly as trustworthy as the value you set for app.tenant_id. That value must come from the verified session on the server, never from a header, a query parameter, or a body field the client controls. The flow is: authenticate the request, resolve the user's tenant from your own records, open a transaction, set the variable inside that transaction, run the request's queries, commit.

The keyword is SET LOCAL (or set_config(name, value, true), which is the same thing in function form). A plain SET persists for the life of the connection; SET LOCAL lasts only until the transaction ends. With a connection pool, that difference is the whole ballgame, because the next request to borrow the same connection must not inherit the previous tenant. Here is the shape with Prisma, which is what most of our Next.js products use; the same idea maps directly onto Drizzle, Kysely, or a raw pg client:

// lib/tenant-db.js
export async function withTenant(tenantId, fn) {
  return prisma.$transaction(async (tx) => {
    // set_config(..., true) scopes the value to this transaction only.
    await tx.$executeRaw`SELECT set_config('app.tenant_id', ${tenantId}, true)`;
    return fn(tx);
  });
}

// pages/api/invoices.js
export default async function handler(req, res) {
  const session = await requireSession(req);          // verified, server-side
  const rows = await withTenant(session.tenantId, (tx) =>
    tx.invoice.findMany({ orderBy: { issuedAt: "desc" } })  // no WHERE tenant_id needed
  );
  res.json(rows);
}

Two rules keep this honest. First, every database access goes throughwithTenant, including background jobs, webhooks, and cron tasks; a job that queries without a tenant set gets empty results, which is loud enough to notice in testing and safe enough to never leak in production. Second, system-level work uses a different rolethat is deliberately not subject to the tenant policy, and that role's credentials never appear in request-handling code. Migrations, billing reconciliation across all tenants, and support tooling run there, with their own audit trail.

Connection pooling: the caveats that bite in production

Most production deployments put a pooler between the application and Postgres, either PgBouncer or a managed equivalent. Poolers run in one of three modes, and only one of them is safe with the session-variable pattern unless you are careful.

Transaction mode is the common choice for high-concurrency apps and it is fine, provided the tenant setting and the queries that depend on it live inside the same explicit transaction. ORMs that open implicit transactions per query can break this in subtle ways: the SET runs in one implicit transaction, ends, and the next query runs on whatever connection the pooler hands out. The withTenant wrapper above avoids the trap precisely because it uses one interactive transaction for the whole unit of work. Some serverless Postgres providers add their own pooling layers; read their documentation for how session state is handled, and verify with the test described below rather than trusting the marketing page.

Performance: indexes and policy shape

RLS adds a predicate to every query, which means the planner treats it like any other WHERE clause. The practical consequences are ordinary and easy to get right. Put tenant_id as the leading column in composite indexes on tenant-owned tables: (tenant_id, issued_at), (tenant_id, status), and so on, so the predicate narrows the scan before anything else runs. Keep the policy expression cheap and deterministic; current_setting() is inexpensive, but a policy that calls a subquery or a volatile function for every row will show up in your slow-query log. If you wrap the setting in a helper function for readability, declare it STABLE so the planner can evaluate it once per query rather than once per row.

On very large tables, the tenant column also becomes a natural partition key. Partitioning by tenant hash is an optimization you reach for at real scale, not a starting point, but it is reassuring that the RLS design and the eventual partitioning strategy point in the same direction.

Testing isolation, not just testing features

A tenant-isolation bug is invisible in ordinary tests because ordinary tests use one tenant. You need tests whose entire purpose is to try to cross the line. The suite we run on every multi-tenant product has four parts, and it is cheap to write once the withTenant wrapper exists.

• Two-tenant fixture. Seed tenant A and tenant B with distinguishable rows in every tenant-owned table. Every isolation test runs against both.
• Read boundary. As tenant A, list every table and assert you never see a row belonging to B. Do this generically, iterating over the schema, so a table added next quarter is covered automatically.
• Write boundary. As tenant A, attempt to insert a row with B's id and to update or delete one of B's rows by primary key. Expect the insert to be rejected by WITH CHECK and the update or delete to affect zero rows.
• No-tenant boundary. Run the same reads without setting a tenant and assert every result is empty. This test is what catches the forgotten wrapper on a new endpoint.

Add one more check at the schema level: a test that queries pg_tables and pg_policies and fails if any table with a tenant_id column lacks RLS or lacks FORCE. That single assertion has caught more real mistakes for us than any amount of code review, because it fails the moment someone adds a table and forgets the ceremony. Run these tests against a real Postgres in CI, not an in-memory substitute; RLS behavior is exactly the sort of thing a lightweight test double does not emulate.

Where RLS stops and other layers begin

RLS is the floor, not the whole building. A few boundaries it does not cover deserve their own design. Object storage: files in S3 or similar need tenant-prefixed keys and short-lived signed URLs generated only after the same server-side tenant check. Search indexes and caches: an Elasticsearch index or a Redis cache does not know about your policies, so tenant id has to be part of every key and every filter there, and it is worth applying the same “wrapper or nothing” discipline. Background analytics: a warehouse that receives replicated tables receives them without RLS, which is fine as long as the warehouse is only reachable by roles that are supposed to see everything. Naming these explicitly during design keeps the “RLS protects us” claim honest.

Shared tables vs. schema per tenant vs. database per tenant

The models are not mutually exclusive. A common mature shape is shared tables with RLS for the long tail of customers and a dedicated database for the one enterprise account whose procurement team wrote physical isolation into the contract. Design the application so that the tenant resolution step can return a connection target as well as a tenant id, and that hybrid becomes a configuration change rather than a rewrite.

A rollout checklist

• Add tenant_id NOT NULL with a foreign key to every tenant-owned table; backfill before adding the constraint on an existing product.
• Enable and force RLS, write USING and WITH CHECK, and default the column from the setting.
• Create a dedicated application role that owns nothing and connects with least privilege; keep the migration role separate.
• Wrap all tenant-scoped data access in one transaction-scoped helper; grep for database calls outside it in code review.
• Confirm your pooler mode and that the helper uses a single explicit transaction.
• Lead every composite index with tenant_id.
• Ship the four isolation tests plus the schema assertion, running against real Postgres in CI.
• Document the non-RLS boundaries (storage, search, cache, warehouse) and their tenant rules.

The takeaway

Tenant isolation belongs in the database, enforced by the engine, expressed once per table and tested by trying to break it. Postgres RLS gives you that with a modest amount of SQL and one disciplined wrapper in the application. Build it in from the first migration; retrofitting isolation onto a live product is far harder, because you have to find every query that was written assuming the application would filter. If you are architecting a B2B platform and want it done right from day one, see how we approach SaaS development, read the companion piece on building SaaS on modern Next.js, or tell us about the product.