Connecting Cloud Functions with Cloud SQL (PostgreSQL) with Public IP and SSL

[ch2023]

Hi there,

We have a CloudSQL PostgreSQL instance with public IP address and SSL encryption requiring trusted client certificates.

We want to connect our Cloud Functions to this PostgreSQL instance using certificates but unfortunately constantly run into the same error:

psycopg2.OperationalError: connection to server at "INSERT_PUBLIC_IP_POSTGRESQL", port 5432 failed: root certificate file "-----BEGIN CERTIFICATE---— “…. certificate here …” does not exist. Either provide the file, use the system's trusted roots with sslrootcert=system, or change sslmode to disable server certificate verification.

Any ideas what to do?

Steps so far:

• Downloaded Server Certificate server-ca.pem under CloudSQL >> Connections
• Created Client Certificate and key (client-cert.pem and client-key.pem)
• Added all three files to Secret Manager, gave Cloud function Secret Manger Secret Accessor rights
• Try to access the secrets within the Cloud Function (with Python runtime) and connect to the DB in the following way:

Show More

# Python Code for Cloud Functions
client = secretmanager.SecretManagerServiceClient()

certificate_name = "projects/PROJECT_ID/secrets/client_secret/versions/latest"
private_key_name = "projects/PROJECT_ID/secrets/key_secret/versions/latest"
server_ca_name = "projects/PROJECT_ID/secrets/server_secret/versions/latest"

certificate_version = client.access_secret_version(name=certificate_name)
private_key_version = client.access_secret_version(name=private_key_name)
server_ca_version = client.access_secret_version(name=server_ca_name)

certificate = certificate_version.payload.data.decode("utf-8")
private_key = private_key_version.payload.data.decode("utf-8")
server_ca = server_ca_version.payload.data.decode("utf-8")

# Database connection parameters
db_config = { "host": "INSERT_PUBLIC_IP_POSTGRESQL",
"user": "INSERT_USER",
"password": "INSERT_USER_PWD",
"dbname": "INSERT_DB_NAME",
"sslmode": "verify-full",
"sslrootcert": server_ca,
"sslcert": certificate,
"sslkey": private_key,
}

# Connect to the database
conn = psycopg2.connect(**db_config)

=> This leads to the error described above.

The error stays the same when:
(1) Saving the certificates in Cloud Storage and loading it from the cloud functions (unsafe; only tried for debugging)
(2) Saving the Secrets via Secret Reference under Cloud Functions >> Configuration >> Security & Image Repo (both the same error for env variables or Mounted as volume")

We have the feeling the error is related to how the cloud functions are reading the .pem files as when we run the following command on the local machine in the folder where the .pem files are saved we can access the db:

Show More

psql "sslmode=verify-ca sslrootcert=server-ca.pem sslcert=client-cert.pem sslkey=client-key.pem hostaddr=PUBLIC_IP_CLOUDSQL_INSTANCE port=5432 user=USER_NAME dbname=DB_NAME"

Note: This command only works for “sslmode=verify-ca” but not for “sslmode=verify-full” for any reason.

Any help is appreciated 🙂 Thanks so much in advance!

[scotty97]

+1 to bring to top

[ms4446]

The error message indicates that psycopg2 is unable to locate or recognize the provided root certificate. This could be due to:

• Incorrect path to the certificate.
• Corrupted certificate file.
• Insufficient permissions on the certificate file.

To troubleshoot:

1. Verify Certificate Location: Ensure the path to the certificate is correctly specified in your connection parameters.
2. Check Certificate Integrity: Open the certificate in a text editor and ensure it has a valid structure, starting with -----BEGIN CERTIFICATE----- and ending with -----END CERTIFICATE-----.
3. File Permissions: Ensure the certificate and key files have the correct permissions. While the certificate can be world-readable (chmod 644 <certificate_file_name>), the private key should have stricter permissions (chmod 600 <client_key_file_name>).

Additional checks:

• Ensure you're using a compatible version of psycopg2 that supports SSL.
• The sslmode parameter should be set to verify-full or verify-ca based on your requirements.
• The sslrootcert should point to the certificate of the CA that issued the server certificate.