How to connect to a GCP Cloud SQL Postgres server using client certs?

Thanatos

7/28/24, 11:30 PM

I'm attempting to authenticate with a cloud SQL instance using a client cert:

psql -h "$IP" "sslmode=verify-ca sslkey=certs/tls.key sslcert=certs/tls.crt sslrootcert=certs/ca.crt dbname=postgres"
Password for user root:

The server's cert is in certs/ca.crt; the client's cert/key in tls.crt/tls.key.

As you can see, that appears to be failing, and psql is apparently falling back to password. How can I either debug this situation, or what is otherwise wrong with the command?

Additionally, when I generate the certificate in GCP, oddly, the only input that is required is a name for the certificate; is this the user? (It seems to end up as the CN, which is what vanilla PG uses.)

Actually, the following interaction is even more bizarre:

If I do not supply a client certificate, I get told that I need to do that:

# psql -h "$IP" "sslmode=verify-ca sslrootcert=certs/ca.crt dbname=postgres"
psql: error: connection to server at "[…]", port 5432 failed: FATAL:  connection requires a valid client certificate

(which is interesting, as we definitely have other stuff merrily working without client certs?)

If I then supply a client cert,

# psql -h "$IP" "sslmode=verify-ca sslrootcert=certs/ca.crt sslkey=certs/tls.key sslcert=certs/tls.crt dbname=postgres"
Password for user root:
psql: error: connection to server at "[…]", port 5432 failed: fe_sendauth: no password supplied

What is going on here? The client cert is valid enough that apparently we no longer get the "connection requires a valid client certificate" error … but yet not valid enough to authenticate? (And we fallback to password, and then ultimately fail?)

1 + 1

postgresql

ssl

google-cloud-sql

gcp

John Hanley

7/31/24, 4:28 PM

Your edit removed the user. The user is required. The certificate encrypts the connection, it does not provide identity to Cloud SQL.

Score:0

Server

John Hanley

7/28/24, 11:55 PM

The password is mandatory for Cloud SQL even though PostgreSQL supports passwordless connectivity while using SSL.

In your example, the user is admin, which is specified by the command line argument user=admin.

Suggestion: Enforce SSL/TLS encryption

+ 1

Thanatos

7/31/24, 2:37 PM

Cloud SQL supports client certificates as well, at least ostensibly. (There's UI to generate them!) Client certs would be orthogonal to enforcing TLS (though they themselves are impossible to use outside of TLS). I goofed in supplying the user on the CLI there. (Regardless, supplying it or not supplying it doesn't seem to affect the outcome: the client cert is apparently rejected & auth falls back to password.)

Elon Musk

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

EN: How to connect to a GCP Cloud SQL Postgres server using client certs?

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.