Multi-Tenant Authorization

The previous lesson used scope to narrow a client’s authority. In every example, there was a single library behind the scenes: one set of members, one staff roster, one pool of records. Once the same server serves more than one institution, a new boundary enters the picture.

The difference of this boundary from the previous models is that it is drawn by data. The North Library’s clerk can see all of their own institution’s records; the South Library’s clerk in the same role can see their own institution’s records too. Their role, permissions, and token scopes are identical. The only thing that separates them is which rows the query touches.

Isolation Is Built by the Query, Not the Role

In a multi-tenant system, every record belongs to a tenant. The tenant is the institution that owns the data, and it sits in the schema as a column. When the authorization check does not look at this column, a request that passes the role check returns a neighboring institution’s record.

The computation below runs two repository implementations over the same data: one has forgotten the tenant filter, the other keeps it mandatory on every query.

cat > tenant.mjs <<'EOF'
// tenant.mjs — carrying tenant isolation into the query and auditing it
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.exec(`
  CREATE TABLE member (member_id TEXT PRIMARY KEY, tenant TEXT NOT NULL, name TEXT NOT NULL);
  CREATE TABLE loan (loan_id TEXT PRIMARY KEY, tenant TEXT NOT NULL, member_id TEXT NOT NULL,
                      isbn TEXT NOT NULL, status TEXT NOT NULL);
  INSERT INTO member VALUES ('U-1','north','Ella Novak'), ('U-2','north','Marcus Lee'),
                         ('U-9','south','Priya Kapoor');
  INSERT INTO loan VALUES ('O-1','north','U-1','978-0201896831','OPEN'),
                           ('O-2','north','U-2','978-0262033848','CLOSED'),
                           ('O-7','south','U-9','978-0131103627','OPEN'),
                           ('O-8','south','U-9','978-0201896831','OPEN');
`);

// 1. Repo that forgot the tenant filter: only the status condition exists.
const looseRepo = {
  name: "no tenant filter",
  openLoans: () => db.prepare("SELECT loan_id, tenant FROM loan WHERE status = 'OPEN'").all(),
  findMember: (id) => db.prepare("SELECT member_id, tenant, name FROM member WHERE member_id = ?").all(id),
};

// 2. Repo with a mandatory tenant filter: every query takes the tenant from context.
const strictRepo = {
  name: "tenant filter required",
  openLoans: (t) => db.prepare("SELECT loan_id, tenant FROM loan WHERE tenant = ? AND status = 'OPEN'").all(t),
  findMember: (t, id) => db.prepare("SELECT member_id, tenant, name FROM member WHERE tenant = ? AND member_id = ?").all(t, id),
};

const TENANT = "north";                       // the tenant of the staff member making the request
const leaked = (rows) => rows.filter((r) => r.tenant !== TENANT).length;

console.log("repo                    query             returned  leaked");
for (const [repo, rows] of [
  [looseRepo, looseRepo.openLoans()],
  [strictRepo, strictRepo.openLoans(TENANT)],
]) {
  console.log(`${repo.name.padEnd(23)} ${"openLoans".padEnd(17)} ${String(rows.length).padStart(5)} ${String(leaked(rows)).padStart(6)}`);
}
for (const [repo, rows] of [
  [looseRepo, looseRepo.findMember("U-9")],
  [strictRepo, strictRepo.findMember(TENANT, "U-9")],
]) {
  console.log(`${repo.name.padEnd(23)} ${"findMember('U-9')".padEnd(17)} ${String(rows.length).padStart(5)} ${String(leaked(rows)).padStart(6)}`);
}

// 3. The source of the tenant: from the request, or from the token?
function resolveTenant(source, token, request) {
  return source === "request" ? request.tenant : token.tenant;
}
const token = { subject: "P-2001", tenant: "north" };
console.log("\ntenant source    value in request  used        rows returned  leaked");
for (const source of ["request", "token"]) {
  const request = { tenant: "south", member_id: "U-9" };       // the client writes a different tenant
  const t = resolveTenant(source, token, request);
  const rows = strictRepo.findMember(t, request.member_id);
  console.log(`${source.padEnd(16)} ${request.tenant.padEnd(17)} ${t.padEnd(11)} ${String(rows.length).padStart(11)} ${String(leaked(rows)).padStart(6)}`);
}

// 4. Static audit: do queries reaching tenant-carrying tables include the filter?
const TENANT_TABLES = ["member", "loan"];
const QUERIES = [
  { name: "openLoans/loose",   sql: "SELECT loan_id FROM loan WHERE status = 'OPEN'" },
  { name: "openLoans/strict",  sql: "SELECT loan_id FROM loan WHERE tenant = ? AND status = 'OPEN'" },
  { name: "findMember/strict", sql: "SELECT name FROM member WHERE tenant = ? AND member_id = ?" },
  { name: "count",             sql: "SELECT count(*) FROM loan" },
  { name: "book list",         sql: "SELECT isbn FROM book" },
];
console.log("\nquery                  table   tenanted  filter  finding");
let findings = 0;
for (const q of QUERIES) {
  const table = /FROM\s+(\w+)/i.exec(q.sql)?.[1] ?? "-";
  const hasTenantCol = TENANT_TABLES.includes(table);
  const hasFilter = /\btenant\s*=\s*\?/i.test(q.sql);
  const flagged = hasTenantCol && !hasFilter;
  if (flagged) findings++;
  console.log(`${q.name.padEnd(22)} ${table.padEnd(7)} ${(hasTenantCol ? "yes" : "no").padEnd(9)} ${(hasFilter ? "yes" : "no").padEnd(7)} ${flagged ? "MISSING FILTER" : "-"}`);
}
console.log(`audit findings: ${findings} / ${QUERIES.length}`);
EOF
node tenant.mjs

repo                    query             returned  leaked
no tenant filter        openLoans             3      2
tenant filter required  openLoans             1      0
no tenant filter        findMember('U-9')     1      1
tenant filter required  findMember('U-9')     0      0

tenant source    value in request  used        rows returned  leaked
request          south             south                 1      1
token            south             north                 0      0

query                  table   tenanted  filter  finding
openLoans/loose        loan    yes       no      MISSING FILTER
openLoans/strict       loan    yes       yes     -
findMember/strict      member  yes       yes     -
count                  loan    yes       no      MISSING FILTER
book list              book    no        no      -
audit findings: 2 / 5

The Observable Consequence of a Forgotten Filter

The first part of the output counts the leak. The filterless repo returns three records for the open-loans query, and two of them belong to a different tenant. The member-lookup query is more striking: the requested identifier belongs to a neighboring institution’s member, and the filterless query returns that record — the role check passed, because the clerk does have “read member” permission. The permission is correct; what is wrong is that which rows the permission applies to was never asked.

The filtered repo returns zero leaks for the same two queries. The second query’s result is empty: the record exists, but not for this tenant. The choice between an empty result and an unauthorized response is a design decision, and an empty result is usually preferred — even the information “this identifier exists at a different institution” is a cross-tenant leak.

Where the Tenant Identity Comes From

The second part shows a more subtle bug. If the tenant identity is read from the request’s body or a path parameter, the client can change it: the request writes south, the query uses that tenant, and a neighboring institution’s record comes back. The same request returns zero records when the tenant identity is taken from the token instead.

The rule is one sentence: the tenant identity comes from the authenticated context; a tenant value coming from the request can be, at most, a filter, and can never be the source of authority. If the tenant the client sends conflicts with the tenant in the context, the request is denied — and this conflict is also an event worth logging.

Making the Filter Hard to Forget

The third part writes an audit: do queries reaching tables that carry a tenant column include the filter? Two of the five queries in the model produced a finding — one is the deliberately loose repo, the other looks like an innocent count query. The count query returns a single number, but that number is the total across all tenants; on a dashboard, this leaks a neighboring institution’s business volume.

The audit’s value is that it catches forgetting structurally. The same job is done by three layers together, and each one catches what the other misses:

1. The repo layer: a query with no tenant parameter cannot be written — the function that builds the query takes the tenant as a mandatory argument.
2. The audit script: newly written queries are scanned in continuous integration; a query without a filter fails the build.
3. The database layer: a row-level security policy filters rows even if the application layer makes a mistake. This is the application-side counterpart of the Row-Level Security lesson in the Relational Database Administration course.

All three are needed. Relying on the repo layer alone breaks in a reporting script that writes raw queries; relying on the database layer alone breaks in a batch job that never binds a tenant context to its session.

Physical Placement of Tenant Data

Isolation has three placement forms, and the choice criterion is the same three questions: the likelihood of accidentally reaching a neighboring tenant’s data, cost per tenant, and the granularity of migration and backup.

Shared table (the model in this lesson) is the cheapest and demands the most care: every record sits in the same table, and the query provides isolation. Schema per tenant moves the filter to the connection level; connecting to the wrong schema is still possible, but a lapse inside the query becomes harmless. Database per tenant gives the strongest isolation; its cost is that migration and backup work is multiplied by the tenant count.

The decision does not have to be made once: small tenants can live in a shared table while large tenants whose contract requires isolation are kept in a separate database. In that case, the application layer must see both placements behind the same interface.

Summary

• In a multi-tenant system, the boundary is drawn by data, not by role or scope; two clerks with the same role see different row sets.
• When the tenant filter is forgotten, a request that passes the role check returns a neighboring institution’s record; in the model, two of the three rows from one query leaked, and the filtered version leaked none.
• The tenant identity comes from the authenticated context; a value coming from the request cannot be the source of authority — in the model, a tenant read from the request opened a neighboring institution’s record.
• Forgetting the filter is made harder with three layers: a repo that makes the tenant a mandatory argument, an audit that scans queries, and a row-level security policy.
• Physical placement (shared table, schema per tenant, database per tenant) is a trade-off between isolation strength and cost per tenant.

Next Step

The tenant boundary determined which set a request can touch. Within that set, individual objects still exist: two members of the same library must not see each other’s loan record. The course’s last lesson takes up this final boundary — what happens when a different identifier is written into the address bar, and where object ownership must be checked.