Skip to main content

Tenancy & Row-Level Security

What this covers / who it's for. The system-wide narrative of the load-bearing invariant: how one Postgres database serves every tenant with database-enforced isolation, how the two runtime contexts (central vs tenant) compose, why the guarantees hold, and the machinery that verifies them. For build checklists (writing a tenant-scoped migration/model, RLS test patterns), defer to the skill reference and the tenancy module README.

The invariant​

Stables runs single-database multi-tenancy on PostgreSQL 18 with Row-Level Security (decision D1, via stancl/tenancy v4). Isolation is enforced by the database, not by application where clauses: every tenant-scoped table carries a forced RLS policy, so even a query with a missing WHERE — Eloquent or raw DB:: — cannot return another tenant's rows. Everything the application adds on top (the tenant.member guard, explicit scoping in operator queries) is defence-in-depth; the database is the last line of defence.

The consequence that shapes the whole codebase: handlers on the tenant surface write Eloquent as if RLS weren't there, and the platform relies on RLS as if the app scoping weren't there. Neither layer trusts the other.

The two contexts​

One codebase serves two faces, split by which PostgreSQL role the connection authenticates as:

CentralTenant
WhoOperator backoffice (web guard) + system/console workClient SPA requests (client guard) + per-tenant jobs
Postgres roleBYPASSRLSNOBYPASSRLS
SeesEvery tenant's rows + central tablesOnly the current tenant's rows
Session variablenoneSET stables.current_tenant = <bigint tenant key>
Tenancy lifecyclenever initialized (default_route_mode => CENTRAL in config/tenancy.php)initialized per request / per job

The identity mapping is deliberate (D25): the X-Tenant header carries the tenant's public UUIDv7, the resolver looks it up against tenants.uuid (tenant_model_column => 'uuid' in config/tenancy.php), and only then does PostgresRLSBootstrapper set the session variable to the internal bigint key (getTenantKey()). The bigint never leaves the app; the uuid never enters a policy. The variable name stables.current_tenant (config/tenancy.php → tenancy.rls.session_variable_name) is baked verbatim into every generated policy body and is immutable once policies exist (D11).

Crossing between the contexts is always explicit: tenancy()->run($tenant, ...) enters a tenant's scope from central (per-tenant jobs, seeders); tenancy()->central(...) opens the BYPASSRLS context from inside tenant scope (the email-first tenant-discovery lookup is the canonical case). There is no implicit escalation path.

Identification selects; it never authorizes​

X-Tenant is attacker-controllable — any caller can name any tenant's uuid. That is fine, because identification only selects which RLS scope the request runs in; it confers no access. Two independent layers then protect the data:

  1. RLS itself — even a "wrong" tenant context can only see that tenant's rows, and credentials are verified inside the scope, so a login attempt only ever matches users of the identified tenant.
  2. The tenant.member guard (EnsureUserBelongsToCurrentTenant, after auth:sanctum) — rejects any authenticated user who does not prove membership via the belongsToTenant() contract. It fails closed: no tenant → 404, no user → 401, no proven membership → 403. There is deliberately no duck-typed tenant_id-attribute fallback.

Why coverage can't silently regress: scopeByDefault​

The auto-policy engine fails safe. TenancyServiceProvider::boot() sets TableRLSManager::$scopeByDefault = true, so php artisan tenants:rls scopes every table with a foreign-key path to tenants — adding a tenant_id FK is sufficient to protect a table (D7). You cannot forget an annotation and leave a tenant table open; the only way to keep an FK-bearing table central is the explicit no-rls comment on the column, which is a visible, reviewable opt-out. A nullable tenant_id FK still yields a forced policy (used by mfa_credentials: client credentials are tenant-visible, operator credentials carry NULL and are invisible in any tenant scope).

Tables with no FK path to tenants (reference data, framework tables, the central spatie RBAC tables) get no policy and stay central — by construction, not by convention.

How jobs and queues carry tenant context​

Tenant context survives the request boundary because the bootstrappers listed in config/tenancy.php run on every TenancyInitialized, in a fixed order: PostgresRLSBootstrapper first (connection swap + session var), then per-tenant cache prefixes (CacheTenancyBootstrapper, Valkey), per-tenant filesystem roots (FilesystemTenancyBootstrapper), and — the async link — QueueTenancyBootstrapper, which serializes the current tenant into every queued job and re-initializes tenancy inside the worker before the job runs, resetting the RLS session variable between jobs so a reused worker connection can never leak the previous job's scope. Multi-tenant sweeps invert this: a central scheduler iterates Tenant::cursor() and enters each scope with tenancy()->run() (the task-runner's per-tenant units make this explicit per unit). Sessions are deliberately not tenant-scoped (D15) — the SPA holds one central session; isolation comes from RLS plus tenant.member, not the session store.

The verification machinery — and the gate you personally are​

Four layers keep "every tenant table is policied" true over time:

LayerWhat it doesWhen it runs
php artisan tenants:rls(Re)generates policies from the live schema (SHA1-versioned bodies — a changed body is dropped and recreated) and provisions the shared NOBYPASSRLS role. Runs as the central role.After every migration touching tenant tables — wired into docker/dev-start-app.sh and the deploy pipeline.
php artisan tenants:verify-isolationThe drift gate: recomputes the set of tables the manager intends to scope and exits non-zero if any lacks an enabled + FORCED policy in pg_policies/pg_class.Locally before merge, non-fatally at dev start.
RlsCoverageTest (app-modules/tenancy/tests/Feature/)Proves verify-isolation itself works — fails the moment a tenant_id table exists without a policy.Every suite run.
The canary — RowLevelSecurityIsolationTestRuntime proof on a fixture table: tenant B cannot read or write tenant A's rows, even via raw DB::, while central sees both. Every real tenant-scoped model adds its own create-as-A/read-as-B test.Every suite run.

CI does not run tenants:verify-isolation yet — wiring it in as a hard deploy gate is tracked in docs/tracking/devops-hardening.md. Until that lands, the engineer (or agent) shipping a tenant-scoped schema change is that gate: run php artisan tenants:rls + php artisan tenants:verify-isolation locally before merge, every time.

The third pattern: central tables that reference tenants​

Beyond "pure central" and "tenant-scoped" there is a deliberate hybrid (D53/D54): a central table carrying a plain bigint tenant_id with no FK — so TableRLSManager finds no FK path and generates no policy. It exists because some rows must reference a tenant without being scoped to one:

  • Written outside tenant scope, or against a rolled-back transaction. The bus's denied/failed/rejected attempt records and the vendor/DLP logs are written on the unmanaged pgsql_logs connection (D54) — a separate BYPASSRLS session with no session variable that commits independently, so the audit row survives the command transaction's rollback (D69). An RLS WITH CHECK would simply reject these writes. Webhook rows are similarly recorded in central context before tenancy initializes.
  • Read cross-tenant by design. Operator queues and regulator exports must see all tenants; a real FK would force a policy that silently scoped the operator's view — the "inverse leak" the model forbids.

The pattern has its own guard: every such table must be registered in CentralTableInverseLeakTest::REGISTERED_CENTRAL_TABLES (app-modules/tenancy/tests/Feature/), which asserts that every public table carrying a tenant_id is either RLS-policied or consciously registered — and that the registration list itself never goes stale. Current members include audit_events, vendor_request_logs, webhook_calls, operator_notifications, the DLP central logs, provider_customers, the compliance central stores, and task_run_units — the full annotated list lives in the test.

Going deeper​

← Engineering wiki index