Multi-Tenant Database Architecture: Row-Level Security vs Schema-per-Tenant

The Architectural Crossroads of B2B SaaS

Every engineer building a multi-tenant B2B application eventually faces the same high-stakes question: How do we isolate customer data so that Company A can never, under any circumstance, see Company B's records?

Choose the wrong model, and you either:

1. 1 Suffer a catastrophic cross-tenant data leak that destroys customer trust.
2. 2 Build an operational monster that requires hours to run a single database migration across hundreds of separate customer schemas.

3 Core Multi-Tenancy Models

Implementing Bulletproof Row-Level Security (RLS)

PostgreSQL Row-Level Security (RLS) is not an application-level filter. It is enforced directly by the database engine, even if an application engineer forgets to include a WHERE tenant_id = ... clause in their query.

-- Enable RLS on the core accounts table
ALTER TABLE work_orders ENABLE ROW LEVEL SECURITY;

-- Force RLS even for table owners and superusers
ALTER TABLE work_orders FORCE ROW LEVEL SECURITY;

-- Create policy tied to current session variable
CREATE POLICY tenant_isolation_policy ON work_orders
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

When a user request arrives at your API:

class="text-[class="text-[#6a9955]">#6a9955]">// Express or Next.js API Middleware
export async function withTenantContext(tenantId: string, callback: (tx: DatabaseClient) => Promise<any>) {
  return await db.transaction(async (tx) => {
    class="text-[class="text-[#6a9955]">#6a9955]">// Set the tenant ID for this transaction boundary only
    await tx.query("SELECT set_config(&class="text-[#6a9955]">#class="text-[#b5cea8]">039;app.current_tenant_id&#class="text-[#b5cea8]">039;, $class="text-[#b5cea8]">1, true)", [tenantId]);
    return await callback(tx);
  });
}

Any subsequent query—including complex nested joins—automatically inherits this filter at the query planner level.

Connection Pooling Gotchas with PgBouncer

When scaling a multi-tenant application with connection poolers like PgBouncer in transaction pooling mode:

• Never use SET session app.current_tenant_id.
• Always pass the third parameter true in set_config(..., true) so the variable resets automatically when the transaction completes.

By combining PostgreSQL RLS with automated SOC2 access auditing, engineering teams can scale to thousands of enterprise tenants with absolute peace of mind.