Implementing RLS in PostgreSQL for SaaS

Row-Level Security pushes tenant isolation down into the PostgreSQL planner so every SELECT, INSERT, UPDATE, and DELETE is filtered by tenant_id before a single row leaves the engine. This guide is a focused part of Shared Database with Row-Level Security: how to attach policies, inject tenant context without leaking it across pooled connections, and keep query plans index-aligned under concurrent load.

Problem Framing

In a shared database, every tenant's rows live in the same physical tables. The only thing standing between tenant A and tenant B's data is a predicate. When that predicate lives in application code — a hand-written WHERE tenant_id = $1 in every query — a single missing clause, a forgotten join condition, or an ORM scope that silently drops becomes a cross-tenant data breach. There is no second line of defense.

RLS moves the predicate into the database. You attach a policy to the table once, set a session variable per request, and PostgreSQL rewrites every query to append the tenant filter automatically. A query that forgets the filter still returns only the current tenant's rows, because the planner injects the policy USING expression into the query tree before execution. The application no longer carries the burden of correctness for isolation.

This matters most as the codebase and the team grow. Application-only filtering is a property you must re-prove on every new query, every ORM upgrade, every junior engineer's first pull request, and every ad-hoc analytics script that touches the database directly. It degrades silently: nothing fails when a filter is missing, it just over-returns. RLS turns isolation from a per-query discipline into a per-table invariant. The cost is a small amount of one-time DDL and a disciplined way of setting one session variable; the payoff is that the failure mode of a forgotten filter changes from "data breach" to "no behavioral difference at all."

The diagram below shows where the policy is enforced relative to a query that omits any tenant filter of its own.

The decision that matters is where isolation is guaranteed. With application-only filtering, every code path is a potential leak. With RLS, the boundary is a single, auditable policy object that even buggy queries cannot escape.

Step-by-Step Guide

1. Add and index the tenant column

Every isolated table needs a non-null tenant_id. Index it as the leading column of a composite index so the policy predicate and your common sort order share one B-tree.

ALTER TABLE orders ADD COLUMN tenant_id uuid NOT NULL;
CREATE INDEX idx_orders_tenant_created ON orders (tenant_id, created_at DESC);

The leading tenant_id lets the planner satisfy the policy filter with an index scan instead of a sequential scan plus filter. Order the index columns to match how the table is queried: tenant_id first because every policy-filtered query constrains it to a single value, then the column you most often sort or range-scan on. A backfill on an existing table should set tenant_id before adding the NOT NULL constraint, and you should add the column with a default only temporarily to avoid a full table rewrite under lock.

2. Create a dedicated, unprivileged application role

RLS does not apply to superusers or table owners by default. The application must connect as a role that is neither.

CREATE ROLE app_role LOGIN PASSWORD 'rotate-me';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

3. Enable and force RLS on the table

ENABLE turns policies on for non-owners. FORCE extends enforcement to the table owner too, closing the most common bypass.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

4. Attach the isolation policy

USING governs which existing rows are visible to SELECT, UPDATE, and DELETE. WITH CHECK validates rows produced by INSERT and UPDATE, so a tenant cannot write a row stamped with someone else's id. When the two expressions are identical, an UPDATE is fully bracketed: the row must be visible to be touched, and the resulting row must still belong to the same tenant. A single CREATE POLICY without a command qualifier applies to all four statement types; you can split it into per-command policies if reads and writes need different predicates, but for tenant isolation a single symmetric policy is the simplest correct choice.

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

5. Inject tenant context per request, scoped to the transaction

Use SET LOCAL inside an explicit transaction. SET LOCAL is reverted at COMMIT/ROLLBACK, so the value never survives on a connection returned to the pool. Passing true as the third argument to set_config makes it transaction-local from the driver.

import { Pool } from "pg";

const pool = new Pool();

export async function withTenant<T>(
  tenantId: string,
  run: (db: import("pg").PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    // transaction-local: cleared automatically at COMMIT/ROLLBACK
    await client.query("SELECT set_config('app.current_tenant_id', $1, true)", [tenantId]);
    const result = await run(client);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

6. Make pooling safe against leftover state

In PgBouncer transaction pooling, a connection is handed to another client between transactions, so any state that survives a commit is a cross-tenant hazard. SET LOCAL already resets at commit, which is exactly why it is the only safe way to set tenant context behind a transaction pooler — but add a defensive server_reset_query so any session-level state, including accidental plain SET calls, is cleared on release.

[pgbouncer]
pool_mode = transaction
server_reset_query = DISCARD ALL

7. Reject requests that arrive without tenant context

A missing app.current_tenant_id makes current_setting() raise, which is the safe default — the query fails closed rather than returning everything. This is deliberate: fail-open behavior, where an unset variable resolves to an empty string or null and the policy matches nothing or everything, is the single most dangerous misconfiguration in an RLS deployment. Keep the two-argument form of current_setting (without a missing_ok default) so the error surfaces, then catch it at the edge and translate it to a clear error instead of a 500.

try {
  await withTenant(req.tenantId, (db) =>
    db.query("SELECT * FROM orders WHERE status = 'active'"),
  );
} catch (e) {
  if (String(e).includes("unrecognized configuration parameter")) {
    return res.status(401).json({ error: "tenant context missing" });
  }
  throw e;
}

Verification

Confirm two things: the policy actually blocks cross-tenant reads, and the planner uses the index rather than scanning. First, prove isolation by switching context and asserting visibility changes.

SET ROLE app_role;

BEGIN;
SELECT set_config('app.current_tenant_id', '11111111-1111-1111-1111-111111111111', true);
SELECT count(*) FROM orders;  -- rows for tenant 1 only
COMMIT;

BEGIN;
SELECT set_config('app.current_tenant_id', '22222222-2222-2222-2222-222222222222', true);
SELECT count(*) FROM orders;  -- rows for tenant 2 only, never tenant 1
COMMIT;

Then confirm the plan. You want an Index Scan (or Index Only Scan) keyed on the composite index, with the policy predicate folded into the index condition.

BEGIN;
SELECT set_config('app.current_tenant_id', '11111111-1111-1111-1111-111111111111', true);
EXPLAIN (ANALYZE, BUFFERS)
  SELECT * FROM orders WHERE status = 'active';
COMMIT;

Expected output contains a line resembling Index Scan using idx_orders_tenant_created on orders. A Seq Scan here means the index is missing or tenant_id is not its leading column. Note that EXPLAIN ANALYZE runs inside the same tenant-scoped transaction, so the plan you see is the one the policy actually produces — running it outside the set_config block would either fail closed or report a plan that does not reflect production. Watch the BUFFERS line too: a large shared read count relative to rows returned signals the index is being used for the filter but not the ordering, which usually means the sort column should join the composite index. For a fuller assertion-based approach — automated suites that attempt cross-tenant access and expect zero rows — see testing RLS policies for tenant isolation.

Failure Modes & Gotchas

FAQ

Do RLS policies apply to table owners and superusers? Not by default — both bypass RLS. Run ALTER TABLE <t> FORCE ROW LEVEL SECURITY to cover the owner, and have the application connect as a dedicated LOGIN role that is neither owner nor superuser.

How do I run a legitimate cross-tenant admin query? Use a separate, audited connection and set SET LOCAL row_security = off inside an explicit transaction, or grant the admin role BYPASSRLS. Keep this path off the normal application pool so it can be logged and reviewed independently.

What is the performance cost of RLS? With tenant_id as the leading index column, overhead is typically a few percent because the policy predicate is satisfied by the same index scan the query would use anyway. Costs only spike when the column is unindexed or the policy expression calls non-immutable functions per row.