psql command, how to enforce certificate check?

Question

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:

localhost:26257:test:test:*****

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?

Answer 1

To have psql verify the server certificate, you must specify the appropriate connection string options. This is done by the libpq client shared library, so it will work the same with all clients that use it.

• You can directly specify the options in the connection string:

  psql 'host=... port=... dbname=... user=... sslmode=verify-full sslrootcert=/path/to/ca-certificate sslcrl=/path/to/certificate-revocation-list'
  

  If the CA certificate of the revocation list are in the default locations, you can omit the respective options.

  See the documentation on sslrootcert and sslcrl.

• You can use environment variables to specify the options:

  export PGSSLMODE=verify-full
  export PGSSLROOTCERT=/path/to/ca-certificate
  export PGSSLCRL=/path/to/certificate-revocation-list
  psql -h ... -p ... -d ... -U ...
  

  See the documentation on environment variables for details.

For details on how server verification works, consult the appropriate section of the documentation.