Multi-tenant SaaS with Postgres RLS: the patterns that matter

Row-level security is the right way to do multi-tenancy. Here are the patterns I learned building Kairos.

Why RLS

The alternative is filtering in application code. Every query needs a WHERE tenant_id = ? clause. Miss one, and you have a data leak. RLS moves this to the database layer — it's impossible to accidentally query another tenant's data.

Setting up RLS

-- Enable RLS on the table
ALTER TABLE objectives ENABLE ROW LEVEL SECURITY;

-- Create policy
CREATE POLICY tenant_isolation ON objectives
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

The connection pool problem

RLS policies use session variables. But connection pools share connections. The solution: set the tenant context at the start of every request.

// Middleware pattern
async function withTenant(tenantId: string, fn: () => Promise<T>) {
  await db.query("SET app.tenant_id = $1", [tenantId]);
  try {
    return await fn();
  } finally {
    await db.query("RESET app.tenant_id");
  }
}

Partitioned audit logs

Audit logs grow fast. Time-based partitioning makes queries efficient and enables easy retention policies.