---
title: Postgres Row-Level Security for Multi-Tenant Apps (2026)
description: 'Implement Postgres row-level security for multi-tenant apps with node-postgres: enable RLS, write current_setting policies, and avoid the pool-leak footgun.'
url: https://nerdleveltech.com/postgres-row-level-security-multi-tenant-nodejs-tutorial
language: en
date: '2026-06-27'
category: backend
tags:
  - postgres
  - row-level security
  - multi-tenant
  - node-postgres
  - rls
  - backend
  - security
markdown_url: https://nerdleveltech.com/postgres-row-level-security-multi-tenant-nodejs-tutorial.md
attribution: 'Source: NerdLevelTech — https://nerdleveltech.com/postgres-row-level-security-multi-tenant-nodejs-tutorial'
terms: https://nerdleveltech.com/llms.txt
---

# 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+.[^since95]
- **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**.[^pg]
- Comfort with basic SQL (`CREATE TABLE`, `GRANT`) and `async/await`.

Pin the driver so your build is reproducible:

```bash
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](https://nerdleveltech.com/nextjs-16-cache-components-multi-tenant-cache-keys-tutorial).

## 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):

```sql
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:

```sql
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.[^rls]

```sql
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.[^createpolicy] `WITH CHECK` validates **new** rows on `INSERT`/`UPDATE`; when it returns false or null the whole command is aborted with an error.[^createpolicy] 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."[^setconfig] 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.[^rls] 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:

```sql
-- 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.[^rls] 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:

```sql
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.

```ts
// 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`.[^setconfig]

```ts
// 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:

```ts
// 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:

```ts
// 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**:

```ts
// 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:

```text
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:

```ts
// 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:

```sql
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:

```ts
// 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:[^rls]

```sql
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.[^createpolicy] That makes a restrictive policy ideal for a non-negotiable guardrail — for example, "refuse all access unless a tenant has actually been set":

```sql
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.[^createpolicy]

## 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.)

```sql
-- 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:

```sql
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:

```ts
// 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.[^rls] 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[^createpolicy]), 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.[^rls] Where that matters, use opaque surrogate keys rather than externally meaningful ones — for example, [UUIDv7 primary keys](https://nerdleveltech.com/postgres-18-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](https://nerdleveltech.com/production-postgres-pooling-pgbouncer-supabase-supavisor-tutorial). 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](https://nerdleveltech.com/cursor-pagination-postgres-nodejs-tutorial).

[^rls]: PostgreSQL 18 Documentation — "5.9. Row Security Policies." https://www.postgresql.org/docs/18/ddl-rowsecurity.html
[^createpolicy]: PostgreSQL 18 Documentation — "CREATE POLICY." https://www.postgresql.org/docs/18/sql-createpolicy.html
[^setconfig]: PostgreSQL 18 Documentation — "9.28.1. Configuration Settings Functions" (`current_setting`, `set_config`). https://www.postgresql.org/docs/18/functions-admin.html
[^pg]: node-postgres (`pg`) — npm package. https://www.npmjs.com/package/pg
[^since95]: 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/