Skip to content

tenant-scoping.md

Latest commit

 

History

History
220 lines (167 loc) · 9.58 KB

tenant-scoping.md

File metadata and controls

220 lines (167 loc) · 9.58 KB
Raw

Tenant scoping

Every compiled query carries a tenantScope:

compiled.tenantScope // "single-tenant" | "cross-tenant" | "untenanted"

"single-tenant" means the query pins itself to one tenant. "cross-tenant" means it reads whatever the credentials can see. The builder only computes and reports this; it never blocks a query by itself. Turn on requireTenantScope on your Database (or check the field in your own executor) so a forgotten tenant filter fails loudly instead of quietly returning another tenant's rows.

All of this is optional. If no table declares a tenantColumn, every query is "untenanted" and none of this page applies.

Read this page before relying on the field. It answers a narrow question precisely, and silently answers "cross-tenant" for everything outside that narrowness.

Declare the tenant column

Row-per-tenant is the usual ClickHouse multi-tenancy shape, but whether a table has one — and what the column is called — is a schema decision, so it is declared on the table:

const Events = CH.table("events", {
	columns: { OrgId: CH.string, Name: CH.string },
	engine: CH.engine.mergeTree(),
	orderBy: ["OrgId"],
	tenantColumn: "OrgId",
})

The option is checked against the column names you just declared, so a typo is a type error rather than a query that silently never scopes. A table with no tenantColumn has nothing to pin, and compiles to the third scope:

const Untenanted = CH.table("untenanted", {
	columns: { tenant_id: CH.string, Name: CH.string },
	engine: CH.engine.mergeTree(),
	orderBy: [],
})

CH.from(Untenanted)
	.select(($) => ({ name: $.Name }))
	.where(($) => [$.tenant_id.eq("org_123")])
// tenantScope: "untenanted"  ← the column is not declared, so it does not scope

"untenanted" is not "cross-tenant", and the difference is the point: "this table has no row-level tenancy" and "this query reads every tenant's rows" are different facts, and an executor that refuses the second should not refuse the first — otherwise every query over a dimension or lookup table is refused.

The builder only says "untenanted" when it can see every source and none declares a tenant column. Anything it cannot see into — a CTE handed to it as an unannotated SQL string, an unscoped subquery over a table that does declare one — keeps the query at "cross-tenant", so the unknown case is still the refused one.

(Backed by docs/tenant-scoping.md > A table without a declared tenant column is untenanted.)

What marks a query scoped

A query is "single-tenant" when every tenanted source is confined to the same tenant. A binding is an equality to a literal or parameter, or a one-value in_. Tenant-key equalities can propagate that binding across joins. Filtering only the main table does not scope an independently joined tenant table.

CH.from(Events)
	.select(($) => ({ name: $.Name }))
	.where(($) => [$.OrgId.eq("org_123")])
// tenantScope: "single-tenant"

(Backed by docs/tenant-scoping.md > An OrgId equality scopes the query.)

Declaring a column that is not actually a tenant key will mark queries as scoped when they are not — the builder takes the declaration at face value. Know which case you are in before building an authorization decision on top of this.

Source-column references are qualified in generated SQL, including queries without joins. This prevents a SELECT alias such as OrgId from replacing the source tenant column in ClickHouse's alias resolution. A computed output alias never supplies tenant evidence.

Only eq and in_ count

$.OrgId.eq("org_123") // scopes
$.OrgId.in_("org_a") // scopes
$.OrgId.in_("org_a", "org_b") // cross-tenant
$.OrgId.eq($.OrgId) // does NOT scope
$.OrgId.neq("org_123") // does NOT scope
$.OrgId.like("org_%") // does NOT scope

!= and LIKE on the tenant column narrow nothing meaningful, and treating them as scoping would be worse than useless.

_(Backed by docs/tenant-scoping.md > in_ also scopes; neq does not.)_

and preserves evidence; or discards it

.where(($) => [$.OrgId.eq("org_123").or($.Name.eq("checkout"))])
// tenantScope: "cross-tenant"

This is the bug the marker exists to catch: OrgId = x OR anything matches rows from other tenants. or() drops scoping evidence deliberately. and() preserves it, so both a separate entry in the where array and $.OrgId.eq("org_123").and(otherCondition) can scope a source.

(Backed by docs/tenant-scoping.md > The marker does not survive or().)

Inherited scope

A query reading only from scoped sources is itself scoped, even with no where of its own:

const inner = CH.from(Events)
	.select(($) => ({ name: $.Name }))
	.where(($) => [$.OrgId.eq("org_123")])

CH.fromQuery(inner, "sub").select(($) => ({ name: $.name }))
// tenantScope: "single-tenant"

For joins, every tenanted source needs a binding to the same value. An inner join on tenant keys can propagate a binding in either direction. A LEFT JOIN's ON clause can constrain the right side, but cannot filter the preserved left side. An unmatched right row therefore never proves the left side is scoped. Bind the preserved side explicitly: with join_use_nulls=0, a filter matching the right column's default can retain unmatched rows from any left-side tenant. The builder conservatively avoids reverse propagation through LEFT JOINs.

Scalar and predicate subqueries also contribute their source scope, including through functions, arithmetic, boolean conditions, and window specifications. An unscoped inner query, or one bound to a different tenant, makes the outer query cross-tenant even when the outer table is filtered. An inner filter never substitutes for the outer table's own filter. Subqueries supplied as SQL strings are conservatively cross-tenant.

Typed CTEs, FROM-subqueries, and unions inherit their inner scope and bound tenant value. A union of org_a and org_b is cross-tenant even when each branch is individually scoped. Apply tenant filters inside each derived query: filtering a projected column outside it cannot prove that its aggregates or other columns exclude other tenants. Handwritten CTEs require a scope declaration; see Unions and CTEs.

crossTenant() — the explicit opt-out

CH.from(Events)
	.select(($) => ({ name: $.Name }))
	.where(($) => [$.OrgId.eq("org_123")])
	.crossTenant()
// tenantScope: "cross-tenant"

crossTenant() forces "cross-tenant" regardless of the predicates, and it wins over everything else. The point is to distinguish "this query deliberately spans tenants" from "someone forgot the filter", two states that are otherwise identical from the outside. Use it to document admin and internal-rollup queries. Both produce the same tenantScope string; the compiled query also carries crossTenantDeclared: true when the opt-out was explicit, which is what requireTenantScope checks. Keep privileged execution paths separately authorized.

Unions, inserts, updates and deletes have .crossTenant() too. Only the statement you run counts: a .crossTenant() subquery or CTE makes the outer query "cross-tenant", and the outer query declares it again.

(Backed by docs/tenant-scoping.md > crossTenant() is the explicit opt-out.)

route(tag)

const compiled = CH.compileUnsafe(CH.from(Events).select(…).route("archive"), params)
compiled.route // "archive"

.route(tag) is unrelated metadata that rides along on the compiled query, as a type-level fact as well as a runtime one. The tag is any string you like: it changes no SQL and means nothing on its own — it exists so a query definition can declare which backend it must be read from, and an executor that understands your vocabulary can honour it. If you have no such executor, ignore it.

(Backed by docs/tenant-scoping.md > route is carried onto the compiled query.)

Handwritten SQL

rawCompiledQuery cannot inspect a string, so its tenantScope is whatever you pass, and "cross-tenant" when you pass nothing. Passing "cross-tenant" explicitly counts as declaring it; leaving it out does not, so requireTenantScope refuses unannotated raw SQL. It also requires a reason and a justification naming why the query isn't a builder query at all. See Extending.

Enforcing it

const DatabaseLive = Db.layerSqlClient({ dialect: CH.clickhouseDialect, requireTenantScope: true })

With requireTenantScope, run dies with a TenantScopeViolation defect for any statement that is "cross-tenant" without crossTenantDeclared. "single-tenant" and "untenanted" run. It is a defect rather than an error because the bug is in the query: no caller should catch it and carry on. query and execute apply the same check to a compiled query, but SQL written with Db.sql has no scope to check and runs as it is, so keep tenant-table reads in the builder.

It is off by default, and without it tenantColumn only labels compiled queries.

(Backed by Database on PGlite > requireTenantScope refuses an undeclared cross-tenant statement and runs everything else.)

Scope is not authorization

"single-tenant" proves a structural restriction to one value, not that the requester is allowed to access that value. Resolve tenant IDs from trusted context. Treat "untenanted" as acceptable only for tables your application intentionally models as shared; omitting tenantColumn from a real tenant table bypasses that evidence. Repeated .where() calls AND with the earlier ones, so a tenant filter on a shared base query survives later filters.