Skip to content

Multi-tenancy & row-level security

What it is

ManpowerIQ is multi-tenant: one deployment serves many business units (BUs), and each BU's operational data is kept strictly separate. Isolation is enforced in three independent layers — EF query filters, a write-stamping interceptor, and PostgreSQL row-level security — so that no single piece of code getting it wrong can leak data across tenants.

Why it's built this way

Tenant separation is the platform's hardest requirement: companies must never see or touch each other's people, plans, or rosters — not by accident, not on purpose. A single guard (just query filters, say) would be one bug away from a cross-tenant leak. Defence-in-depth with three layers means a gap in one is caught by the next, and the database is the final backstop even for a raw query.

Key decision: two connection strings. The owner connection (manpoweriq, BYPASSRLS) is used only for migrations; the runtime connection (manpoweriq_app) is RLS-bound, so the running application physically cannot bypass row-level security (sheet 01 §decisions, Program.cs:147-149, MIQ105_Report.md §7).

How it works

Every tenant-owned row carries a business_unit_id. The tenant identity comes from the signed-in user's JWT claims and flows through all three layers:

flowchart TB
    JWT[JWT claims<br/>business_unit_id, is_super_admin, sub, permissions] --> CTP[CurrentTenantProvider<br/>reads claims via IHttpContextAccessor]
    CTP --> L1[Layer 1 · EF query filters<br/>WHERE business_unit_id = me]
    CTP --> L2[Layer 2 · TenantStampingInterceptor<br/>stamp/guard business_unit_id on write]
    CTP --> MW[TenantContextMiddleware<br/>sets Postgres session GUCs]
    MW --> L3[Layer 3 · PostgreSQL RLS<br/>CREATE POLICY tenant_isolation]
    L1 --> DB[(PostgreSQL)]
    L2 --> DB
    L3 --> DB

  1. Reads — EF global query filters. ManpowerIQDbContext.OnModelCreating applies a BU + soft-delete HasQueryFilter to ~40 entities (sheet 01 §build-status, ManpowerIQDbContext.cs:117-321). The per-entity rule is !IsDeleted && (BusinessUnitId == t.BusinessUnitId || t.IsSuperAdmin). This is the universal layer — every tenant entity has it.

  2. Writes — TenantStampingInterceptor. On insert of a TenantEntity with BusinessUnitId == 0, it stamps the current BU; if a different BU is set and the caller is not super-admin it throws "Cross-tenant insert blocked"; on modify, changing BusinessUnitId throws "Tenant change is not allowed" (sheet 01 §rules, TenantStampingInterceptor.cs:29-58). Registered on the production DbContext (Program.cs:145,167-173).

  3. Database — PostgreSQL RLS. 12 migrations run raw ENABLE/FORCE ROW LEVEL SECURITY + CREATE POLICY tenant_isolation (sheet 01 §build-status, e.g. MIQ105_AddUsersAndTenantRls.cs:95-108, MIQ003_AddRbac.cs:473-483). The policy is USING (business_unit_id = current_setting('app.current_bu', true)::int OR current_setting('app.is_super_admin', true) = 'true'). It is FORCEd so it binds even table owners.

Carrying the tenant to the database. TenantConnectionInitializer sets the app.current_bu / app.is_super_admin session GUCs per request (TenantConnectionInitializer.cs:26-41), and TenantContextMiddleware runs after UseAuthentication and before UseAuthorization (sheet 01 §build-status, Program.cs:476-478). The tenant is read from the JWT by the real CurrentTenantProvider (CurrentTenantProvider.cs:26-105).

Global (non-tenant) data. A deliberate few tables are not scoped: Permission is a global catalog with no business_unit_id; NodeType, HolidayType, ShiftType, and other lookups are shared reference data (sheet 01 §rules, MUST-NOT #4). Some hybrid entities (CertificationType, DemandReason, DemandTemplate, AllocationRuleTypeCatalog) allow both global (BusinessUnitId == null) and BU-owned rows.

Gotchas / constraints

  • The runtime tenant provider is the real CurrentTenantProvider, not a stub. StubCurrentTenantProvider exists but is dead, unreferenced code — do not describe the system as "stubbed / always BU=1". The old "stub" note in MIQ-005 is historical (sheet 01 MUST-NOT #1, Program.cs:179).
  • RLS is real but selective; query filters are universal. RLS covers a specific allow-list of tables (users, terminals, departments, nodes, pools, audit_events, roles, role_permissions, user_roles, + later sprints), not automatically every TenantEntity. EF query filters are the layer that covers everything (sheet 01 MUST-NOT #2).
  • Dev fallback. In Development with no token, CurrentTenantProvider returns BU=1 / username "dev" (sheet 01 MUST-NOT #6, CurrentTenantProvider.cs:36,63). A dev convenience — not the production path.
  • Super-admin bypass is permissive by GUC. is_super_admin='true' opens RLS; only trusted paths may set that GUC (sheet 01 §decisions, MIQ105_Report.md §10.3).
  • Query filters capture _tenant at model-build time. Production is safe (fixed options identity = one model-cache entry), but tests must use PerInstanceModelCacheKeyFactory (sheet 01 edge-cases, Program.cs:150-173).
  • GUC session scope relies on Npgsql resetting connections on pool return to avoid cross-request bleed (sheet 01 edge-cases, TenantConnectionInitializer.cs:24-27).

Build status

Available — all three layers ship and are enforced: query filters (~40 entities), the stamping interceptor, and Postgres RLS (12 migrations). Verified LIVE (sheet 01 §build-status).