CodeOrigin All articles
Software Engineering

The Join Is the Gate: Multi-Tenant Authorization from Relationships to Queries

CodeOrigin
The Join Is the Gate: Multi-Tenant Authorization from Relationships to Queries

Photo: Nick Jenkins, Timo Tijhof, CC BY-SA 4.0, via Wikimedia Commons

Authorization failures rarely begin in the authorization layer. They begin earlier, when a team treats access as a set of role checks layered onto a data model that already knows who belongs to what. At CodeOrigin, the useful starting point is a question: what relationship must exist for this action to be legitimate? Multi-tenant authorization becomes more reliable when that relationship is the source of truth and the query is where it is enforced.

Begin with relationship questions, not role names

A session asks to read document 42. The real question is not whether the user has the editor role in the abstract. It is whether this user is a member of the organization that owns the document, and whether that membership permits reading. Ownership, membership, and tenancy are relationships. Roles are only shorthand for bundles of permissions attached to those relationships. If the schema records organizations, memberships, projects, and documents, it already contains most of the authorization model. The engineering task is to make those relationships explicit and to prevent code from bypassing them.

Give tenancy a column and a constraint

A tenant identifier should not be an optional filter added by careful developers. It should be a structural property of every tenant-owned row. In a relational schema, that means a non-null tenant_id on documents, projects, invoices, and similar tables. It also means composite foreign keys so a child row cannot point at a parent in another tenant. For example:

CREATE TABLE documents (
  id uuid [PRIMARY KEY](https://en.wikipedia.org/wiki/Primary_key),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  project_id uuid NOT NULL,
  title text NOT NULL,
  UNIQUE (id, tenant_id),
  FOREIGN KEY (project_id, tenant_id)
    REFERENCES projects(id, tenant_id)
);

The unique pair on the parent side and the composite foreign key make cross-tenant parentage a database error rather than a review comment. This is the first principle in practice: if the data model can represent an illegal relationship, some code path eventually will.

Compile policy into the query

A common but fragile pattern fetches a resource by primary key, then checks access in application code. It works until a new endpoint forgets the check, a background job reuses the repository, or a cache returns a row that was authorized under different assumptions. The stronger pattern compiles the authorization rule into the query itself. The database returns only rows that satisfy the relationship.

SELECT d.id, d.title
FROM documents d
JOIN memberships m
  ON m.tenant_id = d.tenant_id
 AND m.user_id = $1
WHERE d.id = $2
  AND d.tenant_id = $3
  AND m.role IN ('owner', 'editor', 'viewer');

Here $1 is the user, $2 is the document, and $3 is the tenant from the session. The query cannot return a document from another tenant because the tenant condition and the membership join are part of the same retrieval. For a write action, the permitted role list changes; the shape does not. This keeps the policy close to the schema and makes every repository method state its access assumptions.

Test the edge where identifiers cross tenants

The dangerous test is not the happy path. It is a valid document identifier from tenant A presented with a session scoped to tenant B. The expected result should be indistinguishable from a missing document, usually a 404 rather than a 403, so the API does not confirm that the identifier exists elsewhere. Build a small matrix: a user who belongs to both tenants; a user whose membership was revoked; a user whose role changed from editor to viewer; a nested resource whose parent belongs to a different tenant; and an administrative path that uses a separate, audited mechanism. Each case should assert on the repository result, not only on the HTTP handler.

A compact integration test might look like this:

const sessionB = { userId: 'u1', tenantId: 'tenant-b' };
const docFromA = 'doc-in-tenant-a';

await expect(
  documents.findForSession(sessionB, docFromA)
).resolves.toBeNull();

The test is intentionally boring. That is the point. Authorization should fail by returning no row, not by relying on a later guard to remember the tenant.

Keep the model honest at every boundary

Queries are the main gate, but they are not the only boundary. Cache keys should include the tenant. Background jobs should carry an explicit tenant context. Logs and audit records should separate the acting user from the tenant in which the action occurred. None of these replace the query constraint; they prevent adjacent systems from reintroducing the ambiguity the schema was designed to remove.

Multi-tenant authorization is often described as a policy problem. It is more accurately a modeling problem with a query implementation. When ownership, membership, and tenancy are represented as relationships, permission becomes a join condition. The join is the gate, and the data model is the specification.

All Articles

Related Articles

The Ambient Lie: Configuration as an Explicit Process Boundary

The Ambient Lie: Configuration as an Explicit Process Boundary

Coexistence Is the Constraint: Migrating Schemas While Two Versions Serve Traffic

Coexistence Is the Constraint: Migrating Schemas While Two Versions Serve Traffic

The Cent That Wasn't There: A Money Model for Invoices That Must Add Up

The Cent That Wasn't There: A Money Model for Invoices That Must Add Up