PostgreSQL Multitenancy & Row-Level Security (RLS)

The GERCIA Platform persistence tier is hosted on Percona Distribution for PostgreSQL, ensuring enterprise-grade disaster recovery (pgBackRest), security auditing (pgaudit), and telemetry (PMM). Multitenancy is enforced natively at both the ORM and database engine layers.

---

1. Hybrid Defense-in-Depth Multitenancy

The platform implements a two-tier Defense-in-Depth multi-tenant isolation strategy:

graph TD
    REQ["Incoming HTTP / SignalR Request"] --> AUTH["Auth Middleware (Validates JWT)"]
    AUTH --> TP["ITenantProvider (Resolves TenantId & BranchId)"]
    TP --> EF["EF Core Global Query Filter (Level 1 Defense)<br>WHERE tenant_id = @tenantId AND is_deleted = false"]
    EF --> INTERCEPT["TenantRowLevelSecurityInterceptor<br>SET LOCAL app.current_tenant_id = '...';"]
    INTERCEPT --> PG_RLS["PostgreSQL RLS Policies (Level 2 Hard Defense)<br>USING (tenant_id = current_setting('app.current_tenant_id')::uuid)"]

---

2. PostgreSQL RLS Session Interception

Every database command dispatched by GerciaDbContext passes through TenantRowLevelSecurityInterceptor, injecting session variables into the active PostgreSQL connection:

SET LOCAL app.current_tenant_id = '01954bf4-7b92-71c1-840b-4171e746be95';
SET LOCAL app.current_branch_id = '01954bf4-7b92-71c1-840b-2f557e391c61';
SET LOCAL app.current_user_id   = '01954bf4-7b92-71c1-840b-898223c494c5';

Even if a developer executes raw SQL or uses .IgnoreQueryFilters() in C#, the PostgreSQL database engine actively blocks cross-tenant data leakage.