Multi-Tenant Authorization And Data Isolation

What's being tested

Interviewers probe your ability to design and implement tenant-aware systems that prevent data leaks while remaining operationally efficient. They're checking system-design tradeoffs (shared vs isolated data), concrete enforcement mechanisms (application-layer filters, row-level security, DB indexes, auth tokens), and operational considerations like migrations, backups, and performance isolation. Rippling cares because HR/finance apps handle sensitive PII and regulatory constraints, so you must show both secure defaults and pragmatic engineering tradeoffs.

Core knowledge

Isolation models — three canonical patterns: shared schema (single DB, tenant column), isolated schema (per-tenant schema in same DB), and per-tenant database; cost, operational complexity, and noisy-neighbor risk increase left→right.
Enforcement layers — application-layer filters, DB-level policies (e.g., Postgres row-level security), and network/identity controls; defense-in-depth: prefer DB-enforced policies plus app checks.
Tenant identifier design — use an immutable tenant_id (opaque UUID, not sequential ints) added to every multi-tenant table; enforce via composite primary/unique keys like (tenant_id, id).
Indexing & partitioning — index on (tenant_id, created_at) for tenant-scoped queries; for very large tenants use table partitioning by tenant or time to avoid full-table scans.
Unique constraints — per-tenant uniqueness requires composite unique indexes; global uniqueness (e.g., email across all tenants) needs a global index/table or global namespace strategy.
Auth model — combine RBAC for coarse roles and ABAC/policy for resource-scoped rules; include tenant claim in tokens (JWT claim or session metadata) and validate server-side on every request.
Row-level security practicalities — RLS enforces at DB layer but needs correct session context (e.g., SET local app.current_tenant = '...') and careful testing to avoid accidental bypass by superusers or SECURITY DEFINER functions.
Data residency & compliance — if tenants require regional isolation, prefer per-tenant DB or physical region replication; shared DB complicates legal data-erase/restoration.
Encryption & keys — use envelope encryption with per-tenant data keys stored in KMS; rotate keys and design for per-tenant key revocation without re-encrypting entire DB when possible.
Operational concerns — migrations must be backward-compatible (add columns nullable, backfill asynchronously), deploy feature flags for schema changes, and plan tenant-aware backfills to avoid downtime.
Backups & restores — backing up a shared DB requires tenant-aware restore strategy (logical export of tenant rows, or row-based restore tooling); per-tenant DB simplifies single-tenant restores.
Auditing & observability — log tenant-scoped audit trails for reads/writes (who, what, tenant) and monitor per-tenant resource usage to detect noisy neighbors and enforce quotas.

Worked example — "Design authorization and data isolation for a multi-tenant HR SaaS"

First 30s framing: ask tenant scale (hundreds vs hundreds of thousands), data sensitivity (PII, payroll), compliance (SOC2, GDPR), and expected per-tenant traffic. Declare assumptions (e.g., 10k tenants, medium-sized data, need per-tenant restore for compliance). Skeleton of an answer: (1) pick isolation model: recommend shared schema + DB-level RLS for cost and operational simplicity but per-tenant DB for high-compliance tenants; (2) auth flow: require JWT with tenant_id claim, validate in gateway, set DB session context; (3) schema & indexes: add tenant_id to all multi-tenant tables, create composite indexes and per-tenant partitioning strategy for large tenants; (4) ops: migrations with non-blocking changes, per-tenant backfills, and per-tenant backup/restore plan; (5) monitoring & keys: per-tenant quotas, audit logs, and per-tenant encryption keys via KMS. Key tradeoff to flag: shared schema + RLS gives operational simplicity but requires disciplined CI/testing and DB policy validation to avoid leaks; per-tenant DB gives stronger isolation but increases cost and deployment complexity. Close by stating priorities for next steps: implement a small proof-of-concept RLS policy, build test suite that simulates cross-tenant access, and plan migration strategy; "if I had more time, I'd build automated tests that create tenants and fuzz access controls to prove the RLS correctness."

A second angle — "Implement tenant-aware access control for reporting queries at scale"

Reporting often needs large, cross-tenant aggregation or tenant-isolated analytics. Constraint shift: analytics workloads are heavy and may require denormalized/multi-tenant data warehousing. Use an ETL to copy vetted, tenant-tagged data into a reporting warehouse (e.g., Redshift, BigQuery) where each row still includes tenant_id. Enforce access using dataset-level IAM plus query-layer checks; for per-tenant reports, pre-aggregate per-tenant materialized views or use separate datasets for top customers. Tradeoffs: exporting to warehouse gives performance isolation and faster analytics but increases surface area for leaks if pipeline ACLs are misconfigured. For cross-tenant analytics for platform metrics, use a controlled, audited service account with narrow privileges and aggregated-only datasets (no raw PII).

Common pitfalls

Pitfall: Assuming application-layer tenant checks are sufficient. Relying only on app code leaves dangerous gaps: overlooked endpoints, background jobs, or direct DB access can leak data. Always pair with DB-level enforcement.

Pitfall: Not planning migrations for multi-tenant constraints. Performing a migration that adds a non-null column or changes uniqueness without staged backfill risks downtime and broken tenants; design backward-compatible migrations and phased rollouts.

Pitfall: Treating tenant_id as mutable or using predictable integers. Using mutable or sequential IDs breaks referential integrity, complicates restores, and increases risk; prefer immutable opaque IDs (UUID) and treat tenant_id as part of the primary key.

Connections

Interviewers may pivot to rate limiting & resource quotas (how to prevent noisy neighbors), per-tenant billing/metering, or data residency (GDPR/CCPA) requirements; familiarity with those adjacent areas shows you thought the problem end-to-end.