[google-cloud-sql-discuss] Re: psql to Cloud SQL instance using SSL

Hello Yannick,

Yes, my fault, I realized that I asked my question in a wrong way. I was just in doubt why supplying 'bar' public/private key pair (in the arguments of psql) works in authorization manner -- i.e. there is no connection because 'bar' is no longer on the client certificates list and, on the other hand, not supplying the certificates is enough to establish the connection. 

Thanks for you answer about clientcert setting (can I see pg_hba.conf in Google Cloud Platform that is used for my instance?), this leaded me to a following citation from https://www.postgresql.org/docs/9.6/static/ssl-tcp.html: 

"When clientcert is not specified or is set to 0, the server will still verify any presented client certificates against its CA file, if one is configured — but it will not insist that a client certificate be presented."

That is probably why it verifies 'bar' (because it is presented) but it does not insist on presenting the certificate (the connection is established without supplying the client certificate). Hence, probably instance has clientcert not equal to 1.

Are my conclusions correct?

Thanks for your help.

On Tuesday, September 12, 2017 at 4:14:48 PM UTC+2, Yannick (Cloud Platform Support) wrote:

As explained in this the PostgreSQL documentation, the clientcert parameter needs to be set to 1 for client certificates to be a requirement for a connection. Client certificates are for authenticating users and do not enable encryption of the connection, the server certificate suffices for that.

On Monday, September 11, 2017 at 5:04:14 PM UTC-4, Yannick (Cloud Platform Support) wrote:

Hello Sebastian,

I don't have a full answer for you at the moment but I'll try and inquire. Note that all of the behavior you have noted should correspond to the PostgreSQL documentation.

On Monday, September 11, 2017 at 9:04:01 AM UTC-4, Sebastian Stawicki wrote:

Hi all,

please explain this to me... To simplify my question... I have configured Cloud SQL Postgresql instance in the following way:

- I have whitelisted my IP (in the authorization tab)

- I have created ssl client certificate 'foo' (in the ssl tab) and downloaded foo-client-cert.pem and foo-client-cert.pem

- I have created ssl client certificate 'bar' (in the ssl tab) and downloaded bar-client-cert.pem and bar-client-cert.pem and then I deleted this client certificate (in the ssl tab) 

- I have also downloaded server-ca.pem

Now,

psql "sslmode=verify-full sslrootcert=keys/server-ca.pem sslcert=keys/foo-client-cert.pem sslkey=keys/foo-client-key.pem hostaddr=xx.xx.xx.xx port=xxxx user=xxxx dbname=xxxx host=xxxx"

works as expected -- ssl connection established

psql "sslmode=verify-full sslrootcert=keys/server-ca.pem sslcert=keys/bar-client-cert.pem sslkey=keys/bar-client-key.pem hostaddr=xx.xx.xx.xx port=xxxx user=xxxx dbname=xxxx host=xxxx"

works as expected -- not connecting, giving the following  

psql: SSL error: tlsv1 alert unknown ca

psql "sslmode=verify-full sslrootcert=keys/server-ca.pem hostaddr=xx.xx.xx.xx port=xxxx user=xxxx dbname=xxxx host=xxxx"

without supplying sslcert nor sslkey also establishes the ssl connection

This would be consistent with the documentation on https://cloud.google.com/sql/docs/postgres/connect-admin-ip stating that "The sslcert and sslkey parameters are optional.", but at this moment I don't understand why this works. I.e. you cannot use the deleted 'bar' public/private key pair, however you can connect using (indirectly) some default, system configuration specific, postgres public/private key pair. Am I missing something?

Best regards,

s 

  

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/622ba3c4-bd3d-4d46-ad73-71d3705420e6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.