Every SaaS says it is multi-tenant. In practice, in many of them, isolation between customers depends on a single thing: a developer remembering to write WHERE tenant_id = ? in every query, every endpoint, every report and every background job. One slip, one new route written in a hurry, or one filter taken from the client instead of the session, and one company's data shows up on another company's screen.
Real multi-tenancy is when isolation does not depend on anyone remembering. In this post, you will see how to do it in PostgreSQL with Row-Level Security (RLS) forced on every table, how to pass the current tenant to the database without leaking between pooled connections and, above all, how to have a dedicated automated test that breaks the build if any table is left unprotected.
TL;DR
- The idea: the database, not the application, decides which rows each tenant can read and write. A query without a
WHEREreturns only the current tenant's rows. - Enabling is not enough:
ENABLE ROW LEVEL SECURITYdoes not apply to the table owner. Also useFORCE ROW LEVEL SECURITYand connect the application with a user that is not the owner, not a superuser and has noBYPASSRLS. - Reads and writes: the policy needs
USING(what can be seen) andWITH CHECK(what can be written), otherwise rows can be inserted on behalf of another tenant. - Per-transaction context: set the tenant with
set_config('app.tenant_id', ..., true)inside a transaction, so it does not linger on the connection when it goes back to the pool. - Dedicated test: one test reads the Postgres catalog and fails if any table lacks RLS,
FORCEor a policy. Another creates two tenants and tries to read, change and delete the neighbor's data.
Three ways to isolate tenants
| Model | Isolation | Cost and operations |
|---|---|---|
| One database per tenant | Maximum: each tenant has its own database | Expensive and laborious: migrations, backups and connections multiplied by the number of tenants |
| One schema per tenant | Good, but depends on the right search_path on every connection | Gets heavy with hundreds or thousands of schemas |
Shared tables with a tenant_id column | Depends on whoever applies the filter | Simple and cheap, one migration for everyone |
The third option is the most common, because it scales well and is easy to run. The problem is that, without RLS, isolation lives in application code. With RLS, it moves into the database, and shared tables get isolation close to the other models without their cost.
How Row-Level Security works
RLS is a PostgreSQL feature (since version 9.5) that adds a mandatory filter to each table. Once RLS is enabled, every read and every write must be allowed by a policy. If there is no policy at all, nobody sees or changes anything: the default is deny.
Each policy has two parts:
USING: defines which existing rows are visible toSELECT,UPDATEandDELETE. The others simply do not show up.WITH CHECK: defines which rows can be written byINSERTandUPDATE. If the new row does not pass, the command fails with an error.
If you omit WITH CHECK, Postgres reuses the USING expression. Even so, it is worth writing both explicitly: it is clearer to the reader and avoids surprises when someone changes only one of them.
Why “forced”: who escapes RLS
This is the point that breaks most implementations. According to the PostgreSQL documentation, three kinds of users are not filtered by RLS:
| Who | Escapes RLS? | How to fix |
|---|---|---|
| Table owner | Yes, by default | ALTER TABLE ... FORCE ROW LEVEL SECURITY |
| Superuser | Always, even with FORCE | Never connect the application as a superuser |
User with BYPASSRLS | Always, even with FORCE | Create the application user with NOBYPASSRLS |
It is very common for the application to connect with the same user that ran the migrations and therefore owns the tables. In that scenario you enable RLS, create the policies, everything looks right, and the filter is simply not applied. FORCE closes that door. Separating the application user from the migrations user closes the others.
Step by step
1. Two users: one for migrations, one for the application
-- table owner: used only by migrations
CREATE ROLE app_owner NOLOGIN;
-- used by the application: no superuser and no BYPASSRLS
CREATE ROLE app_user LOGIN NOSUPERUSER NOBYPASSRLS;
CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO app_user;
-- everything app_owner creates in the schema is granted to app_user from the start
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
The app_user password goes into the secrets manager, not the repository. On managed services (RDS, Cloud SQL, Azure, Supabase, Neon), the admin user that comes with the database usually has special privileges. Do not use it in the application.
2. A function to read the current tenant
CREATE FUNCTION app.current_tenant_id() RETURNS uuid
LANGUAGE sql STABLE
AS $
SELECT NULLIF(current_setting('app.tenant_id', true), '')::uuid
$;
Two important details. The second argument, true, makes current_setting return NULL instead of an error when the variable does not exist. And NULLIF handles the case where the variable was already used on the connection and came back empty: without it, ''::uuid raises an error. With NULL, the comparison is never true and no rows show up. Failing closed is exactly what we want.
Watch out for examples online that return
truewhen no tenant is set, “to make admin access easier”. That turns any slip into access to every tenant.
3. The table with RLS enabled and forced
CREATE TABLE app.invoices (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id uuid NOT NULL DEFAULT app.current_tenant_id()
REFERENCES app.tenants (id),
total numeric(12,2) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON app.invoices (tenant_id);
ALTER TABLE app.invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON app.invoices
USING (tenant_id = (SELECT app.current_tenant_id()))
WITH CHECK (tenant_id = (SELECT app.current_tenant_id()));
The DEFAULT app.current_tenant_id() fills in tenant_id on its own in INSERTs. The index on tenant_id keeps the filter from turning into a full table scan. And wrapping the function in (SELECT ...) makes Postgres compute it once per query, not once per row.
Repeat the ENABLE, the FORCE and the policy on every table with tenant data. The app.tenants table itself is included too, with the policy id = (SELECT app.current_tenant_id()), so a tenant cannot list the others.
4. Pass the tenant to the database, per transaction
The current tenant comes from the authenticated session (the token or cookie validated on the server), never from a parameter the browser sends. In Node.js with the pg package, a helper does the job:
import { Pool, type PoolClient } from 'pg'
export const pool = new Pool({ connectionString: process.env.DATABASE_URL }) // connects as app_user
export async function withTenant<T>(tenantId: string, fn: (db: PoolClient) => Promise<T>) {
const db = await pool.connect()
try {
await db.query('BEGIN')
// the trailing "true" keeps the variable only until the end of this transaction
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()
}
}
// usage: no WHERE tenant_id, and still only the session tenant's invoices come back
const invoices = await withTenant(session.tenantId, db =>
db.query('SELECT id, total FROM app.invoices ORDER BY created_at DESC'))
Why per transaction? Connections are reused by the pool (and by poolers like PgBouncer in transaction mode). A plain SET sticks to the connection, and the next request that picks it up inherits the previous tenant. With set_config(..., true), which is equivalent to SET LOCAL, the value disappears on COMMIT or ROLLBACK. set_config has another advantage: it accepts a parameter, so there is no string concatenation in the SQL.
If you use an ORM (Prisma, Drizzle, TypeORM, Sequelize, Eloquent, Django), the idea is the same: open a transaction, call set_config as the first statement and run the queries inside it.
Pitfalls RLS does not solve on its own
- Unique constraints leak existence. Unique and foreign key constraints are checked ignoring RLS. A global
UNIQUE (email)lets tenant A find out, through the duplicate error, that an email exists in tenant B. PreferUNIQUE (tenant_id, email). - Views run with the owner's permissions. A view created by
app_ownermay ignore the RLS of the tables underneath. On PostgreSQL 15 or newer, create views withWITH (security_invoker = true). SECURITY DEFINERfunctions also run as the owner. Use them only when unavoidable and filter byapp.current_tenant_id()inside them.- Partitions have their own policies. The partitioned table's policies apply when the query goes through it. Accessing a partition directly hits the partition's policies. That is why the test below checks partitions too.
TRUNCATEis not subject to RLS. Do not grantTRUNCATEtoapp_user.- Jobs and queues are requests too. Workers, crons and queue consumers need the same
withTenant. If a job really must see every tenant, create a separate user for it, make that explicit and limit what it does. - Backups:
pg_dumpusesrow_security = off, which makes the query error out instead of returning half-filtered data. Run backups with a user that hasBYPASSRLSand is not the application user.
The automated test that guarantees all of this
RLS set up by hand once guarantees nothing six months from now. Someone creates a new table in a migration and forgets the FORCE. Someone switches the connection user to “quickly fix” a permission error. What prevents a leak in those cases is a test that runs on every pull request. It has three parts.
1. Coverage: no table without forced RLS
This query reads the Postgres catalog and lists every table in the app schema (including partitions) that lacks RLS, lacks FORCE or has no policy. The expected result is empty.
SELECT c.oid::regclass AS table_name,
c.relrowsecurity AS rls_enabled,
c.relforcerowsecurity AS rls_forced,
EXISTS (SELECT 1 FROM pg_policy p WHERE p.polrelid = c.oid) AS has_policy
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'app'
AND c.relkind IN ('r', 'p') -- regular and partitioned tables (partitions are 'r')
AND (NOT c.relrowsecurity
OR NOT c.relforcerowsecurity
OR NOT EXISTS (SELECT 1 FROM pg_policy p WHERE p.polrelid = c.oid));
Because it tests the catalog, it catches the new table in the same PR that created it, without anyone having to write a test specifically for it. If a table really is global (a list of countries, for example), put it in another schema, not in an exception list that grows without anyone noticing.
2. The application user cannot escape
SELECT rolname, rolsuper, rolbypassrls
FROM pg_roles
WHERE rolname = current_user
AND (rolsuper OR rolbypassrls); -- expected: empty
SELECT c.oid::regclass
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'app'
AND pg_has_role(current_user, c.relowner, 'MEMBER'); -- expected: empty
Run over the same connection the application uses, these queries confirm it is not a superuser, has no BYPASSRLS and does not own (or belong to the owner of) any table.
3. Behavior: tenant A tries to touch tenant B's data
Here the test creates two tenants with data and tries, connected as app_user, every way of crossing the boundary. Example with Node.js's built-in test runner (node:test) and the withTenant above:
import { test, before } from 'node:test'
import assert from 'node:assert/strict'
import { pool, withTenant } from '../src/db'
import { seedTwoTenants } from './seed' // creates A and B with invoices, using app_owner
let A: string, B: string, invoiceOfB: string
before(async () => ({ A, B, invoiceOfB } = await seedTwoTenants()))
test('A only sees its own rows', async () => {
const { rows } = await withTenant(A, db => db.query('SELECT tenant_id FROM app.invoices'))
assert.ok(rows.length > 0)
assert.ok(rows.every(r => r.tenant_id === A))
})
test('A cannot find B\'s invoice even by id', async () => {
const { rowCount } = await withTenant(A, db =>
db.query('SELECT 1 FROM app.invoices WHERE id = $1', [invoiceOfB]))
assert.equal(rowCount, 0)
})
test('A cannot update or delete B\'s data', async () => {
const upd = await withTenant(A, db =>
db.query('UPDATE app.invoices SET total = 0 WHERE id = $1', [invoiceOfB]))
const del = await withTenant(A, db =>
db.query('DELETE FROM app.invoices WHERE id = $1', [invoiceOfB]))
assert.equal(upd.rowCount, 0)
assert.equal(del.rowCount, 0)
})
test('A cannot write on behalf of B', async () => {
await assert.rejects(
withTenant(A, db =>
db.query('INSERT INTO app.invoices (tenant_id, total) VALUES ($1, 10)', [B])),
/row-level security/)
})
test('with no tenant set, nothing shows up', async () => {
const { rowCount } = await pool.query('SELECT 1 FROM app.invoices')
assert.equal(rowCount, 0)
})
The coverage and user tests guarantee the rule applies to every table. The behavior tests guarantee the rule works. It is worth generating the behavior tests in a loop from the catalog's table list, so every new table automatically gets the same break-in attempts.
If you prefer to test straight in SQL, the pgTAP extension has functions like policies_are(), policy_cmd_is() and results_eq(). Just remember that the first two check policy metadata, not whether the neighbor's data is actually hidden. The behavior test is still needed.
Running it in CI
# .github/workflows/rls.yml
name: rls
on: [pull_request]
jobs:
isolation:
runs-on: ubuntu-latest
services:
postgres:
image: postgres:18
env: { POSTGRES_PASSWORD: postgres }
ports: ['5432:5432']
options: >-
--health-cmd pg_isready --health-interval 5s --health-retries 10
steps:
- uses: actions/checkout@v4
- uses: actions/setup-node@v4
with: { node-version: 24 }
- run: npm ci
- run: npm run db:migrate # runs as app_owner
- run: npm run test:rls # runs as app_user
Use in CI the same major Postgres version as production, and run migrations and tests with the same users as production. A test that runs as a superuser always passes, and therefore proves nothing.
What about performance?
With the index on tenant_id and the function inside (SELECT ...), the policy becomes, in practice, one more filter in the query plan, like the WHERE you would write by hand. Check it with EXPLAIN ANALYZE on the heaviest queries. On large tables, composite indexes starting with tenant_id (for example, (tenant_id, created_at)) usually help more than the single-column index.
Also keep Postgres up to date. The November 12, 2026 round brought versions 18.7, 17.12, 16.16 and 15.20, and marked the end of support for PostgreSQL 14.
Checklist
- The application connects with a user that is not a superuser, has no
BYPASSRLSand does not own the tables. - Every table with tenant data has
ENABLEandFORCE ROW LEVEL SECURITY. - Every policy has
USINGandWITH CHECK, and fails closed when no tenant is set. - The tenant comes from the authenticated session and is set with
set_config(..., true)inside a transaction. - Unique constraints include
tenant_id. Views usesecurity_invoker. - Jobs, queues and crons go through the same helper.
- CI has the catalog coverage test, the user test and the cross-tenant break-in test, running on every PR.
With this in place, a forgotten WHERE tenant_id stops being a leak and becomes just one more line that was not needed. And a new unprotected table never reaches production, because the build breaks first.


