Tenant isolation

In short

On SpeculaPro SaaS, several institutions share one installation. Each institution is a tenant (you see it as your workspace). Their data must never mix.

Many systems rely on the application to remember to add “only this institution’s rows” to every database query. Forget once, and one customer sees another’s data. SpeculaPro adds a second lock: the database itself filters every query by tenant, using a Postgres feature called row-level security (RLS).

An analogy: a shared office building with a receptionist who checks badges is the application. RLS is when, in addition, every office door only opens for badges from that company. Even if the receptionist waves the wrong person through, they still can’t open the wrong door.

 Analyst at Bank A                              Postgres
       │  "show open cases"                ┌───────────────────────────────┐
       ▼                                   │ cases                         │
 SpeculaPro API ── sets tenant = Bank A ──▶│  Bank A  case 101   ✔ visible  │
                                           │  Bank B  case 207   ✘ hidden   │
                                           │  Bank A  case 102   ✔ visible  │
                                           └───────────────────────────────┘

How it works

1. Every row is labelled. Every table holding an institution’s data has a tenant_id column saying which tenant owns the row. That covers users, roles and teams, customers, accounts and counterparties, transactions, rules and their versions, hits, alerts, cases and their timelines, lists, suppressions, reports, sign-in sessions and the audit log.
2. Every table has a policy. Postgres is told: “only show, change or insert rows whose tenant_id matches the current tenant”.
3. Every request sets the current tenant. When the API handles a signed-in request, it opens a database transaction and sets the tenant for that transaction only (a setting called app.tenant_id). When the transaction ends, the setting is gone.
4. No tenant set means no rows. A query run without a tenant returns nothing rather than everything.
5. Writing into another tenant is refused. The same policy checks new and changed rows. An attempt to insert a row labelled with a different tenant fails with a row-level security error; it is not silently re-labelled.

Platform-level audit entries

A few audit entries belong to no workspace, for example actions taken by the platform operator. Their tenant is empty (NULL in the database), so no workspace can see them, including yours, and SpeculaPro’s own app account can’t write one: they are written only through the system account. On a self-hosted installation, your database administrator can read them with the owner account.

Because the setting lives only for one transaction, one request can never leak its tenant to the next request that reuses the same database connection.

Three database accounts, three levels of trust

SpeculaPro connects to Postgres with different accounts for different jobs:

Database account	Used by	Row-level security	Why
speculapro_owner	Schema migrations only	n/a (owns the tables)	Changing the schema is rare and done deliberately
speculapro_app	The web app and API (api role)	Applies	Everything a person does goes through this account, so it is always filtered by tenant
speculapro_system	Background roles that process many tenants in bulk (decision, stream, persist, connector, analytics, worker), and every process for the installation’s own tables	Bypassed	Writing a batch of events from many tenants at once can’t be done one tenant at a time

Guard the system account

speculapro_system can read every tenant’s data. Keep its password in a secret manager, and never put it in SP_DB_URL, the connection the web app and API use for everything people and API keys ask. See Self-hosting for how the accounts are created.

Tables with no tenant

A few tables belong to the installation, not to any tenant: the licence, its usage figures and the key that signs usage reports, the backtest runners’ heartbeats and the notification channel record. With no tenant_id to filter on, a policy can’t protect them, so the app account is refused them altogether: row-level security is switched on with no policy (which answers the app account no rows, whatever tenant it sets), and its privileges on the tables are revoked as well, so neither guard depends on the other. Every process reads the licence through the system account instead, on a connection of its own (SP_DB_SYSTEM_URL) that requests from people and API keys never use.

Two more gaps of the same kind are closed:

• Monthly partitions. The largest tables are stored as one partition per month, and a partition is a table in its own right. A query that names a partition directly would not pass through the parent table’s policy, so every partition has row-level security switched on with no policy, and each new month’s partition gets it when it is created. The app account reads these tables only through the parent, where the tenant policy applies.
• Shared catalogues. Rule packs and jurisdiction definitions are the same for every tenant. The app account can read them and add a verified new version, but can’t change or delete one.

An automatic test fails the build if a new table is neither covered by a tenant policy nor on the explicit list of installation-wide tables.

Before you sign in: narrow, single-purpose lookups

There is one awkward moment: when someone signs in, SpeculaPro doesn’t yet know their tenant, so RLS would hide their user record.

Instead of giving the app account a general way around RLS, the database offers a few single-purpose lookup functions: find a user by workspace and email, find a session by its token fingerprint, find an API key by its prefix, find a workspace for single sign-on, find a SCIM token by its prefix. Each returns only what that step needs. The app account can call these functions and nothing else that crosses tenants.

These functions are created by the database migrations. Self-hosters then allow the app to call them by running deploy/sql/grants.sql; see How to prepare the database.

Regions: a second boundary on a multi-region service

Where SpeculaPro is hosted in more than one region, each region is a separate installation with its own database, and a workspace is pinned to one of them. On top of the workspace boundary described above, a region refuses any workspace pinned to another region: on sign-in, on existing sessions, on API keys, and when sending webhooks and notifications. A copy of a workspace in the wrong region’s database can’t be signed in to and sends nothing. See Data residency and regions and Multi-region.

Self-hosted installations

A self-hosted installation normally has one tenant, but the same isolation stays switched on. Self-hosting is “SaaS with one tenant”, not a separate, less-tested configuration.

How this is tested

The schema test suite creates a throwaway database with two tenants and, connecting as a non-superuser account like speculapro_app, checks that:

• with no tenant set, a query sees no customers;
• with tenant A set, it sees only tenant A’s customers;
• inserting a row labelled with tenant B, while tenant A is set, is refused.

How to check isolation on your own installation

If you self-host, you can confirm these protections on your own database in a few minutes. You need psql and the connection details for the three database accounts. Run these on a staging copy if you can; they only read data, but the first one shows real customer counts.

1. Confirm only the system account bypasses row-level security.

   psql "$SP_DB_URL" -c "SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE rolname LIKE 'speculapro%';"

   In the rolbypassrls column you should see t (true) for speculapro_system only, and rolsuper should be f (false) everywhere: a superuser also ignores row-level security. If speculapro_app shows t in either column, isolation is switched off for the web app. Fix it as a superuser with ALTER ROLE speculapro_app NOSUPERUSER NOBYPASSRLS;.

2. Confirm the app account sees nothing without a tenant. Connect as speculapro_app and count customers:

   psql "postgres://speculapro_app:…@db-host/speculapro" -c "SELECT count(*) FROM customers;"

   The answer must be 0, even though the table has rows.

3. Confirm the app account sees one tenant at a time. Still as speculapro_app, set a workspace’s ID for one transaction:

   psql "postgres://speculapro_app:…@db-host/speculapro" <<'SQL'
   BEGIN;
   SELECT set_config('app.tenant_id', '<workspace id>', true);
   SELECT count(*) FROM customers;
   ROLLBACK;
   SQL

   The count is that workspace’s customers only. Workspace IDs are in the tenants table; read them with the owner account.

4. Confirm the web app doesn’t use the system account for people’s requests. Check that SP_DB_URL for every process names speculapro_app. SP_DB_SYSTEM_URL names speculapro_system in every process, api included: an api process uses it only to read the licence. See Database roles.

5. Confirm the app account can’t read the installation’s own tables or a partition directly. As speculapro_app:

   psql "postgres://speculapro_app:…@db-host/speculapro" -c "SELECT count(*) FROM license_installation;"

   The answer must be permission denied for table license_installation. If it is a count, run deploy/sql/grants.sql again (see How to prepare the database).

Never put the system account in SP_DB_URL

If an api process’s SP_DB_URL connects as speculapro_system, row-level security no longer protects anything that process does. A single missing filter in application code could then show one workspace’s data to another.

Related

• Security model
• Data model
• Sign-in and MFA
• Data residency and regions