pg with google cloud postgres

[ghost]

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

ssl: {
    **rejectUnauthorized: false,**
    ca: fs.readFileSync('/path/to/server-certificates/root.crt').toString(),
    key: fs.readFileSync('/path/to/client-key/postgresql.key').toString(),
    cert: fs.readFileSync('/path/to/client-certificates/postgresql.crt').toString(),
  },

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

ssl: {
    host: "google-cloud-project:postgres-instance",
    ca: fs.readFileSync('/path/to/server-certificates/root.crt').toString(),
    key: fs.readFileSync('/path/to/client-key/postgresql.key').toString(),
    cert: fs.readFileSync('/path/to/client-certificates/postgresql.crt').toString(),
  },

It would be helpful to add this to the documentation page.

[ghost]

Allow me to use some key words to potentially help another unfortunate soul:

GCP Cloud SQL SSL TLS Certificate Host Name

Well, after hours of digging, the certificates are no longer created with the alternate hosts specifying "google-cloud-project:postgres-instance". Instead they use a goofy DNS entry in the form of "1-\<uuid>.\<geographical zone>.sql.goog". I'm not sure where this uuid can be found or if it's even predictable. I will be raising the issue with the GCP team soon and may update following.

[ghost]

So it appears that the real reason that this changed has to do with the node TLS library as documented here. A custom implementation of checkIdentityServer() appears to be the best way to securely use GCP Cloud SQL with node-postgres

[ghost]

Chapter 3 in my spam here, I have discovered a working solution. ssl.host should be set to "google-cloud-project:postgres-instance" as mentioned originally. Now, because the checkIdentityServer() default implementation changed it must be implemented manually like so:

checkServerIdentity: (host, cert) => {
    if (host !== cert.subject.CN) {
        return Error("invalid hostname: " + host)
    }
},

[gemyago]

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 subjectaltname added which is equals to something like DNS:<uuid>.us-central1.sql.goog, older instances don't have it so looks like default checkServerIdentity implementation is checking against subjectaltname if it's present.

[robdiciuccio]

I 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.

[stx-chris]

This 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 the server-ca.pem file.

Has anybody found a way to upgrade the instance to receive a new <id>.sql.goog SAN? We are currently unable to initiate a proper db connection over node-postgres due to the malformed hostname (the colon in the server name prevents us from pre-defining the hostname in /etc/hosts or 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 sslmode variants and all mode configurations of the instance itself. For newer Google Cloud SQL instances this approach works fine since the hostname can be resolved. psql connection 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!

[gemyago]

Has anybody found a way to upgrade the instance to receive a new <id>.sql.goog SAN?

No, unfortunately.

We are currently unable to initiate a proper db connection over node-postgres due to the malformed hostname

we managed to workaround it by using a custom implementation of checkServerIdentity that 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 subject field with CN set to the host, so the check above works for both old and new certs.

[stx-chris]

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_CERT and the like or a plain CONNECTION_STRING.

Unfortunately, callbacks cannot be implemented this way. Interestingly, though, psql's implementation works out of the box.

[stx-chris]

Got it working, ref. here: https://github.com/directus/directus/issues/22159#issuecomment-2051489257