How do I configure SSL/TLS to connect to a Cloud SQL instance?

x-yuri

8/30/24, 11:22 AM

As far as I can see connection over SSL/TLS is always available with Cloud SQL. If I enforce it, it becomes required. But the maximum level of protection I was able reach is verify-ca from the perspective of both the client and the server. That is:

the server won't make sure (auth-options) the common name in the client certificate matches the user I'm trying to connect as (any common name can be used)
the client won't make sure the common name in the server certificate matches the hostname I'm connecting to (any hostname can be used)

According to the docs I can connect with sslmode=verify-full but either I don't know what instance name they mean, or the information in the docs is out of date. (1) How do I connect with verify-full?

The docs also says:

An SSL mode of verify-full is not required; verify-ca is enough because the CA is instance-specific.

In the pg docs I can see:

The difference between verify-ca and verify-full depends on the policy of the root CA. If a public CA is used, verify-ca allows connections to a server that somebody else may have registered with the CA. In this case, verify-full should always be used. If a local CA is used, or even a self-signed certificate, using verify-ca often provides enough protection.

(2) What does this mean? If the CA is local, only authorized people can create a certificate and a private key, and sign the certificate with the CA's root certificate? And although one can probably get the server's certificate (if the server is public), but one can't get the private key? As such, no unauthorized person can have a valid certificate? As such, if a certificate is valid, it was created by an authorized person, and it doesn't matter what the CN is? Why? It seems like I'm close, but there's still something I'm missing.

(3) If the CA is local, verify-ca == verify-full (no eavesdropping, no MITM)?

More details on what exactly I did can be found here.

1 + 2

postgresql

ssl

google-cloud-sql

google-cloud-platform

Robert G

8/30/24, 7:42 PM

Since Google Cloud SQL is a fully managed RDBMS service, some of the native functions may differ or may not work as intended. There are certain functions like [configuring SSL/TLS certificates](https://cloud.google.com/sql/docs/postgres/configure-ssl-instance) which is configured differently with native PostgreSQL. You may still use `verify-full` by following this [documentation](https://cloud.google.com/sql/docs/postgres/connect-admin-ip#connect-ssl:~:text=to%20use%20the-,verify%2Dfull,-mode%20of%20PostgreSQL) for your setup.

x-yuri

8/31/24, 8:05 AM

@RobertG It occurred to me that I indeed can use `verify-full` but then I need to use this long domain (e.g. `xx-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.europe-central2.sql.goog`) as a host. Which might occasionally change (who knows?). And I see no other way to figure it out other than by trying to connect with an incorrect value. Actually it says "and 1 other name" but this one I don't know how to elicit. All this leads me to believe that `verify-full` better be avoided (not to be used).

Score:1

Server

x-yuri

9/1/24, 12:47 AM

How do I connect with verify-full?

You first need to figure out the server certificate's CN:

$ psql -h xx.yyy.xx.yyy -U postgres sslmode=verify-full
psql: error: connection to server at "xx.yyy.xx.yyy", port 5432 failed: server certificate for "31-0628fb91-0e3b-4c89-adca-ad557023a699.europe-central2.sql.goog" (and 1 other name) does not match host name "xx.yyy.xx.yyy"

Then you can connect:

$ psql -U postgres 'sslmode=verify-full hostaddr=xx.yyy.xx.yyy host=xx-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.europe-central2.sql.goog'
Password for user postgres: 
psql (15.4, server 11.19)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=>

I don't know other ways to figure it out (the CN) other than by using this trick. As such, it doesn't seem like you're supposed to use verify-full or that it makes any sense. The documentation suggests that verify-full at least not significantly better than verify-ca.

What does this mean?

With a local CA only authorized people can obtain a certificate, as such no unauthorized person can fake the target server.

If the CA is local, verify-ca == verify-full (no eavesdropping, no MITM)?

verify-ca != verify-full, but supposedly there's not much difference in terms of security. Particularly no MITMs should be possible.

Example of configuring SSL/TLS can be found here.

+ 0

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: How do I configure SSL/TLS to connect to a Cloud SQL instance?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.