Home / server / Questions / 1142715
Accepted
x-yuri
Asked: 2023-08-30 19:22:19 +0800 CST

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

  • 772

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.

ssl

1 Answers

  1. Best Answer

    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 noone can pretend to be a 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.

    • 1