Row-Level Security in Postgres for Multi-Tenant SaaS: Policies, Performance, and Migration Patterns

Multi-tenant SaaS applications have exactly one critical invariant: tenant A must never see tenant B’s data. The standard way to enforce this is in application code: every query carries a WHERE tenant_id = $1 clause, every ORM scope applies a filter, and every developer on the team knows to not forget it. The problem is that “every developer knows to not forget it” is not a guarantee. It is a convention, and conventions break under deadline pressure, new hires, and code that runs in contexts the original author did not anticipate.

Postgres Row-Level Security (RLS) moves that guarantee into the database itself. Policies defined at the table level control which rows each connection can see or modify, regardless of what SQL the application sends. A query that forgets the tenant filter still cannot return the wrong tenant’s data, because the database enforces it before the rows are returned.

This article covers how to set up RLS for a multi-tenant SaaS application: the policy syntax, how to pass tenant context through session variables, the performance implications and how to handle them, how to test policies correctly, and how to migrate an existing application that relies on application-level filtering.

The Two Isolation Models

Before writing a policy, choose your isolation model. There are two main options:

Shared schema with RLS puts all tenants in the same tables and uses RLS to enforce row-level visibility. Lower operational overhead. Simpler migrations. One schema to maintain. The tradeoff is that a misconfigured policy or a superuser connection exposes cross-tenant data.

Schema-per-tenant gives each tenant its own Postgres schema (or database). Complete isolation at the schema level, no policies needed for tenant boundaries. The tradeoff is operational complexity: n schemas to migrate, monitor, and manage connection pools for.

Most early-stage multi-tenant SaaS applications start with shared schema and RLS. It is operationally simpler, scales to thousands of tenants without provisioning overhead, and RLS provides strong-enough guarantees when implemented correctly. Schema-per-tenant becomes worth the complexity when tenants have significantly different data volumes, need per-tenant database-level controls, or operate in regulatory environments that require physical isolation.

This article focuses on shared schema with RLS.

Enabling RLS and Writing Your First Policy

RLS must be explicitly enabled per table. Enabling it with no policies means all rows are blocked for non-owners of the table.

-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Without a policy, only the table owner can access rows.
-- Create a permissive policy for SELECT.
CREATE POLICY tenant_isolation_orders_select
  ON orders
  FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

The USING clause is the row-level filter for read operations (SELECT, UPDATE, DELETE). The WITH CHECK clause controls which rows an INSERT or UPDATE can write. A policy that only defines USING applies that filter to reads. For write operations you need WITH CHECK explicitly.

-- Full CRUD policy: reads filtered by tenant, writes enforced too
CREATE POLICY tenant_isolation_orders
  ON orders
  AS PERMISSIVE
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

The AS PERMISSIVE default means policies are OR’d together. If multiple permissive policies exist and any of them passes, the row is visible. AS RESTRICTIVE policies are AND’d and must all pass. For tenant isolation, use restrictive if you need layered access control (tenant isolation AND role-based row access), permissive if tenant ID alone is enough.

Setting Tenant Context with Session Variables

The current_setting('app.tenant_id') call reads a session-level variable. You need to set this at the start of every connection or transaction from your application layer.

import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function withTenantContext<T>(
  tenantId: string,
  fn: (client: import("pg").PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    // Set the tenant context for this connection before any queries run.
    // Use a transaction to ensure atomicity and avoid context leaking.
    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 (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

The third argument to set_config is is_local. When true, the setting is scoped to the current transaction and resets when the transaction ends. This is the safe default: the setting cannot leak to the next query that reuses the connection from the pool. When false, it persists for the life of the connection session, which is dangerous with connection pooling.

If you use PgBouncer or a similar pooler in transaction pooling mode, transaction-scoped settings are correct and necessary. In session pooling mode, either approach works, but transaction-scoped is still safer because it avoids relying on connection lifecycle assumptions.

One failure mode to avoid: setting the tenant ID as an application-level parameter passed to the policy via a function that reads from the connection options. This can work but is more fragile than set_config. The current_setting approach is the idiomatic Postgres pattern.

Performance: The Index Problem

RLS policies add a predicate to every query on the table. The question is whether Postgres can use an index for that predicate.

For the policy tenant_id = current_setting('app.tenant_id')::uuid, Postgres needs a way to quickly find rows by tenant_id. Without an index on tenant_id, every query on a large table triggers a sequential scan filtered by the policy, which defeats the purpose of having any other index.

-- The index that makes RLS practical
CREATE INDEX idx_orders_tenant_id ON orders (tenant_id);

-- Better: composite index that covers common query patterns
-- Most queries on a multi-tenant table filter by tenant then by something else.
CREATE INDEX idx_orders_tenant_created ON orders (tenant_id, created_at DESC);
CREATE INDEX idx_orders_tenant_status ON orders (tenant_id, status);

With composite indexes, a query like SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC will use the (tenant_id, status) or (tenant_id, created_at) index because the RLS policy adds the tenant_id = ? predicate. Postgres sees the full predicate set and picks accordingly.

Run EXPLAIN ANALYZE on your most common queries after enabling RLS to confirm index usage. Look for Index Scan or Index Only Scan on the tenant-aware indexes. A Seq Scan with a large row count estimate is a signal that you need a better index.

One subtlety: Postgres cannot always inline current_setting() into an index scan as a constant at plan time, because it is treated as a stable function (not immutable). This means some complex queries may not use the index as efficiently as a literal value would. If you observe this, wrapping the setting in a deterministic function can sometimes help the planner:

CREATE OR REPLACE FUNCTION current_tenant_id() RETURNS uuid
  LANGUAGE sql STABLE
  AS $$ SELECT current_setting('app.tenant_id')::uuid $$;

Testing RLS Policies

Testing RLS requires simulating the session context that policies evaluate against. The simplest approach is a helper that sets up a transaction with a specific tenant ID, runs assertions, and rolls back.

import { Pool } from "pg";
import { describe, it, expect, beforeAll, afterAll } from "vitest";

const pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });

async function queryAsTenant<T>(
  tenantId: string,
  query: string,
  params: unknown[] = []
): 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 client.query<T>(query, params);
    await client.query("ROLLBACK"); // always roll back in tests
    return result.rows;
  } finally {
    client.release();
  }
}

describe("orders RLS policies", () => {
  const tenantA = "tenant-a-uuid";
  const tenantB = "tenant-b-uuid";

  it("returns only the requesting tenant's rows", async () => {
    const rows = await queryAsTenant(
      tenantA,
      "SELECT tenant_id FROM orders"
    );
    expect(rows.every((r) => r.tenant_id === tenantA)).toBe(true);
  });

  it("does not return another tenant's rows", async () => {
    const rows = await queryAsTenant(
      tenantA,
      "SELECT id FROM orders WHERE tenant_id = $1",
      [tenantB]
    );
    // Even though we explicitly query for tenantB, RLS blocks it.
    expect(rows).toHaveLength(0);
  });

  it("blocks INSERT with a mismatched tenant_id", async () => {
    await expect(
      queryAsTenant(
        tenantA,
        "INSERT INTO orders (tenant_id, amount) VALUES ($1, 100)",
        [tenantB]
      )
    ).rejects.toThrow();
  });
});

Two additional tests that teams consistently skip:

First, test what happens when app.tenant_id is not set. current_setting('app.tenant_id') throws by default if the variable is missing. You can use current_setting('app.tenant_id', true) (the missing_ok flag) to return null instead, then write your policy to treat null as deny-all. Test both paths.

Second, test JOIN paths. RLS applies to the table in context, but if you join an RLS-protected table as a subquery or lateral join, the policy still applies. Verify that a JOIN from a cross-tenant reference does not leak rows through association.

Migrating from Application-Level Filtering

If you have an existing application with WHERE tenant_id = ? in every query, migrating to RLS requires careful sequencing.

Step 1: Enable RLS in audit mode first. Before writing real policies, add a permissive policy that allows everything but logs when it fires. This helps you find all the code paths that hit each table without immediately breaking anything.

Step 2: Add policies, keep application filters. Enable RLS and add tenant isolation policies, but do not remove application-level filters yet. Run both in parallel for a release cycle. This gives you a safety net: if a policy misconfiguration causes an issue, the application filter is still there.

Step 3: Audit for superuser bypass. Postgres RLS is bypassed by superusers and by table owners (unless you use FORCE ROW LEVEL SECURITY). Check which database roles your application connects as.

-- If your app role is the table owner, this is necessary:
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- FORCE RLS makes policies apply even to the table owner.
-- It does NOT apply to superusers. Never run application code as a superuser.

Step 4: Remove application filters after validation. Once you have confirmed that RLS is enforcing tenant isolation correctly through your test suite, you can remove the redundant WHERE tenant_id = ? clauses. Do this table by table, not all at once.

// Before migration: application-level filter required
async function getOrders(tenantId: string) {
  return db.query("SELECT * FROM orders WHERE tenant_id = $1", [tenantId]);
}

// After migration: RLS handles isolation, session context is set upstream
async function getOrders() {
  return db.query("SELECT * FROM orders");
}

Common Pitfalls

Forgotten policies on new tables. When a new table is added to the schema, it has no RLS policy and no RLS enabled. Data in that table is unprotected. Establish a convention: every migration that creates a table must include ENABLE ROW LEVEL SECURITY and the tenant isolation policy. Add a linting step that checks for tables missing RLS if your tooling supports it.

Superuser bypass. Application code should never run as a Postgres superuser. Create a dedicated application role with only the permissions it needs, confirm that RLS applies to that role, and ensure FORCE ROW LEVEL SECURITY is set on tables the role owns.

JOIN leaks. RLS applies when a query accesses the protected table. If table A has RLS and table B does not, a JOIN from B to A still applies RLS to A. But the reverse can be surprising: a policy on A that allows SELECT does not protect data if B contains a foreign key reference that leaks structural information without directly reading A’s rows. Design your schema and policies with this in mind.

Policy errors returning empty sets silently. If current_setting('app.tenant_id') is not set and you have not handled the missing_ok case, the policy throws an error. Depending on how your application handles database errors, this might surface as an empty result instead of an error, which looks correct but is wrong. Always handle the missing-setting case explicitly.

Testing with a superuser connection. If your test database connection is a superuser, RLS policies are bypassed entirely and your tests give false confidence. Use a non-superuser application role in tests, identical to production.

Tradeoffs Summary

Approach	Isolation strength	Operational cost	Query complexity	Migration effort
Application-level filtering	Convention only	Low	Application owns it	Already in place
RLS with shared schema	Database-enforced	Low	Policies + session vars	Moderate
Schema-per-tenant	Physical isolation	High (per-tenant ops)	No cross-tenant queries	High
RLS + FORCE ROW LEVEL SECURITY	Database-enforced, owner bypass closed	Low	Same as RLS	Small addition to RLS

Production Considerations

Monitor for policy evaluation overhead. RLS adds a predicate per row access, which is minimal when indexes are in place but shows up under high query volume on unindexed tables. Include RLS-protected tables in your slow query log review.

Use pg_policies to audit what policies exist on each table. Run this as part of your deployment pipeline to catch tables that are missing policies.

SELECT tablename, policyname, permissive, cmd, qual
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename;

Connection pool sizing matters more with set_config in transaction mode. Each query that requires tenant context needs to acquire a client, set the context, run the query, and release. Under high concurrency, pool exhaustion becomes a realistic failure mode. Size your pool for peak concurrent transactions, not peak concurrent requests.

If you use database migrations (Flyway, Liquibase, or a TypeScript migration library), run migrations as the table owner or superuser, not the application role. RLS with FORCE ROW LEVEL SECURITY will block your migration scripts if they run as the application role and the policies are not set up to allow DDL operations.

The Real Guarantee

Application-level filtering is correct until it is not. A new endpoint added without the filter, a raw query in a migration script, a background job that skips the ORM layer. Each of these is a potential cross-tenant data leak that your test suite may not catch.

RLS moves the invariant from “all developers remember to add the filter” to “the database rejects queries that violate isolation, regardless of who wrote them.” That shift does not eliminate the need for application-level awareness, but it changes the failure mode from silent data exposure to an explicit error that surfaces immediately.

For any SaaS application where tenant isolation is load-bearing, that is a meaningful difference.