octabits

@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.

npm i @octabits-io/rls                 # no dependencies; your ORM is yours already
                                       # ./drizzle expects drizzle-orm and pg, which a Drizzle host already has
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:

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.
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:

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:

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

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

From @octabits-io/rls@0.5.0 in the platform repository.