Multi-tenant Data Scoping

The Missing WHERE Clause

Multi-tenant breaches rarely look like hacks. They look like a forgotten filter:

// broken: the id alone decides what comes back
Query: {
  invoice: (_, { id }) => db.oneOrNone('SELECT * FROM invoices WHERE id = $1', [id]),
},

Any authenticated caller can walk ids — invoice(id: "41"), invoice(id: "42") — and read other companies' invoices. The tenant exists everywhere in the system except the statement that fetches data.

The fix belongs in the query, and the value comes from verified claims:

Query: {
  invoice: (_, { id }, ctx) =>
    db.oneOrNone(
      'SELECT * FROM invoices WHERE id = $1 AND tenant_id = $2',
      [id, ctx.viewer.tenantId]                 // set during authentication
    ),
},

Two tempting shortcuts, both wrong:

Fetch-then-check. const row = await get(id); if (row.tenantId !== ctx.viewer.tenantId) throw … still shipped the row out of the database, requires the same check at every call site, and makes timing and error differences observable. Filtering in SQL means the row never leaves the store for a caller who may not see it.

Client-supplied scope. invoice(id: ID!, tenantId: ID!) hands the filter to the attacker. Tenant comes from the token claim or from a host header you resolve server-side to a tenant record — never from an argument the request invents. The same applies one level down: loader batches must carry the scope in both the cache key and the statement:

new DataLoader(async (ids) => {
  const rows = await db.query(
    'SELECT * FROM invoices WHERE tenant_id = $1 AND id = ANY($2)',
    [viewer.tenantId, ids]
  );
  ...
});

The rule: every statement that touches tenant data has a tenant predicate, including the ones written for background jobs, exports, and "temporary" admin scripts. Grep for the table name and count predicates — that audit is faster than any framework.

Row-Level Security as a Second Layer

Application filters depend on every developer remembering them forever. PostgreSQL row-level security (RLS) enforces the same rule inside the database, so a forgotten predicate returns nothing instead of another tenant's rows:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant_id', true));

The application sets the scope once per transaction, and the predicate applies to every statement automatically:

BEGIN;
SET LOCAL app.current_tenant_id = 'acme';
SELECT * FROM invoices;               -- acme rows only, no explicit filter
-- role without BYPASSRLS cannot opt out
COMMIT;

Defense in depth, with honest caveats:

• It catches the bug you have not written yet. A new repository method missing WHERE tenant_id = still cannot cross tenants. That is the whole argument for the second layer.
• SET LOCAL is transaction-scoped; a pooled connection that skips the transaction leaks scope. Use SET inside a transaction you control, and RESET ALL (or a fresh connection) on release so the next borrower does not inherit acme's identity. This is the single most common RLS footgun.
• The database role matters. The table owner and roles with BYPASSRLS are exempt by design — an application role must not be the owner.
• Background jobs have no request. Workers must set an explicit tenant context per unit of work or run as a bypass role with its own audit trail.

RLS does not remove the need for the application filter: it protects reads, while your queries still decide which columns and joins are appropriate, and it does nothing for object-level checks inside a single tenant.

Enumeration, Object Checks, and Loader Scope

Isolation that returns the wrong answer still leaks. Three leak paths survive a correct tenant filter:

Distinguishing not-yours from does-not-exist. FORBIDDEN for another tenant's id and NOT_FOUND for a random id is a membership oracle: an attacker probing sequential ids learns which ones are real and roughly how large your dataset is. Return the same shape, same extensions.code, and similar timing for both — null or an identical NOT_FOUND is usually the right answer for cross-tenant reads. Reserve a distinguishable error for records the caller genuinely owns.

Listing endpoints. Single-record resolvers get the famous audit; invoices(first: 50) written as SELECT * FROM invoices ORDER BY created_at LIMIT 50 hands over a stranger's page. Every list, connection, search, and aggregate needs the same predicate — aggregates especially, since count() across tenants leaks business volume even when individual rows are filtered.

Shared loader caches. A batch query with a tenant filter is still wrong if its results are memoized under a bare id across requests — the hit skips the filtered query entirely. Scope keys and instances per request; the mechanics are in request-scoped loaders.

Ownership checks belong with role-based field access: the directive or helper decides whether to look, and the tenant predicate decides what the lookup can see. Together they give the property an interviewer will ask you to state precisely: a caller can only ever read rows their tenant owns, and cannot tell which ids outside their tenant exist.

A compact audit for a new feature: find every statement touching tenant tables, confirm each has a tenant predicate, confirm list endpoints are filtered before LIMIT, and confirm the error response for a foreign id matches the response for a missing one.