PostgreSQL Row-Level Security for Multi-Tenant SaaS, with Tests

To isolate tenants with PostgreSQL row-level security, enable and force RLS on every tenant table, write a policy comparing tenant_id with current_setting('app.tenant_id', true), set that value per transaction with SET LOCAL or set_config(..., true), and connect as a role that neither owns the tables nor has BYPASSRLS. Then prove it with tests in CI.

This is the hands-on version: the SQL, the connection code and the tests. It assumes you have already chosen a shared schema with a tenant column. If you are still choosing a tenancy model, or want the wider picture of RBAC, billing and audit logs, read how to build multi-tenant B2B SaaS first; this guide goes deeper on one part of it. PostgreSQL behaviour is checked against the PostgreSQL 18 documentation. The examples use node-pg, but the SQL is the same from any driver.

What does row-level security do in a multi-tenant app?

Row-level security (RLS) makes PostgreSQL add a condition to every query on a table, based on policies you define. In a multi-tenant app, the condition is "this row's tenant is the current tenant". A query that forgets its WHERE tenant_id = ... then returns only the current tenant's rows, instead of every tenant's.

Two facts from PostgreSQL's row security documentation shape everything below:

• It fails closed. "If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified."
• Some roles skip it. "Superusers and roles with the BYPASSRLS attribute always bypass the row security system", and table owners normally bypass it too, unless the table uses FORCE ROW LEVEL SECURITY.

RLS is a second wall behind scoped queries in your application code, not a replacement for them.

How do you set up RLS for tenants, step by step?

Separate the role that owns the schema from the role the app logs in as, then enable, force and write the policy.

-- 1. Two roles: migrations own the tables; the app owns nothing.
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_user LOGIN PASSWORD 'change-me' NOSUPERUSER NOBYPASSRLS;

-- 2. A tenant table, owned by app_owner, indexed tenant-first.
CREATE TABLE invoices (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    uuid NOT NULL REFERENCES tenants (id),
  amount_cents integer NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE invoices OWNER TO app_owner;
CREATE INDEX invoices_tenant_created_idx ON invoices (tenant_id, created_at);

-- 3. Turn RLS on, and make it apply to the owner as well.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

-- 4. One policy for reads and writes. NULLIF turns an unset tenant into NULL,
--    which matches no rows: the query fails closed.
CREATE POLICY tenant_isolation ON invoices
  FOR ALL
  TO app_user
  USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);

-- 5. Ordinary privileges still apply; RLS only filters rows.
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;

Why each part matters:

• current_setting('app.tenant_id', true). The second argument is missing_ok. PostgreSQL's configuration functions reference says that with it set to true, a missing setting returns NULL instead of an error.
• NULLIF(..., ''). Once a custom setting has been set in a session, it can read back as an empty string after the transaction ends rather than NULL. Casting an empty string to uuid raises an error; NULLIF makes it a clean "no rows".
• USING and WITH CHECK. USING decides which existing rows a query can see, update or delete. WITH CHECK decides which rows an INSERT or UPDATE may write, so a tenant cannot create a row for another tenant or move its own row across.
• The tenant-first index. Every query now carries the tenant condition, so indexes should start with tenant_id. More in the PostgreSQL indexing guide.

The tenants table needs its own policy, typically USING (id = ...) on the same setting, so a tenant can read its own record and nobody else's.

Why does the table owner bypass RLS, and how do you stop it?

Because PostgreSQL assumes the owner is administering the table. Unless you add FORCE ROW LEVEL SECURITY, the owner sees every row, and so does any app that connects as the owner. This is the most common reason "RLS is on but data still leaks".

Many setups create tables with the same role the app uses, especially in early projects and hosted databases with one default user. RLS then does nothing for the app. The fix has two parts:

1. Connect the app as a separate role that owns no tables, is not a superuser and has NOBYPASSRLS.
2. Force RLS anyway, so a mistake in role setup fails closed instead of open.

Forcing has a side effect worth planning for. With the policy scoped TO app_user and RLS forced, the owner has no applicable policy, so it sees no rows. Schema migrations still work, but data backfills run as the owner will find nothing. Run those under a dedicated maintenance role with BYPASSRLS, used only by migration jobs and logged, so bypassing RLS is always a deliberate act.

Check the app's role from its own connection:

SELECT current_user, rolsuper, rolbypassrls
FROM pg_roles
WHERE rolname = current_user;           -- expect false, false

SELECT tablename
FROM pg_tables
WHERE tableowner = current_user;        -- expect no rows

How do you pass the tenant safely through a connection pool?

Set it inside a transaction with set_config('app.tenant_id', $1, true), the function form of SET LOCAL, so it reverts at COMMIT or ROLLBACK. A plain session-level SET stays on the connection, and the pool hands that connection to the next request, which then runs as the previous tenant.

Method	Scope	Safe with a pool?	Notes
SET app.tenant_id = '...'	Whole session	No	Leaks into the next request on the same connection
SET LOCAL app.tenant_id = '...'	Current transaction	Yes, inside BEGIN ... COMMIT	Cannot take a bind parameter, so the value must be built into the SQL string
SELECT set_config('app.tenant_id', $1, true)	Current transaction	Yes, inside a transaction	Takes a bind parameter; the usual choice from application code

PostgreSQL's reference says that with is_local true, set_config applies "only during the current transaction". Outside a transaction block there is nothing to scope it to, so always open one first. External poolers raise the stakes: PgBouncer's feature table marks SET/RESET as never supported in transaction pooling mode, because consecutive transactions from one client can land on different server connections. A transaction-scoped setting stays on the one connection that runs the transaction, which is exactly what you want.

A small helper makes the safe path the only path:

import { Pool, type PoolClient } from 'pg'

// Connects as app_user: no table ownership, no BYPASSRLS.
const pool = new Pool({ connectionString: process.env.DATABASE_URL })

export async function withTenant<T>(
  tenantId: string,
  fn: (db: PoolClient) => Promise<T>,
): Promise<T> {
  const db = await pool.connect()
  try {
    await db.query('BEGIN')
    // Transaction-scoped: gone at COMMIT or ROLLBACK, so the next request starts clean.
    await db.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId])
    const result = await fn(db)
    await db.query('COMMIT')
    return result
  } catch (err) {
    await db.query('ROLLBACK')
    throw err
  } finally {
    db.release()
  }
}

// The tenant always comes from the verified session, never from the request body.
const invoices = await withTenant(session.tenantId, (db) =>
  db.query('SELECT id, amount_cents FROM invoices ORDER BY created_at DESC LIMIT 50'),
)

What else can bypass or leak around RLS?

Policies apply to queries on the table. Several features reach the data another way.

Pitfall	What happens	What to do
Views	By default a view runs with its owner's rights, so the owner's bypass applies	On PostgreSQL 15 and later, create views WITH (security_invoker = true)
SECURITY DEFINER functions	Run as the function owner, who may bypass RLS	Prefer SECURITY INVOKER; if you must use definer, filter by tenant inside
Materialized views	Contents depend on the role that refreshed them, not the reader; if that role bypasses RLS, every reader sees every tenant's rows	Include tenant_id and query them only through a filtered path, or avoid them for tenant data
Unique and foreign-key checks	PostgreSQL says integrity checks "always bypass row security", which can reveal that a value exists	Scope unique constraints by tenant, such as UNIQUE (tenant_id, email)
Backups and exports	A dump under RLS could silently omit rows	PostgreSQL's row_security = off makes filtered queries error instead of silently dropping rows
Several permissive policies	Permissive policies combine with OR, so a broad one widens access	Make the tenant rule AS RESTRICTIVE, combined with AND, when you add role-based policies

The integrity-check and row_security behaviour are both described in PostgreSQL's row security documentation, which warns about "covert channel" leaks through referential integrity checks.

How do you test tenant isolation with RLS?

Three layers of tests, all run in CI against a real PostgreSQL, not a mock.

1. A catalogue check: no tenant table without RLS

This query lists tables that have a tenant_id column but are missing RLS, forced RLS or a policy. The test fails if it returns any rows.

SELECT c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'tenant_id' AND NOT a.attisdropped
WHERE n.nspname = 'public'
  AND c.relkind IN ('r', 'p')
  AND (
    NOT c.relrowsecurity
    OR NOT c.relforcerowsecurity
    OR NOT EXISTS (
      SELECT 1 FROM pg_policies p
      WHERE p.schemaname = n.nspname AND p.tablename = c.relname
    )
  );

2. Behaviour tests at the database

it('tenant A cannot read tenant B rows', async () => {
  const res = await withTenant(tenantA, (db) =>
    db.query('SELECT id FROM invoices WHERE id = $1', [invoiceOfB]))
  expect(res.rowCount).toBe(0)
})

it('tenant A cannot write a row for tenant B', async () => {
  await expect(withTenant(tenantA, (db) =>
    db.query('INSERT INTO invoices (tenant_id, amount_cents) VALUES ($1, 100)', [tenantB]),
  )).rejects.toThrow(/row-level security/)
})

it('tenant A cannot delete tenant B rows', async () => {
  const res = await withTenant(tenantA, (db) =>
    db.query('DELETE FROM invoices WHERE id = $1', [invoiceOfB]))
  expect(res.rowCount).toBe(0)
})

it('no tenant set means no rows', async () => {
  const res = await pool.query('SELECT count(*) FROM invoices')
  expect(res.rows[0].count).toBe('0')
})

it('the tenant does not leak to the next use of a connection', async () => {
  await withTenant(tenantA, (db) => db.query('SELECT 1'))
  const res = await pool.query("SELECT current_setting('app.tenant_id', true) AS t")
  expect(res.rows[0].t || null).toBeNull()
})

A blocked insert fails with PostgreSQL's "new row violates row-level security policy" error, which is what the second test matches. The last test needs a pool of one connection so both calls share it.

3. API-level IDOR tests

Log in as tenant A and call every endpoint that takes an ID with tenant B's records, expecting 404. The database tests prove the wall holds; these prove the app does not route around it with a privileged connection or a cache.

This is the pattern RAITHub uses on Sundor Skin, a B2B wholesale platform it built with a 146-table PostgreSQL core and row-level security. Every buyer query runs under a policy scoped to that buyer, CI fails the build if a buyer-scoped table lacks a policy, and a 21-case IDOR suite tries to read other buyers' data, among 530+ automated tests. Sundor Skin is not a SaaS, but buyer isolation is the same problem. See the Sundor Skin case study, and the wider method in how RAITHub tests software.

Does RLS slow PostgreSQL down?

Usually very little, if the policy is a simple equality on an indexed column. The policy condition is added to the query plan like any other WHERE clause. Costs appear when a policy calls a slow function per row, joins other tables, or when the tenant column is not the first column of the indexes queries use. Check plans with EXPLAIN (ANALYZE) under withTenant, not as a superuser, because a superuser's plan skips the policy entirely.

Why RAITHub for RLS and tenant isolation?

Because RAITHub has run database-enforced isolation in production, with the missing-policy check and IDOR suite described here gated in CI.

• Proven on a large schema. Sundor Skin: 146 PostgreSQL tables, row-level security, 12 staff roles from 88 permission codes, 530+ tests.
• PostgreSQL and TypeScript as the default stack, with node-pg, transaction-scoped tenant context and tests against a real database.
• Retrofits as well as new builds. Adding RLS to an existing schema means finding every table, fixing ownership and backfills, and testing each path. See the API and backend development service.
• Fixed scope. A free 15-minute technical audit, then a written, fixed quote. You own the code; an NDA is standard.

When you don't need RLS, or us

• Single-tenant software. An internal tool for one company has no tenant boundary to enforce.
• Database per tenant. Physical separation already isolates tenants; RLS adds little.
• A handful of tables and a disciplined team. Scoped queries plus the IDOR tests above may be enough for now, as long as you add RLS before the schema grows.
• You only need a review. Run the catalogue query and the role check above yourself first; they answer most "is our RLS real?" questions in minutes.

To add or audit row-level security in your product, book the free technical audit with your schema and how the app connects to the database.

Last reviewed: 29 September 2026. Checked against the PostgreSQL 18 documentation and PgBouncer's feature table.

Frequently asked questions

How do I use PostgreSQL row-level security for multi-tenancy?

Add a tenant_id column to every tenant table, enable and force row-level security, and create a policy comparing tenant_id with current_setting('app.tenant_id', true). Set that value per transaction with set_config and is_local true, and connect as a role that does not own the tables.

Why can the table owner still see every row with RLS enabled?

Table owners bypass row-level security unless the table uses ALTER TABLE ... FORCE ROW LEVEL SECURITY. Superusers and roles with BYPASSRLS always bypass it. Connect the app as a separate, non-owner role and force RLS as well.

Should I use SET or SET LOCAL for the tenant ID?

SET LOCAL, or set_config with is_local true, inside a transaction. A session-level SET stays on a pooled connection and can be inherited by the next request. PgBouncer does not support SET in transaction pooling mode.

What happens if the tenant setting is not set?

With current_setting's missing_ok argument set to true and a NULLIF guard for the empty string, the comparison is NULL and the query returns no rows. The query fails closed rather than exposing every tenant.

How do I test that RLS is working?

Run a catalogue query in CI that fails if any tenant table lacks forced RLS or a policy, database tests that try to read, write and delete another tenant's rows, and API-level IDOR tests that expect 404 for other tenants' records.

Do views respect row-level security?

By default a view runs with its owner's privileges, so an owner's bypass applies. On PostgreSQL 15 and later, create views with security_invoker = true so the caller's policies apply.

Is RLS enough to isolate tenants on its own?

It is a strong second wall, but keep tenant-scoped queries in the application, tenant keys in caches, and tests at both the database and API level.