Multi-Tenant SaaS Architecture: Postgres RLS Tested 6 Ways

Multi-tenant SaaS architecture means one application serves many customers (tenants) from shared infrastructure, with every request carrying a tenant context and the database refusing to return another tenant's rows. For a new SaaS we'd start in the pool model: one shared schema, a tenant_id on every row, and a Postgres row-level security policy that filters on it, with a silo for the few tenants who contractually need their own database.

Row-level security (RLS) is the core of multi-tenant Postgres, and it fails in quiet ways. We ran six tests on a local Postgres (PGlite 0.5.8, ElectricSQL's WebAssembly build of PostgreSQL, reporting 18.3) with seeded synthetic data, and three leaked. The superuser, and the table owner until we added FORCE ROW LEVEL SECURITY, saw all 100,000 rows, and a view the superuser created still handed all 100,000 to the application role after FORCE. A session-level SET leaked tenant 1's 39,798 rows to the next request on the same connection. A global UNIQUE(email) and a plain foreign key let tenant 2 detect tenant 1's data.

Designing multi-tenant data models is part of our API and SaaS development work. This post is for a team designing a new SaaS. If you're moving an existing single-tenant app over, start with re-architecting a SaaS after product-market fit. Every Postgres behavior below was checked against the PostgreSQL 18 docs on 2026-10-13, and the full script is at the end.

How multi-tenant SaaS architecture works

In a multi-tenant SaaS, every request resolves a tenant from a verified identity, sets that tenant in the database transaction, and lets row-level security filter every query. Concretely, authentication resolves the user and the tenant they act for, and middleware opens a database transaction that sets the tenant context (app.tenant_id) as its first statement. The tenant usually arrives as a subdomain, a claim in the JWT or a segment of the URL path, and whichever you pick, the server should accept it only after checking it against the verified identity, so nobody switches tenants by editing a hostname or a path. Every query in that transaction runs as an application role that is subject to RLS, so the policy filters each table to that tenant even when a query forgets its WHERE tenant_id. The same tenant_id then keys everything else: per-tenant rate limits, the usage ledger that billing reads, cache keys, file paths, search filters and log fields. Isolation lives in the database and in one middleware layer.

Pool, bridge or silo: which tenancy model to start with

Start with pool unless a customer contract says otherwise. AWS's SaaS Lens names three models: in the silo model "tenants are provided dedicated resources"; in the pool model "tenants share resources"; and the bridge model acknowledges that many systems are "a mixed mode where some of the system is implemented in a silo model and some is in a pooled model." AWS also stresses that a silo still runs on shared identity, onboarding and operations; separate onboarding per customer is a managed service, not SaaS.

Our recommendation: pool everyone by default, keep the code silo-ready (every query tenant-scoped, the tenant's database location looked up rather than hard-coded), and move a tenant into a silo when one of the triggers further down applies.

Shared schema vs schema per tenant vs database per tenant

For a new SaaS, a shared schema with a tenant_id column is the cheapest to run, and RLS is what makes it safe. The three database layouts trade isolation for operating cost.

A shared schema is one set of tables with one migration, one backup and one connection pool, and its isolation depends entirely on the policy and the tests in this post. Schema per tenant repeats the same tables in a Postgres schema for each tenant, so every migration runs once per schema, tables, indexes and catalog entries multiply with the tenant count, and cross-tenant reporting needs a union across schemas. Database per tenant gives the strongest isolation and easy per-tenant restore, but you run a connection pool and a migration per database, and onboarding has to create one.

We didn't benchmark migration fan-out across many schemas, so the operational cost above is the qualitative case. The upgrade path matters more early on: going from shared schema to a silo for one tenant is a data copy filtered by tenant_id, while going the other way means merging tables and remapping keys.

How to carry tenant context from the request to the database

Set the tenant context once per transaction with set_config('app.tenant_id', $1, true), from the tenant your authentication resolved, never from a header or body field the client controls. The third argument matters: per the Postgres configuration functions docs, if is_local is true, "the new value will only apply during the current transaction." That's SET LOCAL with one practical difference we hit in the test run: SET LOCAL app.tenant_id = $1 with a bind parameter failed with syntax error at or near "$1", while set_config accepts one, so you never build the tenant id into SQL text. The tenant record your authentication resolves is also where you pin each customer's API version.

// Illustrative, node-postgres. Every request's queries run in one transaction
// whose first statement sets the tenant context.
import { Pool, PoolClient } from "pg";
const pool = new Pool(); // connects as app_user: not the table owner, NOBYPASSRLS

export async function withTenant<T>(tenantId: string, fn: (db: PoolClient) => Promise<T>) {
  const client = await pool.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");
    throw err;
  } finally {
    client.release(); // the setting ended with the transaction
  }
}

Avoid session-level SET behind any pool. The Postgres SET docs say a committed SET persists "until the end of the session, unless overridden by another SET", and a pooled connection's session outlives the request. PgBouncer's features page lists SET/RESET as "Never" supported in transaction pooling, where "a server connection is assigned to a client only during a transaction." A transaction-local setting lives exactly as long as that assignment. We didn't run PgBouncer for this post; Test 4 below shows the same leak on PGlite's single connection.

Postgres row-level security for multi-tenant SaaS

The setup is three pieces: a policy on every tenant table, an application role the policy applies to, and FORCE ROW LEVEL SECURITY so the table owner is covered too. Postgres's Row Security Policies page sets the rule: "Superusers and roles with the BYPASSRLS attribute always bypass the row security system when accessing a table. Table owners normally bypass row security as well, though a table owner can choose to be subject to row security with ALTER TABLE ... FORCE ROW LEVEL SECURITY."

CREATE ROLE app_owner NOLOGIN;              -- owns tables, runs migrations
CREATE ROLE app_user LOGIN NOBYPASSRLS;     -- the application connects as this

ALTER TABLE documents OWNER TO app_owner;
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
  USING      (tenant_id = nullif(current_setting('app.tenant_id', true), '')::int)
  WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::int);

GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO app_user;

USING filters the rows a query can see or change; per CREATE POLICY, rows where it returns false or null are, typically, "silently suppressed." WITH CHECK validates new rows, and an error "will be thrown if the expression evaluates to false or null." Test 3 shows why the policy wraps the setting in nullif(..., ''): an ended setting reads as an empty string, not NULL. If you enable RLS and create no policy, Postgres applies a default-deny policy, per ALTER TABLE.

AWS published the same pattern in its Database Blog post Multi-tenant data isolation with PostgreSQL Row Level Security (Michael Beardsley, May 18, 2020), which also warns that "using session variables may be incompatible with server-side connection pooling such as pgBouncer."

Catch a table shipped without RLS in CI

A new table added in a later migration starts with RLS off, and nothing complains. We put a catalog query in the test script that lists every table in the app schema without both ENABLE and FORCE; run the same query in CI and fail the build on any name you haven't allowlisted:

SELECT c.relname, c.relrowsecurity AS enabled, c.relforcerowsecurity AS forced
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p')
  AND NOT (c.relrowsecurity AND c.relforcerowsecurity)
ORDER BY 1;

On the 2026-10-13 run it printed documents_plain (enabled=false, forced=false), tenants (enabled=false, forced=false). Both are right: documents_plain is the test's deliberate no-RLS copy, and tenants is the tenant registry, which you may choose to leave unscoped and put on the allowlist. The query checks the flags, not the policies. A table with RLS enabled and no policy is default-deny, so that mistake shows up as a broken feature rather than a leak.

Six RLS tests, three leaks

We tested a query with no WHERE tenant_id (RLS fixes it), roles and views that bypass the policy, a missing tenant setting, a session SET leaking across pooled requests, a global unique key and plain foreign key revealing other tenants, and indexes that don't lead with tenant_id; the bypassing roles and views, the session SET and the constraints leaked data across tenants.

All six tests come from one script. We first ran it on 2026-10-12 and re-ran it on 2026-10-13, after adding the view, CI and offboarding checks, and every earlier number came back identical. Both runs used Node 24.20.0 and @electric-sql/pglite 0.5.8, which reports "PostgreSQL 18.3" and connects as the superuser postgres. The data is synthetic and seeded (seed 20261012): 100,000 documents rows across 50 tenants, where each row had a 40% chance of going to tenant 1. Realized: tenant 1 holds 39,798 rows (39.8%) and tenant 7, our small tenant, holds 1,265. Each test starts on a fresh in-memory database. PGlite's docs say it "only has a single exclusive connection to the database," which makes it a fair stand-in for one pooled connection.

Test 1: the forgotten tenant filter

RLS turns a missing WHERE tenant_id from a data leak into a correct query. As app_user with tenant 7 set, the same unfiltered statements ran against an RLS table and an identical table without RLS:

The unfiltered UPDATE is the incident a policy prevents: without RLS it rewrote every tenant's documents.

Test 2: which roles bypass RLS

The superuser, the owner without FORCE, and a BYPASSRLS role all saw every row, tenant context or not. Rows visible from SELECT count(*) FROM documents:

That matches the Postgres docs exactly, and it's an easy way for RLS to work in tests and leak in production: the app connects as the role that ran the migrations, which owns the tables. FORCE fixed the owner. Nothing fixes the superuser or a BYPASSRLS role, so keep those out of the request path.

Views carry the same problem one step removed. We created three views over documents and read each one as app_user with tenant 7 set:

A view queries its tables with its owner's rights unless you say otherwise. The CREATE VIEW docs put it this way: "If any of the underlying base relations has row-level security enabled, then by default, the row-level security policies of the view owner are applied, and access to any additional relations referred to by those policies is determined by the permissions of the view owner." So FORCE rescued the owner's view, because the owner is now subject to the policy, but did nothing for the view the superuser made. That's the view you get when a migration runs as postgres. Create every view over tenant tables WITH (security_invoker = true), which makes Postgres apply "the policies and permissions of the invoking user" instead. The option arrived in PostgreSQL 15; the 15.0 release notes describe it as "Allow table accesses done by a view to optionally be controlled by privileges of the view's caller," and the PostgreSQL 14 CREATE VIEW page doesn't list it.

Test 3: a missing tenant setting, fail closed or fail open

None of the three policy styles failed open, but two of them failed with errors you need to expect. Postgres's docs say current_setting "throws an error unless missing_ok is supplied and is true (in which case NULL is returned)." The gotcha is the second column: in our run, once a transaction-local setting had ended, the setting read as an empty string, not NULL.

An error is a safe failure, but an intermittent one: the same code path errors or returns nothing depending on whether an earlier transaction on that connection set the tenant. The nullif version gives one predictable answer, zero rows, which is why our policy uses it. Pair it with a check in middleware that refuses to run a query without a tenant, so "zero rows" never reaches a user as "you have no documents."

Test 4: tenant context leaking on a shared connection

A session-level SET leaked every one of tenant 1's 39,798 rows to the next request. On one connection, request A set tenant 1 and counted documents; request B then forgot to set any tenant and counted again:

Two rows deserve a second look. set_config(..., false) is a session SET under another name. And SET LOCAL outside a transaction printed WARNING: SET LOCAL can only be used in transaction blocks and set nothing, exactly as the SET docs describe ("emits a warning and otherwise has no effect"); in autocommit code that warning is easy to miss in logs. The 0 for request B depends on the nullif policy from Test 3; with the plain missing_ok policy B gets the empty-string error instead.

Test 5: unique constraints and foreign keys leak across tenants

Constraints ignore RLS by design, and our test turned both kinds into cross-tenant leaks. The Postgres docs are explicit: "Referential integrity checks, such as unique or primary key constraints and foreign key references, always bypass row security," and schemas must avoid "covert channel" leaks through them. As app_user, tenant 2 could not see tenant 1's user ana@example.com (0 rows) or tenant 1's folder (0 rows), then tried:

Postgres does try: under RLS the duplicate-key error came back with no DETAIL, while the same insert by the superuser printed DETAIL: Key (email)=(ana@example.com) already exists. The error itself is still the leak, though, because tenant 2 learns that an address it can't see is registered. The plain foreign key is worse: tenant 2 created a row pointing at another tenant's folder, and comparing folder 1 (accepted) with folder 999 (rejected) reveals which ids exist.

The fix is to make tenant_id part of every unique and foreign key. That needs UNIQUE (tenant_id, id) on the parent table so a composite foreign key can reference it. If emails must be globally unique (one login across tenants), keep that table outside tenant scope and give the signup flow a response that doesn't confirm existence. AWS's RLS recommendations for pooled Postgres show a tenant_user table with email VARCHAR(255) NOT NULL UNIQUE; that's a fair design if each email belongs to one tenant, but under RLS it's exactly the existence check above.

Test 6: RLS performance with and without tenant_id-leading indexes

An index that doesn't lead with tenant_id makes small tenants pay for big tenants' rows. We ran EXPLAIN (ANALYZE, TIMING OFF) on SELECT id, created_at FROM documents ORDER BY created_at DESC LIMIT 20 as app_user, for small tenant 7 and big tenant 1:

With only a created_at index, tenant 7's query walked 1,426 index entries to keep 20, while tenant 1 needed 49, because the newest rows in the index mostly belong to other tenants and the scan has to step over them. With the composite index the policy became an Index Cond and both tenants read exactly 20 rows.

Indexing and performance under RLS

Lead every index on a tenant table with tenant_id, because the policy adds tenant_id = ... to every query whether you wrote it or not. Test 6 shows the payoff: with (tenant_id, created_at), Postgres used the policy as an index condition, and the work stopped depending on how much data other tenants had.

We also tried wrapping the setting in a subquery, tenant_id = (SELECT nullif(current_setting(...), '')::int). It changed the plan text (an InitPlan computed once, shown as (InitPlan 1).col1), but in all six cases it chose the same scan and returned and removed the same row counts as the direct call. We didn't time either variant, so we make no speed claim for it on this query. Supabase's RLS performance guidance recommends the same wrapping for functions like auth.uid(), because the initPlan lets Postgres "cache" the result per statement rather than calling the function on each row; our plans show that InitPlan, but the row counts didn't change.

The (created_at) result is the noisy-neighbor problem at the storage layer: tenant 7's latest-20 query had to step over 1,406 of other tenants' rows because tenant 1 wrote more. The same thing happens at the API layer, which is why per-tenant limits matter; our post on SaaS API rate limiting covers that half. In the database, composite indexes, per-tenant statement timeouts for heavy endpoints, and moving very large tenants to a silo cover the rest. When the pool outgrows one server, sharding by tenant_id with an extension such as Citus keeps the same schema; we haven't tested that here.

Tenant-aware everything else: jobs, caches, files, search, logs

RLS only protects Postgres tables, so everything outside the database needs its own tenant key. The checklist we'd use on a new build:

• Background jobs should carry tenant_id in the payload, and the worker should open its transaction with set_config('app.tenant_id', ..., true) the way a request does. A worker that runs as the table owner skips the policy unless FORCE is on (Test 2).
• Prefix every cache key with the tenant (t:{tenant_id}:...). A cache hit never reaches the database, so the policy never sees it.
• Store files under a tenant prefix and issue short-lived signed URLs only after checking the tenant.
• A search engine doesn't know your RLS policy. Filter on a tenant field in every search query, or keep an index per tenant.
• Logs, traces and error reports get tenant_id as a structured field; keep row data out of error messages.
• Admin and support tools are the legitimate BYPASSRLS users. Give them a separate role and connection, and audit every query.
• SECURITY DEFINER functions need the same care as admin tools: per CREATE FUNCTION, such a function "is to be executed with the privileges of the user that owns it," so one owned by a superuser, or by the table owner without FORCE, reads past the policy just like the views in Test 2. The docs add that its "search_path should be set to exclude any schemas writable by untrusted users," with pg_temp written last; set it in the function definition. We didn't test functions in this run.
• The per-tenant rate limiter and the usage ledger should key on the same tenant_id, so limits, usage and invoices agree.
• Run the integration suite as app_user. Run it as a superuser and every test in this post passes for the wrong reason.

A missing or weak policy is a known failure in generated apps: CVE-2025-48757 at NVD describes "an insufficient database Row-Level Security policy" in apps generated by one platform, a record the supplier disputes. We cover that failure class in taking a vibe-coded prototype to production.

When to move a tenant to its own silo

Move a tenant to a silo when a requirement can't be met in the pool. The triggers we'd write down at design time:

1. The customer's contract requires a dedicated database or account, or its own encryption keys.
2. The tenant's data must stay in a region where you don't run the pool.
3. One tenant's queries dominate the shared database even with composite indexes and per-tenant limits. Pick a threshold you can measure, such as share of rows or share of query time, and record it.
4. The tenant needs its own maintenance window, restore point or upgrade schedule.

Plan tiers often map onto the same split: standard plans in the pool, a bridge or silo for enterprise contracts that ask for one.

Because every query already carries tenant_id, moving one tenant is a filtered copy plus a routing entry that says which database serves it. A silo also adds a database to your bill for each tenant in it, a line worth having in the cost to build a SaaS MVP before you promise one. Scope this before launch, alongside the other early decisions in how to scope a SaaS MVP, even if no tenant needs a silo yet.

Deleting or exporting one tenant's data

Offboarding in the pool is one delete if tenant_id is in every foreign key. We added this to the test script: a tenants table, projects referencing it, and tasks with a composite foreign key (tenant_id, project_id) to projects, all with ON DELETE CASCADE. Before, two tenants held 20 projects and 1,000 tasks. After DELETE FROM tenants WHERE id = 1, tenant 1 had 0 projects and 0 tasks left, and tenant 2 still had all 500 of its tasks.

Export uses the same tenant filter as everything else: run it as app_user with that tenant's context set, and RLS limits the dump to that tenant's rows. Caches, files and search indexes sit outside Postgres, so delete or export them by the same tenant prefix.

Per-tenant restore is the pool's weak spot. A point-in-time restore brings back the whole database, so the approach we'd take is to restore to a side database and copy that tenant's rows back, filtered by tenant_id. That part is reasoning, not something we tested for this post.

What this test doesn't prove

• The data is 100,000 generated, seeded rows, not production, client or Keplaris product data. We run no multi-tenant SaaS of our own.
• PGlite is one process with one connection, not a server. Behavior matched the PostgreSQL 18 docs we re-read on 2026-10-13, but those docs describe 18.6 while PGlite reports 18.3, and plan choices on a real server with real statistics and memory settings can differ.
• Test 4 simulates a shared connection. We didn't run PgBouncer, Prisma, Drizzle or any other client library.
• Test 6 reports plan shape and row counts only, with no timings.
• The offboarding test ran as the superuser on two small tenants. It checks that the composite foreign keys cascade, not RLS, and we didn't test export or restore.
• This isn't a security review. RLS is one layer; authentication, authorization inside a tenant (roles and permissions), secrets and backups are separate work.

The full test script

Save it as rls-tests.mjs in an empty directory, run npm install --save-exact @electric-sql/pglite@0.5.8, then node rls-tests.mjs. It prints every number in this post.

// rls-tests.mjs: six tests of Postgres row-level security in a multi-tenant SaaS, plus offboarding.
// Run: npm install --save-exact @electric-sql/pglite@0.5.8 && node rls-tests.mjs
// Synthetic data, seeded. Each test gets a fresh in-memory database, so session state
// from one test can't leak into the next.
import { PGlite } from "@electric-sql/pglite";

const SEED = 20261012;
const TENANTS = 50;
const DOCS = 100_000;
const BIG_TENANT = 1; // holds about 40% of rows
const SMALL_TENANT = 7;

// mulberry32: tiny deterministic PRNG
function rng(seed) {
  let a = seed >>> 0;
  return () => {
    a = (a + 0x6d2b79f5) >>> 0;
    let t = a;
    t = Math.imul(t ^ (t >>> 15), t | 1);
    t ^= t + Math.imul(t ^ (t >>> 7), t | 61);
    return ((t ^ (t >>> 14)) >>> 0) / 4294967296;
  };
}

// Generate the data once, in JS, from the seed.
const rand = rng(SEED);
const docTenant = [], docCreated = [];
const start = Date.UTC(2026, 0, 1);
for (let i = 0; i < DOCS; i++) {
  docTenant.push(rand() < 0.4 ? BIG_TENANT : 2 + Math.floor(rand() * (TENANTS - 1)));
  docCreated.push(new Date(start + Math.floor(rand() * 270 * 86400_000)).toISOString());
}

// The tenant context policy: NULL (not '') when the setting is missing or reset.
const TENANT = `nullif(current_setting('app.tenant_id', true), '')::int`;

async function fresh({ policyExpr = TENANT, force = false } = {}) {
  const db = new PGlite();
  await db.exec(`
    CREATE ROLE app_owner NOLOGIN;              -- owns the tables (runs migrations)
    CREATE ROLE app_user NOLOGIN NOBYPASSRLS;   -- what the app connects as
    CREATE ROLE app_admin NOLOGIN BYPASSRLS;    -- e.g. an internal support tool

    CREATE TABLE tenants (id int PRIMARY KEY);
    CREATE TABLE documents (
      id bigserial PRIMARY KEY,
      tenant_id int NOT NULL REFERENCES tenants(id),
      title text NOT NULL,
      created_at timestamptz NOT NULL
    );
    CREATE TABLE documents_plain (LIKE documents INCLUDING ALL); -- same rows, no RLS
  `);
  await db.query(`INSERT INTO tenants SELECT generate_series(1, $1)`, [TENANTS]);
  await db.query(
    `INSERT INTO documents (tenant_id, title, created_at)
     SELECT t, 'doc', c FROM unnest($1::int[], $2::timestamptz[]) AS u(t, c)`,
    [docTenant, docCreated]
  );
  await db.exec(`
    INSERT INTO documents_plain SELECT * FROM documents;
    ALTER TABLE tenants OWNER TO app_owner;
    ALTER TABLE documents OWNER TO app_owner;
    ALTER TABLE documents_plain OWNER TO app_owner;
    ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
    ${force ? "ALTER TABLE documents FORCE ROW LEVEL SECURITY;" : ""}
    CREATE POLICY tenant_isolation ON documents
      USING (tenant_id = ${policyExpr})
      WITH CHECK (tenant_id = ${policyExpr});
    GRANT SELECT, INSERT, UPDATE, DELETE ON documents, documents_plain TO app_user, app_admin;
    GRANT USAGE ON SEQUENCE documents_id_seq TO app_user;
    GRANT SELECT ON tenants TO app_user;
    ANALYZE;
  `);
  return db;
}

const fmt = (e) => `ERROR ${e.code}: ${e.message}` + (e.detail ? ` | DETAIL: ${e.detail}` : " | (no DETAIL)");
const count = async (db, sql) => Number((await db.query(sql)).rows[0].n);
const tryCount = async (db, sql) => {
  try { return String(await count(db, sql)); } catch (e) { return `ERROR: ${e.message}`; }
};

// ---------- setup facts ----------
{
  const db = await fresh();
  const v = (await db.query(`SELECT version() AS v, current_user AS u,
    (SELECT rolsuper FROM pg_roles WHERE rolname = current_user) AS su`)).rows[0];
  console.log(`Run ${new Date().toISOString()} | Node ${process.version} | PGlite 0.5.8`);
  console.log(`${v.v.split(" on ")[0]} | connected as ${v.u} (superuser: ${v.su})`);
  console.log(`Seed ${SEED}: ${DOCS} documents across ${TENANTS} tenants`);
  const big = await count(db, `SELECT count(*) AS n FROM documents WHERE tenant_id = ${BIG_TENANT}`);
  const small = await count(db, `SELECT count(*) AS n FROM documents WHERE tenant_id = ${SMALL_TENANT}`);
  console.log(`tenant ${BIG_TENANT}: ${big} rows (${(100 * big / DOCS).toFixed(1)}%), tenant ${SMALL_TENANT}: ${small} rows\n`);
  await db.close();
}

// ---------- Test 1: the forgotten tenant filter ----------
{
  const db = await fresh();
  await db.exec(`SET ROLE app_user; SET app.tenant_id = '${SMALL_TENANT}';`);
  console.log("TEST 1: app query with no WHERE tenant_id, as app_user, tenant", SMALL_TENANT);
  console.log("  SELECT count(*) FROM documents_plain (no RLS):", await count(db, "SELECT count(*) AS n FROM documents_plain"));
  console.log("  SELECT count(*) FROM documents (RLS):        ", await count(db, "SELECT count(*) AS n FROM documents"));
  await db.exec("BEGIN");
  const u1 = await db.query("UPDATE documents_plain SET title = 'oops'");
  const u2 = await db.query("UPDATE documents SET title = 'oops'");
  await db.exec("ROLLBACK");
  console.log("  UPDATE ... SET title = 'oops' (no WHERE): plain", u1.affectedRows, "| RLS", u2.affectedRows);
  try {
    await db.query(`INSERT INTO documents (tenant_id, title, created_at) VALUES (${BIG_TENANT}, 'x', now())`);
    console.log("  INSERT for another tenant: accepted");
  } catch (e) { console.log("  INSERT for another tenant:", "ERROR:", e.message); }
  console.log();
  await db.close();
}

// ---------- Test 2: who bypasses RLS ----------
console.log("TEST 2: rows visible in documents (no WHERE), by role");
for (const force of [false, true]) {
  const db = await fresh({ force });
  for (const role of ["postgres", "app_owner", "app_admin", "app_user"]) {
    const setRole = role === "postgres" ? "RESET ROLE" : `SET ROLE ${role}`;
    await db.exec(`RESET ROLE; RESET app.tenant_id; ${setRole};`);
    const unset = await tryCount(db, "SELECT count(*) AS n FROM documents");
    await db.exec(`SET app.tenant_id = '${SMALL_TENANT}'`);
    const set = await tryCount(db, "SELECT count(*) AS n FROM documents");
    console.log(`  FORCE=${force ? "on " : "off"} ${role.padEnd(9)} tenant unset: ${unset.padEnd(6)} tenant ${SMALL_TENANT}: ${set}`);
  }
  await db.close();
}
// Views run with their owner's rights by default, so the owner's RLS status decides.
console.log("TEST 2b: app_user reads through a view, tenant", SMALL_TENANT);
for (const force of [false, true]) {
  const db = await fresh({ force });
  await db.exec(`
    CREATE VIEW docs_by_superuser AS SELECT * FROM documents;
    GRANT CREATE ON SCHEMA public TO app_owner; -- Postgres 15+ no longer grants this to everyone
    SET ROLE app_owner;
    CREATE VIEW docs_by_owner AS SELECT * FROM documents;
    CREATE VIEW docs_invoker WITH (security_invoker = true) AS SELECT * FROM documents;
    GRANT SELECT ON docs_by_owner, docs_invoker TO app_user;
    RESET ROLE;
    GRANT SELECT ON docs_by_superuser TO app_user;
    SET ROLE app_user; SET app.tenant_id = '${SMALL_TENANT}';
  `);
  const r = [];
  for (const v of ["docs_by_superuser", "docs_by_owner", "docs_invoker"])
    r.push(`${v}: ${await tryCount(db, `SELECT count(*) AS n FROM ${v}`)}`);
  console.log(`  FORCE=${force ? "on " : "off"} ${r.join(" | ")}`);
  await db.close();
}

// CI guard: every table in the app schema should have RLS enabled and forced.
{
  const db = await fresh({ force: true });
  const r = await db.query(`
    SELECT c.relname, c.relrowsecurity AS enabled, c.relforcerowsecurity AS forced
    FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p')
      AND NOT (c.relrowsecurity AND c.relforcerowsecurity)
    ORDER BY 1`);
  console.log("  CI check, tables missing ENABLE or FORCE:", r.rows.map((x) => `${x.relname} (enabled=${x.enabled}, forced=${x.forced})`).join(", "));
  await db.close();
}
console.log();

// ---------- Test 3: missing tenant context, fail closed or open ----------
console.log("TEST 3: tenant context missing, as app_user");
const variants = [
  ["current_setting('app.tenant_id')::int", "current_setting('app.tenant_id')::int"],
  ["current_setting('app.tenant_id', true)::int", "current_setting('app.tenant_id', true)::int"],
  ["nullif(current_setting('app.tenant_id', true), '')::int", TENANT],
];
for (const [label, expr] of variants) {
  const db = await fresh({ policyExpr: expr });
  await db.exec("SET ROLE app_user");
  const never = await tryCount(db, "SELECT count(*) AS n FROM documents");
  const raw1 = (await db.query(`SELECT current_setting('app.tenant_id', true) AS s`)).rows[0].s;
  await db.exec(`BEGIN; SET LOCAL app.tenant_id = '${SMALL_TENANT}'; COMMIT;`);
  const raw2 = (await db.query(`SELECT current_setting('app.tenant_id', true) AS s`)).rows[0].s;
  const after = await tryCount(db, "SELECT count(*) AS n FROM documents");
  console.log(`  policy: ${label}`);
  console.log(`    never set (setting reads ${JSON.stringify(raw1)}): ${never}`);
  console.log(`    after a SET LOCAL ended (setting reads ${JSON.stringify(raw2)}): ${after}`);
  await db.close();
}
console.log();

// ---------- Test 4: tenant context leaking on a shared connection ----------
console.log("TEST 4: one connection, request A (tenant", BIG_TENANT + ") then request B (forgets to set a tenant)");
{
  const db = await fresh();
  await db.exec("SET ROLE app_user");
  const q = "SELECT count(*) AS n FROM documents";
  // session-level SET
  await db.exec(`SET app.tenant_id = '${BIG_TENANT}'`);
  const a1 = await count(db, q);
  const b1 = await count(db, q);
  console.log(`  SET (session):              A sees ${a1} | B sees ${b1}`);
  await db.exec("RESET app.tenant_id");
  // SET LOCAL inside a transaction
  await db.exec(`BEGIN; SET LOCAL app.tenant_id = '${BIG_TENANT}';`);
  const a2 = await count(db, q);
  await db.exec("COMMIT");
  const b2 = await count(db, q);
  console.log(`  SET LOCAL in a transaction: A sees ${a2} | B sees ${b2}`);
  // set_config(..., is_local => true)
  await db.exec("BEGIN");
  await db.query("SELECT set_config('app.tenant_id', $1, true)", [String(BIG_TENANT)]);
  const a3 = await count(db, q);
  await db.exec("COMMIT");
  const b3 = await count(db, q);
  console.log(`  set_config(.., true):       A sees ${a3} | B sees ${b3}`);
  // set_config(..., false) is a session SET in disguise
  await db.query("SELECT set_config('app.tenant_id', $1, false)", [String(BIG_TENANT)]);
  const a4 = await count(db, q);
  const b4 = await count(db, q);
  console.log(`  set_config(.., false):      A sees ${a4} | B sees ${b4}`);
  await db.exec("RESET app.tenant_id");
  // SET LOCAL outside a transaction block
  const notices = [];
  await db.query(`SET LOCAL app.tenant_id = '${BIG_TENANT}'`, [], { onNotice: (n) => notices.push(`${n.severity}: ${n.message}`) });
  const a5 = await count(db, q);
  console.log(`  SET LOCAL with no BEGIN:    ${notices.join("; ") || "no notice"}; A then sees ${a5}`);
  // SET can't take a bind parameter; set_config can
  await db.exec("BEGIN");
  const p = await db.query("SET LOCAL app.tenant_id = $1", [String(BIG_TENANT)]).then(() => "ok", (e) => `ERROR ${e.code}: ${e.message}`);
  await db.exec("ROLLBACK");
  console.log(`  SET LOCAL app.tenant_id = $1 (bind parameter): ${p}`);
  console.log();
  await db.close();
}

// ---------- Test 5: unique constraints and foreign keys across tenants ----------
console.log("TEST 5: constraints across tenants, as app_user");
{
  const db = await fresh();
  await db.exec(`
    CREATE TABLE users_global (id serial PRIMARY KEY, tenant_id int NOT NULL, email text NOT NULL UNIQUE);
    CREATE TABLE users_scoped (id serial PRIMARY KEY, tenant_id int NOT NULL, email text NOT NULL,
                               UNIQUE (tenant_id, email));
    CREATE TABLE folders (id serial PRIMARY KEY, tenant_id int NOT NULL, name text,
                          UNIQUE (tenant_id, id));
    CREATE TABLE files_fk (id serial PRIMARY KEY, tenant_id int NOT NULL,
                           folder_id int REFERENCES folders(id));
    CREATE TABLE files_fk_scoped (id serial PRIMARY KEY, tenant_id int NOT NULL, folder_id int,
                           FOREIGN KEY (tenant_id, folder_id) REFERENCES folders (tenant_id, id));
    INSERT INTO users_global (tenant_id, email) VALUES (1, 'ana@example.com');
    INSERT INTO users_scoped (tenant_id, email) VALUES (1, 'ana@example.com');
    INSERT INTO folders (tenant_id, name) VALUES (1, 'Tenant 1 board pack');  -- id 1
  `);
  for (const t of ["users_global", "users_scoped", "folders", "files_fk", "files_fk_scoped"]) {
    await db.exec(`
      ALTER TABLE ${t} OWNER TO app_owner;
      ALTER TABLE ${t} ENABLE ROW LEVEL SECURITY;
      CREATE POLICY tenant_isolation ON ${t} USING (tenant_id = ${TENANT}) WITH CHECK (tenant_id = ${TENANT});
      GRANT SELECT, INSERT ON ${t} TO app_user;
      GRANT USAGE ON SEQUENCE ${t}_id_seq TO app_user;
    `);
  }
  await db.exec(`SET ROLE app_user; SET app.tenant_id = '2';`);
  console.log("  tenant 2 SELECT ana@example.com:", await count(db, "SELECT count(*) AS n FROM users_global WHERE email = 'ana@example.com'"), "rows");
  for (const t of ["users_global", "users_scoped"]) {
    try {
      await db.query(`INSERT INTO ${t} (tenant_id, email) VALUES (2, 'ana@example.com')`);
      console.log(`  tenant 2 INSERT into ${t}: accepted`);
    } catch (e) {
      console.log(`  tenant 2 INSERT into ${t}: ${fmt(e)}`);
    }
  }
  console.log("  tenant 2 SELECT folders:", await count(db, "SELECT count(*) AS n FROM folders"), "rows");
  for (const t of ["files_fk", "files_fk_scoped"]) {
    try {
      await db.query(`INSERT INTO ${t} (tenant_id, folder_id) VALUES (2, 1)`);
      console.log(`  tenant 2 INSERT into ${t} pointing at tenant 1's folder 1: accepted`);
    } catch (e) {
      console.log(`  tenant 2 INSERT into ${t} pointing at tenant 1's folder 1: ${fmt(e)}`);
    }
  }
  try {
    await db.query(`INSERT INTO files_fk (tenant_id, folder_id) VALUES (2, 999)`);
    console.log("  tenant 2 INSERT into files_fk pointing at folder 999 (does not exist): accepted");
  } catch (e) {
    console.log(`  tenant 2 INSERT into files_fk pointing at folder 999 (does not exist): ${fmt(e)}`);
  }
  // Control: the same duplicate insert by the superuser (RLS not applied) shows the key in DETAIL.
  await db.exec("RESET ROLE");
  try { await db.query(`INSERT INTO users_global (tenant_id, email) VALUES (2, 'ana@example.com')`); }
  catch (e) { console.log(`  control, superuser INSERT into users_global: ${fmt(e)}`); }
  console.log();
  await db.close();
}

// ---------- Test 6: plans under the policy ----------
console.log("TEST 6: EXPLAIN (ANALYZE, TIMING OFF) SELECT id, created_at FROM documents ORDER BY created_at DESC LIMIT 20, as app_user");
const indexSets = [
  ["no extra index", ""],
  ["index on (created_at)", "CREATE INDEX docs_created ON documents (created_at);"],
  ["index on (tenant_id, created_at)", "CREATE INDEX docs_tenant_created ON documents (tenant_id, created_at);"],
];
const policies = [
  ["policy calls current_setting() directly", TENANT],
  ["policy wraps it: (SELECT current_setting(...))", `(SELECT ${TENANT})`],
];
for (const [plabel, pexpr] of policies) {
  console.log(` ${plabel}`);
  for (const [ilabel, ddl] of indexSets) {
    const db = await fresh({ policyExpr: pexpr });
    if (ddl) await db.exec(ddl + " ANALYZE documents;");
    await db.exec("SET ROLE app_user");
    for (const tenant of [SMALL_TENANT, BIG_TENANT]) {
      await db.exec(`SET app.tenant_id = '${tenant}'`);
      const plan = (await db.query(
        `EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, COSTS OFF)
         SELECT id, created_at FROM documents ORDER BY created_at DESC LIMIT 20`
      )).rows.map((r) => r["QUERY PLAN"]);
      const nodes = plan
        .filter((l, i) => i === 0 || l.includes("->"))
        .map((l) => l.trim().replace(/^->\s*/, "").replace(/\s+\(actual.*$/, "").replace(/ on documents.*| using .*/, ""));
      const conds = plan.filter((l) => /^\s*(Index Cond|Filter):/.test(l)).map((l) => l.trim());
      const removed = plan.filter((l) => /Rows Removed by Filter/.test(l)).map((l) => l.trim().split(": ")[1]);
      const scanLine = plan.find((l) => /Scan/.test(l)) || "";
      const actualRows = (scanLine.match(/rows=(\d+)/) || [])[1];
      console.log(`  ${ilabel.padEnd(34)} tenant ${String(tenant).padEnd(2)} ${nodes.join(", ")} | ${conds.join("; ")} | scan returned ${actualRows} | removed by filter: ${removed.join(",") || 0}`);
    }
    await db.close();
  }
}

// ---------- Offboarding: delete one tenant in the pool ----------
{
  const db = new PGlite();
  await db.exec(`
    CREATE TABLE tenants (id int PRIMARY KEY);
    CREATE TABLE projects (
      tenant_id int NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
      id int NOT NULL, PRIMARY KEY (tenant_id, id));
    CREATE TABLE tasks (
      tenant_id int NOT NULL, id int NOT NULL, project_id int NOT NULL,
      PRIMARY KEY (tenant_id, id),
      FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id) ON DELETE CASCADE);
    INSERT INTO tenants VALUES (1), (2);
    INSERT INTO projects SELECT t, p FROM generate_series(1, 2) t, generate_series(1, 10) p;
    INSERT INTO tasks SELECT t, p * 100 + k, p FROM generate_series(1, 2) t, generate_series(1, 10) p, generate_series(1, 50) k;
  `);
  const before = (await db.query(`SELECT (SELECT count(*) FROM projects) AS p, (SELECT count(*) FROM tasks) AS t`)).rows[0];
  await db.exec(`DELETE FROM tenants WHERE id = 1`);
  const after = (await db.query(`SELECT tenant_id, count(*) AS n FROM tasks GROUP BY 1 ORDER BY 1`)).rows;
  const projLeft = (await db.query(`SELECT count(*) AS n FROM projects WHERE tenant_id = 1`)).rows[0].n;
  console.log("\nOFFBOARDING: DELETE FROM tenants WHERE id = 1, composite FKs with ON DELETE CASCADE");
  console.log(`  before: ${before.p} projects, ${before.t} tasks across 2 tenants`);
  console.log(`  after: tenant 1 projects left ${projLeft} | tasks by tenant ${after.map((r) => `${r.tenant_id}=${r.n}`).join(", ")}`);
  await db.close();
}

If you want the tenant model, the policies, the middleware and the tests designed together for a new SaaS, that's what the Keplaris API and SaaS development team builds, from the first migration to the per-tenant limits on top.