PostgreSQL Row-Level Security: tenant isolation that survives application bugs

A junior developer copies a handler from an internal admin tool into a customer-facing API. The query still joins on tenant_id, but one branch forgets the predicate. For a week, some requests return another tenant’s invoices. Automated tests passed because fixtures only had one tenant. Code review caught style, not data scope. In freelance and consulting work, defense in depth is not sloganeering: when the same datastore serves many customers, the database should refuse cross-tenant reads and writes even when application code is wrong.

PostgreSQL Row-Level Security (RLS) moves part of that guarantee into the server. Every row access is filtered by policies evaluated for the current database role and session context. This article explains how RLS fits multi-tenant SaaS, how to wire tenant_id into policies without sprinkling it in every query string, and where the approach costs performance or surprises operators.

What RLS does (and what it does not)

RLS attaches predicates to tables. When RLS is enabled, ordinary SELECT, INSERT, UPDATE, and DELETE only see or touch rows that satisfy the policies for the current user and session settings. Policies are defined with SQL and stored in the catalog; they apply regardless of whether the client used an ORM, raw SQL, or a reporting tool.

What it does: reduces the blast radius of missing WHERE tenant_id = $1 clauses, mis-merged branches, and some classes of SQL injection that append predicates but not tenant scope.

What it does not do: replace authentication, replace network security, or fix logic bugs inside a tenant (e.g. user A seeing user B’s documents within the same tenant). It also does not help if the app connects as a superuser or a role that bypasses RLS (BYPASSRLS). Production apps should use a non-bypass role and treat bypass as break-glass only.

Session context: binding the tenant to the connection

Policies need a stable way to know “which tenant is this session?” Common patterns:

Application-set session variables

After authenticating the request, the application sets a per-request tenant identifier before running business SQL:

SELECT set_config('app.tenant_id', '0193a7b2-...', true);

The third argument true means local to the current transaction (if you are inside one) or the current session—pick one strategy and document it. Many teams set the variable once per request inside a middleware or repository wrapper, then run all queries in that scope.

Policies reference it with current_setting:

current_setting('app.tenant_id', true)

The true means “return NULL if unset” instead of erroring; you can combine with explicit checks.

JWT claims (PostgreSQL 14+)

current_setting('request.jwt.claims', true)::json can carry claims from a verified token when using appropriate extensions or proxies. This shifts trust to how the JWT reaches Postgres (often via PgBouncer or application-layer signing). It is powerful but operationally heavier; session variables set by a trusted app tier remain the most portable pattern.

Enabling RLS and defining policies

Enable RLS on the table and revoke broad privileges so clients cannot opt out accidentally:

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

FORCE ROW LEVEL SECURITY applies RLS even to the table owner, which prevents the owner role from accidentally bypassing policies during migrations if you run them as the same role—tune this carefully with migration users.

A minimal tenant-scoped read policy:

CREATE POLICY invoice_select_tenant ON invoice
  FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

Write policies typically mirror the same predicate for USING (which rows are visible) and WITH CHECK (which rows may be inserted or updated):

CREATE POLICY invoice_modify_tenant ON invoice
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

FOR ALL covers SELECT, INSERT, UPDATE, DELETE unless you split policies per command for finer control.

Separate policies for admin or background jobs

Support or analytics often need cross-tenant access. Options:

• A different database role (app_admin) with policies that allow broader USING clauses, used only from controlled hosts.
• SET ROLE after strong authentication for break-glass scripts.
• Avoid granting BYPASSRLS to the application role; reserve it for migrations owned by CI with audited scripts.

Performance: indexes and planner interaction

RLS predicates are folded into the query plan. That helps when indexes align with the policy:

• If almost every query is scoped by tenant_id, a composite index starting with tenant_id (e.g. (tenant_id, created_at DESC)) keeps tenant-scoped lists fast.
• Overly broad policies that call volatile or expensive functions per row can hurt; keep policy expressions simple and sargable where possible.

EXPLAIN (ANALYZE, BUFFERS) with realistic set_config values is mandatory before declaring a rollout done. In engagements focused on scalable, production-ready data layers, RLS is not free: it is insurance with a measurable CPU and planning cost.

Trade-offs and limitations

Connection pooling. Session variables are per connection. With PgBouncer in transaction pooling mode, set_config in one “logical” request may leak to another unless you set the variable inside each transaction or use statement-level pooling patterns that reset state. Session pooling or transaction-scoped set_config(..., true) inside BEGIN/COMMIT pairs are the usual fixes.

Migrations and bulk jobs. Schema changes and backfills often run as a role that must see all rows. Use dedicated migration users, temporarily DISABLE ROW LEVEL SECURITY in controlled windows, or use SECURITY DEFINER functions very carefully with fixed search paths and explicit qualification.

ORM abstraction. Developers who rely on “load by primary key” may assume a row exists; with RLS, the same id returns no row if the tenant variable is wrong—often correct, but confusing if errors are not handled as 404.

Foreign keys and RLS. Child tables need consistent policies; a parent row invisible under RLS can still block inserts if FK checks run as a role that sees parent rows differently. Test referential integrity under the application role.

Practical example: invoices with RLS and a repository boundary

The following illustrates end-to-end intent: schema, policies, and application code that sets app.tenant_id before queries. Adapt types and pooling to your stack.

Schema (simplified):

CREATE TABLE tenant (
  id uuid PRIMARY KEY
);

CREATE TABLE invoice (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL REFERENCES tenant (id),
  amount_cents integer NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX invoice_tenant_created ON invoice (tenant_id, created_at DESC);

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

CREATE POLICY invoice_isolation ON invoice
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

Request-scoped execution (TypeScript-style sketch):

import pg from "pg";

const pool = new pg.Pool({ /* connectionString, max, etc. */ });

export async function withTenant<T>(
  tenantId: string,
  fn: (client: pg.PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (e) {
    await client.query("ROLLBACK");
    throw e;
  } finally {
    client.release();
  }
}

export async function listInvoices(client: pg.PoolClient) {
  const { rows } = await client.query(
    `SELECT id, amount_cents, created_at FROM invoice ORDER BY created_at DESC`
  );
  return rows;
}

Here set_config uses local scope (true) tied to the transaction opened in withTenant, which pairs well with transaction pooling: the variable does not survive past COMMIT. Even if listInvoices omits WHERE tenant_id, RLS restricts rows. In production you would add observability, timeouts, and explicit error mapping.

Common mistakes and pitfalls

Forgetting to set app.tenant_id. If the setting is NULL and policies cast to uuid, behavior may error or match nothing—know which you intend.

Superuser / owner bypass. Connecting as postgres or a role with BYPASSRLS during “quick fixes” undermines the model; restrict CI and humans to least privilege.

Policies that disagree with the app. If the app still adds AND tenant_id = $1 but the session variable is wrong, you get empty results and hard-to-debug support tickets. Single source of truth: either trust RLS and simplify queries, or document a hybrid clearly.

Missing indexes. RLS does not remove the need for tenant-aligned indexes; it adds predicates that must be cheap.

Extensions and views. SECURITY DEFINER views and some extensions run with elevated rights—audit them; they can expose rows policy would normally hide.

Conclusion

PostgreSQL Row-Level Security turns tenant isolation from a convention enforced only in application strings into a server-enforced rule: every row access is re-checked against policies tied to session context. That extra layer has helped in real projects where velocity and copy-paste risk coexist with strict data boundaries.

Key takeaways:

• Use session variables (or an equivalent claim path) set in a narrow, documented scope—often per transaction for pooling safety
• Align indexes with policy predicates and validate plans under load
• Use separate roles for app traffic vs migrations vs admin, instead of widening policies ad hoc

For teams building scalable, production-ready multi-tenant systems, RLS is a sharp tool: it does not replace careful API design, but it narrows the damage when design and implementation drift. If you want to discuss architecture or collaboration, the contact page is the right place to reach out.