Repository navigation
pg with google cloud postgres #79
Description
Activity
Chapter 3 in my spam here, I have discovered a working solution. ssl.host should be set to "google-cloud-project:postgres-instance"
This worked for me. Also found that google started issuing certificates for new instances with
subjectaltnameadded which is equals to something likeDNS:<uuid>.us-central1.sql.goog, older instances don't have it so looks like defaultcheckServerIdentityimplementation is checking against subjectaltname if it's present.Reacted by Angus RyerI received the following reply from GCP support regarding this issue:
I have inspected the project “xxxxxxx” and noticed that the Cloud PostgreSQL instance was created on May 15, 2023. As per the update from Engineering team new Cloud SQL postgreSQL instances created from January 2023, they have a certification that has SAN in it, so using IP address or CN doesn't work with the cert.
This requires that the host uses the Cloud SQL DNS entry and invalidates using IP address or “region:instance” as the host when using sslmode verify-full. Basically in the error message you will see the DNS entry ending in “.sql.goog”.
Inorder to mitigate the issue we suggest to adjust as following:
For PSQL
$psql "sslmode=verify-full sslrootcert=server-ca.pem sslcert=client-cert.pem sslkey=client-key.pem hostaddr=IP_ADDRESS port=5432 user=postgres dbname=postgres host=DNS_NAME"
Add the host field with the DNS_NAME from the certificate (ie xxxxxxxxxxxxxxxxx.us-west2.sql.goog )
For Node.js
Set the servername field to the DNS address from the certificate. ie:
ssl: {
rejectUnauthorized: true,
ca: fs.readFileSync(
/etc/db/certs/cacerts.pem) ,servername: 'xxxxxxxxxxxxxxxxx.us-west2.sql.goog',
}
Still unclear 1) where this is documented (nowhere that I've been able to find) and 2) how this will work with a Terraform-based setup.
Reacted by Stephen Reddekopp and Angus RyerThis issue has been raised a while ago, but we are still struggling with it in 2024. We have Google Cloud PostgreSQL instances that were created before 2023 and therefore suffer from the
<project>:<instance-name>CN issue in theserver-ca.pemfile.Has anybody found a way to upgrade the instance to receive a new
<id>.sql.googSAN? We are currently unable to initiate a proper db connection overnode-postgresdue to the malformed hostname (the colon in the server name prevents us from pre-defining the hostname in/etc/hostsor the like).We are using a standard connection string at the moment:
postgresql://<user>:<pwd>@<ip>:5432/<db>?sslmode=verify-full&sslrootcert=server-ca.pem&sslcert=client-cert.pem&sslkey=client-key.pem&host=<project>:<instance>&hostaddr=<ip>and tried all
sslmodevariants and all mode configurations of the instance itself. For newer Google Cloud SQL instances this approach works fine since the hostname can be resolved.psqlconnection works fine for both hostname types.We know that we can set up new instances and migrate all databases to these new instances, but since it only affects the server CA certificate, this seems overkill.
Would be grateful for any suggestions, thanks!
Has anybody found a way to upgrade the instance to receive a new
<id>.sql.googSAN?No, unfortunately.
We are currently unable to initiate a proper db connection over
node-postgresdue to the malformed hostnamewe managed to workaround it by using a custom implementation of
checkServerIdentitythat looks similar to below:ssl.checkServerIdentity = (h, c) => { if (h !== c.subject.CN) { return new ErrServerIdentityMismatch(`Server certificate CN ${c.subject.CN} does not match host ${h}`, h, c); } return undefined; };Older certificates have just
subjectfield withCNset to the host, so the check above works for both old and new certs.Thanks for the workaround @evgeny-myasishchev! Unfortunately, we are in a setting where we can just provide environment variables for the DB connection, e.g. either dedicated SSL settings for
CA,CLIENT_KEY,CLIENT_CERTand the like or a plainCONNECTION_STRING.Unfortunately, callbacks cannot be implemented this way. Interestingly, though,
psql's implementation works out of the box.Got it working, ref. here: directus/directus#22159 (comment)
As I understand it,
@google-cloud/cloud-sql-connectoris one well-supported way to make a properly safe connection.
My team has a postgres instance in google cloud, and ran into trouble connecting to the database after upgrading to pg 8.0.3 from 7.x
After reading the changelog, we were able to connect by adding rejectUnauthorized : false in the ssl settings
This raised some red flags with us, and one of the developers found the setting for host in the ssl object, which works as expected
It would be helpful to add this to the documentation page.