SSL connection to Google Cloud Postgres

Question

I have migrated my postgres database to Google Cloud SQL.

Without SSL enabled I can connect with no issues.

However I am struggling to get the SSL connection working.

I am using the pgx pool driver.

I have downloaded the server, client and private key pem files.

The error message I get back is

failed to write startup message (x509: certificate signed by unknown authority)

    serverCert, err := ioutil.ReadFile("server-ca.pem")
    if err != nil {
        log.Fatal(err)
    }

    clientCert, err := ioutil.ReadFile("client-cert.pem")
    if err != nil {
        log.Fatal(err)
    }

    caCertPool := x509.NewCertPool()
    ok := caCertPool.AppendCertsFromPEM(serverCert)
    ok = caCertPool.AppendCertsFromPEM(clientCert)
    fmt.Println(ok)

    keypair, err := tls.LoadX509KeyPair("server-client-certs.pem", "client-key.pem")
    if err != nil {
        log.Fatal(err)
    }

    tlsConfig := &tls.Config{
        Certificates: []tls.Certificate{keypair},
        ServerName:   s.Host,
        ClientCAs:    caCertPool,
        ClientAuth:   tls.RequestClientCert,
        GetClientCertificate: func(*tls.CertificateRequestInfo) (*tls.Certificate, error) {
            return &keypair, nil
        },
    }

    connectionString := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s connect_timeout=%d sslmode=require",
        s.Host, s.Port, s.User, s.Password, s.Name, s.ConnectTimeout)

    connConfig, err := pgxpool.ParseConfig(connectionString)
    if connConfig != nil {
        connConfig.ConnConfig.TLSConfig = tlsConfig
    }

    var pool *pgxpool.Pool

    pool, err = pgxpool.ConnectConfig(context.Background(), connConfig)

I've been having problems connecting to Cloud SQL using SSL as well. I've used a similar approach to the code above and found an issue. In the pgx source code you can see the ParseConfig function configures TLSConfig based on the connection string. If you then override TLSConfig all that configuration is lost. E.g. connConfig.ConnConfig.TLSConfig = tlsConfig. One approach would be to set the values of TLSConfig individually. E.g connConfig.ConnConfig.TLSConfig.ServerName = s.Host. — Phil Hale

Marc Marc · Accepted Answer · 2020-07-28T13:02:26

You have a number of problems with your tls.Config, I recommend you read the docs to understand what each field does.

Building the CA pool:

The CertPool should contain the CA certificate used to sign the server certificate, this is server-ca.pem. It does not need the client certificate.

Specifying the CA pool:

ClientCAs is the CA pool used by the server to verify the client certificates, it is only used on the server side. You need to specify your CA pool in RootCAs.

This is the cause of your problem, your client is attempting to verify the server certificate but does not know its CA.

Other fields:

ClientAuth is a server side field used to enforce a specific client certificate behavior, it has no effect when set on the client side.

GetClientCertificate is not needed as long as Certificates is set, you can just get rid of it.

You should also double check your client certificate. You're loading the keypair server-client-certs.pem / client-key.pem. If these are indeed the client certificate and key, then you should be fine.

Assuming your can connect to database host (whitelisted in the firewall), your certificates are all correct (CA certificate for the server, client certificate, and client key), the corrections listed here will let you connect.

After all this, your code becomes:

    caCertPool := x509.NewCertPool()
    ok := caCertPool.AppendCertsFromPEM(serverCert)
    fmt.Println(ok)

    keypair, err := tls.LoadX509KeyPair("server-client-certs.pem", "client-key.pem")
    if err != nil {
        log.Fatal(err)
    }

    tlsConfig := &tls.Config{
        Certificates: []tls.Certificate{keypair},
        ServerName:   s.Host,
        RootCAs:      caCertPool,
    }

Final note: server-ca.pem is not a server certificate, it's the CA certificate used to sign the server cert. The client will not know the server cert ahead of time, it will receive it during the TLS handshake.

SSL connection to Google Cloud Postgres

1 Answers

Building the CA pool:

Specifying the CA pool:

Other fields: