Architecting Multi-Tenant Database Isolation: Row-Level Security vs Schema-Per-Tenant at Enterprise Scale

-- 1. Enable RLS on core enterprise tables
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;

-- 2. Define dynamic policy bound to session variable
CREATE POLICY tenant_isolation_policy ON accounts
  FOR ALL
  USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

CREATE POLICY tenant_invoices_policy ON invoices
  FOR ALL
  USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

-- 3. High-performance index optimization for RLS tenant lookups
CREATE INDEX CONCURRENTLY idx_accounts_tenant_id ON accounts (tenant_id, id);
CREATE INDEX CONCURRENTLY idx_invoices_tenant_id ON invoices (tenant_id, created_at DESC);

import { Pool, PoolClient } from 'pg';

const dbPool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 50,
  idleTimeoutMillis: 30000,
});

export async function withTenantContext<T>(
  tenantId: string,
  operation: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await dbPool.connect();
  try {
    await client.query('BEGIN;');
    // Set local session setting scoped ONLY to current transaction
    await client.query("SELECT set_config('app.current_tenant_id', $1, true);", [tenantId]);
    
    const result = await operation(client);
    
    await client.query('COMMIT;');
    return result;
  } catch (error) {
    await client.query('ROLLBACK;');
    throw error;
  } finally {
    // Release client back to pool with reset context
    client.release();
  }
}