Database Transport Security & Zero-Trust Architecture

The GERCIA Platform enforces strict Zero-Trust Network Architecture (ZTNA) and TLS 1.3 transport-level encryption across all database communications, operating entirely within Google Cloud Platform’s private network infrastructure.

Architectural Evolution: Retiring Manual PKI in Favor of Cloud-Native Security

In alignment with ADR-007, GERCIA retired legacy manual internal PKI and expiring bilateral client certificates (PKI_CA_CERT, PKI_CLIENT_GERCIA_SERVICES_*) from the database connection layer in favor of cloud-native managed transport security.

In Google Cloud SQL, defense-in-depth is achieved through isolated Private IP VPC networking, enforced TLS 1.3 encryption, and high-entropy SCRAM-SHA-256 credentials rotated continuously via PostgresSecurityHelper. This completely eliminates operational certificate expiration incidents while guaranteeing 100% compliance with ANVISA, CFM, and LGPD healthcare mandates.

---

1. Network Topology & Zero-Trust Transport Boundary

The persistence tier resides strictly within an isolated Google Cloud VPC. PostgreSQL ports are completely invisible to the public internet:

flowchart TD
    subgraph GCP ["Google Cloud Platform (southamerica-east1)"]
        subgraph RUN ["Google Cloud Run (Serverless Workloads)"]
            SERVER_SVC["Gercia.Server (Cloud Run Service)<br>REST Gateway, OpenIddict STS & MCP"]
            SERVER_JOB["gercia-migrator (Cloud Run Job)<br>Pre-Deploy DDL Migrations (--migrate)"]
        end

        subgraph VPC ["Serverless VPC Access (10.8.0.0/28)"]
            CONNECTOR["VPC Access Connector<br>(Private Subnet Peering)"]
        end

        subgraph DATA ["Private Data Subnet (No Public IP)"]
            CSQL[("Google Cloud SQL for PostgreSQL 18<br>HA Regional Cluster<br>Private IP (10.x.x.x)<br>Enforce SSL/TLS = ON (TLS 1.3)")]
        end

        subgraph GSM ["Google Secret Manager"]
            SECRETS["CONNECTION_STRING_*_PRD/STG<br>(64-character SCRAM-SHA-256 secrets)"]
        end
    end

    SERVER_SVC -- "1. Private Egress" --> CONNECTOR
    SERVER_JOB -- "1. Private Egress" --> CONNECTOR

    CONNECTOR -- "2. Encrypted TLS 1.3 Handshake" --> CSQL

    SERVER_SVC -. "Dynamic 15-min Fetch" .-> SECRETS
    SERVER_JOB -. "Dynamic 15-min Fetch" .-> SECRETS

---

2. Security Defense-in-Depth Layers

Rather than maintaining a brittle, self-signed X.509 client certificate infrastructure with fixed expiration dates, GERCIA implements three impenetrable defense layers:

Layer 1: Network Isolation (Zero Public IP)

• Private IP Only: Cloud SQL instances (gercia-db-prd, gercia-db-stg) are provisioned exclusively with private IP addresses via VPC peering. They do not possess public IPv4 addresses, preventing internet scanning, DDoS, or unauthorized connection attempts.
• Serverless VPC Access: Cloud Run workloads route all database traffic through dedicated /28 serverless connector subnets directly into the VPC.
• Andromeda SDN Encryption: Google’s internal software-defined network automatically encrypts all VM-to-VM and container-to-VPC traffic at the physical network layer.

Layer 2: Transport Layer Encryption (TLS 1.3)

• Enforced TLS 1.3: PostgreSQL instances enforce ssl = on with ssl_min_protocol_version = 'TLSv1.3'. Unencrypted plain-text TCP connections are rejected immediately at the socket handshake.
• Cipher Suite Restriction: Connections enforce modern, forward-secret cipher suites (ECDHE-ECDSA-AES256-GCM-SHA384, ECDHE-RSA-AES256-GCM-SHA384), protecting clinical health data against retrospective decryption.

Layer 3: Application Identity & SCRAM-SHA-256

• Salted Challenge Response Authentication: All service accounts authenticate using SCRAM-SHA-256 with 4,096 iterations, eliminating plain-text password exposure on the wire.
• Dynamic Zero-Downtime Rotation: C# hosts utilize Npgsql’s UsePeriodicPasswordProvider in PostgresSecurityHelper. Secret strings are refreshed from Google Secret Manager every 15 minutes, with immediate 1-second retries upon authentication failure, allowing database password rotation with zero service restarts.

---

3. C# Host Integration (PostgresSecurityHelper)

Host applications (such as Gercia.Server) configure database transport security via Gercia.Core.Security.PostgresSecurityHelper:

var csBuilder = new NpgsqlConnectionStringBuilder(connectionString)
{
    Pooling = true,
    MinPoolSize = 5,
    MaxPoolSize = 80,
    Timeout = 15,
    CommandTimeout = 30,
    Timezone = "UTC",
    ApplicationName = builder.Environment.ApplicationName,
};

// Enforces TLS 1.3 in staging/production while allowing local container development
PostgresSecurityHelper.ConfigureSsl(csBuilder, builder.Environment.IsDevelopment());

var dataSourceBuilder = new NpgsqlDataSourceBuilder(csBuilder.ConnectionString);

// Configures non-blocking dynamic password refresh from Google Secret Manager
PostgresSecurityHelper.ConfigureReactivePasswordProvider(
    dataSourceBuilder,
    gcpProjectId,
    activeSecretName,
    builder.Environment.IsDevelopment()
);

var dataSource = dataSourceBuilder.Build();

---

4. Regulatory Compliance Mapping

Standard	Legal Requirement	Technical Implementation
CFM Res. 1.821 / Res. 2.299	Mandatory cryptographic protection for Electronic Health Records (PEP) in transit.	Cloud SQL enforced TLS 1.3 + Andromeda hardware encryption.
LGPD (Art. 46)	Security, technical, and administrative measures to protect personal data from unauthorized access.	Private IP VPC boundary + SCRAM-SHA-256 + GSM KMS encryption.
ANVISA RDC 67/2007	Traceability and data integrity for compounding pharmacy records.	Non-login schema ownership (gercia_owner) + Kernel-level soft delete + PostgreSQL RLS.
HIPAA (45 CFR § 164.312)	Encryption of Protected Health Information (PHI) in transit and at rest.	Cloud SQL TLS 1.3 in transit + Cloud KMS AES-256 storage volume encryption at rest.

---

5. Cloud SQL Administrative & DBA Access Runbook

When human DBAs or engineers require operational access to Cloud SQL instances:

1. Cloud SQL Auth Proxy over Cloudflare WARP: DBAs authenticate via Google Cloud IAM (gcloud auth login) and launch the Cloud SQL Auth Proxy locally on their authorized developer machine:

   cloud-sql-proxy gercia-gcp:southamerica-east1:gercia-db-stg --port 5432 --private-ip

2. Ephemeral IAM mTLS: The Cloud SQL Auth Proxy automatically negotiates ephemeral, self-rotating 1-hour mTLS certificates directly with Google’s Cloud SQL admin plane, requiring zero manually managed client certificates.
3. Audit Logging: Every administrative session is correlated and recorded in Cloud Audit Logs.