Open-source AI teammates in Slack, Teams, Discord, Telegram and Mattermost. Self-host it, with any agent harness and any model.
Python agent-orchestrationaiai-agentschatopsdiscordlangchainmcpmicrosoft-teamsmulti-agentopen-sourceopenaiself-hostedslack
"""The whole exemption from row-level security, written out as nineteen functions.
Row-level security is enforced by `require_tenant_id()` (`db/rls_ddl.py`),
which raises when no tenant is bound. That is the property everything else
rests on, and it has one unavoidable consequence: **resolving who the caller
is is what produces the tenant, so it necessarily runs unbound.** A bearer
token names an `api_keys` row before anyone knows which tenant that row is in.
A boot pass over every bridge has to see rows in tenants it has not bound yet.
Both are reads the policy refuses.
The design this replaces called that an "unscoped session" and left it at
that. Under the owner connection every environment used, an unscoped session
did read across tenants, so the model looked complete. Under a restricted
role it is not a hatch at all — unscoped is precisely the state
`require_tenant_id()` raises on — and a real deployment under a restricted
role died at the first of nineteen such sites before it finished booting:
seventeen of them the `unscoped_session` helper's own call sites
(`db/session_scope.py`), and two more raw-session credential reads that
leaned on the same ownership exemption without ever calling it.
**What is exempt is this module, and nothing else.** Each function below is
`SECURITY DEFINER`, owned by the schema owner, and so runs outside the
policies (Postgres exempts a table's owner from its own policies, and this
design deliberately never sets `force row level security` — see
`test_tenant_lookup.py`, which pins that). Two properties make the exemption
worth having:
- **They answer *which tenant*, never *what row*.** Every one returns
`setof text`: tenant ids. What exactly that does and does not concede is
worth stating precisely, and is stated below rather than summarised here,
because the obvious summary of it is false.
- **They are a closed list.** `TENANT_LOOKUPS` below is the list; a test
compares it against the functions actually installed, against the
migration's frozen copy, and against what each one answers when called as
the restricted role. Adding one is an edit a reviewer sees, and is meant to
be argued with rather than waved through.
**What the exemption gives, stated exactly.** Every lookup returns `setof
text` — tenant ids, never a row of a scoped table. That much is the part
worth having: no customer data crosses a tenant boundary through this module,
on any function, for any argument.
Three things the shorter statement of that property — "the most any caller can
extract is the tenant of an identifier it already holds" — got wrong, all of
them about *metadata* rather than rows:
1. `all_tenant_ids()` takes no identifier. It enumerates the deployment: how
many tenants there are and what their ids are. Nothing about it is narrowed
to what the caller already holds, and nothing can be — the boot fan-outs
and the runtime-state sweep exist precisely to visit tenants the caller has
never heard of.
2. `EXECUTE` used to be left at the `PUBLIC` default a new function gets, so
any role with `CONNECT` on this database could call every one of them. That
is closed: `grant_runtime_role` revokes `EXECUTE` on the schema's functions
from `PUBLIC` and grants it to the runtime role by name, and the boot
self-check refuses to serve if `PUBLIC` has it back.
3. `users` and `oidc_identities` carry no tenant and so no policy
(`db/rls_ddl.py`'s `GLOBAL_TABLES`) — a person is global, and the
per-tenant object is the `tenant_members` row. A session with nothing bound
may therefore read every user id, and feeding those one at a time to
`tenants_of_user` reconstructs the whole user-to-tenant membership graph.
That follows from the schema rather than from this module, and it is not
closed here.
So the property this design actually holds is: **the exemption discloses the
shape of the deployment — which tenants exist, and which tenant a given user,
credential, room, bridge, connector, invitation or installed workspace belongs
to — and no row of any tenant-scoped table.** The same holds for organizations:
`all_organization_ids()` discloses which exist, and no row of an
organization-scoped table. It is a boundary on data, not on
metadata. Narrowing
the second is a question about who may hold the runtime role's credentials at
all, since everything above is reachable by anyone who has them.
Everything else in the process binds a tenant. The nineteen sites that could
not are now one of three shapes, and the shape is the interesting part:
1. **Credential resolution** — the bearer token, the JWT subject, the OIDC
client id, the registration token. Resolve the *tenant* here, bind it,
then read the row itself through the ordinary scoped store. The row never
arrives from an exempt path.
2. **Cross-tenant enumeration** — the boot passes over every client, bridge,
connector and room, and the runtime-state sweep. `all_tenant_ids()` and
then one scoped pass per tenant. These sites already fanned out per row
and bound that row's tenant; the loop simply moved one level up, and the
read inside it is now subject to the policies like any other.
3. **Per-item work that can derive its tenant** — a bridge or connector being
started by id, a room reached by id. One lookup by the globally unique
identifier the caller already has, and everything after it is scoped.
**A lookup whose caller already knows the answer does not belong here.** There
was one more, `tenant_of_client`, and it was the one called most: every
client's transport asked it, once per transport, and so did every agent
client's `start`. Both were built from a `clients` row that names the tenant
in a column, so the question was asked of the database with the answer already
in hand. `Actor` and `PostgresTransport` now take a `tenant_id` the way
they take a `client_id`, and revision `b1d7c4f0a92e` drops the function. The
test that keeps the list honest is the one that would have let this stand: a
function nobody needs is still a function every role could call, so the shorter
list is the whole point of noticing.
**`tenant_of_invitation` is the eighth, and it is the same shape as
`tenant_of_api_key`.** Accepting an invitation is credential resolution: the
request carries a token and nothing else, no tenant is bound yet, and the
table it would have to read (`invitations`) is tenant-scoped like everything
else, so the policy refuses exactly the read that has to happen first. Resolve
the tenant here, bind it, then read the invitation itself — its role, its
email, whether it is spent, expired or revoked — through the ordinary scoped
store, whose `get_valid_by_token_hash` and `consume` are where those three
gates are actually enforced. Nothing about *that* row crosses the exemption;
only the tenant id does, which is the property every lookup in this module
rests on.
**`tenant_of_messaging_install` is the ninth, and the only one whose caller is
not a person.** An event from the Switch app installed in a customer's Slack
arrives over a public endpoint that no one has authenticated to: what it
carries is a workspace id, and the tenant is precisely what has to be worked out before
anything else may happen. There is no row in hand to read the answer off, so
this is shape 1 with the credential replaced by a workspace — resolve the
tenant, bind it, and read everything after that through the ordinary scoped
store. It takes both the platform and the workspace id because that pair,
not the workspace id alone, is what `messaging_installs` makes unique; a
lookup on the id alone would answer twice the first time two platforms
happened to mint the same string, and refuse a customer's traffic for a
reason in someone else's account.
It also filters on `status = 'active'`, which is not a refinement but the
other half of the same uniqueness. Only active rows are unique by index, so a
workspace that was installed, removed and installed again has two rows and
this would otherwise answer twice — refusing a customer's live traffic on the
strength of an install they themselves ended. The predicate here and the one
on the index are the same predicate, and have to stay that way.
**`tenants_inviting_email` is the tenth, and the only one keyed on a
person's address rather than on something they hold.** An invitation
addressed to someone is theirs to accept whichever workspace they are signed
in to, or with none selected at all; listing those means reading
`invitations`, which is scoped, across every tenant that might have written
one. So this answers which tenants hold a live invitation addressed to an
e-mail — the gates `InvitationStore`'s `_usable` applies, compared
case-insensitively as the acceptance does — and the caller binds each tenant
in turn and reads its own invitations through the ordinary scoped store. Like
every lookup here it discloses a tenant id and no row. What it adds to the
metadata above is which workspaces have invited a given address, to anyone
holding the runtime role's credentials; the endpoint that calls it only ever
passes the signed-in caller's own address.
**`tenants_open_to_domain` is the eleventh**, and the same shape one step
wider: keyed on the domain of a person's address rather than on the address.
A workspace can let anyone at a domain join it without an invitation, and
offering those workspaces means reading `tenant_join_domains` across every
tenant before any is bound. It discloses which workspaces are open to a
domain; the endpoint that calls it only ever passes the domain of the
signed-in caller's own address.
**`tenants_of_cloud_machine` is the twelfth.** A user's cloud machine is
global (`cloud_machines`, one VM per person), and the cloud controller that
drives it acts for no tenant; what the machine runs in each workspace is that
workspace's scoped `machine_workspaces` row. The lookup says which workspaces
those are, so each row is read with its own tenant bound. It discloses which
workspaces a machine, and so its owner, belongs to: what `tenants_of_user`
already discloses about the same person.
**`tenants_bindable_by_user` is the fourteenth**, and `tenants_of_user`
widened to the organization above the workspace. An organization's
`org_admin` administers every workspace in it, and may bind one without
holding a membership row there, so resolving a request's workspace has to
read which workspaces those are before any is bound. One function answers
both halves, so every authenticated request costs one lookup rather than
two. It discloses which workspaces a person belongs to or administers
through an organization; the gateway asks it only about the signed-in
caller.
**`all_organization_ids` is the fifteenth**, and `all_tenant_ids` one level
up. A few tables belong to an organization rather than a workspace, and a
boot pass over them (rewriting an organization's connector-app secrets under
the current key) has to visit every organization, including one with no
workspace to reach it through. It discloses which organizations exist.
**`directory_organizations_of_user` is the sixteenth**, and the one that
answers with organizations rather than workspaces. Under the WorkOS directory
(`workos_directory.py`) a sign-in names the WorkOS organizations the person
belongs to; which Switch organizations follow those, and which ones the
person is already in that follow WorkOS at all, has to be read before
anything is bound, to bring their membership rows up to date. Its second
argument is a JSON array of WorkOS organization ids, so one call answers for
all of them. It discloses which WorkOS organizations have a Switch
organization, and which linked organizations a person belongs to; the
gateway asks it only about the signed-in person and the organizations WorkOS
says they belong to.
**`directory_organizations` is the seventeenth**: every organization that
follows one in the WorkOS directory. The directory mirror acts for no tenant
and no person: it reads the directory's changes and has to know which
organizations they may reach before it binds any of them. It discloses which
organizations are linked; the mirror is its only caller.
**`tenants_of_organization` is the eighteenth**: the workspaces of one
organization. The platform operator may bind any workspace and holds no
membership to narrow by, so listing an organization's workspaces for them, or
reading what needs attention in each, would otherwise mean binding every
tenant on the server in turn to find the few that belong to it. It discloses
which workspaces an organization has, what `all_tenant_ids` already
discloses, narrowed; the gateway asks it only for the platform operator.
**`tenants_with_pending_decisions` is the nineteenth**: the workspaces with
a message waiting for the decision service (`pending_decisions`). Every
process sweeps those messages every few seconds so that one whose process
stopped is still decided; binding every tenant in turn to find the few with
any cost each tenant several sessions per sweep whether or not auto-route was
on anywhere in it. It discloses which workspaces have a decision waiting at
that moment, which says that auto-route is on and in use there; the sweep is
its only caller.
Why not the obvious alternatives is argued in
`docs/old/multi-tenancy-phase1-db.md`, "The bootstrap problem"; the short
version is that returning rows instead of tenant ids would put a second copy
of a dozen store methods in SQL and widen the exemption from "a tenant id"
to "any row of every table they read", and that a second engine connected as the owner
would make the exemption ambient again — reachable from anything holding the
factory, and no longer a list anybody can read.
**A fan-out over `all_tenant_ids()` filters what it reads back.** Every such
loop keeps only the rows whose own `tenant_id` matches the tenant it bound.
That looks redundant — the policy has already narrowed the read — and it is
not, because the policy is what does *not* apply on an owner connection. Most
store methods here carry no `WHERE tenant_id = …` of their own and lean on
row-level security for it, so on a connection Postgres exempts (the test
suite's, and any deployment still running with
`DB_REQUIRE_RESTRICTED_ROLE=false`) the same read returns every tenant's rows
on every pass, and a fan-out over N tenants acts on each row N times. Silent
duplication is exactly the failure this design refuses to ship, so the filter
is written out rather than inferred from the connection.
**Cost.** A lookup is its own short session and so its own round trip. On the
paths where that would be per request or per message it is already behind a
cache that predates this change (`ApiKeyCache`, the transport's room map, the
bridge's room→tenant map), so the steady-state cost is one extra round trip
per *new* credential, room or bridge rather than per use. `all_tenant_ids()`
on the runtime-state sweep is one extra round trip every five seconds.
"""
from __future__ import annotations
import json
from collections.abc import Iterable
from dataclasses import dataclass
from sqlalchemy import DDL, MetaData, TextClause, event, text
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker
from switch_core.tenant_context import no_tenant
class TenantLookupError(RuntimeError):
"""A lookup that must name at most one tenant named several.
Every key these lookups take is unique by constraint, so this is a
guard against a schema that has drifted from the models rather than an
expected answer. Raised rather than resolved by picking one: a credential that resolves to two tenants is a
provisioning fault, and guessing which one it meant is how a caller ends
up authenticated into somebody else's data.
"""
@dataclass(frozen=True)
class TenantLookup:
"""One exempt function: its name, its arguments, and what it reads.
`arguments` are the parameter names *without* the `p_` prefix the SQL carries.
The prefix is not decoration: a `LANGUAGE sql` function whose parameter is
spelled like a column of a table in its own query resolves the name to the
column, silently, rather than to the parameter. Verified on Postgres 16,
because it is easy to get backwards: against a table that has a
`client_id` column, `WHERE id = client_id` becomes a comparison between
two columns of the same row, `id` and the table's own `client_id`, which
is false for every row whose `client_id` does not happen to equal its
`id` — a confident, silent *zero* rows, not every row. The sharper
failure needs the parameter named after the very column being compared:
`tenant_of_api_key(key_hash text) ... WHERE key_hash = key_hash` shadows
to a column compared with itself, true unconditionally, and *that* matches
every row regardless of the argument. Either shape is a caller left
unable to resolve a tenant it should have found, or handed every tenant
the row could belong to, in place of the one it actually named —
so prefixing every parameter makes the shadowing unrepresentable rather
than a thing to check per function, whichever direction it would have
failed.
"""
name: str
arguments: tuple[str, ...]
query: str
purpose: str
@property
def parameters(self) -> tuple[str, ...]:
return tuple(f"p_{argument}" for argument in self.arguments)
@property
def parameter_declaration(self) -> str:
return ", ".join(f"{parameter} text" for parameter in self.parameters)
@property
def signature(self) -> str:
return f"{self.name}({', '.join('text' for _ in self.arguments)})"
# The nineteen of them. Ordered as the three shapes above: enumeration, then
# credential resolution, then deriving a tenant from an identifier in hand.
TENANT_LOOKUPS: tuple[TenantLookup, ...] = (
TenantLookup(
name="all_tenant_ids",
arguments=(),
query="SELECT id FROM tenants ORDER BY created_at, id",
purpose=(
"Every tenant in the deployment, oldest first. The one question a "
"scoped session cannot answer about itself, and the whole of what "
"the boot enumerations and the runtime-state sweep need."
),
),
TenantLookup(
name="all_organization_ids",
arguments=(),
query="SELECT id FROM organizations ORDER BY created_at, id",
purpose=(
"Every organization in the deployment, oldest first, for the boot "
"passes over organization-scoped tables. An organization is "
"visited directly rather than through its workspaces, so one "
"with none is not missed."
),
),
TenantLookup(
name="tenants_of_user",
arguments=("user_id",),
query=(
"SELECT tenant_id FROM tenant_members "
"WHERE user_id = p_user_id ORDER BY tenant_id"
),
purpose=(
"Which tenants a gateway login belongs to. A person is global "
"(`users` carries no tenant); the membership row is the per-tenant "
"object, and it is scoped, so a session cannot read its own way in."
),
),
TenantLookup(
name="tenants_bindable_by_user",
arguments=("user_id",),
query=(
"SELECT tenant_id FROM tenant_members WHERE user_id = p_user_id "
"UNION SELECT tenants.id FROM tenants "
"JOIN organization_members "
"ON organization_members.organization_id = tenants.organization_id "
"WHERE organization_members.user_id = p_user_id "
"AND organization_members.role = 'org_admin' ORDER BY 1"
),
purpose=(
"Which tenants a gateway login may bind: those it holds a "
"membership row in, and those it administers as its "
"organization's `org_admin`. Read before a tenant is bound, for "
"the same reason as `tenants_of_user`, and once per request."
),
),
TenantLookup(
name="tenant_of_api_key",
arguments=("key_hash",),
query="SELECT tenant_id FROM api_keys WHERE key_hash = p_key_hash",
purpose=(
"Which tenant a bearer credential belongs to. `api_keys.key_hash` "
"is one of the two columns deliberately left globally unique for "
"exactly this: the hash is resolved before a tenant exists."
),
),
TenantLookup(
name="tenant_of_agent_oauth_client",
arguments=("oauth_client_id",),
query=(
"SELECT tenant_id FROM agents WHERE oauth_client_id = p_oauth_client_id"
),
purpose=(
"Which tenant an agent authenticating by OIDC belongs to. The "
"client id is unique across the deployment by index; the caller "
"still refuses rather than picking if it ever answers twice."
),
),
TenantLookup(
name="tenant_of_room",
arguments=("room_id",),
query="SELECT tenant_id FROM rooms WHERE id = p_room_id",
purpose=(
"Which tenant a room belongs to, for work reaching a room by its "
"globally unique id with nothing bound."
),
),
TenantLookup(
name="tenant_of_collaboration_bridge",
arguments=("bridge_id",),
query="SELECT tenant_id FROM collaboration_bridges WHERE id = p_bridge_id",
purpose=(
"Which tenant a bridge belongs to. Asked from boot, with nothing "
"bound, and from an HTTP request that restarted it, where what is "
"bound is the operator's tenant and not necessarily the bridge's."
),
),
TenantLookup(
name="tenant_of_server_connector",
arguments=("connector_id",),
query="SELECT tenant_id FROM server_connectors WHERE id = p_connector_id",
purpose=(
"Same as the bridge, for a server-side connector. Nothing calls it "
"any more; it goes with `server_connectors`, a release later."
),
),
TenantLookup(
name="tenant_of_invitation",
arguments=("token_hash",),
query="SELECT tenant_id FROM invitations WHERE token_hash = p_token_hash",
purpose=(
"Which tenant an invitation belongs to, resolved from the hash of "
"its token before any tenant is bound — the same credential-"
"resolution shape as a bearer token, and necessary for the same "
"reason: accepting an invitation is exactly the read the policy "
"refuses to a session with nothing bound yet."
),
),
TenantLookup(
name="tenants_inviting_email",
arguments=("email",),
query=(
"SELECT DISTINCT tenant_id FROM invitations "
"WHERE lower(email) = lower(p_email) "
"AND revoked_at IS NULL AND expires_at > now() AND uses_remaining > 0 "
"ORDER BY tenant_id"
),
purpose=(
"Which tenants hold a live invitation addressed to an e-mail, so a "
"signed-in person can be shown the invitations waiting for them "
"before any of those tenants is bound. Live by the same three "
"gates `InvitationStore` applies; the rows themselves are read "
"afterwards, one bound tenant at a time."
),
),
TenantLookup(
name="tenants_open_to_domain",
arguments=("domain",),
query=(
"SELECT tenant_id FROM tenant_join_domains "
"WHERE domain = lower(p_domain) ORDER BY tenant_id"
),
purpose=(
"Which tenants let anyone with an address at a domain join them "
"without an invitation, so a signed-in person can be offered those "
"workspaces before any of them is bound."
),
),
TenantLookup(
name="tenants_of_cloud_machine",
arguments=("machine_id",),
query=(
"SELECT tenant_id FROM machine_workspaces "
"WHERE machine_id = p_machine_id ORDER BY created_at, tenant_id"
),
purpose=(
"Which workspaces a user's cloud machine serves, oldest first. The "
"machine is global and acts for none of them; the cloud controller "
"reads each workspace's row on it, the controller it runs there "
"and its enrollment code, one bound tenant at a time."
),
),
TenantLookup(
name="tenant_of_sign_in_flow",
arguments=("state",),
query=(
"SELECT tenant_id FROM sign_in_flows "
"WHERE id = p_state AND expires_at > now()"
),
purpose=(
"Which tenant a sign-in under way belongs to, from the state the "
"vendor hands back to the person's browser. The browser's steps "
"(leaving for the vendor, coming back, continuing to Switch "
"Console) carry no Switch session, only that state, and may reach "
"any switch-core process; the flow itself is read afterwards in "
"the tenant this names."
),
),
TenantLookup(
name="directory_organizations_of_user",
arguments=("user_id", "workos_organization_ids"),
query=(
"SELECT id FROM organizations "
"WHERE workos_organization_id IN "
"(SELECT jsonb_array_elements_text(p_workos_organization_ids::jsonb)) "
"OR (workos_organization_id IS NOT NULL AND id IN "
"(SELECT organization_id FROM organization_members "
"WHERE user_id = p_user_id)) "
"ORDER BY id"
),
purpose=(
"Which organizations follow a WorkOS organization a sign-in names "
"(a JSON array of their ids), and which organizations following "
"WorkOS the person is already in. Read before anything is bound, "
"to bring the person's organization memberships up to date."
),
),
TenantLookup(
name="directory_organizations",
arguments=(),
query=(
"SELECT id FROM organizations "
"WHERE workos_organization_id IS NOT NULL ORDER BY id"
),
purpose=(
"Every organization that follows one in the WorkOS directory, for "
"the directory mirror to read each one's link with it bound."
),
),
TenantLookup(
name="tenants_of_organization",
arguments=("organization_id",),
query=(
"SELECT id FROM tenants WHERE organization_id = p_organization_id "
"ORDER BY id"
),
purpose=(
"The workspaces of one organization, for the platform operator, "
"who may bind any workspace and holds no membership to narrow by."
),
),
TenantLookup(
name="tenants_with_pending_decisions",
arguments=(),
query="SELECT DISTINCT tenant_id FROM pending_decisions ORDER BY tenant_id",
purpose=(
"The workspaces with a message waiting for the decision service, "
"so the decision sweep binds only those rather than every tenant "
"on the server."
),
),
TenantLookup(
name="tenant_of_messaging_install",
arguments=("platform", "external_workspace_id"),
query=(
"SELECT tenant_id FROM messaging_installs "
"WHERE platform = p_platform "
"AND external_workspace_id = p_external_workspace_id "
"AND status = 'active'"
),
purpose=(
"Which tenant an inbound event from an installed workspace belongs "
"to. The public webhook is unauthenticated by nature and knows only "
"the platform it was posted to and the workspace the payload names, "
"so this runs before anything else the request does. The status "
"predicate is the index's own: only active rows are unique, and an "
"install that ended must stop answering for its workspace rather "
"than compete with the one that replaced it."
),
),
)
TENANT_LOOKUPS_BY_NAME: dict[str, TenantLookup] = {
lookup.name: lookup for lookup in TENANT_LOOKUPS
}
# `pg_temp` last, per the Postgres note on writing SECURITY DEFINER functions
# safely: a role that can create a temporary table could otherwise shadow one
# of the tables read above and have the function read theirs instead. `public`
# is not writable by PUBLIC on 15+, which is the floor this schema already
# requires for `ON DELETE SET NULL (col)`.
SECURE_SEARCH_PATH = "pg_catalog, public, pg_temp"
def create_lookup_ddl(lookup: TenantLookup) -> str:
return (
f"CREATE OR REPLACE FUNCTION {lookup.name}({lookup.parameter_declaration})\n"
f" RETURNS SETOF text\n"
f" LANGUAGE sql STABLE SECURITY DEFINER\n"
f" SET search_path = {SECURE_SEARCH_PATH}\n"
f"AS $${lookup.query}$$"
)
def drop_lookup_ddl(lookup: TenantLookup) -> str:
return f"DROP FUNCTION IF EXISTS {lookup.signature}"
def attach_tenant_lookups(metadata: MetaData) -> None:
"""Create every lookup after `create_all`, drop them before `drop_all`.
On the metadata's own events rather than a table's, and *after* create
rather than before: a `LANGUAGE sql` body is parsed and its names resolved
when the function is created, so every table it reads has to exist by
then. That is a feature — a lookup naming a column that has been renamed
fails at `create_all`, in every test, rather than at the first call in a
deployment.
"""
for lookup in TENANT_LOOKUPS:
event.listen(
metadata,
"after_create",
DDL(create_lookup_ddl(lookup)).execute_if(dialect="postgresql"),
)
event.listen(
metadata,
"before_drop",
DDL(drop_lookup_ddl(lookup)).execute_if(dialect="postgresql"),
)
# ── Calling them ──────────────────────────────────────────────────────────────
# One `text()` per lookup, written out rather than assembled from `lookup.name`
# at call time: the nineteen names are fixed and known here, so there is nothing
# for a call site to build. Each bind is named after the lookup's own argument,
# which is what lets `_call` zip them positionally against the dataclass and
# fail loudly on a mismatch rather than binding the workspace to the platform.
# The assertion below is what keeps this dict from quietly falling behind
# `TENANT_LOOKUPS`: a twentieth lookup with no entry here fails at import, not
# with a `KeyError` on whatever request reaches it first.
_LOOKUP_STATEMENTS: dict[str, TextClause] = {
"all_tenant_ids": text("SELECT tenant_id FROM all_tenant_ids() AS tenant_id"),
"all_organization_ids": text(
"SELECT organization_id FROM all_organization_ids() AS organization_id"
),
"tenants_of_user": text(
"SELECT tenant_id FROM tenants_of_user(:user_id) AS tenant_id"
),
"tenants_bindable_by_user": text(
"SELECT tenant_id FROM tenants_bindable_by_user(:user_id) AS tenant_id"
),
"tenant_of_api_key": text(
"SELECT tenant_id FROM tenant_of_api_key(:key_hash) AS tenant_id"
),
"tenant_of_agent_oauth_client": text(
"SELECT tenant_id FROM tenant_of_agent_oauth_client(:oauth_client_id) "
"AS tenant_id"
),
"tenant_of_room": text(
"SELECT tenant_id FROM tenant_of_room(:room_id) AS tenant_id"
),
"tenant_of_collaboration_bridge": text(
"SELECT tenant_id FROM tenant_of_collaboration_bridge(:bridge_id) AS tenant_id"
),
"tenant_of_server_connector": text(
"SELECT tenant_id FROM tenant_of_server_connector(:connector_id) AS tenant_id"
),
"tenant_of_invitation": text(
"SELECT tenant_id FROM tenant_of_invitation(:token_hash) AS tenant_id"
),
"tenants_inviting_email": text(
"SELECT tenant_id FROM tenants_inviting_email(:email) AS tenant_id"
),
"tenants_open_to_domain": text(
"SELECT tenant_id FROM tenants_open_to_domain(:domain) AS tenant_id"
),
"tenants_of_cloud_machine": text(
"SELECT tenant_id FROM tenants_of_cloud_machine(:machine_id) AS tenant_id"
),
"tenant_of_messaging_install": text(
"SELECT tenant_id FROM "
"tenant_of_messaging_install(:platform, :external_workspace_id) AS tenant_id"
),
"tenant_of_sign_in_flow": text(
"SELECT tenant_id FROM tenant_of_sign_in_flow(:state) AS tenant_id"
),
"directory_organizations": text(
"SELECT tenant_id FROM directory_organizations() AS tenant_id"
),
"directory_organizations_of_user": text(
"SELECT tenant_id FROM directory_organizations_of_user("
":user_id, :workos_organization_ids) AS tenant_id"
),
"tenants_of_organization": text(
"SELECT tenant_id FROM tenants_of_organization(:organization_id) AS tenant_id"
),
"tenants_with_pending_decisions": text(
"SELECT tenant_id FROM tenants_with_pending_decisions() AS tenant_id"
),
}
assert _LOOKUP_STATEMENTS.keys() == TENANT_LOOKUPS_BY_NAME.keys(), (
"_LOOKUP_STATEMENTS must carry exactly the names in TENANT_LOOKUPS"
)
async def _call(
session_factory: async_sessionmaker[AsyncSession],
lookup: TenantLookup,
*arguments: str,
) -> list[str]:
"""Run one lookup on a session of its own, with nothing bound.
Its own session, deliberately. The caller may have a tenant bound — the
gateway resolving a bridge's tenant from inside an operator's request does
— and the answer must not be narrowed to it. Nothing bound is also the
honest state: this session may not touch a scoped table, and the database
is what enforces that now rather than an allowlist.
`strict=True` on the zip is the arity check. A lookup called with one
argument too few would otherwise leave a bind unfilled, and a lookup whose
two arguments were passed the wrong way round is a webhook resolving the
wrong tenant — both are worth a `ValueError` here rather than a surprise
further down.
"""
parameters: dict[str, object] = dict(zip(lookup.arguments, arguments, strict=True))
with no_tenant():
async with session_factory() as session:
result = await session.execute(_LOOKUP_STATEMENTS[lookup.name], parameters)
return [row[0] for row in result]
def _at_most_one(lookup: TenantLookup, tenant_ids: Iterable[str]) -> str | None:
found = list(tenant_ids)
if len(found) > 1:
raise TenantLookupError(
f"{lookup.name} resolved {len(found)} tenants ({sorted(found)}); "
"refusing to pick one. Whatever was looked up is present in more "
"than one tenant, which is a provisioning fault, not a credential "
"the caller may use."
)
return found[0] if found else None
async def all_tenant_ids(
session_factory: async_sessionmaker[AsyncSession],
) -> list[str]:
return await _call(session_factory, TENANT_LOOKUPS_BY_NAME["all_tenant_ids"])
async def tenants_with_pending_decisions(
session_factory: async_sessionmaker[AsyncSession],
) -> list[str]:
return await _call(
session_factory, TENANT_LOOKUPS_BY_NAME["tenants_with_pending_decisions"]
)
async def all_organization_ids(
session_factory: async_sessionmaker[AsyncSession],
) -> list[str]:
return await _call(session_factory, TENANT_LOOKUPS_BY_NAME["all_organization_ids"])
async def tenants_of_user(
session_factory: async_sessionmaker[AsyncSession], user_id: str
) -> list[str]:
return await _call(
session_factory, TENANT_LOOKUPS_BY_NAME["tenants_of_user"], user_id
)
async def tenants_bindable_by_user(
session_factory: async_sessionmaker[AsyncSession], user_id: str
) -> list[str]:
return await _call(
session_factory, TENANT_LOOKUPS_BY_NAME["tenants_bindable_by_user"], user_id
)
async def tenant_of_api_key(
session_factory: async_sessionmaker[AsyncSession], key_hash: str
) -> str | None:
lookup = TENANT_LOOKUPS_BY_NAME["tenant_of_api_key"]
return _at_most_one(lookup, await _call(session_factory, lookup, key_hash))
async def tenant_of_agent_oauth_client(
session_factory: async_sessionmaker[AsyncSession], oauth_client_id: str
) -> str | None:
lookup = TENANT_LOOKUPS_BY_NAME["tenant_of_agent_oauth_client"]
return _at_most_one(lookup, await _call(session_factory, lookup, oauth_client_id))
async def tenant_of_room(
session_factory: async_sessionmaker[AsyncSession], room_id: str
) -> str | None:
lookup = TENANT_LOOKUPS_BY_NAME["tenant_of_room"]
return _at_most_one(lookup, await _call(session_factory, lookup, room_id))
async def tenant_of_collaboration_bridge(
session_factory: async_sessionmaker[AsyncSession], bridge_id: str
) -> str | None:
lookup = TENANT_LOOKUPS_BY_NAME["tenant_of_collaboration_bridge"]
return _at_most_one(lookup, await _call(session_factory, lookup, bridge_id))
async def tenant_of_server_connector(
session_factory: async_sessionmaker[AsyncSession], connector_id: str
) -> str | None:
lookup = TENANT_LOOKUPS_BY_NAME["tenant_of_server_connector"]
return _at_most_one(lookup, await _call(session_factory, lookup, connector_id))
async def tenant_of_invitation(
session_factory: async_sessionmaker[AsyncSession], token_hash: str
) -> str | None:
lookup = TENANT_LOOKUPS_BY_NAME["tenant_of_invitation"]
return _at_most_one(lookup, await _call(session_factory, lookup, token_hash))
async def tenants_inviting_email(
session_factory: async_sessionmaker[AsyncSession], email: str
) -> list[str]:
return await _call(
session_factory, TENANT_LOOKUPS_BY_NAME["tenants_inviting_email"], email
)
async def tenants_open_to_domain(
session_factory: async_sessionmaker[AsyncSession], domain: str
) -> list[str]:
return await _call(
session_factory, TENANT_LOOKUPS_BY_NAME["tenants_open_to_domain"], domain
)
async def tenant_of_messaging_install(
session_factory: async_sessionmaker[AsyncSession],
platform: str,
external_workspace_id: str,
) -> str | None:
lookup = TENANT_LOOKUPS_BY_NAME["tenant_of_messaging_install"]
return _at_most_one(
lookup, await _call(session_factory, lookup, platform, external_workspace_id)
)
async def tenants_of_cloud_machine(
session_factory: async_sessionmaker[AsyncSession], machine_id: str
) -> list[str]:
return await _call(
session_factory, TENANT_LOOKUPS_BY_NAME["tenants_of_cloud_machine"], machine_id
)
async def tenant_of_sign_in_flow(
session_factory: async_sessionmaker[AsyncSession], state: str
) -> str | None:
lookup = TENANT_LOOKUPS_BY_NAME["tenant_of_sign_in_flow"]
return _at_most_one(lookup, await _call(session_factory, lookup, state))
async def directory_organizations_of_user(
session_factory: async_sessionmaker[AsyncSession],
user_id: str,
workos_organization_ids: list[str],
) -> list[str]:
return await _call(
session_factory,
TENANT_LOOKUPS_BY_NAME["directory_organizations_of_user"],
user_id,
json.dumps(sorted(workos_organization_ids)),
)
async def directory_organizations(
session_factory: async_sessionmaker[AsyncSession],
) -> list[str]:
return await _call(
session_factory, TENANT_LOOKUPS_BY_NAME["directory_organizations"]
)
async def tenants_of_organization(
session_factory: async_sessionmaker[AsyncSession], organization_id: str
) -> list[str]:
return await _call(
session_factory,
TENANT_LOOKUPS_BY_NAME["tenants_of_organization"],
organization_id,
)