Postgres Row-Level Security for Multi-Tenant Apps (2026)

Postgres row-level security (RLS) enforces multi-tenant isolation in the database: enable RLS on each table, write a policy that compares tenant_id to a per-request session variable, connect node-postgres as a least-privilege role, and set the tenant with set_config(..., true) inside a transaction so it never leaks across pooled connections.

TL;DR

Postgres row-level security turns tenant isolation into a database guarantee instead of a rule you must remember in every query. You will build a tenant-isolated documents API on PostgreSQL 18 with node-postgres (pg) 8.21.0. A single tenant_isolation policy makes the database — not your WHERE clauses — the thing that stops one tenant from reading or writing another tenant's rows. The whole build takes about 30 minutes, every SQL statement here was executed against Postgres 18 before publishing — along with the withTenant helper and the test suite — and the trickiest part (a connection-pool leak that silently defeats RLS) gets a one-line fix.

What you'll learn

• How row-level security enforces multi-tenant isolation at the database layer
• How to enable RLS and write a USING policy keyed to tenant_id
• Why RLS looks "ignored" for table owners and superusers, and the least-privilege role that fixes it
• How to pass the current tenant from node-postgres with current_setting and set_config
• The connection-pool leak that breaks naive RLS, and the transaction-local fix
• How WITH CHECK stops a tenant from writing into another tenant's rows
• When to reach for FORCE ROW LEVEL SECURITY and RESTRICTIVE policies
• How to test tenant isolation automatically so a later migration can't silently break it

Prerequisites

• PostgreSQL 18 running locally (any reachable instance works). RLS itself is not new — it has been a core feature since PostgreSQL 9.5 (2016), so the policies below also run on 9.5+.¹
• Node.js 20.6+ (any current LTS; pg itself needs only Node 16+, but the optional test suite at the end uses node --test --import, added in 20.6) and node-postgres pg 8.21.0.²
• Comfort with basic SQL (CREATE TABLE, GRANT) and async/await.

Pin the driver so your build is reproducible:

npm install pg@8.21.0
npm install -D typescript@6.0.3 tsx@4.22.4 @types/pg@8.20.0 @types/node@26.0.1

Row-level security is the database-side half of multi-tenancy. If you also cache tenant data in your app, pair this with per-tenant cache keys so an isolation bug in one layer cannot be undone by the other — see the companion guide on multi-tenant cache keys in Next.js.

Step 1 — Model tenants with a tenant_id column

Multi-tenant apps usually pick one of three isolation models: a separate database per tenant (strongest isolation, heaviest to operate), a schema per tenant (a middle ground that gets awkward past a few hundred tenants), or a shared schema where every tenant's rows live in the same tables tagged with a tenant_id. The shared-schema model scales to thousands of tenants on one database and is the most common — but it normally puts the entire burden of isolation on getting a tenant_id filter right in every single query. Row-level security removes that burden by moving the filter into the database itself, where a forgotten WHERE clause can no longer expose another tenant's data.

Start with the schema. Every tenant-scoped table carries a tenant_id column. Run this as a privileged migration role (the table owner):

CREATE TABLE documents (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   bigint      NOT NULL,
  title       text        NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO documents (tenant_id, title) VALUES
  (1, 'Acme roadmap'),
  (1, 'Acme invoices'),
  (2, 'Globex memo');

Using GENERATED ALWAYS AS IDENTITY instead of serial matters later: identity columns are owned by the table, so your application role needs no separate sequence grant to insert.

Add an index that leads with tenant_id. Because every RLS-filtered query effectively gains a tenant_id = <current tenant> predicate, a tenant_id-leading index keeps those queries fast:

CREATE INDEX documents_tenant_id_idx ON documents (tenant_id, created_at DESC);

Step 2 — Turn on row-level security and write your first policy

Enabling RLS flips a table from "anyone with table privileges sees every row" to default-deny: once row security is enabled, if no policy grants access, no rows are visible or modifiable at all.³

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
  FOR ALL
  USING      (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint);

Two expressions do two jobs. USING filters which existing rows are visible to reads, updates, and deletes; rows where it returns false or null are silently skipped — no error.⁴ WITH CHECK validates new rows on INSERT/UPDATE; when it returns false or null the whole command is aborted with an error.⁴ We will lean on both.

The current_setting('app.tenant_id', true) call reads a per-session variable your application sets on each request — it is the bridge between your auth layer and the database. The second argument, true (missing_ok), means "return NULL instead of erroring if the variable was never set."⁵ The NULLIF(..., '') wrapper is not decoration; Step 5 shows the exact crash it prevents.

How a policy rewrites your queries

RLS does not run a separate permission check after your query returns — it rewrites the query before it runs. With row security enabled, Postgres adds the policy's USING expression as an implicit predicate and evaluates it for each row prior to any conditions coming from your own query.³ A plain SELECT * FROM documents issued by app_user behaves as though you had written SELECT * FROM documents WHERE tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint. That is the whole point — and it is also why the policy expression should stay cheap and index-friendly, because it runs on every row of every query against the table.

Step 3 — Why your policy looks ignored (owners and superusers bypass RLS)

Here is a common "RLS isn't working" surprise. Connect as a superuser (or as the table's owner) and query the table:

-- connected as a superuser / the table owner
SELECT count(*) FROM documents;   -- returns 3, NOT 0

That is correct behavior, not a bug. Superusers and roles with the BYPASSRLS attribute always bypass row security, and a table's owner bypasses it too unless you explicitly force it.³ Your migration role and your psql session are usually privileged, so they see everything — which is exactly why RLS must be exercised by an unprivileged role.

Create a dedicated application role that is neither a superuser nor the table owner, and grant it only the data privileges it needs:

CREATE ROLE app_user NOSUPERUSER NOINHERIT LOGIN PASSWORD 'change-me';
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- so future tables are covered automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

Your application connects as app_user. Because that role does not own documents and is not a superuser, the tenant_isolation policy actually applies to it. Run your app under a least-privilege role in every environment — RLS is only as strong as the role your queries actually run as.

Step 4 — Bridge the request to the database with a session variable

Now wire node-postgres to the policy. The application connects with the app_user credentials and, for each request, tells Postgres which tenant is active by setting the app.tenant_id variable the policy reads.

// db.ts
import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL, // postgres://app_user:change-me@localhost:5432/app
  max: 10,
});

Do not run a bare SET app.tenant_id = ... on a pooled connection (Step 5 explains why). Instead, wrap every unit of work in a transaction and use set_config(name, value, true). The third argument, is_local = true, scopes the setting to the current transaction — it is the function form of SET LOCAL, and it resets automatically at COMMIT or ROLLBACK.⁵

// with-tenant.ts
import type { Pool, PoolClient } from 'pg';

export async function withTenant<T>(
  pool: Pool,
  tenantId: number,
  fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // transaction-local: cannot leak to the next request on this connection
    await client.query('SELECT set_config($1, $2, true)', ['app.tenant_id', String(tenantId)]);
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Every tenant-scoped query now goes through withTenant, and the database filters rows for you:

// route handler (Express-style)
app.get('/documents', async (req, res) => {
  const tenantId = req.tenant.id; // resolved from your JWT / session, never the client body
  const docs = await withTenant(pool, tenantId, (client) =>
    client.query('SELECT id, title, created_at FROM documents ORDER BY id'),
  );
  res.json(docs.rows);
});

Notice what is missing: there is no WHERE tenant_id = $1. The policy adds it for every query against documents, so a forgotten filter is no longer a cross-tenant data breach. Setting the tenant from your trusted auth context — never from a request parameter — is what makes that safe.

Step 5 — The connection-pool footgun (and the one-line fix)

This is the mistake that turns RLS into a false sense of security, and many guides skip it. Connection pools reuse physical connections across requests. A plain SET (without LOCAL) changes the setting for the whole session, so it survives client.release() and bleeds into whoever checks out that connection next.

Here is the leak, reproduced against Postgres 18 with a single-connection pool to force reuse:

// ANTI-PATTERN — do not do this
const a = await pool.connect();
await a.query("SET app.tenant_id = '2'");                 // session scope
await a.query('SELECT count(*) FROM documents');          // sees tenant 2's rows
a.release();                                               // returned WITHOUT resetting

const b = await pool.connect();                            // SAME physical connection
await b.query('SELECT count(*) FROM documents');          // STILL sees tenant 2 — LEAK

The second request never set a tenant, yet current_setting('app.tenant_id') still reads 2, so it sees another tenant's data. The withTenant helper from Step 4 fixes this for free: set_config(..., true) is transaction-local, so the value is gone the instant the transaction ends and a leaked connection cannot carry it forward.

There is a sharp edge hiding in that revert. After a transaction-local setting reverts, the variable does not become NULL — on a connection that has set it before, current_setting('app.tenant_id', true) returns an empty string:

// same physical connection, just after a withTenant() transaction committed
const { rows } = await client.query("SELECT current_setting('app.tenant_id', true) AS raw");
console.log(JSON.stringify(rows[0].raw)); // ""  (empty string, not null)

If your policy cast were the naive current_setting('app.tenant_id', true)::bigint, that empty string would explode the next time the connection is used outside a tenant context:

error: invalid input syntax for type bigint: ""

That is precisely why the policy in Step 2 wraps the value in NULLIF(current_setting('app.tenant_id', true), '')::bigint. NULLIF turns both "never set" (NULL) and "set then reverted" (empty string) into NULL, and tenant_id = NULL matches no rows — so an unset context fails safe to zero rows instead of throwing. This single guard is the difference between a robust policy and one that crashes intermittently under load.

Step 6 — Block cross-tenant writes with WITH CHECK

USING protects reads. Without WITH CHECK, a tenant could still INSERT a row stamped with someone else's tenant_id. Because our policy is FOR ALL with an explicit WITH CHECK, that write is rejected. Tenant 1 inserting its own row succeeds; tenant 1 inserting a row for tenant 2 fails:

// inside withTenant(pool, 1, ...)
await client.query("INSERT INTO documents (tenant_id, title) VALUES (1, 'Acme Q3 plan')"); // OK

await client.query("INSERT INTO documents (tenant_id, title) VALUES (2, 'sneaky')");
// throws: new row violates row-level security policy for table "documents"

You can make this impossible to get wrong by giving tenant_id a default sourced from the same session variable, so application code never sets it by hand:

ALTER TABLE documents
  ALTER COLUMN tenant_id
  SET DEFAULT NULLIF(current_setting('app.tenant_id', true), '')::bigint;

Now an insert that omits tenant_id is stamped with the current tenant automatically:

// inside withTenant(pool, 2, ...)
const { rows } = await client.query(
  "INSERT INTO documents (title) VALUES ('Globex plan') RETURNING id, tenant_id",
);
console.log(rows[0]); // e.g. { id: '6', tenant_id: '2' } — tenant_id auto-filled from the GUC

The same protection applies to UPDATE and DELETE: their USING filter means a tenant simply cannot see another tenant's rows to modify them, so a cross-tenant UPDATE affects zero rows rather than raising an error.

Step 7 — Defense in depth: FORCE and RESTRICTIVE policies

Two more tools harden the setup.

FORCE ROW LEVEL SECURITY makes the policy apply even to the table's owner:³

ALTER TABLE documents FORCE ROW LEVEL SECURITY;

This only matters if the role your application uses also owns the tables — a setup you should avoid, but FORCE is the safety net when you cannot. One caveat: FORCE does not affect superusers or BYPASSRLS roles. Nothing short of dropping those privileges subjects them to RLS, which is the real reason to keep the app role unprivileged.

RESTRICTIVE policies combine differently from the default. Multiple permissive policies are OR-ed together (any one can grant access); restrictive policies are AND-ed, and every one must pass.⁴ That makes a restrictive policy ideal for a non-negotiable guardrail — for example, "refuse all access unless a tenant has actually been set":

CREATE POLICY require_tenant ON documents
  AS RESTRICTIVE
  USING (NULLIF(current_setting('app.tenant_id', true), '') IS NOT NULL);

With this in place, a query that forgot to set app.tenant_id is blocked by the restrictive policy in addition to matching no rows through the permissive one. Remember the ordering rule: there must be at least one permissive policy granting access before restrictive policies can usefully narrow it; restrictive policies alone deny everything.⁴

Verify it end to end

The surest way to trust an isolation rule is to watch it work from an unprivileged role. In psql, switch into app_user and set a tenant per transaction. (The row lists below assume the seed data from Step 1; if you also ran the inserts in Step 6, each tenant will have one extra row.)

-- as a privileged role first, hand the test role the tenant context
BEGIN;
SET LOCAL ROLE app_user;
SELECT set_config('app.tenant_id', '1', true);
SELECT tenant_id, title FROM documents ORDER BY id;
--  tenant_id |     title
-- -----------+---------------
--          1 | Acme roadmap
--          1 | Acme invoices
COMMIT;

BEGIN;
SET LOCAL ROLE app_user;
SELECT set_config('app.tenant_id', '2', true);
SELECT tenant_id, title FROM documents ORDER BY id;
--  tenant_id |    title
-- -----------+-------------
--          2 | Globex memo
COMMIT;

Tenant 1 sees only Acme rows; tenant 2 sees only Globex. Try a cross-tenant write and watch WITH CHECK reject it:

BEGIN;
SET LOCAL ROLE app_user;
SELECT set_config('app.tenant_id', '1', true);
INSERT INTO documents (tenant_id, title) VALUES (2, 'sneaky');
-- ERROR:  new row violates row-level security policy for table "documents"
ROLLBACK;

From Node, the same checks run through withTenant. Setting tenant_id to 1 then 2 returns disjoint row sets, and the cross-tenant insert throws — that is the entire contract of the feature, verified.

Test tenant isolation in CI

Isolation is a property you want a test to defend, because a future migration — a new table without RLS enabled, a policy dropped during a refactor — can quietly reopen the door. Two small tests using Node's built-in test runner cover the contract. They connect as app_user through the same withTenant helper your app uses, so they exercise the real policy, not a mock:

// rls.test.ts  —  run with:  node --test --import tsx rls.test.ts
import assert from 'node:assert/strict';
import { test, after } from 'node:test';
import { pool } from './db.ts';
import { withTenant } from './with-tenant.ts';

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

test('a tenant sees only its own rows', async () => {
  const res = await withTenant(pool, 1, (c) =>
    c.query<{ tenant_id: string }>('SELECT tenant_id FROM documents'),
  );
  assert.ok(res.rows.length > 0);
  // pg returns bigint as a string by default, so compare against '1'
  assert.ok(res.rows.every((r) => r.tenant_id === '1'));
});

test('a tenant cannot write into another tenant', async () => {
  await assert.rejects(
    withTenant(pool, 1, (c) =>
      c.query("INSERT INTO documents (tenant_id, title) VALUES (2, 'nope')"),
    ),
    /row-level security policy/,
  );
});

The first test asserts every row a tenant reads belongs to that tenant; the second asserts a cross-tenant write is rejected by WITH CHECK. Wire node --test into your pipeline and a dropped policy becomes a red build instead of a silent breach. One detail worth internalizing: node-postgres returns bigint columns as JavaScript strings to avoid precision loss, which is why the assertion compares against '1' rather than the number 1.

Troubleshooting

"RLS isn't filtering anything." You are connected as a superuser or the table owner; both bypass row security.³ Connect as the unprivileged app_user role (Step 3), or apply FORCE ROW LEVEL SECURITY if the app role owns the table.

"Every query returns zero rows." Either RLS is enabled with no applicable policy (default-deny⁴), or you forgot to set app.tenant_id, so NULLIF(...) is NULL and nothing matches. Confirm the variable inside the same transaction: SELECT current_setting('app.tenant_id', true).

invalid input syntax for type bigint: "". A reused pooled connection is carrying an empty-string value from a prior transaction-local setting. Cast through NULLIF(current_setting('app.tenant_id', true), '')::bigint as shown in Step 5.

"Tenant data leaks between requests." You used a session-level SET instead of transaction-local set_config(..., true) / SET LOCAL. Wrap each request in a transaction and set the variable there (Step 4).

A unique or foreign-key error reveals a hidden row. Referential-integrity checks bypass RLS by design, so a failed unique insert can betray that a value exists in a row you cannot see.³ Where that matters, use opaque surrogate keys rather than externally meaningful ones — for example, UUIDv7 primary keys.

Next steps

You now have Postgres row-level security enforcing multi-tenancy through node-postgres: a tenant_id model, a tenant_isolation policy, a least-privilege role, a transaction-local tenant bridge, and WITH CHECK on writes — all verified against Postgres 18.

From here, two things deserve attention. First, pooling: the transaction-local pattern is exactly what makes RLS safe behind PgBouncer or Supavisor in transaction mode, covered in the production Postgres connection pooling guide. Second, performance: keep tenant_id as the leading column of your hot indexes (Step 1) and confirm with EXPLAIN that the policy predicate uses them, the same keyset discipline used in cursor pagination with node-postgres.

Footnotes

1. PostgreSQL 9.5 release announcement — "UPSERT, Row Level Security, and Big Data" (released 2016-01-07). https://www.postgresql.org/about/news/postgresql-95-upsert-row-level-security-and-big-data-1636/ ↩

2. node-postgres (pg) — npm package. https://www.npmjs.com/package/pg ↩

3. PostgreSQL 18 Documentation — "5.9. Row Security Policies." https://www.postgresql.org/docs/18/ddl-rowsecurity.html ↩ ↩² ↩³ ↩⁴ ↩⁵ ↩⁶

4. PostgreSQL 18 Documentation — "CREATE POLICY." https://www.postgresql.org/docs/18/sql-createpolicy.html ↩ ↩² ↩³ ↩⁴ ↩⁵

5. PostgreSQL 18 Documentation — "9.28.1. Configuration Settings Functions" (current_setting, set_config). https://www.postgresql.org/docs/18/functions-admin.html ↩ ↩²