[object Object]

← back to Rentv

PR intel (b2 complete): tenant isolation via Postgres RLS

ea0ef6cd9dda74d58fbb4a817608dd2a05863bb0 · 2026-08-06 10:08:44 -0700 · Steve

- migration 007: ENABLE + FORCE RLS + tenant policy on all 17 entity tables
  (NULLIF-guarded so an unset GUC = full system access, never a cast error).
  Auth tables excluded (login is pre-tenant).
- db.js: AsyncLocalStorage — tenantMiddleware checks out one connection per web
  request, SET app.tenant_id, and every query auto-scopes to the caller's tenant.
  Worker/CLI run unscoped = full cross-tenant.
- index.js: PR router mounts db.tenantMiddleware after the capability gate.
- app connects as non-superuser pr_app (superusers bypass RLS); PR_DATABASE_URL.
- retired the PR_MULTI_TENANT_READY guard — isolation is DB-enforced.
- PROVEN end-to-end: a tenant-2 user sees ONLY tenant-2 data via HTTP; tenant-1
  sees 0 tenant-2 rows; unset (worker) sees all.

Files touched

Diff

commit ea0ef6cd9dda74d58fbb4a817608dd2a05863bb0
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Aug 6 10:08:44 2026 -0700

    PR intel (b2 complete): tenant isolation via Postgres RLS
    
    - migration 007: ENABLE + FORCE RLS + tenant policy on all 17 entity tables
      (NULLIF-guarded so an unset GUC = full system access, never a cast error).
      Auth tables excluded (login is pre-tenant).
    - db.js: AsyncLocalStorage — tenantMiddleware checks out one connection per web
      request, SET app.tenant_id, and every query auto-scopes to the caller's tenant.
      Worker/CLI run unscoped = full cross-tenant.
    - index.js: PR router mounts db.tenantMiddleware after the capability gate.
    - app connects as non-superuser pr_app (superusers bypass RLS); PR_DATABASE_URL.
    - retired the PR_MULTI_TENANT_READY guard — isolation is DB-enforced.
    - PROVEN end-to-end: a tenant-2 user sees ONLY tenant-2 data via HTTP; tenant-1
      sees 0 tenant-2 rows; unset (worker) sees all.
---
 src/pr/db.js                                   | 41 ++++++++++++++++++++++++-
 src/pr/index.js                                |  3 ++
 src/pr/migrations/007_rls_tenant_isolation.sql | 42 ++++++++++++++++++++++++++
 src/pr/services/auth.js                        |  9 ++----
 4 files changed, 88 insertions(+), 7 deletions(-)

diff --git a/src/pr/db.js b/src/pr/db.js
index 0f722142..100416b2 100644
--- a/src/pr/db.js
+++ b/src/pr/db.js
@@ -51,8 +51,21 @@ function pool() {
   return _pool;
 }
 
+// ── Tenant scope (RLS) ───────────────────────────────────────────────────────
+// A web request runs inside `tenantMiddleware`, which checks out ONE client, sets the
+// `app.tenant_id` GUC (read by the RLS policies), and stashes the client in AsyncLocalStorage
+// for the life of the request. Every db.query/one/rows/tx below then runs on THAT client, so
+// RLS scopes them to the caller's tenant automatically. Outside a request (worker, CLI tools,
+// migrations) there's no ALS client → queries hit the pool with the GUC unset → the policy
+// grants full cross-tenant access (system operations).
+const { AsyncLocalStorage } = require('async_hooks');
+const _als = new AsyncLocalStorage();
+function _scopedClient() { const s = _als.getStore(); return s && s.client; }
+
 /** Run a parameterized query. Returns the pg result. Throws on error (callers handle). */
 async function query(text, params) {
+  const c = _scopedClient();
+  if (c) return c.query(text, params);
   const p = pool();
   if (!p) throw new Error(_initError || 'database unavailable');
   return p.query(text, params);
@@ -65,6 +78,12 @@ async function one(text, params) { return (await query(text, params)).rows[0] ||
 
 /** Run fn(client) inside a transaction. Rolls back on throw. */
 async function tx(fn) {
+  const scoped = _scopedClient();
+  if (scoped) { // already on the request's tenant-scoped connection — reuse it (keeps RLS GUC)
+    await scoped.query('BEGIN');
+    try { const out = await fn(scoped); await scoped.query('COMMIT'); return out; }
+    catch (e) { try { await scoped.query('ROLLBACK'); } catch { /* ignore */ } throw e; }
+  }
   const p = pool();
   if (!p) throw new Error(_initError || 'database unavailable');
   const client = await p.connect();
@@ -81,6 +100,26 @@ async function tx(fn) {
   }
 }
 
+/** Express middleware: scope all db access in this request to `getTenantId(req)`'s tenant
+ *  (via a dedicated connection + the app.tenant_id GUC + AsyncLocalStorage). Releases the
+ *  connection when the response finishes. No-ops (falls back to the pool) if no tenant. */
+function tenantMiddleware(getTenantId) {
+  return (req, res, next) => {
+    const tid = Number(getTenantId(req)) || 0;
+    if (!tid) return next();
+    const p = pool();
+    if (!p) return next();
+    p.connect().then((client) => {
+      let released = false;
+      const release = () => { if (released) return; released = true; client.query('RESET app.tenant_id').catch(() => {}).finally(() => client.release()); };
+      res.on('close', release); res.on('finish', release);
+      client.query('SELECT set_config($1, $2, false)', ['app.tenant_id', String(tid)])
+        .then(() => { _als.run({ client, tenantId: tid }, () => next()); })
+        .catch((e) => { release(); next(e); });
+    }).catch(next);
+  };
+}
+
 /** Lightweight health probe used by /api/pr/health and the mount guard. */
 async function health() {
   if (!Pool) return { ok: false, db: 'unavailable', reason: 'pg driver not installed (run npm install)' };
@@ -122,4 +161,4 @@ async function runMigrations({ log = () => {} } = {}) {
   return { applied: done, alreadyApplied: [...applied] };
 }
 
-module.exports = { query, rows, one, tx, health, runMigrations, DB_NAME, connConfig };
+module.exports = { query, rows, one, tx, health, runMigrations, DB_NAME, connConfig, tenantMiddleware };
diff --git a/src/pr/index.js b/src/pr/index.js
index 17b17f73..d2e42d94 100644
--- a/src/pr/index.js
+++ b/src/pr/index.js
@@ -121,6 +121,9 @@ module.exports = function mountPR(app, { adminOnly, sendPage }) {
         next();
       } catch (e) { res.status(500).json({ ok: false, error: e.message }); }
     });
+    // After auth: bind every subsequent data query in this request to the caller's tenant
+    // (RLS via db.js). Runs after the gate so req.prAuth.tenant.id is set.
+    app2.use('/api/pr', db.tenantMiddleware((req) => req.prAuth && req.prAuth.tenant && req.prAuth.tenant.id));
   }
   app.post('/api/pr/auth/login', h(async (req, res) => {
     const b = req.body || {};
diff --git a/src/pr/migrations/007_rls_tenant_isolation.sql b/src/pr/migrations/007_rls_tenant_isolation.sql
new file mode 100644
index 00000000..f26918eb
--- /dev/null
+++ b/src/pr/migrations/007_rls_tenant_isolation.sql
@@ -0,0 +1,42 @@
+-- Multi-tenant isolation via Postgres Row-Level Security (the b2 finishing pass).
+-- Every entity table is scoped by tenant automatically — impossible to forget a WHERE
+-- filter. RLS applies only to non-superuser roles (the app connects as pr_app); the
+-- pr-worker + system tools run with app.tenant_id UNSET → the policy grants full cross-
+-- tenant access (they legitimately operate across tenants). Web requests SET app.tenant_id
+-- per request (db.js AsyncLocalStorage) → scoped to the caller's tenant.
+--
+-- Auth tables (pr_tenants/pr_users/pr_sessions) are deliberately NOT RLS-guarded — login
+-- needs to resolve a user across tenants before a tenant context exists.
+
+-- Ensure pr_app can read/write everything (idempotent; role created out-of-band).
+DO $$ BEGIN
+  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname='pr_app') THEN
+    GRANT USAGE ON SCHEMA public TO pr_app;
+    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO pr_app;
+    GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO pr_app;
+  END IF;
+END $$;
+
+DO $$
+DECLARE t text; pol text; cond text;
+BEGIN
+  -- NULLIF(...,'') so an UNSET or empty GUC (worker/tools/reset connection) never reaches the
+  -- ::bigint cast (which errors on '') — it short-circuits to full cross-tenant access instead.
+  cond := $c$(
+    NULLIF(current_setting('app.tenant_id', true), '') IS NULL
+    OR current_setting('app.tenant_id', true) = '0'
+    OR tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint
+  )$c$;
+  FOREACH t IN ARRAY ARRAY[
+    'pr_organizations','pr_people','pr_org_relationships','pr_sources','pr_field_evidence',
+    'pr_research_runs','pr_jobs','pr_query_templates','pr_campaigns','pr_letter_templates',
+    'pr_letter_blocks','pr_outreach_messages','pr_activities','pr_tasks','pr_suppression',
+    'pr_audit_log','pr_import_batches'
+  ] LOOP
+    EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t);
+    EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', t);   -- apply even to the table owner
+    pol := t || '_tenant_pol';
+    EXECUTE format('DROP POLICY IF EXISTS %I ON %I', pol, t);
+    EXECUTE format('CREATE POLICY %I ON %I USING %s WITH CHECK %s', pol, t, cond, cond);
+  END LOOP;
+END $$;
diff --git a/src/pr/services/auth.js b/src/pr/services/auth.js
index a9a7a632..c8bd564c 100644
--- a/src/pr/services/auth.js
+++ b/src/pr/services/auth.js
@@ -39,12 +39,9 @@ function can(role, cap) { return !!(CAPS[role] && CAPS[role].has(cap)); }
 
 // ── Users ────────────────────────────────────────────────────────────────────
 async function createUser({ tenant_id = 1, email, name, role = 'user', password }, actor) {
-  // SAFETY GUARD: full per-query tenant isolation isn't complete yet (only the core
-  // org/people reads are scoped). Refuse to onboard a 2nd tenant until PR_MULTI_TENANT_READY=1,
-  // so we never ship FALSE isolation where tenant 2 could read tenant 1's data.
-  if (Number(tenant_id) !== 1 && !process.env.PR_MULTI_TENANT_READY) {
-    throw new Error('multi-tenant isolation not yet complete — onboarding tenant ' + tenant_id + ' is blocked until every entity query is tenant-scoped (set PR_MULTI_TENANT_READY=1 to override)');
-  }
+  // (Tenant isolation is now enforced at the DB via RLS — migration 007 + the app connecting
+  // as the non-superuser pr_app role + per-request app.tenant_id. The old PR_MULTI_TENANT_READY
+  // guard is retired; onboarding additional tenants is safe.)
   const em = String(email || '').trim().toLowerCase();
   if (!em) throw new Error('email required');
   if (!['admin', 'developer', 'pro', 'user'].includes(role)) throw new Error('invalid role');

← 0044e5f8 News map: collapse ALL controls into one LEFT hamburger; str  ·  back to Rentv  ·  fix(pr-intelligence): graceful 404 on people?id=<dead> — ope b14737eb →