I have created a cockroachdb v20.1.0 cluster based on this: https://www.cockroachlabs.com/docs/stable/deploy-cockroachdb-on-premises.html
I have also created a test user like this:
CREATE USER test with password '****'; CREATE DATABASE test; GRANT ALL ON DATABASE test TO test; SHOW GRANTS ON DATABASE test; SHOW GRANTS FOR test;
Then I have created a unix user called test, and tried to connect to this database.
I put this into .pgpass:
If I try to connect with psql client, everything seems to be fine:
psql --host=localhost -d test -p 26257 psql (12.2 (Ubuntu 12.2-4), server 9.5.0) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off) Type "help" for help. test=>
The connection is encrypted with TLSv1.0, the authentication succeeds.
However, if I try to connect with cocroachdb sql command, then it fails:
$ cockroach sql --host=localhost --user=test 130 ↵ # # Welcome to the CockroachDB SQL shell. # All statements must be terminated by a semicolon. # To exit, type: \q. # ERROR: cannot load certificates. Check your certificate settings, set --certs-dir, or use --insecure for insecure clusters. problem with CA certificate: not found Failed running "sql"
It is clearly stated in the cockroachdb docs ( https://www.cockroachlabs.com/docs/stable/authentication.html#client-authentication ) that the cockroachdb client always needs a CA certificate to validate the node's certificate.
In the postgresql documentation ( https://www.postgresql.org/docs/12/libpq-ssl.html ) they say the following:
By default, PostgreSQL will not perform any verification of the server certificate. This means that it is possible to spoof the server identity (for example by modifying a DNS record or by taking over the server IP address) without the client knowing. In order to prevent spoofing, the client must be able to verify the server's identity via a chain of trust.
This documentation also tells that when "sslmode is set to verify-full" then libpq will verify the server's certificate.
However, I do not see any command line option (for the psql command!) to turn on certification verification forcedly.
Is there an environment variable or a command line option for psql that will enforce certificate check for the connection?