Multi-tenant SaaS: isolating tenant data

-- Run as a superuser once (migration). The app never connects as owner or superuser.
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_user LOGIN PASSWORD 'change-me' NOSUPERUSER NOBYPASSRLS;

CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO app_user;
SET ROLE app_owner;

-- Tenant of the current transaction; NULL when not set, so policies match nothing.
CREATE FUNCTION app.current_tenant() RETURNS uuid
  LANGUAGE sql STABLE
  AS $$ SELECT nullif(current_setting('app.tenant_id', true), '')::uuid $$;

CREATE TABLE app.tenants (
  id   uuid PRIMARY KEY,
  name text NOT NULL,
  plan text NOT NULL DEFAULT 'basic'
);

CREATE TABLE app.clients (
  tenant_id uuid NOT NULL DEFAULT app.current_tenant() REFERENCES app.tenants (id),
  id        uuid NOT NULL DEFAULT gen_random_uuid(),
  name      text NOT NULL,
  PRIMARY KEY (tenant_id, id)
);

CREATE TABLE app.bookings (
  tenant_id uuid NOT NULL DEFAULT app.current_tenant(),
  id        uuid NOT NULL DEFAULT gen_random_uuid(),
  client_id uuid NOT NULL,
  starts_at timestamptz NOT NULL,
  PRIMARY KEY (tenant_id, id),
  -- composite key: a booking cannot point at another tenant's client
  FOREIGN KEY (tenant_id, client_id) REFERENCES app.clients (tenant_id, id)
);
CREATE INDEX bookings_tenant_starts_idx ON app.bookings (tenant_id, starts_at);

ALTER TABLE app.tenants  ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.clients  ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.bookings ENABLE ROW LEVEL SECURITY;
-- FORCE: the table owner is subject to the policies too
ALTER TABLE app.tenants  FORCE ROW LEVEL SECURITY;
ALTER TABLE app.clients  FORCE ROW LEVEL SECURITY;
ALTER TABLE app.bookings FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_self ON app.tenants
  USING (id = app.current_tenant());
CREATE POLICY tenant_isolation ON app.clients
  USING (tenant_id = app.current_tenant())
  WITH CHECK (tenant_id = app.current_tenant());
CREATE POLICY tenant_isolation ON app.bookings
  USING (tenant_id = app.current_tenant())
  WITH CHECK (tenant_id = app.current_tenant());

GRANT SELECT ON app.tenants TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.clients, app.bookings TO app_user;
RESET ROLE;

-- Run as the superuser: 200 more tenants, 5 clients each, 200 bookings per client
INSERT INTO app.tenants (id, name)
  SELECT gen_random_uuid(), 'Tenant ' || g FROM generate_series(1, 200) g;
INSERT INTO app.clients (tenant_id, name)
  SELECT t.id, 'Client ' || g FROM app.tenants t, generate_series(1, 5) g;
INSERT INTO app.bookings (tenant_id, client_id, starts_at)
  SELECT c.tenant_id, c.id, '2026-11-01 09:00+05'::timestamptz + g * interval '1 hour'
    FROM app.clients c, generate_series(1, 200) g;
ANALYZE app.tenants, app.clients, app.bookings;

SET ROLE app_user;
BEGIN;
SELECT set_config('app.tenant_id', '11111111-1111-4111-8111-111111111111', true);
EXPLAIN (COSTS OFF)
  SELECT * FROM app.bookings
   WHERE starts_at >= '2026-11-02' AND starts_at < '2026-11-03'
   ORDER BY starts_at;
COMMIT;
RESET ROLE;

$ docker exec kb-tenants sh -c "psql -U postgres -d saas -X -e -f /sql/05-explain.sql 2>&1"
…
EXPLAIN (COSTS OFF)
  SELECT * FROM app.bookings
   WHERE starts_at >= '2026-11-02' AND starts_at < '2026-11-03'
   ORDER BY starts_at;
                                                                                                                         QUERY PLAN                                                                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort
   Sort Key: starts_at
   ->  Bitmap Heap Scan on bookings
         Recheck Cond: ((tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid) AND (starts_at >= '2026-11-02 00:00:00+00'::timestamp with time zone) AND (starts_at < '2026-11-03 00:00:00+00'::timestamp with time zone))
         ->  Bitmap Index Scan on bookings_tenant_starts_idx
               Index Cond: ((tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid) AND (starts_at >= '2026-11-02 00:00:00+00'::timestamp with time zone) AND (starts_at < '2026-11-03 00:00:00+00'::timestamp with time zone))
(6 rows)

docker run -d --rm --name kb-tenants -e POSTGRES_PASSWORD=test \
  -p 127.0.0.1:55439:5432 -v "$PWD/sql:/sql:ro" postgres:17-alpine
sleep 5
docker exec kb-tenants psql -U postgres -X -qc "CREATE DATABASE saas"
docker exec kb-tenants psql -U postgres -d saas -X -v ON_ERROR_STOP=1 -f /sql/01-schema.sql
docker exec kb-tenants psql -U postgres -d saas -X -v ON_ERROR_STOP=1 -f /sql/02-seed.sql

-- Test data, loaded by the superuser (it bypasses RLS).
INSERT INTO app.tenants (id, name) VALUES
  ('11111111-1111-4111-8111-111111111111', 'Lavender Salon'),
  ('22222222-2222-4222-8222-222222222222', 'Mint Barbershop');

INSERT INTO app.clients (tenant_id, id, name) VALUES
  ('11111111-1111-4111-8111-111111111111', 'aaaaaaaa-0000-4000-8000-000000000001', 'Aigerim'),
  ('11111111-1111-4111-8111-111111111111', 'aaaaaaaa-0000-4000-8000-000000000002', 'Dana'),
  ('22222222-2222-4222-8222-222222222222', 'bbbbbbbb-0000-4000-8000-000000000001', 'Timur');

INSERT INTO app.bookings (tenant_id, client_id, starts_at) VALUES
  ('11111111-1111-4111-8111-111111111111', 'aaaaaaaa-0000-4000-8000-000000000001', '2026-11-02 10:00+05'),
  ('22222222-2222-4222-8222-222222222222', 'bbbbbbbb-0000-4000-8000-000000000001', '2026-11-02 11:00+05');

-- Run as app_user: psql -U app_user -d saas -f 03-isolation.sql
\set A '11111111-1111-4111-8111-111111111111'
\set B '22222222-2222-4222-8222-222222222222'

-- 1. No tenant in the transaction: nothing is visible
SELECT count(*) AS clients_without_tenant FROM app.clients;

-- 2. Tenant A sees only its own rows, even when asking for B explicitly
BEGIN;
SELECT set_config('app.tenant_id', :'A', true);
SELECT t.name AS tenant, c.name AS client
  FROM app.clients c JOIN app.tenants t ON t.id = c.tenant_id
  ORDER BY c.name;
SELECT count(*) AS rows_of_b FROM app.clients WHERE tenant_id = :'B';
UPDATE app.clients SET name = 'Hacked' WHERE tenant_id = :'B';
DELETE FROM app.bookings WHERE tenant_id = :'B';
COMMIT;

-- 3. Writing into another tenant is rejected by WITH CHECK
BEGIN;
SELECT set_config('app.tenant_id', :'A', true);
INSERT INTO app.clients (tenant_id, name) VALUES (:'B', 'Mallory');
ROLLBACK;

-- 4. tenant_id defaults to the current tenant; a booking cannot point at B's client
BEGIN;
SELECT set_config('app.tenant_id', :'A', true);
INSERT INTO app.clients (name) VALUES ('Saule') RETURNING tenant_id, name;
INSERT INTO app.bookings (client_id, starts_at)
  VALUES ('bbbbbbbb-0000-4000-8000-000000000001', '2026-11-03 12:00+05');
ROLLBACK;

-- 5. The setting ends with the transaction
SELECT current_setting('app.tenant_id', true) AS after_commit,
       (SELECT count(*) FROM app.clients) AS visible_clients;

$ docker exec kb-tenants sh -c "psql -U app_user -d saas -X -e -f /sql/03-isolation.sql 2>&1"
SELECT count(*) AS clients_without_tenant FROM app.clients;
 clients_without_tenant 
------------------------
                      0
(1 row)

BEGIN;
BEGIN
SELECT set_config('app.tenant_id', '11111111-1111-4111-8111-111111111111', true);
              set_config              
--------------------------------------
 11111111-1111-4111-8111-111111111111
(1 row)

SELECT t.name AS tenant, c.name AS client
  FROM app.clients c JOIN app.tenants t ON t.id = c.tenant_id
  ORDER BY c.name;
     tenant     | client  
----------------+---------
 Lavender Salon | Aigerim
 Lavender Salon | Dana
(2 rows)

SELECT count(*) AS rows_of_b FROM app.clients WHERE tenant_id = '22222222-2222-4222-8222-222222222222';
 rows_of_b 
-----------
         0
(1 row)

UPDATE app.clients SET name = 'Hacked' WHERE tenant_id = '22222222-2222-4222-8222-222222222222';
UPDATE 0
DELETE FROM app.bookings WHERE tenant_id = '22222222-2222-4222-8222-222222222222';
DELETE 0
COMMIT;
COMMIT
BEGIN;
BEGIN
SELECT set_config('app.tenant_id', '11111111-1111-4111-8111-111111111111', true);
              set_config              
--------------------------------------
 11111111-1111-4111-8111-111111111111
(1 row)

INSERT INTO app.clients (tenant_id, name) VALUES ('22222222-2222-4222-8222-222222222222', 'Mallory');
psql:/sql/03-isolation.sql:22: ERROR:  new row violates row-level security policy for table "clients"
ROLLBACK;
ROLLBACK
BEGIN;
BEGIN
SELECT set_config('app.tenant_id', '11111111-1111-4111-8111-111111111111', true);
              set_config              
--------------------------------------
 11111111-1111-4111-8111-111111111111
(1 row)

INSERT INTO app.clients (name) VALUES ('Saule') RETURNING tenant_id, name;
              tenant_id               | name  
--------------------------------------+-------
 11111111-1111-4111-8111-111111111111 | Saule
(1 row)

INSERT 0 1
INSERT INTO app.bookings (client_id, starts_at)
  VALUES ('bbbbbbbb-0000-4000-8000-000000000001', '2026-11-03 12:00+05');
psql:/sql/03-isolation.sql:30: ERROR:  insert or update on table "bookings" violates foreign key constraint "bookings_tenant_id_client_id_fkey"
DETAIL:  Key is not present in table "clients".
ROLLBACK;
ROLLBACK
SELECT current_setting('app.tenant_id', true) AS after_commit,
       (SELECT count(*) FROM app.clients) AS visible_clients;
 after_commit | visible_clients 
--------------+-----------------
              |               0
(1 row)

{
  "name": "tenant-isolation",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "node --test"
  },
  "dependencies": {
    "pg": "^8.23.1"
  }
}

import pg from "pg";

export const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 5 });

const UUID = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;

// Every tenant query runs in a transaction with a transaction-local tenant:
// set_config(..., true) is reset on COMMIT/ROLLBACK, so a pooled connection
// never carries the previous request's tenant.
export async function withTenant(tenantId, fn, db = pool) {
  if (!UUID.test(tenantId)) throw new Error("withTenant: tenantId must be a UUID");
  const client = await db.connect();
  try {
    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").catch(() => {});
    throw err;
  } finally {
    client.release();
  }
}

-- Run as the superuser: psql -U postgres -d saas -f 04-pitfalls.sql
-- 1. Superusers bypass RLS: every tenant is visible
SELECT current_user, count(*) AS clients FROM app.clients;

-- 2. So does any role with BYPASSRLS
CREATE ROLE reporting LOGIN BYPASSRLS;
GRANT USAGE ON SCHEMA app TO reporting;
GRANT SELECT ON app.clients TO reporting;
SET ROLE reporting;
SELECT current_user, count(*) AS clients FROM app.clients;
RESET ROLE;

-- 3. Without FORCE, the table owner bypasses RLS
ALTER TABLE app.clients NO FORCE ROW LEVEL SECURITY;
SET ROLE app_owner;
SELECT current_user, count(*) AS clients_no_force FROM app.clients;
RESET ROLE;
ALTER TABLE app.clients FORCE ROW LEVEL SECURITY;
SET ROLE app_owner;
SELECT current_user, count(*) AS clients_force FROM app.clients;
RESET ROLE;

-- 4. A view checks RLS as its owner: a superuser-owned view leaks everything
CREATE VIEW app.client_names AS SELECT tenant_id, name FROM app.clients;
GRANT SELECT ON app.client_names TO app_user;
SET ROLE app_user;
SELECT count(*) AS via_view_without_tenant FROM app.client_names;
RESET ROLE;
ALTER VIEW app.client_names SET (security_invoker = true);
SET ROLE app_user;
SELECT count(*) AS via_invoker_view FROM app.client_names;
RESET ROLE;

-- 5. Session-level SET survives the "request" on a pooled connection
SET ROLE app_user;
SET app.tenant_id = '11111111-1111-4111-8111-111111111111';   -- request 1, tenant A
SELECT count(*) AS request_1 FROM app.clients;
-- request 2 for another tenant forgets to set its tenant and reuses the connection
SELECT count(*) AS request_2_sees_a FROM app.clients;
RESET app.tenant_id;
RESET ROLE;

$ docker exec kb-tenants sh -c "psql -U postgres -d saas -X -e -f /sql/04-pitfalls.sql 2>&1"
SELECT current_user, count(*) AS clients FROM app.clients;
 current_user | clients 
--------------+---------
 postgres     |       3
(1 row)

CREATE ROLE reporting LOGIN BYPASSRLS;
CREATE ROLE
GRANT USAGE ON SCHEMA app TO reporting;
GRANT
GRANT SELECT ON app.clients TO reporting;
GRANT
SET ROLE reporting;
SET
SELECT current_user, count(*) AS clients FROM app.clients;
 current_user | clients 
--------------+---------
 reporting    |       3
(1 row)

RESET ROLE;
RESET
ALTER TABLE app.clients NO FORCE ROW LEVEL SECURITY;
ALTER TABLE
SET ROLE app_owner;
SET
SELECT current_user, count(*) AS clients_no_force FROM app.clients;
 current_user | clients_no_force 
--------------+------------------
 app_owner    |                3
(1 row)

RESET ROLE;
RESET
ALTER TABLE app.clients FORCE ROW LEVEL SECURITY;
ALTER TABLE
SET ROLE app_owner;
SET
SELECT current_user, count(*) AS clients_force FROM app.clients;
 current_user | clients_force 
--------------+---------------
 app_owner    |             0
(1 row)

RESET ROLE;
RESET
CREATE VIEW app.client_names AS SELECT tenant_id, name FROM app.clients;
CREATE VIEW
GRANT SELECT ON app.client_names TO app_user;
GRANT
SET ROLE app_user;
SET
SELECT count(*) AS via_view_without_tenant FROM app.client_names;
 via_view_without_tenant 
-------------------------
                       3
(1 row)

RESET ROLE;
RESET
ALTER VIEW app.client_names SET (security_invoker = true);
ALTER VIEW
SET ROLE app_user;
SET
SELECT count(*) AS via_invoker_view FROM app.client_names;
 via_invoker_view 
------------------
                0
(1 row)

RESET ROLE;
RESET
SET ROLE app_user;
SET
SET app.tenant_id = '11111111-1111-4111-8111-111111111111';
SET
SELECT count(*) AS request_1 FROM app.clients;
 request_1 
-----------
         2
(1 row)

SELECT count(*) AS request_2_sees_a FROM app.clients;
 request_2_sees_a 
------------------
                2
(1 row)

RESET app.tenant_id;
RESET
RESET ROLE;
RESET

import { test, after } from "node:test";
import assert from "node:assert/strict";
import pg from "pg";
import { pool, withTenant } from "../src/db.js";

const A = "11111111-1111-4111-8111-111111111111";
const B = "22222222-2222-4222-8222-222222222222";
const AIGERIM = "aaaaaaaa-0000-4000-8000-000000000001";

after(() => pool.end());

const names = (tenant) =>
  withTenant(tenant, async (db) =>
    (await db.query("SELECT name FROM app.clients ORDER BY name")).rows.map((r) => r.name));

test("each tenant sees only its own clients", async () => {
  assert.deepEqual(await names(A), ["Aigerim", "Dana"]);
  assert.deepEqual(await names(B), ["Timur"]);
});

test("tenant B cannot read tenant A's row even by primary key", async () => {
  const rows = await withTenant(B, async (db) =>
    (await db.query("SELECT * FROM app.clients WHERE id = $1", [AIGERIM])).rows);
  assert.deepEqual(rows, []);
});

test("writing a row into another tenant is rejected (42501)", async () => {
  await assert.rejects(
    withTenant(A, (db) =>
      db.query("INSERT INTO app.clients (tenant_id, name) VALUES ($1, 'Mallory')", [B])),
    { code: "42501" },
  );
});

test("without a tenant nothing is visible", async () => {
  const { rows } = await pool.query("SELECT count(*)::int AS n FROM app.clients");
  assert.equal(rows[0].n, 0);
});

test("a pooled connection does not carry the tenant into the next request", async () => {
  const single = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 1 });
  try {
    await withTenant(A, (db) => db.query("SELECT 1"), single);
    const { rows } = await single.query(
      "SELECT current_setting('app.tenant_id', true) AS t, (SELECT count(*)::int FROM app.clients) AS n");
    assert.deepEqual(rows[0], { t: "", n: 0 });
  } finally {
    await single.end();
  }
});

test("concurrent requests of different tenants stay isolated", async () => {
  const seen = await Promise.all(
    Array.from({ length: 40 }, (_, i) => {
      const tenant = i % 2 ? A : B;
      return withTenant(tenant, async (db) => {
        await db.query("SELECT pg_sleep(0.01)");
        const { rows } = await db.query("SELECT DISTINCT tenant_id FROM app.clients");
        return { tenant, ids: rows.map((r) => r.tenant_id) };
      });
    }),
  );
  for (const { tenant, ids } of seen) assert.deepEqual(ids, [tenant]);
});

test("guard: RLS is enabled and forced everywhere, the app role cannot bypass it", async () => {
  const unprotected = await pool.query(`
    SELECT c.relname
      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 = 'app' AND c.relkind = 'r'
       AND NOT (c.relrowsecurity AND c.relforcerowsecurity)`);
  assert.deepEqual(unprotected.rows, []);

  const ownerViews = await pool.query(`
    SELECT c.relname
      FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
     WHERE n.nspname = 'app' AND c.relkind = 'v'
       AND NOT coalesce('security_invoker=true' = ANY (c.reloptions), false)`);
  assert.deepEqual(ownerViews.rows, []);

  const role = await pool.query(
    "SELECT rolsuper, rolbypassrls FROM pg_roles WHERE rolname = current_user");
  assert.deepEqual(role.rows[0], { rolsuper: false, rolbypassrls: false });
});

$ DATABASE_URL=postgres://app_user:change-me@127.0.0.1:55439/saas npm test
> tenant-isolation@1.0.0 test
> node --test

✔ each tenant sees only its own clients (19.0221ms)
✔ tenant B cannot read tenant A's row even by primary key (2.6029ms)
✔ writing a row into another tenant is rejected (42501) (2.7856ms)
✔ without a tenant nothing is visible (1.1808ms)
✔ a pooled connection does not carry the tenant into the next request (9.664ms)
✔ concurrent requests of different tenants stay isolated (109.7941ms)
✔ guard: RLS is enabled and forced everywhere, the app role cannot bypass it (5.9816ms)
ℹ tests 7
ℹ suites 0
ℹ pass 7
ℹ fail 0
ℹ cancelled 0
ℹ skipped 0
ℹ todo 0
ℹ duration_ms 268.1635