0

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?)

Thanatos
  • 356
  • 3
  • 12
  • Your edit removed the user. The user is required. The certificate encrypts the connection, it does not provide identity to Cloud SQL. – John Hanley Jul 31 '23 at 16:28

1 Answers1

0

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

John Hanley
  • 4,754
  • 1
  • 11
  • 21
  • 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.) – Thanatos Jul 31 '23 at 14:37