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
client = secretmanager.SecretManagerServiceClient()
Show More
certificate_name = “projects/PROJECT_ID/secrets/client_secret/versions/latest”
Show More
private_key_name = “projects/PROJECT_ID/secrets/key_secret/versions/latest”
Show More
server_ca_name = “projects/PROJECT_ID/secrets/server_secret/versions/latest”
Show More
certificate_version = client.access_secret_version(name=certificate_name)
Show More
private_key_version = client.access_secret_version(name=private_key_name)
Show More
server_ca_version = client.access_secret_version(name=server_ca_name)
Show More
certificate = certificate_version.payload.data.decode(“utf-8”)
Show More
private_key = private_key_version.payload.data.decode(“utf-8”)
Show More
server_ca = server_ca_version.payload.data.decode(“utf-8”)
Show More
db_config = { “host”: “INSERT_PUBLIC_IP_POSTGRESQL”,
Show More
“user”: “INSERT_USER”,
Show More
“password”: “INSERT_USER_PWD”,
Show More
“dbname”: “INSERT_DB_NAME”,
Show More
“sslmode”: “verify-full”,
Show More
“sslrootcert”: server_ca,
Show More
“sslcert”: certificate,
Show More
“sslkey”: private_key,
Show More
}
Show More
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!