---
title: "@octabits-io/rls"
summary: "Row-level security for Postgres as a capability, not an ORM feature: the canonical policy as SQL — enable, force, one policy per scoped table with the system-mode bypass and NULLIF so an unset setting matches nothing — and the one parameterised set_config statement every runtime runs first in its transaction."
install: "pnpm add @octabits-io/rls"
availability: "Available from November 2026"
version: "0.5.0"
license: "MIT"
related: []
canonical_url: "https://octabits.io/platform/rls"
---

# `@octabits-io/rls`

Row-level security for Postgres as a capability you ask for by name, not a
feature bolted to an ORM. The root is the policy as SQL and the one
`set_config` statement every runtime runs first — pure strings, no
dependency at all. Each subpath is a runtime for something you already run:
`./prisma` for a Prisma client, `./drizzle` for Drizzle on node-postgres,
`./postgres` for plain SQL — `pg`, PGlite, postgres.js or Kysely, no ORM.
`./testing` is the tenant isolation matrix for your own test suite: every
persona against every scoped table, as the app role, with the failures
returned as values.

```bash
npm i @octabits-io/rls                 # no dependencies; your ORM is yours already
                                       # ./drizzle expects drizzle-orm and pg, which a Drizzle host already has
```

```ts
import { rlsPolicySql } from '@octabits-io/rls';
import { createScopedPrismaClient, withSystemMode } from '@octabits-io/rls/prisma';

// once, at setup — the policy is idempotent
await rawPrisma.$executeRawUnsafe(rlsPolicySql({ table: 'invoice', scopeColumn: 'org_id', scopeSetting: 'app.org_id' }));

// every operation on `prisma` — model and raw — runs as $transaction([set_config…, operation])
export const prisma = createScopedPrismaClient(rawPrisma, { gucs: { 'app.org_id': orgId } });

// background work that must see every row
await withSystemMode(rawPrisma, (tx) => tx.invoice.count());
```

| Subpath | Deps | Contents |
|---|---|---|
| `.` | none | `rlsPolicySql`, `setConfigStatement`, `SessionVars` — the SQL, for any stack that can send a string |
| `./prisma` | none (the client is structural) | `withGucs`, `withSystemMode`, `createScopedPrismaClient`; re-exports `rlsPolicySql` and `setConfigStatement` so a Prisma host needs one import |
| `./drizzle` | `drizzle-orm`, `pg` (optional peers) | `createScopedDb`, `runWithGucs`, `withSystemMode`, `augmentDrizzle`, the pinned-connection helpers |
| `./postgres` | none (executor and runner are structural) | `withGucs`, `withSystemMode`, `createScopedExecutor`, `poolTransactions`, `postgresJsTransactions`; re-exports `rlsPolicySql` and `setConfigStatement` |
| `./testing` | none (no test framework, no driver) | `defineIsolationMatrix`, `runIsolationMatrix`, `formatIsolationFailures`, `policiedTables` — the isolation matrix, failures as values |

A boundary lint keeps the root importing nothing at all, `./prisma` and
`./postgres` importing only the root and `./testing` only `./postgres`, and
everything but `./drizzle` type-checks with no Node typings.

## How it works

Every unit of work runs inside a short transaction whose first statement is
`set_config(name, value, true)` — transaction-local, so nothing leaks to the
next checkout of a pooled connection, and safe behind PgBouncer — and the
policy compares the row's scope column against `current_setting(name, true)`.
A request that never set the setting matches zero rows, which is the safe
failure.

`rlsPolicySql` is that policy, idempotent:

```sql
ALTER TABLE invoice ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoice FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS scope_isolation ON invoice;
CREATE POLICY scope_isolation ON invoice
  USING (org_id = NULLIF(current_setting('app.org_id', true), '') OR NULLIF(current_setting('app.system_mode', true), '') = 'true')
  WITH CHECK (org_id = NULLIF(current_setting('app.org_id', true), '') OR NULLIF(current_setting('app.system_mode', true), '') = 'true');
```

`FORCE` because the table's owner is not exempt otherwise; `NULLIF` because
an unset setting reads as NULL or `''` depending on the connection's history,
and either must match nothing; the system-mode branch is how background work
sees every row on purpose. Every identifier — table, column, setting, policy
— is validated against a pattern before it is interpolated; no SQL is built
from an unchecked name.

`setConfigStatement(gucs)` returns the one parameterised
`SELECT set_config($1, $2, true), …` statement and its parameters, for
whatever runs first in your transaction.

## `./prisma`

- `withGucs(client, gucs, fn)` — one interactive transaction with the
  settings applied first; `fn` receives the transaction client. Use that
  inside it, never the scoped client, whose per-call transaction would nest.
- `withSystemMode(client, fn)` — `withGucs` with `app.system_mode = 'true'`,
  for background work that must see every row.
- `createScopedPrismaClient(client, { gucs })` — a client extension over
  `$allOperations`: every operation, model and raw alike, runs as
  `$transaction([set_config…, operation])`. One transaction per operation —
  the same trade the Drizzle per-call proxy makes and won: concurrent
  operations inside a request still run in parallel on separate connections.
  It returns the client's own type, because it changes what the operations
  do, not what they are. An empty `gucs` throws: a scoped client with no
  scope would run everything unscoped.

The client is typed by the three methods used (`$transaction`,
`$executeRawUnsafe`, `$extends`), so `@prisma/client` is never imported and
never a peer; a generated Prisma 7 client satisfies it as-is.

## `./drizzle`

- `createScopedDb(db, gucs)` — a proxy shaped exactly like your Drizzle db:
  every top-level operation (builder chains, `query.*.findFirst/findMany`,
  `execute`, `transaction`) runs in a short transaction whose first
  statement sets the settings. Single statements take a pinned-client fast
  path: `BEGIN; SELECT set_config(…)` as one packet, then the statement, then
  `COMMIT` — three round-trips. It is **fail-closed**: a member of the db the
  proxy has not classified throws instead of running without the settings,
  and a unit test enumerates the installed drizzle-orm's surface, so an
  upgrade that adds a builder fails your suite, not your tenant isolation.
  Synchronous builder APIs (`toSQL`, `prepare`) throw on the proxy; use
  `runWithGucs` for those.
- `runWithGucs(db, gucs, fn)` / `withSystemMode(db, fn)` — one Drizzle-managed
  transaction with the settings applied first; nested `tx.transaction()`
  keeps savepoint semantics.
- `acquireScopedClient` / `releaseScopedClient` — the pinned-connection
  model: one transaction per request, committed or rolled back at the end.
- `augmentDrizzle(db, schema)` — adds `.tables`, which the fast path reads to
  rebuild a client-bound instance. A db without it still works, through the
  Drizzle-managed transaction, one round-trip dearer.
- `assertSafeGucListValue` / `joinGucList` — for a list joined into one
  setting: an element with `,` or `'` would widen the policy's match set, so
  it throws.

```ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { augmentDrizzle, createScopedDb, withSystemMode } from '@octabits-io/rls/drizzle';

const db = augmentDrizzle(drizzle({ client: pool, schema }), schema);
const scoped = createScopedDb(db, { 'app.org_id': orgId });   // per request
await scoped.select().from(invoices);                         // only this org's rows
await withSystemMode(db, (tx) => tx.delete(sessions));        // every scope, one unit of work
```

One transaction per operation, deliberately: concurrent queries inside a
request run in parallel on separate connections. A benchmark against one
pinned connection per request lost on every route by 5–47 % at p50.
`src/drizzle.ts` is the one file of the package outside its portability gate:
it types the pool with `pg`.

## `./postgres`

For a host with no ORM. Every Postgres store in this repository takes an
executor — `query(text, params) → { rows }` — and an executor alone cannot
carry a setting: over a pool, the `set_config` and the query may land on two
connections. So the runtime takes a **transaction runner**,
`transaction(fn)`, which hands `fn` an executor bound to one open transaction.

- `withGucs(runner, gucs, fn)` — one transaction with the settings applied
  first; every query `fn` sends through `tx` is scoped. Use `tx` inside it.
- `withSystemMode(runner, fn)` — the same with `app.system_mode = 'true'`.
- `createScopedExecutor(runner, { gucs })` — an executor whose every `query`
  runs as its own short transaction, settings first. Hand it to a store that
  takes an executor — the ledger, the feed, the pending proposals — and the
  store is scoped without knowing it. Two queries that must commit together
  belong in `withGucs`. An empty `gucs` throws.

The runner, per client:

```ts
import { createScopedExecutor, poolTransactions, postgresJsTransactions, withSystemMode } from '@octabits-io/rls/postgres';

const runner = poolTransactions(pool);          // pg: checkout, BEGIN, fn, COMMIT or ROLLBACK, release
const runner = pglite;                          // PGlite: its own transaction() is one, as-is
const runner = postgresJsTransactions(sql);     // postgres.js: its own begin()

const db = createScopedExecutor(runner, { gucs: { 'app.org_id': orgId } });   // per request
const ledger = createPostgresAgentLedgerStore({ db });                         // scoped
await withSystemMode(runner, (tx) => tx.query('delete from session where expires_at < now()'));
```

Kysely needs no adapter of its own — its transaction runs compiled raw SQL:

```ts
import { CompiledQuery } from 'kysely';

const runner: TransactionRunner = {
  transaction: (fn) => kysely.transaction().execute((trx) =>
    fn({ query: (text, params = []) => trx.executeQuery(CompiledQuery.raw(text, params)) })),
};
```

`poolTransactions` destroys a client whose `BEGIN` or `ROLLBACK` failed instead
of returning it to the pool. Nothing here imports a driver, not even as a type,
and the module type-checks with no Node typings.

## `./testing`

[`@octabits-io/data-lifecycle`](https://octabits.io/platform/data-lifecycle.md) audits that every
scoped table *carries* a policy. The matrix tests what the policies *do*: a
persona of scope A sees exactly the rows it should and none of scope B's,
writes none of B's, and cannot widen its own rights. It is data — the
personas, the tables with their id column, the ids each persona must see —
plus one seam, `as(persona, fn)`: run `fn` with an executor bound to one
transaction as that persona, the app role with the settings applied. The
runner returns the failures as values, so any test runner asserts on them;
nothing here imports a test framework.

```ts
import { withGucs } from '@octabits-io/rls/postgres';
import { defineIsolationMatrix, formatIsolationFailures, runIsolationMatrix } from '@octabits-io/rls/testing';

const matrix = defineIsolationMatrix({
  personas: ['acme-member', 'globex-member', 'stranger'],
  tables: [
    { table: 'invoice' },
    // A row of another scope the persona must not be able to insert, every NOT NULL column named.
    { table: 'note', insert: (persona) => ({ id: `probe-${persona}`, org_id: persona === 'acme-member' ? 'globex' : 'acme', body: '' }) },
    { table: 'staff_only' },
  ],
  expect: {
    'acme-member':   { invoice: ['inv-a1', 'inv-a2'], note: ['n-a1'], staff_only: [] },
    'globex-member': { invoice: ['inv-g1'],           note: [],       staff_only: [] },
    stranger:        { invoice: [],                   note: [],       staff_only: [] },
  },
  // The host's scope setter: the app role, the settings applied, one transaction that rolls back when fn throws.
  as: (persona, fn) => withGucs(runner, GUCS[persona], fn),
  escalations: [
    // Must be refused or touch nothing — write it with RETURNING.
    { name: 'promote oneself', sql: `UPDATE role_assignment SET role = 'owner' WHERE member = current_setting('app.member', true) RETURNING id` },
  ],
});

const failures = await runIsolationMatrix(matrix, { owner: adminPool }); // owner: sees every row, only reads
expect(failures, formatIsolationFailures(failures)).toEqual([]);
```

What it runs, per persona and table:

- **Visible:** `SELECT id` as the persona against `expect` — ids outside
  the expectation are `leakedIds`, expected ids the persona did not see are
  `missingIds` (a policy that hides a scope's own rows is a bug too).
- **Write probes:** `UPDATE` and `DELETE` against rows of another scope
  (the owner's rows minus the expectation, `probeLimit` of them, default 3),
  each `RETURNING` — a returned row is a `write` failure; then an `INSERT`
  of the row `insert(persona)` names, which the policy's `WITH CHECK` must
  refuse. Every probe runs in its own `as` transaction and ends by throwing
  a private marker, so the runner rolls it back: the fixture survives a
  leaking policy, and an owner read after each probe proves the rollback
  happened. `as` must therefore roll back when `fn` throws — the
  `TransactionRunner` contract, which `withGucs`, a Drizzle transaction and
  a Prisma `$transaction` all keep.
- **Escalations:** each statement as each persona (or the `personas` it
  names); one that resolves with rows is an `escalation` failure. A
  `SECURITY DEFINER` helper is probed the same way: `SELECT … FROM helper()
  WHERE scope <> current scope`.
- **Coverage:** every table under row-level security in the database
  (`pg_class.relrowsecurity` or a row in `pg_policy`; `policiedTables(owner)`
  lists them) must be in `tables`, every listed table must carry a policy,
  and every persona needs an expectation for every table — so a new scoped
  table fails the suite until someone adds its expectations on purpose.
  `skipCoverage: true` for a database with policied tables the matrix is not
  about.

`where` (with `whereParams`) narrows a table's reads to the fixture's rows —
`"id = ANY($1)"` — so rows other tests leave behind are not counted; ids are
compared as strings, so a serial or uuid column works as is, and a
non-unique `idColumn` compares as a set. Failures are
`{ kind: 'visible' | 'write' | 'escalation' | 'coverage', persona, table,
detail, leakedIds?, missingIds? }`; `formatIsolationFailures` prints one
line each for the assertion message.

The reference hosts run one: `apps/starter`'s `pnpm guard` (check 20 in
`src/guard.test.ts`) writes a row of every organisation into every scoped
table, made up from the column types, and derives the expectations as the
owner (exactly the rows whose scope column is mine) on PGlite;
`apps/starter-next/src/isolation.test.ts` writes a fixture of its own into
the live Postgres and names the ids. The `add-a-scoped-table` skill ends
with adding the table to the matrix.

## When not to use it

- **Another database.** `set_config`, `current_setting` and forced row-level
  security are Postgres. There is no equivalent to bind on MySQL or SQLite.
- **A scope that changes inside one client.** `createScopedPrismaClient`
  decides the scope once, where the client is made. Make one scoped client
  per request (or per process in a single-scope app); do not try to swap the
  settings on a shared client.

## What no runtime can do for you

Connect as a role that is not a superuser (Postgres never applies policies to
superusers), give the scope column a default of
`current_setting('app.org_id', true)` if creates inside a scoped transaction
should land in that scope without every call site naming it, and audit that
every scoped table actually carries a policy —
[`@octabits-io/data-lifecycle`](https://octabits.io/platform/data-lifecycle.md) does the last one
from a live database. `apps/starter-next` in the repository does all three and
proves the policies bite with a `NOSUPERUSER` role.

## Stability

Pre-1.0: patches never break, minors say so in the changelog. The emitted SQL,
the extension name (`octabits-rls`) and the shape of every function are part
of the surface.