ADR-004: Defense-in-Depth Multi-Tenancy via PostgreSQL Row-Level Security (RLS)

Status

ACCEPTED  2026-09-06

---

Context

The GERCIA Platform operates a multi-vertical B2B SaaS ecosystem triangulating Doctors (Clinical), Patients (Mobile), and Compounding Pharmacies (Pharmacy). Data privacy, tenant isolation, and regulatory compliance (LGPD, Brazilian Federal Medical Council CFM Res. 1.821/2007, and ANVISA RDC 67/2007) are non-negotiable legal requirements. In compounding pharmacy and clinical management, accidental cross-tenant data exposure (e.g., Clinic A accessing Clinic B’s patients or proprietary formulation batches) represents an existential legal liability and catastrophic breach of patient medical confidentiality.

In multi-tenant SaaS architecture, isolation models present critical trade-offs:

1. Database-per-Tenant: Deploying a separate physical database for each client tenant. While offering complete physical isolation, it creates severe operational overhead, connection pool exhaustion on dedicated hosts, and an unsustainable migration bottleneck (running schema migrations across hundreds of databases concurrently during CI/CD).
2. Schema-per-Tenant: Deploying a separate PostgreSQL schema per tenant within a single database. This model suffers from PostgreSQL internal system catalog bloat (pg_class), heavy shared buffer contention, and migration orchestration complexity once the tenant count scales past 100-200 schemas.
3. Shared Database / Shared Schema with ORM-Only Discriminator Filters: Adding a tenant_id column to every table and relying exclusively on application-level filters (such as Entity Framework Core HasQueryFilter). This model is highly efficient, but carries unacceptable catastrophic risk: any raw SQL query (FromSqlRaw, Dapper), overlooked filter in ad-hoc queries, developer invocation of .IgnoreQueryFilters(), or reporting tool integration completely bypasses application logic, exposing all tenants’ records in plain text.

---

Decision

The GERCIA Platform adopts a Consolidated Shared Database with Two-Tier Defense-in-Depth Multi-Tenancy, enforcing tenant isolation at both the application ORM layer and the PostgreSQL database kernel layer.

flowchart TD
    subgraph INGRESS ["1. Request Ingress & Identity Context"]
        REQ["Incoming HTTP / REST Request"]
        AUTH["OpenIddict JWT Authentication Handler"]
        TP["ITenantProvider (Scoped)<br>Resolves TenantId, BranchId, UserId"]
        REQ --> AUTH --> TP
    end

    subgraph TIER1 ["2. Tier 1: Application-Level Isolation (EF Core)"]
        DBCONTEXT["GerciaDbContext"]
        GQF["EF Core Global Query Filter<br><code>WHERE e.tenant_id = @current_tenant_id</code>"]
        TP --> DBCONTEXT --> GQF
    end

    subgraph INTERCEPTION ["3. Session Context Interception"]
        INTERCEPTOR["TenantRowLevelSecurityInterceptor<br>(DbConnectionInterceptor / DbCommandInterceptor)"]
        SET_LOCAL["Injects Transaction Session Variables:<br><code>SET LOCAL app.current_tenant_id = '...'</code><br><code>SET LOCAL app.current_branch_id = '...'</code><br><code>SET LOCAL app.current_user_id = '...'</code>"]
        DBCONTEXT --> INTERCEPTOR --> SET_LOCAL
    end

    subgraph TIER2 ["4. Tier 2: Kernel-Level Isolation (PostgreSQL Engine)"]
        PG_ENGINE["Google Cloud SQL for PostgreSQL 18+"]
        RLS_POL["FORCE ROW LEVEL SECURITY<br><code>CREATE POLICY tenant_isolation_policy ON table<br>USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)</code>"]
        DATA["Physical Table Rows (core, clinical, dental, pharmacy, veterinary, operations)"]
        SET_LOCAL --> PG_ENGINE --> RLS_POL --> DATA
    end

    GQF -- "Generates pre-filtered query plan" --> PG_ENGINE

Architectural Principles:

1. Single Consolidated Platform Database (gercia): All verticals share a single physical PostgreSQL database on managed Google Cloud SQL (gercia-db-prd), segregated logically by domain schemas (core, clinical, dental, pharmacy, veterinary, operations).
2. Tier 1 - Application-Level Query Optimization (EF Core):
   • All multi-tenant entities inherit from TenantAggregateRoot or implement ITenantScoped.
   • GerciaDbContext automatically applies HasQueryFilter(e => e.TenantId == _currentTenantId) during OnModelCreating.
   • Tier 1 ensures generated SQL queries include the tenant_id filter in WHERE clauses, enabling the PostgreSQL optimizer to perform index scans directly on (tenant_id, ...) composite B-Tree indexes without evaluating RLS policies sequentially.
3. Tier 2 - Kernel-Level Hard Enforcement (PostgreSQL RLS):
   • Every multi-tenant table is configured with ENABLE ROW LEVEL SECURITY and FORCE ROW LEVEL SECURITY.
   • RLS policies evaluate current_setting('app.current_tenant_id', true)::uuid.
   • Even if a developer executes raw SQL, uses Dapper, accidentally invokes .IgnoreQueryFilters(), or an application bug occurs, the PostgreSQL kernel itself discards any row belonging to a different tenant, returning 0 rows.
4. Automated Session Interception (TenantRowLevelSecurityInterceptor):
   • Registered in EF Core’s dependency injection pipeline.
   • On every database connection checkout or transaction start, it dispatches SET LOCAL app.current_tenant_id = '{TenantId}' within the transaction scope.
   • SET LOCAL guarantees that session variables are strictly scoped to the active transaction and automatically cleared upon commit or rollback, preventing tenant context leaks across pooled Npgsql connections.
5. Strict Superuser & Administrative Bypass Controls:
   • Database migration tools run under the explicit migration role (gercia_migrator), which possesses BYPASSRLS to apply structural DDL and seed migrations across all schemas.
   • BYPASSRLS permissions are strictly forbidden for application database users (gercia_server / gercia_api) and restricted exclusively to administrative migration runners (gercia_migrator) and DBA emergency accounts.

---

Comparison of Alternatives

Evaluation Criteria	Database-per-Tenant	Schema-per-Tenant	Shared Schema (ORM-Only)	GERCIA Two-Tier RLS (Adopted)
Cross-Tenant Leak Risk	Zero (Physical)	Low	High (Catastrophic)	Zero (Engine-Enforced)
Migration Velocity (CI/CD)	Extremely Slow (1 per tenant)	Slow (Catalog lock)	Fast (Single schema)	Fast (Single schema)
Host Resource Consumption	High (RAM/Connection exhaustion)	Moderate (Buffer bloat)	Minimal	Minimal (Optimal pooling)
Raw SQL & Dapper Safety	Safe	Safe	Vulnerable	Safe (PostgreSQL Kernel Blocks)
Query Optimizer Efficiency	High	High	High	High (Tier 1 Pre-Filters Query)
Operational Complexity	High	High	Low	Low (Single Database Infrastructure)

---

Consequences

Positive

• Provable Zero-Leak Security: Compliance with Brazilian LGPD, CFM, and ANVISA is guaranteed at the database kernel level, eliminating human error in application code from compromising medical records.
• Deterministic Resource Scaling: Operating on Google Cloud SQL with regional High Availability (HA) in southamerica-east1 maximizes buffer cache utilization and eliminates connection pool starvation through client-side connection budgeting (documented in PostgreSQL Architecture).
• Streamlined CI/CD Migrations: Schema migrations run as single, atomic DDL operations via EF Core migrations during Cloud Build deployments, rather than orchestrating multi-tenant migration loops.
• Defense-in-Depth: Tier 1 optimizes query planning and execution paths; Tier 2 provides an unbreakable security perimeter.

Mitigations

• Transaction Variable Injection Overhead: Executing SET LOCAL on transaction begin incurs a sub-millisecond overhead. With low-latency private Serverless VPC Access and persistent Npgsql connection pooling, this overhead is mathematically negligible (< 0.05ms).
• Connection Pooling Hygiene: To prevent pooled connection contamination, SET LOCAL is used exclusively (instead of persistent SET), ensuring session settings automatically revert at the end of the transaction block.