Row-Level Security for Multi-Tenant SaaS in Postgres

A support engineer runs a quick query to debug a customer complaint. She forgets one WHERE tenant_id = $1. The dashboard now shows invoices from three other companies, and one of them is a competitor of the customer on the phone. Nobody wrote malicious code. Somebody wrote incomplete code, which in shared-table multi-tenancy is the same thing.

That is the uncomfortable truth about the most common SaaS data model: every tenant’s rows sit in the same table, and the only thing keeping Acme’s data away from Globex is a filter clause that a human has to remember, on every query, forever. Humans do not do “forever” well. The fix is to stop trusting the application to filter and make the database enforce isolation itself, with Row-Level Security (RLS). Here is how to wire it up correctly, including the pooler gotcha that silently breaks it.

The model: one table, one policy

Assume the classic shared-schema layout: a tenant_id column on every tenant-scoped table.

CREATE TABLE invoices (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   uuid NOT NULL,
  amount_cents bigint NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

Without RLS, correctness depends on every query carrying the right filter. With RLS, you enable a default-deny wall and then poke exactly one hole through it: rows whose tenant_id matches the tenant of the current request.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

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

Two things to know. When you run ENABLE ROW LEVEL SECURITY, PostgreSQL applies a default-deny stance: if no policy exists for the table, a default-deny policy is used, so no rows are visible or can be modified. Enabling without a policy locks the table shut, which is the safe direction to fail. The policy above uses USING to filter which existing rows are visible (for SELECT, UPDATE, DELETE) and WITH CHECK to validate rows being written (for INSERT, UPDATE). If you omit WITH CHECK, PostgreSQL reuses the USING expression for writes, but writing it explicitly makes intent obvious and prevents a tenant from inserting rows stamped with someone else’s id.

current_setting('app.tenant_id') reads a custom run-time parameter. Custom parameters need a dot in the name (a namespace), and you never have to declare app.* ahead of time. That is the value we will set per request. (One safety note: with no missing_ok argument, current_setting raises an error if the parameter was never set, so an unset context fails the query rather than leaking rows.)

The owner and BYPASSRLS trap

Here is the gotcha that makes teams think RLS “does not work”. Straight from the docs: “Superusers and roles with the BYPASSRLS attribute always bypass the row security system.” And: “Table owners normally bypass row security as well.”

So if your application connects as the role that owns the tables (very common in dev setups and plenty of production ones), RLS is silently ignored and every tenant sees everything. Enabling policies changed nothing, because the owner was never subject to them.

Two defenses, use both:

-- 1) Make the owner obey its own policies.
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

-- 2) Connect the app as a dedicated, non-owner, non-superuser role
--    that does NOT have BYPASSRLS.
CREATE ROLE app_user LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;

FORCE ROW LEVEL SECURITY subjects the table owner to the policies too. Schema migrations still work, because row security governs row visibility and modification (DML), not DDL such as ALTER TABLE. Referential-integrity checks (unique and primary keys, foreign keys) always bypass row security for everyone to keep data integrity intact, so constraints keep working. What does change: a data-backfill migration run as the owner is now filtered by policy, so either set the tenant context inside it or run it as a role that legitimately bypasses RLS. The belt-and-suspenders rule for the app itself: it should log in as a role that is neither superuser, nor the table owner, nor BYPASSRLS. Verify it:

SELECT rolsuper, rolbypassrls FROM pg_roles WHERE rolname = 'app_user';
-- both must be false

Setting the tenant per request

The policy reads app.tenant_id, but nobody has set it yet. This is where a subtle, high-severity bug lives. You could use SET app.tenant_id = ..., but a plain SET changes the setting for the rest of the session (the connection). Behind a connection pooler (PgBouncer, or pg.Pool reusing sockets), that connection is handed to the next request, which may belong to a different tenant. Now request B inherits request A’s tenant context. That is a cross-tenant leak created by the very mechanism meant to prevent one.

The fix is SET LOCAL, which scopes the value to the current transaction and resets it automatically at COMMIT or ROLLBACK. Wrap every request in a transaction, set the tenant with SET LOCAL, do the work, commit. The connection goes back to the pool with no residual tenant context.

sequenceDiagram
    participant App as Node request
    participant Pool as pg.Pool / PgBouncer
    participant PG as Postgres

    App->>Pool: acquire connection
    App->>PG: BEGIN
    App->>PG: SET LOCAL app.tenant_id = $tenant
    App->>PG: SELECT * FROM invoices
    PG-->>App: only this tenant's rows (policy enforced)
    App->>PG: COMMIT
    Note over PG: SET LOCAL is discarded at COMMIT
    App->>Pool: release connection (no tenant leaks)

One caveat that trips people up: you cannot use a bound parameter ($1) with SET LOCAL, because SET is a utility command, not a parameterized query. Use set_config(setting_name, new_value, is_local) instead, which is a normal function and takes parameters safely.

import { Pool } from "pg";

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

// Run a set of queries scoped to one tenant, inside a single transaction.
export async function withTenant(tenantId, fn) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    // set_config(setting_name, new_value, is_local=true) -> transaction-scoped, param-safe
    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();
  }
}

Use it in a request handler. The query has no tenant_id filter at all, because the database is enforcing it now.

app.get("/invoices", async (req, res) => {
  const tenantId = req.auth.tenantId; // from your verified JWT/session, never the client body
  const rows = await withTenant(tenantId, async (client) => {
    const { rows } = await client.query(
      "SELECT id, amount_cents, created_at FROM invoices ORDER BY created_at DESC LIMIT 50"
    );
    return rows;
  });
  res.json(rows);
});

If you run PgBouncer in transaction pooling mode, this pattern is exactly what makes it safe: SET LOCAL and set_config(..., true) live and die inside the transaction, which is the unit PgBouncer multiplexes. See connection pooling with PgBouncer for why session-level state and transaction pooling do not mix.

Do not forget the index

RLS rewrites every query to append your policy predicate. tenant_id = current_setting('app.tenant_id')::uuid runs on every read. If tenant_id is not indexed, that predicate degrades into sequential scans across all tenants’ rows, and your isolation layer becomes your latency problem.

CREATE INDEX ON invoices (tenant_id);
-- Even better for tenant-scoped queries that also sort/filter:
CREATE INDEX ON invoices (tenant_id, created_at DESC);

Lead composite indexes with tenant_id so tenant-scoped queries stay index-driven. This is the same discipline you would apply when architecting a multi-tenant backend, and it compounds with the resilience patterns in building scalable systems: isolation you cannot afford to keep on is isolation you will eventually turn off.

Takeaway

RLS moves tenant isolation from a convention every developer must remember into an invariant the database enforces. Enable it, FORCE it so the owner obeys, connect as a plain non-owner role without BYPASSRLS, set the tenant with SET LOCAL (or set_config(..., true)) inside a per-request transaction so pooled connections never leak context, and index the tenant column. Do that and the forgotten WHERE clause becomes a non-event: the worst a missing filter can do is return your own tenant’s rows.

References

• PostgreSQL: Row Security Policies
• PostgreSQL: CREATE POLICY
• PostgreSQL: SET (and SET LOCAL)
• PostgreSQL: System Administration Functions (set_config, current_setting)
• PgBouncer: pooling modes