Trouble with psql client connection to cockroach - SSL error


#1

I have setup a 3 node cockroach cluster with a Loadbalancer( ha proxy) on AWS. The cluster is up and I am able to access cockroach DB via cockroach sql client from a remote machine. I have setup a secure cluster.

/usr/local/bin/cockroach sql --certs-dir=certs --host=52.43.2.102

warning: no current database set. Use SET database = to change, CREATE DATABASE to make a new database.
root@52.43.2.102:26257/> show “session_user”;
±-------------+
| session_user |
±-------------+
| root |
±-------------+
(1 row)

Time: 986.505µs

I am having trouble connecting via psql client on the remote machine. I have generated node certifcate for the remote node and client certificate[ root user ] , copied the node cert as root cert in the .postgresql directory , also have the client certs. I keep getting the error as below, please let me know what I am missing,
psql "postgresql://root@52.43.2.102:26257?sslcert=certs%2Froot.crt&sslkey=certs%2Froot.key&sslmode=verify-ca"
psql: SSL error: certificate verify failed


(Raphael 'kena' Poss) #2

Hi V4s

the issue is the parameter you’re using for the psql command line:

If you look closely you can see that these two parameters are really giving a file name to the keys sslcert and sslkey. You are giving the file names certs/root.crt and certs/root.key.

Meanwhile you also write:

So you have copied the files in a different directory from certs. So the psql command cannot find them.

You need to pass the full path to the cert/key files in sslcert and sslkey.

Can you try this?


#3

Thanks knz,
That dint seem to work. I tried giving the absolute path as well.

[root@ip-172-30-0-110 ~]# psql “postgresql://root@52.43.2.102:26257?sslcert=certs%2Froot.crt&sslkey=certs%2Froot.key&sslmode=verify-ca”
psql: SSL error: certificate verify failed
[root@ip-172-30-0-110 ~]#
[root@ip-172-30-0-110 ~]#
[root@ip-172-30-0-110 ~]# psql “postgresql://root@52.43.2.102:26257?sslcert=%2Froot%2Fcerts%2Froot.crt&sslkey=%2Froot%2Fcerts%2Froot.key&sslmode=verify-ca”
psql: SSL error: certificate verify failed
[root@ip-172-30-0-110 ~]# ls certs/
ca.crt client.root.crt client.root.key node.crt node.key root.crt root.key
[root@ip-172-30-0-110 ~]# pwd
/root


(Marc) #4

You’re specifying sslmode=verify-ca, but you’re not giving psql a CA certificate. You can do so with the connection string arg: sslrootcert=path/to/ca.crt


#5

Thanks Marc, that worked!

psql “postgresql://root@52.43.2.102:26257?sslcert=%2Froot%2Fcerts%2Froot.crt&sslkey=%2Froot%2Fcerts%2Froot.key&sslrootcert=%2Froot%2Fcerts%2Fca.crt&sslmode=verify-ca”
psql (9.6.6, server 9.5.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type “help” for help.