A
Aesthetic AI
← All Articles • Security & Architecture • 6 min read

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.