Row-Level Security (RLS)
Row-level security (RLS) is a Postgres feature that filters every query at the database layer so a session only sees rows a policy predicate admits, typically scoped to a tenant id. Caisson's tenancy-rls module makes it fail-closed: FORCE RLS plus a withTenant wrapper mean a query with no bound tenant context returns zero rows, never another tenant's data.
In code
export interface TenantPolicyOptions {
/** The tenant-key column. Default `account_id`. */
column?: string;
/** The role policies apply to (it must NOT be a superuser / BYPASSRLS). Default `app`. */
role?: string;
}
/**
* SQL that makes `table` fail-closed tenant-isolated: ENABLE + **FORCE** RLS, GRANT CRUD to the
* app role, and a policy that admits a row only when its tenant column equals the bound GUC.
* Emitted into the table's migration (ADR-0014) so a tenant table can never ship without it.
*
* The GUC read is wrapped in `NULLIF(..., '')` (pgbouncer/pooler hardening): a pooled connection
* that resets custom GUCs to `''` instead of fully unsetting them would otherwise compare
* `column = ''`. `NULLIF` folds `''` to `NULL` first, so the comparison is always `NULL` (deny).
*/
export function buildTenantPolicySql(
table: string,
{ column = "account_id", role = "app" }: TenantPolicyOptions = {},
): string {
const guc = `NULLIF(current_setting('${TENANT_GUC}', true), '')`;
return [
`ALTER TABLE ${table} ENABLE ROW LEVEL SECURITY;`,
`ALTER TABLE ${table} FORCE ROW LEVEL SECURITY;`,
`GRANT SELECT, INSERT, UPDATE, DELETE ON ${table} TO ${role};`,
`CREATE POLICY ${table}_tenant_isolation ON ${table}`,
` USING (${column} = ${guc})`,
` WITH CHECK (${column} = ${guc});`,
].join("\n");
}How it holds
FORCE closes the owner loophole
Plain ENABLE ROW LEVEL SECURITY still lets the table owner bypass the policy. buildTenantPolicySql always emits FORCE ROW LEVEL SECURITY too, so the policy applies even to that connection, only a genuine superuser or BYPASSRLS role escapes it.
withTenant is the sole entry point
withTenant opens a transaction, binds the app.current_account GUC, then drops to the non-superuser app role before running the callback. A code path that forgets withTenant entirely never sets the GUC, so the policy predicate compares against null and the query returns nothing, fail-closed by construction.
The role itself is verified, not assumed
assertRoleNotPrivileged queries pg_roles once per (connection, role) and throws before ever SET LOCAL ROLE-ing into it if that role turns out to be SUPERUSER or BYPASSRLS, a misconfigured role can't silently reopen cross-tenant access with zero runtime signal.
Admin writes get their own role, not a bypass
The operator mutation surface runs as a separate admin_write role with its own USING(true) policy scoped TO admin_write only. RLS OR-combines permissive policies per role, so admin_write can see every tenant while app's isolation is untouched. That admin-write layer ships in the commercial @caisson/org-controls package; the free tenancy-rls package carries the buyer tenant-isolation floor itself.