0

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?

2
  • 1
    Your question is unclear: do you want normal PostgreSQL to check the server certificate, or do you want to get rid of the error message in CockroachDB? – Laurenz Albe Jun 2 '20 at 6:33
  • 1
    I want normal psql to check the server certificate. The cockroachdb documentation is very clear about that: it must have a CA cert, there is no way to get rid of that check. The libpq API is also clear, it has an sslmode option for controlling this. But the psql command has no documentation or any option about certificate checks. Or at least I could not find it. – nagylzs Jun 3 '20 at 7:11
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.