PostgreSQL Multitenancy & Row-Level Security (RLS)

The GERCIA Platform persistence tier is hosted on Google Cloud SQL for PostgreSQL, ensuring enterprise-grade automated Point-in-Time Recovery (PITR), security auditing, and telemetry via Cloud SQL Query Insights. Multitenancy is enforced natively through a defense-in-depth model operating at both the ORM (EF Core) and database kernel (PostgreSQL RLS) layers.

---

1. Hybrid Defense-in-Depth Multitenancy Architecture

The platform rejects single-layer multi-tenancy models. Relying exclusively on ORM filters risks catastrophic cross-tenant data leaks when raw SQL queries, reporting tools, or third-party extensions execute without applying the filter. Conversely, relying solely on database triggers introduces excessive runtime query overhead.

GERCIA implements a comprehensive 4-tier Defense-in-Depth multi-tenant isolation strategy from cryptographic session ingress to database kernel:

flowchart TD
    REQ["1. Incoming HTTP / REST Request"] --> AUTH["2. Cryptographic Session Ingress<br>(Data Protection HMAC-SHA256 / AES-256)"]
    AUTH --> TP["3. Sovereign ITenantProvider<br>(Extracts TenantId strictly from verified claims)"]
    
    subgraph TIER1 ["Tier 2: Application Layer (EF Core 10)"]
        TP --> EF["4. Global Query Filters<br>(Automatically appends WHERE tenant_id = @TenantId)"]
        EF --> INTERCEPT["5. TenantRowLevelSecurityInterceptor<br>(Injects SET LOCAL session variables)"]
    end
    
    subgraph TIER2 ["Tier 3: Database Kernel Layer (Google Cloud SQL)"]
        INTERCEPT --> PG_CMD["6. Execute SQL Query within Transaction"]
        PG_CMD --> PG_RLS["7. PostgreSQL RLS Kernel Enforcement<br>(FORCE ROW LEVEL SECURITY evaluated on every tuple)"]
    end

    PG_RLS --> RESULT["8. Isolated Tenant Result Set Returned"]

Layer Comparison Matrix

Security Layer	Primary Mechanism	Responsibility	Bypass Vulnerability
Tier 0: Cryptographic Session	ASP.NET Core Data Protection (HMAC-SHA256 / AES-256)	Cookie immutability, tamper detection, and cross-portal segregation (.gercia.ops.session vs .gercia.app.session)	Mathematically impossible without server-side master key; single-bit tampering invalidates session.
Tier 1: Sovereign Resolution	WebTenantProvider reading solely from verified claims	Zero-trust parameter isolation; ignores all incoming query strings, route tokens, and custom headers	Immune to IDOR; client-provided parameters are never evaluated for tenant identification.
Tier 2: Application ORM (EF Core)	HasQueryFilter(e => e.TenantId == _tenantId)	Developer ergonomics, optimized query execution plans, automatic WHERE appending	Vulnerable to .IgnoreQueryFilters(), raw FromSqlRaw, and external DBA reporting tools.
Tier 3: Kernel (PostgreSQL RLS)	CREATE POLICY ... USING (tenant_id = current_setting('app.current_tenant_id'))	Hard kernel-level row isolation enforced directly on disk by PostgreSQL query executor	Impossible to bypass by application microservices (FORCE ROW LEVEL SECURITY).

---

2. PostgreSQL Kernel RLS Policies

Every tenant-aware table in the core, clinical, dental, pharmacy, veterinary, and operations schemas enforces Row-Level Security at the PostgreSQL engine level.

Standard RLS DDL Pattern

-- 1. Enable Row-Level Security on target table
ALTER TABLE clinical.patients ENABLE ROW LEVEL SECURITY;

-- 2. FORCE RLS to ensure table owners cannot bypass policies
ALTER TABLE clinical.patients FORCE ROW LEVEL SECURITY;

-- 3. Create comprehensive tenant isolation policy
CREATE POLICY tenant_isolation_policy ON clinical.patients
    AS RESTRICTIVE
    FOR ALL
    TO PUBLIC
    USING (
        tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
    )
    WITH CHECK (
        tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
    );

Critical Policy Invariants:

1. AS RESTRICTIVE: Restrictive policies are combined with AND, ensuring that tenant isolation cannot be weakened by permissive policies added later.
2. FORCE ROW LEVEL SECURITY: In PostgreSQL, table owners and roles with administrative privileges bypass RLS by default. Applying FORCE ROW LEVEL SECURITY guarantees that application connection users (such as gercia_server and gercia_api) are strictly subjected to the policy regardless of role ownership.
3. NULLIF(..., true): Passing true as the missing-setting parameter prevents PostgreSQL from throwing a fatal transaction error when the setting has not yet been initialized, safely evaluating to NULL (which matches zero tenant IDs and blocks all data access).

---

3. EF Core Session Interception Pattern

Application database commands dispatch through TenantRowLevelSecurityInterceptor (inheriting from DbConnectionInterceptor and DbCommandInterceptor).

Upon opening or borrowing a connection from the pool, the interceptor immediately executes lightweight session variable configuration within the current transaction scope:

public sealed class TenantRowLevelSecurityInterceptor : DbConnectionInterceptor
{
    private readonly ITenantProvider _tenantProvider;

    public TenantRowLevelSecurityInterceptor(ITenantProvider tenantProvider)
    {
        _tenantProvider = tenantProvider;
    }

    public override async Task ConnectionOpenedAsync(
        DbConnection connection,
        ConnectionEndEventData eventData,
        CancellationToken cancellationToken = default)
    {
        var tenantId = _tenantProvider.GetCurrentTenantId();
        var branchId = _tenantProvider.GetCurrentBranchId();
        var userId = _tenantProvider.GetCurrentUserId();

        await using var command = connection.CreateCommand();
        command.CommandText = $"""
            SET LOCAL app.current_tenant_id = '{tenantId}';
            SET LOCAL app.current_branch_id = '{branchId}';
            SET LOCAL app.current_user_id   = '{userId}';
            """;

        await command.ExecuteNonQueryAsync(cancellationToken);
        await base.ConnectionOpenedAsync(connection, eventData, cancellationToken);
    }
}

Why SET LOCAL?

• Transaction Scope: SET LOCAL scopes variable assignments strictly to the active transaction. When the transaction commits or rolls back, or when the connection is returned to the Npgsql connection pool, the variables automatically reset, preventing cross-request state contamination in pooled connections.
• Sub-Millisecond Execution: Setting session configuration in PostgreSQL executes in microsecond timeframes directly in memory without disk writes or lock contention.

---

4. Background Workers, Auditing & Administration

Certain background processing daemons and administrative migration pipelines require access across multiple tenants:

A. Database Migrations (dotnet ef database update)

Database migrations run exclusively under a dedicated migration user (gercia_migrator) possessing the BYPASSRLS attribute. This allows schema definitions, indexing, and seed data provisioning to proceed without tenant context.

B. Asynchronous Background Handlers & Cloud Tasks

When background handlers or Cloud Tasks endpoints execute a job for a specific tenant:

1. The handler instantiates an IServiceScope.
2. The job handler sets the scoped ITenantProvider to the targeted TenantId payload.
3. The database interceptor fires SET LOCAL app.current_tenant_id, ensuring the server operates within the exact boundary of the affected tenant.

---

5. Verification & DBA Inspection Runbook

Engineers and DBAs can verify RLS isolation directly in pgAdmin 4 or psql:

-- Step 1: Connect as application user and start a transaction
BEGIN;

-- Step 2: Attempt query without setting tenant context (returns 0 rows)
SELECT id, full_name, medical_chart_number FROM clinical.patients;
-- Result: 0 rows returned (blocked by RLS)

-- Step 3: Inject Clinic Alpha tenant context
SET LOCAL app.current_tenant_id = '01954bf4-7b92-71c1-840b-4171e746be95';
SELECT id, full_name, medical_chart_number FROM clinical.patients;
-- Result: Returns only patients belonging to Clinic Alpha

-- Step 4: Inject Clinic Beta tenant context
SET LOCAL app.current_tenant_id = '01954bf4-7b92-71c1-840b-2f557e391c61';
SELECT id, full_name, medical_chart_number FROM clinical.patients;
-- Result: Returns only patients belonging to Clinic Beta

COMMIT;

---

6. Architectural Decision Record Reference

For comprehensive architectural trade-off evaluations (Shared Database vs Database-per-Tenant, RLS performance profiling, and regulatory compliance analysis), refer to:

👉 ADR-004: Defense-in-Depth Multi-Tenancy Architecture (PostgreSQL RLS)