SSL on PostgreSQL(GCP CloudSQL)

Cloud SQL SSL/TLS Compliance and Certificate Management: DORA, PostgreSQL, and CloudSQL

Prerequisites for Secure Cloud SQL Setup

• Ensure the connecting principal (user or service account) has the Cloud SQL Client IAM role.
• Add your client IP to the list of authorized networks, if necessary.
• Enable either “Allow only SSL connections?” for TLS or “Require trusted client certificates?” for mTLS under the Connections/Security tab.

DORA Regulatory Requirements and PostgreSQL SSL Modes

DORA (Digital Operational Resilience Act) Key Requirements:

• Encryption of Data in Transit: All communications with third-party providers, including databases, must use SSL/TLS encryption. Modes like sslmode=disable, allow, or prefer are non-compliant as they allow unencrypted connections.
• Certificate Validation: Strong authentication mechanisms are required, including server certificate validation to prevent MITM (Man-In-The-Middle) attacks. Modes like sslmode=require (which does not validate certificates) do not meet this standard.
• Hostname Verification: Ensures connections are only made to authorized servers. This is enforced by sslmode=verify-full, which checks both the certificate chain and hostname.

Conclusion: DORA’s technical intent maps to verify-full for databases. Financial entities must implement certificate validation and hostname verification to comply with Articles 9(4)(c-d) and Annex IV.

PostgreSQL SSL Modes: Security Comparison

Cloud SQL SSL Modes

• ALLOW_UNENCRYPTED_AND_ENCRYPTED: Permits both unencrypted and encrypted connections. Not secure.
• ENCRYPTED_ONLY: Only allows encrypted (TLS) connections, but does not validate certificates.
• TRUSTED_CLIENT_CERTIFICATE_REQUIRED: Default and most secure; only allows TLS connections with valid client certificates.

Types of Certificates Supported in Cloud SQL

Infrastructure Configuration Example

resource "google_sql_database_instance" "postgres_instance" {
  name             = "postgres-instance"
  region           = "asia-northeast1"
  database_version = "POSTGRES_14"
  settings {
    tier = "db-custom-2-7680"
    ip_configuration {
      ssl_mode = "TRUSTED_CLIENT_CERTIFICATE_REQUIRED"
    }
  }
  deletion_protection = false
}

Certificate Rotation: Minimizing Downtime

1. Prepare a New Certificate:
   • Create and download a new server CA certificate before rotation.
2. Update Clients:
   • Distribute the new certificate to all clients and test connections.
3. Complete the Rotation:
   • Promote the new certificate to active status and verify client connectivity.
4. Rollback if Needed:
   • If issues arise, revert to the previous certificate.
5. Leverage Cloud SQL Features:
   • Use multi-certificate support and Cloud SQL Proxy for seamless updates.

Example Commands:

# Create a new server CA certificate
gcloud sql ssl server-ca-certs create --instance=INSTANCE_NAME

# Download the new certificate
gcloud sql ssl server-ca-certs list --format="value(cert)" --instance=INSTANCE_NAME > FILE_PATH/FILE_NAME.pem

# Rotate the certificate
gcloud sql ssl server-ca-certs rotate --instance=INSTANCE_NAME

# Roll back if necessary
gcloud sql ssl server-ca-certs rollback --instance=INSTANCE_NAME

Troubleshooting and Best Practices

• Hostname Verification Errors: Ensure the certificate’s Subject Alternative Name (SAN) matches the server’s hostname or IP address. Hostname mismatches will cause verify-full to fail.
• Cloud SQL Proxy: Use the Cloud SQL Proxy to automate SSL handling and certificate updates, reducing manual intervention and downtime.
• Client Configuration: Always set sslmode=verify-full with the correct CA bundle for DORA compliance.

Summary

• DORA requires strong encryption and certificate validation for database connections, effectively mandating sslmode=verify-full for PostgreSQL.
• Cloud SQL supports multiple SSL modes; only the strictest modes (TRUSTED_CLIENT_CERTIFICATE_REQUIRED and verify-full) are compliant with DORA.
• Proper certificate management and rotation procedures minimize downtime and maintain compliance.
• Always verify infrastructure and client configurations to ensure secure, validated, and compliant database connectivity.