Skip to content

Tenant isolation

Ochema is multi-tenant: every carrier that signs up is a company, called a tenant in the code, and all companies share one database. This page describes the mechanism that keeps the data of one company out of reach of every other. It is part of section 9 of the arc42 template. The reasons for the design are in ADR-0002 and ADR-0003; this page shows how the code applies it and what proves it.

A recruiting and compliance system holds Social Security numbers, driver records and test results. A single query that forgets its company filter must not be able to show one carrier the drivers of another. The protection therefore cannot live in the queries of the application: it has to hold when the application code is wrong.

  1. Every business table has a tenant_id column and a row level security policy that compares it with the company of the current transaction.
  2. The company of a transaction comes from one of three places: the session of the user, a public identifier that the database itself resolves, or the worker’s list of companies with work due. It never comes from a parameter of the request.
  3. The database role used by the API and the worker cannot bypass row level security, and owns no table.
  4. Anything that must act before a company is known, or across companies, is a database function from a closed list, in the schemas gate, staff and search.

The roles are created by packages/db/sql/bootstrap-roles.sql. None is a superuser and none has BYPASSRLS.

Role Logs in What it is for
ochema_owner Yes Owns the schemas and the tables. Used only to run migrations.
ochema_app Yes The runtime role of the API and the worker. Owns nothing, and every privilege it has is an explicit grant on one table or one function.
ochema_gate No Owns the functions of the gate schema.
ochema_staff No Owns the functions of the staff schema, the platform panel.
ochema_search No Owns the functions of the search schema.

Every table has ENABLE and FORCE ROW LEVEL SECURITY, so the owner of the tables obeys the policies too.

Two settings carry who is acting and in which company: app.user_id and app.tenant_id. The functions app.user_id() and app.tenant_id() read them (packages/db/migrations/0000_foundation_prelude.sql), and the policies call those functions.

withContext (packages/db/src/context.ts) opens a transaction and sets both values in its first statement, with set_config(..., true). The third argument makes the value local to the transaction: it ends on commit and on rollback, so a pooled connection goes back clean. Each value must be a UUID or the call throws before reaching the database.

---
config:
  sequence:
    width: 110
    actorMargin: 20
---
sequenceDiagram
  actor U as User<br/>(web app)
  participant API
  participant DB as PostgreSQL

  U->>API: Request with the<br/>session cookie
  API->>DB: gate.session_resolve<br/>(hash of the token)
  DB-->>API: User and<br/>active company
  API->>DB: BEGIN, then set<br/>app.user_id and<br/>app.tenant_id
  loop Each statement of the route
    API->>DB: Query
    DB-->>API: Rows of that<br/>company only
  end
  API->>DB: COMMIT
  Note over API,DB: Both settings end<br/>with the transaction
  API-->>U: Response
  1. SessionGuard (apps/api/src/core/auth/guards.ts) hashes the cookie token and calls gate.session_resolve. The active company is a column of the session row, checked against the memberships of the user. See Authentication and permissions.
  2. TransactionInterceptor (apps/api/src/core/http/transaction.interceptor.ts) wraps the route in withContext with the user and the company of the session. Every service of every module reads and writes through that one transaction (RequestContext.tx).
  3. A statement without any company filter still returns only rows of that company. A statement that would write a row into another company fails the WITH CHECK of the policy.

A business table declares its policy with the helper appAll (packages/db/src/schema/helpers.ts), which produces <table>_app_all:

CREATE POLICY candidates_app_all ON candidates FOR ALL TO ochema_app
USING (tenant_id = (SELECT app.tenant_id()))
WITH CHECK (tenant_id = (SELECT app.tenant_id()));

Three more properties of the schema support the policy:

  • Foreign keys carry the company. A foreign key between two tables of a company is composite, (tenant_id, id), so a row cannot point at a row of another company. The foreign keys without tenant_id point at tables that belong to no company: users and the catalogs of the platform.
  • Privileges are explicit. ochema_app receives SELECT, INSERT, UPDATE and DELETE table by table, often column by column. Tables that must be kept have no DELETE at all. See ADR-0011.
  • Tables without tenant_id have policies too. They are the identity tables and the catalogs. A session reads the users who are members of its company, only its own sessions, and the companies it belongs to. Catalogs such as permissions and compliance_rules are read-only for the application.

One policy goes further than the company. The tables of the drug and alcohol file, and the rows of that vault in files, also call app.has_permission(...), so they exist only for a user who holds the permission. See Files and vaults.

%%{init: {"flowchart": {"wrappingWidth": 170}}}%%
flowchart LR
  session["<b>Request with<br/>a session</b><br/>Company kept<br/>in the session"]
  public["<b>Request without<br/>a session</b><br/>Slug, number,<br/>key or token"]
  worker["<b>Worker</b><br/>Company from the<br/>discovery or<br/>from the job"]
  gatefn["<b>gate function</b><br/>public_context_by_*"]
  ctx["<b>Transaction context</b><br/>app.tenant_id,<br/>app.user_id"]
  rls["<b>Policies</b><br/>tenant_id =<br/>app.tenant_id()"]

  session -->|"withContext"| ctx
  public -->|"withPublicContext"| gatefn
  gatefn -->|"Sets the company"| ctx
  worker -->|"withContext,<br/>no user"| ctx
  ctx --> rls

Legend. Box: a step. Arrow: the call that leads to the next step, labelled with the helper of packages/db/src/context.ts that makes it. Every path ends in the same policies.

With a session. Described above.

Without a session. A public page, a webhook or a link has no user. The route calls withPublicContext with a key, and the first statement of the transaction is a gate.public_context_by_* function. The function finds the company from an identifier that is unique in the platform, sets app.tenant_id for that transaction and returns only ids. The route code runs only if a row came back. From there the transaction is ochema_app under the same policies as a session of that company, with no user.

The request presents Function Used by
Slug of the careers page gate.public_context_by_slug Careers page, job page, application form
Code of a short link gate.public_context_by_link /r/<code>
SHA-256 of an API key gate.public_context_by_api_key Lead intake API
SMS number of the company gate.public_context_by_sms_number Inbound SMS and SMS status webhooks
SHA-256 of a reply token gate.public_context_by_reply_token Inbound email
Provider and its message id gate.public_context_by_provider_message Bounce and complaint notices
SHA-256 of a scheduling token gate.public_context_by_scheduling_token Interview scheduling page
SHA-256 of an unsubscribe token gate.public_context_by_unsubscribe_token Unsubscribe page
SHA-256 of a calendar token gate.public_context_by_calendar_token Calendar feed of a recruiter
SHA-256 of a full application token gate.public_context_by_full_application_token Full application
SHA-256 of a portal token gate.public_context_by_portal_token Candidate and driver area
SHA-256 of a signing token gate.public_context_by_signing_token Signing page
SHA-256 of a safety history token gate.public_context_by_safety_history_token Response of a previous employer

The thirteen functions share four properties, each covered by tests:

  • None takes a company id. The company is the result of the lookup.
  • Each refuses, with an error, a transaction that already has a company or a user. They can only give a company to a transaction that has none, once.
  • An identifier that is unknown, expired or revoked, and a suspended company, return no row and leave the context empty. The route then answers 404 for a page or a link and 401 for an API key, and accepts a webhook without writing anything.
  • They return ids only. A webhook verifies the signature of the provider before it opens the transaction.

In the worker. A routine never reads a business table without a company. It asks gate.tenants_with_due_work(kind) which active companies have work of one kind due, outside any context, and then opens one withContext per company with no user. A job put on a queue by the API carries the company id in its body and opens its transaction the same way. See Background work.

Every function of the schema is SECURITY DEFINER, owned by ochema_gate, with a fixed search_path. The role reads only the lookup columns each function needs, through select-only policies and column grants. The typed wrappers in packages/db/src/gate.ts and packages/db/src/context.ts are the only callers.

Group Functions What ochema_app may do
Account flows that start without a company register_tenant, login_lookup, session_resolve, invitation_lookup, accept_invitation, password_reset_request, password_reset_consume Execute
Public context The thirteen public_context_by_* functions Execute
Work discovery tenants_with_due_work Execute
Contact form of the landing site submit_lead, mark_lead_notified Execute
Cleanup of expired rows purge_expired, purge_expired_application_drafts Execute
Platform configuration read by the API calendar_provider_config Execute
Drug and alcohol file, narrow answers drug_alcohol_on_file, drug_alcohol_attach_person, drug_alcohol_set_termination Execute
Triggers that keep a derived column For example candidate_consents_sync, which keeps candidates.consent_sms equal to the latest consent row Nothing: only the trigger runs them
Default data of a new company The ensure_default_* functions Nothing: only migrations and register_tenant run them

The trigger functions and the drug and alcohol functions are tied to the company of the transaction: the policies they run under compare tenant_id with app.tenant_id(), so they fail without a context instead of acting across companies.

The platform team is not a company user and has no company context. The panel at /admin reaches the database only through functions of the staff schema, owned by ochema_staff. Each function receives the hash of the panel session token as its first argument and checks that session itself before doing anything, so no setting made by the API can stand in for a signed-in member. What the panel can do to a company is the list of those functions. See Authentication and permissions and ADR-0023.

Text search is the one read that does not go through the company policy directly, because a policy keeps a LIKE condition out of the trigram index. Six functions, search.<table>_ids, cover candidates, people, vehicles, customers, jobs and locations. They belong to ochema_search, which may read four columns of those tables: id, tenant_id, archived_at and search_text. The function body filters by app.tenant_id() and returns ids only; without a context it returns nothing. The API then loads the rows by id in an ordinary query, so the policy checks every row again. See ADR-0022.

  • Files. Every object storage key starts with tenants/<tenant_id>/, and the key is built by the server, never received. See Files and vaults.
  • Queues. A job carries the company id it was created for; the worker never takes it from anywhere else.
  • Browser tabs. The office system sends the header X-Ochema-Tenant with the company the tab is showing. When another tab switched company, the API answers 409 tenant_changed before anything runs. The header never selects the company.
Test file What it proves
packages/db/test/isolation.test.ts and the packages/db/test/isolation-wave*.test.ts files Table by table, as ochema_app: reads return only the rows of company A; updates and deletes aimed at rows of B change zero rows; inserts into B violate the policy; without a context every table returns zero rows; the context does not leak to the next transaction on the same connection.
packages/db/test/privileges.test.ts No role is a superuser or bypasses row level security; every table has it enabled and forced; the runtime role owns no table; turning row_security off makes a query fail instead of returning everything.
packages/db/test/public-context.test.ts, wave02-public-functions.test.ts, wave03-public-functions.test.ts, wave04-public-functions.test.ts Each public context function resolves only its own identifier, refuses a transaction that already has a context, returns nothing for an unknown, expired or revoked identifier and for a suspended company, and leaves no context on the connection.
packages/db/test/gate.test.ts Sign-up, sign-in lookup, session resolution, invitations and password reset.
packages/db/test/wave03-automation-functions.test.ts gate.tenants_with_due_work returns only ids of active companies, sets no context, fails inside a context and on an unknown kind.
packages/db/test/volume-plans.test.ts A search term of company B returns no id in A; the search role reads only four columns; the six functions are definer functions of ochema_search.
packages/db/test/staff-boundary.test.ts, staff-privileges.test.ts Every panel function refuses a call without a valid panel session; setting app.tenant_id gives the panel role no access.
apps/api/test/isolation.test.ts Through the HTTP API: lists show only the active company, forged ids of another company change nothing, a user cannot switch into a company without a membership.
apps/api/test/tenant-header.test.ts A request from a tab that shows another company answers 409 and writes nothing in either company.

The API tests of each module carry their own isolation case as well, for example apps/api/test/audit-log.test.ts, messaging-settings.test.ts and drug-alcohol.test.ts.

  • One database for every company. All companies share the capacity and the maintenance windows of one database server. There is no option to place one company in a database of its own.
  • One company at a time. A person who belongs to two companies switches between them. No screen, report or export combines the data of two companies, and two browser tabs cannot work in two companies at once.
  • The platform team does not browse company data. Support can see the list of companies, turn modules on and off and set up the messaging sender of a company, because those are the functions the panel has. It cannot open the candidates, drivers or files of a company.
  • Search needs three characters. A search term needs at least three letters or digits in a row, a consequence of how search is isolated.
  • A new public entry point needs a database migration. Each kind of public link has its own function, by design.