Authorization Models for Multi-Tenant Data Isolation

Multi-tenancy turns authorization from a per-request question into a per-row invariant. In a single-tenant system, an authorization error exposes one organization’s data to its own users. In a multi-tenant system, the same error exposes one organization’s data to another’s, and the damage is not recoverable by an operator who knows the parties. The architectural question is where the tenant boundary is enforced, and the answer determines how many code paths must be correct for the boundary to hold.

Tenant identity is not user identity

A user identity says who is acting. A tenant identity says which partition of the data the action may touch. The two are related by membership, which is itself data that can change: a user may belong to several tenants, and the selection among them is per-request state rather than a property of the account.

The first design decision is where the tenant selection lives. If it is inferred from the resource identifier, then the lookup that finds the resource is the authorization check, and any path that resolves an identifier without checking tenancy is a hole. If it is carried in the credential, then the credential must be scoped to one tenant, and a user who switches tenants obtains a new credential. The second is safer because the check is on every path that trusts the credential, and the first requires discipline in every query.

Binding tenancy into a signed token has a specific failure mode: the token’s tenant claim is trusted by services downstream, so a token issued for tenant A must not be accepted for an operation on tenant B by a service that only checks the claim. If the request also names a tenant, the service must compare the two rather than using the named one. Parameters that are authorized and parameters that are used must be the same value, which is the check-and-use rule at the request level.

Filtering in the application versus filtering in the database

Application-level filtering means every query includes a tenant predicate: WHERE tenant_id = ?. The predicate is written by the developer at each call site, and the security property is that no call site forgets it. Any new query, any ad-hoc analytics console, any ORM escape hatch that builds SQL, and any batch job that reads the table directly is a place the predicate can be missing.

Database-level enforcement moves the predicate into the storage engine. PostgreSQL row security policies, for instance, attach a policy expression to a table and apply it to every statement by every role that is subject to it, with BYPASSRLS and table-owner exceptions. The policy is expressed in SQL and evaluated per row, which means the developer cannot forget it, and also means the cost is paid per row rather than per query.

The performance character differs. An application-level predicate on an indexed column is a cheap range restriction that the planner can use to choose an index. A row security policy is also visible to the planner in current PostgreSQL versions and can use indexes when the policy expression is indexable, but a policy that calls a function or references a session variable through a subquery can defeat the index and force a scan. The realistic conclusion is that database enforcement is the correct default and requires attention to how the policy is written.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

The two clauses answer different questions. USING filters which existing rows are visible to SELECT, UPDATE, and DELETE. WITH CHECK validates rows being inserted or updated, so it is what prevents a caller from writing a row belonging to another tenant. Omitting WITH CHECK leaves reads constrained and writes unrestricted, which is a complete bypass through an insert.

Setting the session variable is the trust boundary. If the application sets app.tenant_id from a request parameter without validating it against the caller’s memberships, the policy enforces consistency with an attacker-chosen value. The variable must be set from the authenticated context, and connection pooling means it must be reset or transactionally scoped, since a pooled connection carries session state from the previous tenant.

Isolation models and what each one guarantees

Separate databases per tenant give the strongest boundary available in a relational system: no shared table means no policy that can be miswritten to cross tenants, and backup, restore, and deletion are per-tenant operations. The costs are connection and catalog overhead proportional to tenant count, migration time proportional to tenant count, and a control plane that must route each request to the right database. Practical systems with thousands of small tenants find the per-tenant database model expensive in operational rather than compute terms.

Shared tables with a tenant column, enforced by row policies, give weaker isolation with lower overhead. Restores are per-tenant only if the backup format supports selective extraction, which most do not; a point-in-time restore restores all tenants to the same moment, so recovering one tenant’s accidental deletion also reverts every other tenant’s data to that moment. This is the trade that is rarely stated: shared tables make single-tenant recovery a cross-tenant operation.

Separate schemas per tenant sit between the two. They share a database and its connection pool, separate the catalog, and allow per-tenant backups at the schema level. They suffer from catalog size and from the fact that every connection’s search_path becomes security-relevant, since a schema name resolved from a request parameter selects the data.

Physical separation, one deployment per tenant, gives the strongest guarantee and the highest cost. It is chosen when the tenant’s compliance obligations require dedicated infrastructure or when the tenants’ workloads have incompatible scaling characteristics.

The queries that bypass the boundary

The dangerous paths are the ones that do not look like feature code: administrative tooling that connects with elevated credentials, cross-tenant analytics that legitimately need to see everything, cache keys that omit the tenant, and object storage paths where the tenant is a prefix rather than an enforced condition.

Each of these needs an explicit decision. An analytics role with BYPASSRLS is a credential that can read every tenant’s data, so its issuance must be controlled and its use audited independently. A cache keyed by resource id alone will serve one tenant’s cached object to another, and the fix is to key by the pair, which is a global invariant of the caching layer rather than a local fix. Object storage typically has no row-level policy, so isolation is enforced by bucket policy and prefix scoping, both of which depend on the request path being built correctly.

The strongest structural answer is to make the tenant a required component of the request context at the framework boundary, so that a handler which does not have a tenant cannot construct a query. Type-level or middleware-level enforcement does not prove the tenant is correct, but it removes the entire class of forgotten-predicate defects, which is where most real failures originate. The same reasoning applies to the enforcement point decomposition: the boundary is only as complete as the set of paths that pass through it, and every path that does not is an assumption.

Verification for this class of system is best done adversarially and mechanically: enumerate every route and every data store, and for each, assert that a request authenticated as tenant A cannot read, write, or infer tenant B’s data. The inference clause matters because response timing and error differentiation can leak the existence of another tenant’s records even when the content is filtered, which turns an authorization policy into a membership oracle.

Deletion and retention as authorization operations

Deleting a tenant is an authorization operation with a wider blast radius than deleting a row, and the model must decide what a delete means. In a shared-table design, deletion is a predicate applied across every table, ordered by foreign keys, and it must be complete: a row left behind in an audit table is both a privacy obligation and a cross-tenant data exposure if the orphaned row’s identifier is later reused.

Identifier reuse is the case that breaks shared-table designs quietly. If tenant identifiers are integers allocated from a sequence and a deleted tenant’s identifier is later assigned to a new tenant, every row the first tenant left behind becomes visible to the second. Random identifiers, generated with enough entropy that collision is not a practical concern, remove the failure by making reuse impossible rather than by making deletion complete. The two properties are independent, and relying on the stronger deletion discipline to compensate for guessable identifiers is the usual mistake.

Retention obligations push in the other direction. A record that must be retained for a stated period cannot be deleted when the tenant requests deletion, so the retention rule has to be expressible as a per-record attribute that the row policy consults, and the deletion operation becomes a state transition rather than a removal. Systems that implement retention by copying records into a separate store lose the row policy on the copy, which is why archival stores need the same tenant predicate applied independently rather than inheriting the guarantee by assumption.