Postgres RLS — Multi-Tenant Data Isolation

HIPAA-07 · Available from v0.1.163

Row-Level Security (RLS) is enforced at the Postgres database layer on all user-scoped tables. This provides a defence-in-depth guarantee: even if application code accidentally omits a userId WHERE clause in a Drizzle query, Postgres will silently prevent any cross-tenant data leakage.

How it works

Every user-facing database transaction must set a transaction-local session variable that the RLS policies inspect:

Request arrives → auth() resolves userId
    ↓
withUserContext(userId, async (txDb) => {
  // Postgres sees: app.current_user_id = 'user-abc' (transaction-local)
  // RLS policy:    USING (user_id = current_app_user_id())
  // → only rows owned by 'user-abc' are visible
  return txDb.select().from(people);
})

The session variable is transaction-local (set_config(..., TRUE)), so it is automatically cleared when the transaction ends. It is never visible to other concurrent connections.

Application helpers

All helpers are exported from src/lib/db/rls.ts.

withUserContext(userId, fn)

Run a block of database operations as a specific user. All RLS policies will filter to rows belonging to that user.

import { withUserContext } from '@/lib/db/rls';

// In a tRPC procedure or API route handler:
const results = await withUserContext(session.userId, async (txDb) => {
  return txDb.select().from(people);
});

Use in: user-facing API routes, tRPC procedures.

withSystemContext(fn)

Run a block of database operations with the RLS bypass flag set (app.bypass_rls = 'on'). All rows across all tenants are visible.

import { withSystemContext } from '@/lib/db/rls';

// In an Inngest background job:
await withSystemContext(async (txDb) => {
  const allPeople = await txDb.select().from(people);
  // process all tenants ...
});

Use in: Inngest background jobs, cron functions, nightly OFSI sync.

isRlsAvailable()

Returns true if a real (non-dummy) DATABASE_URL is configured and the pg.Pool has been initialised. Use for health checks.

import { isRlsAvailable } from '@/lib/db/rls';

if (!isRlsAvailable()) {
  // Running in CI or test environment — RLS helpers are no-ops
}

CI / test fallback

When DATABASE_URL is a dummy value (e.g. in CI or unit tests), the module detects this and skips creating the pg.Pool. Calls to withUserContext and withSystemContext fall back to the standard neon-http db client, so tests run without a real Postgres instance.

Tables covered

RLS is enabled on the following 13 user-scoped tables:

Table	Policy column
people	user_id
matches	via people.user_id (subquery join on person_id)
match_reviews	reviewer_id
user_settings	user_id
api_keys	user_id
notification_preferences	user_id
adverse_media_screenings	user_id
credit_accounts	user_id
credit_transactions	user_id
monitoring_billing_cycles	user_id
monitoring_charges	user_id
sms_log	user_id
feedback	user_id (NULL rows also permitted)

Policy design

Each policy uses the following pattern:

CREATE POLICY <table>_user_isolation ON <table>
  USING (
    is_system_context()
    OR user_id = current_app_user_id()
  )
  WITH CHECK (
    is_system_context()
    OR user_id = current_app_user_id()
  );

The two PostgreSQL helper functions are:

• is_system_context() — returns TRUE when current_setting('app.bypass_rls', TRUE) = 'on'
• current_app_user_id() — returns current_setting('app.current_user_id', TRUE), or NULL if not set

Using TRUE as the second argument to current_setting means a missing setting returns NULL rather than throwing an error, so queries run outside a user context (e.g. during migrations) are safe.

Important: FORCE ROW LEVEL SECURITY not used

FORCE ROW LEVEL SECURITY is intentionally not applied. Without it, the application database user (table owner) bypasses RLS automatically. This keeps database migrations, health checks, and admin tooling working without needing to set session variables. The primary data-isolation mechanism remains userId predicates in Drizzle queries; RLS is a defence-in-depth safety net.

Migration

The RLS policies are applied by migration 0026_rls_user_isolation.sql, run automatically by scripts/migrate.js. The migration is idempotent — re-running it is safe.

No breaking changes

All existing Drizzle queries continue to work unchanged. Adopting withUserContext in new or existing routes is opt-in.