Multi-tenant Postgres: row-level security explained (with real code)

Row-level security in Postgres is the single most underused defense-in-depth feature available to a multi-tenant SaaS. Turn it on at the schema level and a buggy select * from invoices from an application bug returns only the current tenant's rows — not because the application remembered to filter, but because the database refused to return anything else. RLS is not a substitute for application-level filtering. It is the airbag that fires when application-level filtering fails.

This is the pattern we use on every multi-tenant SaaS we ship — including CloudChat, where customer conversations are obviously tenant-scoped and a single missed filter would be a breach.

The three failure modes RLS prevents

Every multi-tenant SaaS at some point ships one of these bugs:

1. A missing where tenant_id = ? in a hand-rolled SQL string.
2. An ORM query that joins through a related table and forgets to scope the join.
3. A "show all my data" admin endpoint that was supposed to be tenant-scoped but inherited the wrong context.

All three result in the same symptom: a customer sees another customer's data. RLS prevents all three at the storage layer.

The minimum viable RLS setup

Three pieces: a session variable that holds the current tenant id, a policy that filters every query by that variable, and a discipline of always setting the variable before issuing a query.

-- 1. Tag every domain table with the tenant id.
alter table widgets add column org_id text not null;
create index widgets_org_idx on widgets(org_id);

-- 2. Enable RLS.
alter table widgets enable row level security;

-- 3. Write the policy.
create policy widgets_tenant_isolation on widgets
  for all
  using (org_id = current_setting('app.current_org_id', true))
  with check (org_id = current_setting('app.current_org_id', true));

The using clause filters reads. The with check clause prevents inserts and updates from writing rows that belong to a different tenant. You need both.

Now in your application code, before every query:

// src/lib/db.ts
import { pool } from "./pool";

export async function withOrg<T>(
  orgId: string,
  fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("SET LOCAL app.current_org_id = $1", [orgId]);
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (e) {
    await client.query("ROLLBACK");
    throw e;
  } finally {
    client.release();
  }
}

Wrap every tenant-scoped query in withOrg. The SET LOCAL scopes the variable to the transaction — so when the connection returns to the pool, the next request starts clean. This matters.

What happens when the variable isn't set

If you forget to call withOrg, the policy evaluates current_setting('app.current_org_id', true) to NULL. The using clause becomes org_id = NULL, which is never true for any row. The query returns zero rows.

This is the correct fail-closed behavior. A bug returns no data instead of all data. Compare to the alternative — application filter forgotten, where clause missing, the query returns the entire table.

The bypass role for migrations and admin

Sometimes you legitimately need to read every tenant's rows. Database migrations. Admin tools. Background jobs that aggregate metrics. Stripe webhook handlers that update a subscription across the boundary.

You need a way to bypass RLS for trusted code paths. There are two options.

Option 1: Use a different role. Postgres roles can have BYPASSRLS set. Your migration scripts and admin code connect as that role. Application code connects as the restricted role.

create role app_user nologin;
create role app_admin nologin bypassrls;
grant app_user, app_admin to your_application_role;

Option 2: A "force" function with elevated permissions. A security definer function that turns off the policy for a specific query. We prefer the role approach — it's more explicit.

Connection pooling — the part that breaks

Most teams use PgBouncer or RDS Proxy. RLS plus PgBouncer in transaction mode works. RLS plus PgBouncer in session mode is fine. RLS plus PgBouncer in statement mode does not work — your SET LOCAL gets stripped between statements.

# pgbouncer.ini — the only mode that plays well with RLS
pool_mode = transaction

If you're on Supabase, this is already configured. If you're on Neon, this is the default. If you're running your own PgBouncer, double-check.

Performance impact

Negligible if the policy is org_id = something. Postgres is smart enough to use the org_id index. We see <1% query overhead in production.

It becomes meaningful if the policy includes a subquery (org_id in (select org_id from memberships where user_id = ...)). Don't do that. Resolve the membership in application code and pass the org id down.

-- Don't do this. The subquery runs per row.
create policy bad_widgets_policy on widgets
  using (org_id in (
    select org_id from memberships
    where user_id = current_setting('app.current_user_id', true)
  ));

-- Do this. The application is responsible for resolving membership.
create policy good_widgets_policy on widgets
  using (org_id = current_setting('app.current_org_id', true));

Testing RLS in CI

A test that pretends to be tenant A and verifies it can't read tenant B's rows. Run it in CI. Run it after every schema change. This is the test that fails when someone adds a new table and forgets to enable RLS on it.

// tests/rls.test.ts
import { test, expect } from "vitest";
import { withOrg, db } from "@/lib/db";

test("tenant A cannot read tenant B widgets", async () => {
  await withOrg("org_a", async (c) => {
    await c.query("insert into widgets(id, org_id, name) values ('w_a','org_a','A')");
  });
  await withOrg("org_b", async (c) => {
    await c.query("insert into widgets(id, org_id, name) values ('w_b','org_b','B')");
  });
  const aResults = await withOrg("org_a", (c) =>
    c.query("select id from widgets")
  );
  expect(aResults.rows.map((r) => r.id)).toEqual(["w_a"]);
});

test("every public table has RLS enabled", async () => {
  const { rows } = await db.query(`
    select c.relname
    from pg_class c
    join pg_namespace n on n.oid = c.relnamespace
    where n.nspname = 'public'
      and c.relkind = 'r'
      and c.relrowsecurity = false
  `);
  expect(rows).toEqual([]);
});

The second test is the one that catches schema drift. New table, no RLS, test fails, you can't ship.

RLS plus ORMs

Drizzle, Prisma, Kysely — all work with RLS as long as you control the connection acquisition. The trick is that the ORM's "give me a query builder" call needs to use your connection (the one with the session variable set), not its own.

// Drizzle example — withOrg yields a client we wrap in drizzle
import { drizzle } from "drizzle-orm/node-postgres";

export async function withOrgDrizzle<T>(
  orgId: string,
  fn: (db: ReturnType<typeof drizzle>) => Promise<T>,
) {
  return withOrg(orgId, async (client) => fn(drizzle(client)));
}

Prisma is more awkward because it owns its own connection pool. The cleanest workaround is to write tenant-scoped queries with $queryRaw and let RLS do its thing. If you use Prisma heavily, plan for one or two queries to be uncomfortable.

When RLS is the wrong answer

Three cases where RLS is overkill:

• Single-tenant SaaS where each customer gets their own database. RLS adds nothing — the database itself is the boundary.
• Schema-per-tenant. Same — separate schemas are a stronger boundary than RLS within a single schema.
• Read-heavy analytics warehouses where queries scan billions of rows. RLS policies on huge scans can confuse the query planner. Use application-level filtering and trust your tests.

For the standard "shared database, shared schema, tenant_id column" pattern that ~90% of SaaS uses, RLS is the right call.

What we ship by default

On every multi-tenant SaaS we build:

1. org_id on every domain table, with a btree index.
2. RLS enabled on every public table.
3. SET LOCAL app.current_org_id inside a per-request transaction.
4. A CI test that asserts every table has RLS on.
5. A separate BYPASSRLS role for migrations and the admin surface.

That's it. Five small pieces. They have caught at least three would-be cross-tenant bugs across our portfolio that I can personally remember.

FAQ

Does RLS replace application-level filtering?

No. RLS is defense in depth. Always also filter in application code — your queries should be tenant-scoped on their own. RLS catches the bug when you forget.

Can I use RLS with serverless / edge?

Yes, as long as your connection pool gives you a transaction-scoped session. Neon serverless driver, Cloudflare D1's transaction model, and PlanetScale's serverless driver all work. The constraint is that the connection must hold for the duration of BEGIN/COMMIT.

What about Supabase RLS specifically?

Supabase uses RLS as its core auth model — every request from the client SDK is filtered by auth.uid(). The pattern is the same as what's described here, just with auth.jwt() in the policy instead of a session variable. The trade-offs are identical.

Does RLS hurt query planning?

In simple cases (column = setting), no — the planner inlines the comparison. In complex cases (subqueries in the policy), yes — sometimes significantly. Keep policies simple.

How do I do bulk admin operations across tenants?

Use the BYPASSRLS role. Bulk jobs (analytics rollups, billing reconciliation, GDPR exports) connect as that role and skip the policy.

What about column-level security?

Postgres also supports column privileges. We rarely use them — tenant isolation at the row level is usually sufficient and column-level grants get hard to maintain. Use them only if you have a hard regulatory requirement to restrict specific columns to specific roles.

Can I migrate an existing SaaS to RLS without downtime?

Yes. The migration: add org_id to every table (backfill), add the policy in permissive mode, log queries that would have failed, fix them, then flip to restrictive. Plan for a sprint. Worth it.

What's the relationship between RLS and SOC2?

RLS doesn't satisfy any specific SOC2 control on its own, but it's the strongest piece of evidence you have for "tenant data is isolated by the storage layer." Your auditor will love it. Mention it in your control narrative.