Why We Enforce PostgreSQL Row-Level Security on Every AI Tenant Table
By Aesthetic AI Architecture Team
The Fatal Weakness of Application-Level Multi-Tenancy
In standard SaaS web applications, multi-tenancy is frequently implemented at the ORM or query builder layer:
// Dangerous pattern: A single forgotten where clause leaks confidential patient data
const data = await db.select().from(documents).where(eq(documents.id, docId));
If a single developer forgets to append `eq(documents.tenantId, currentTenantId)`, or if a query joins a secondary relation without an explicit tenant predicate, one tenant can view or delete another tenant's rows. In an aesthetic clinic or hospital setting, this constitutes a severe HIPAA and KVKK privacy violation.
Hardware-Level Defense: PostgreSQL Row-Level Security
PostgreSQL Row-Level Security (RLS) pushes isolation logic directly into the database engine. When RLS is enabled and forced, PostgreSQL automatically applies the security filter to **every query**, regardless of who wrote the application SQL:
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_policy ON documents
FOR ALL
USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);
Fail-Closed Connection Pooling
When using connection poolers like Cloudflare Hyperdrive or PgBouncer, connections are reused across different web requests. If a tenant variable were set globally via `SET app.current_tenant_id = '...'`, the next request reusing that socket could inherit the previous tenant's identity! To make connection pooling 100% leak-proof, Aesthetic AI enforces **transaction-local session configuration**:
export async function withTenantContext(
tenantId: string,
operation: (tx: any) => Promise
): Promise {
if (!tenantId) {
throw new TenantIsolationError("Tenant context missing; aborting fail-closed.");
}
return await db.transaction(async (tx) => {
// SET LOCAL only applies to the current transaction and reverts on COMMIT/ROLLBACK
await tx.execute(sql`SELECT set_config('app.current_tenant_id', ${tenantId}, true)`);
return await operation(tx);
});
}
Even if an attacker crafts an injection or exploits an unauthenticated endpoint, PostgreSQL returns zero rows because the tenant context is empty.