Postgres Row Level Security: tenant isolation built into the database

-- 1. Add tenant_id to your tables
ALTER TABLE orders ADD COLUMN tenant_id UUID NOT NULL;
ALTER TABLE users ADD COLUMN tenant_id UUID NOT NULL;

-- 2. Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- 3. Create a policy using current_setting to get the active tenant
CREATE POLICY orders_tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY users_tenant_isolation ON users
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- 4. Bypass RLS for your admin/superuser role
ALTER ROLE myapp_admin BYPASSRLS;

-- 5. The application role should NOT bypass RLS
ALTER ROLE myapp_app NOBYPASSRLS;

import asyncpg
from contextlib import asynccontextmanager

@asynccontextmanager
async def tenant_connection(pool: asyncpg.Pool, tenant_id: str):
    """Get a connection with RLS tenant context set."""
    async with pool.acquire() as conn:
        # Set the current tenant for this connection
        # All queries on this connection will be filtered by RLS
        await conn.execute(
            "SELECT set_config('app.current_tenant_id', $1, true)",
            tenant_id
        )
        yield conn
        # The config is scoped to this transaction — safe to return to pool

# Usage in FastAPI
async def get_user_orders(tenant_id: str, user_id: str) -> list[dict]:
    async with tenant_connection(pool, tenant_id) as conn:
        # No WHERE tenant_id needed — RLS adds it automatically
        rows = await conn.fetch(
            "SELECT * FROM orders WHERE user_id = $1",
            user_id
        )
        return [dict(row) for row in rows]

import { Pool, PoolClient } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function withTenant(
  tenantId: string,
  fn: (client: PoolClient) => Promise
): Promise {
  const client = await pool.connect();
  try {
    await client.query(
      "SELECT set_config('app.current_tenant_id', $1, true)",
      [tenantId]
    );
    return await fn(client);
  } finally {
    client.release();
  }
}

// Usage:
const orders = await withTenant(req.tenantId, async (client) => {
  // RLS automatically scopes to tenantId
  const result = await client.query(
    'SELECT * FROM orders WHERE status = $1',
    ['completed']
  );
  return result.rows;
});

-- Separate policies for SELECT, INSERT, UPDATE, DELETE
-- More control than a single USING clause

-- SELECT: can only see own tenant's rows
CREATE POLICY orders_select ON orders FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- INSERT: automatically set tenant_id on insert
CREATE POLICY orders_insert ON orders FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- UPDATE: can only update own rows (and cannot change tenant_id)
CREATE POLICY orders_update ON orders FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- DELETE: can only delete own rows
CREATE POLICY orders_delete ON orders FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Test as the app role (NOT superuser)
SET ROLE myapp_app;

-- Without setting tenant — should return 0 rows
SELECT COUNT(*) FROM orders;  -- Should return 0 (no tenant set)

-- With tenant A set
SELECT set_config('app.current_tenant_id', 'tenant-a-uuid', true);
SELECT COUNT(*) FROM orders;  -- Only tenant A's rows

-- Switch to tenant B
SELECT set_config('app.current_tenant_id', 'tenant-b-uuid', true);
SELECT COUNT(*) FROM orders;  -- Only tenant B's rows, never tenant A's

RESET ROLE;

-- Essential: index on tenant_id for all RLS-protected tables
CREATE INDEX idx_orders_tenant_id ON orders (tenant_id);
CREATE INDEX idx_users_tenant_id ON users (tenant_id);