Multi-Tenant SaaS on PostgreSQL: Row-Level Security from Node.js, and the Five Ways It Silently Fails

Most SaaS products keep every customer in the same tables with a tenant_id column. Row-level security moves the tenant filter out of application code and into the database — provided you avoid the five mistakes that quietly switch it off.

Most multi-tenant SaaS products start the same way: every customer's rows live in the same tables, distinguished by a tenant_id column, and every query in the application remembers to filter on it. That is the right starting architecture for most products. Its weakness is the word "remembers" — one query that forgets the filter shows one customer another customer's data, and nothing in the database objects.

That class of bug is not exotic. Broken access control sits at the top of the OWASP Top 10 in both the 2021 edition and the 2025 edition. PostgreSQL's row-level security (RLS) addresses the multi-tenant version of it by moving the tenant filter from every query into a policy the database enforces. This article shows the pattern we use from Node.js, and — more usefully — the five ways it quietly stops protecting anything.

Three ways to separate tenants

The AWS SaaS Lens describes tenant isolation in terms of silo, pool and bridge models: silo gives each tenant dedicated resources, pool shares resources across tenants, and bridge is a mix of the two across different parts of a system. At the database layer that usually comes down to three options.

For a new product we would normally start with shared tables and move a specific customer to a dedicated database when a contract calls for it — which is the bridge model in practice. The rest of this article is about making the shared-table option safe.

The pattern: a policy, and tenant context set per transaction

The policy compares each row's tenant_id with a setting the application provides for the current transaction. PostgreSQL's documentation is explicit that once RLS is enabled on a table, all normal access must be allowed by a policy, and a table with no policy defaults to deny.

-- Every tenant-owned table carries tenant_id, and it is indexed.
CREATE TABLE invoices (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    uuid NOT NULL REFERENCES tenants (id),
  amount_cents bigint NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX invoices_tenant_id_idx ON invoices (tenant_id);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

-- nullif: an unset or cleared setting means "no tenant", which matches no rows.
CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);

USING controls which existing rows a query can see, update or delete; WITH CHECK controls which rows can be written, so an INSERT cannot place a row in another tenant. The second argument to current_setting makes a missing setting return NULL instead of raising an error, and the nullif covers the case where the setting exists but is empty. Either way the comparison fails and the query sees nothing — the system fails closed.

On the application side, the tenant is set inside a transaction with set_config and its is_local argument set to true, which PostgreSQL documents as applying only to the current transaction — the equivalent of SET LOCAL. With node-postgres that means checking out one client for the whole transaction, because the library warns that you must use the same client instance for all statements within a transaction and must not run transactions through pool.query.

import pg from "pg";

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

// The tenant id is set with is_local = true, so it disappears at COMMIT or
// ROLLBACK and cannot leak into the next request that borrows this connection.
export async function withTenant(tenantId, work) {
  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 work(client);
    await client.query("COMMIT");
    return result;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

// tenantId comes from the verified session or token, never from the request body.
const { rows } = await withTenant(session.tenantId, (db) =>
  db.query("SELECT id, amount_cents FROM invoices ORDER BY created_at DESC LIMIT 50")
);

Notice what is missing from the query: there is no WHERE tenant_id clause. That is the point. A developer who forgets it gets the correct result anyway, and a developer who deliberately writes a query for another tenant's id gets nothing.

Five ways it silently fails

1. The application connects as the table owner or a superuser

According to the PostgreSQL documentation, superusers and roles with BYPASSRLS always bypass row security, and table owners normally do too unless the table uses FORCE ROW LEVEL SECURITY. The most common real-world failure is an application that runs as the same role that ran the migrations. Every policy is in place, every test written against a separate role passes, and production enforces nothing. Run migrations as the owner; run the application as a separate role with neither superuser nor BYPASSRLS; and force RLS anyway, as a second line.

2. Tenant context set for the session instead of the transaction

A plain SET, or set_config with is_local set to false, stays on the connection after the request finishes. The pool then lends that connection to the next request, which runs with the previous request's tenant until something overwrites it. Behind a connection pooler it is worse: PgBouncer's own feature table lists session-level SET/RESET as never supported in transaction pooling mode, because the server connection is only yours for the length of one transaction. Transaction-scoped settings work in every pooling mode; session-scoped ones only look as if they do.

3. Views that run with their owner's permissions

By default a view reads its underlying tables with the permissions of the view's owner, and row security is evaluated as that owner too. If the view is owned by a role that bypasses row security — a superuser, which is often who ran the migrations, or the table owner on a table without FORCE — the policies are not applied to the application's queries through the view at all. PostgreSQL 15 added the option to have a view run with the caller's privileges instead. Create reporting and convenience views WITH (security_invoker = true), and include views in the isolation tests described below.

4. Background jobs and admin tools that skip the wrapper

Queue workers, scheduled jobs and internal support dashboards are where tenant context most often goes missing, because they do not start from an authenticated request. A job should carry its tenant id in its payload and run its queries through the same wrapper as the web application. Genuine cross-tenant work, such as billing runs or support tooling, is better served by a separate, audited database role than by a shortcut that turns the policies off.

5. Unique constraints that span tenants

Referential integrity checks — unique constraints, primary keys and foreign keys — always bypass row security, and the documentation warns about "covert channel" leaks through them. A unique constraint on email across the whole users table lets one tenant discover that an address already exists in another tenant, simply by trying to insert it. Scope uniqueness to the tenant, as UNIQUE (tenant_id, email), unless global uniqueness is a deliberate product decision.

Test the isolation, not just the features

Feature tests pass happily against a database whose policies never run, so isolation needs tests of its own. Three are enough to catch all five failures above.

• A two-tenant integration test: create tenants A and B, write data as A, then read every tenant-owned table and view as B and assert that nothing comes back. Generate the table list from the schema, so a new table is covered the day it is added.
• A role check that runs against the real application connection: the current role must not be a superuser and must not have BYPASSRLS, which pg_roles exposes as rolsuper and rolbypassrls.
• A catalogue check in CI that fails the build if any table with a tenant_id column does not have row-level security both enabled and forced.

SELECT c.relname AS unprotected_table
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 = 'r'
  AND NOT (c.relrowsecurity AND c.relforcerowsecurity);

When to move a tenant out of the shared database

• A contract requires dedicated infrastructure, or requires the data to be held in a specific region.
• One tenant's workload regularly degrades response times for everyone else, and query tuning has stopped helping.
• A customer needs point-in-time restores of their own data alone, which is awkward to do from a backup shared with every other tenant.

Plan for the move from the start by keeping tenant_id on every tenant-owned row even in a dedicated database. A tenant can then be copied between the shared and dedicated models without changing the schema or the application's queries.

If you are planning a SaaS product and want the tenancy model settled before the first table exists, our guide to getting help building a SaaS product covers what else a first version cannot skip, and our dedicated development teams run this kind of technical discovery before a build starts.