Psql remote connection certificate not valid

hey there!

i have started to use cockroachdb the last few days in a test environment. i really like it and everything was straight forward till now.
today i tried to access my CockroachDB with a psql client from a remote machine but i failed somehow.

cockroach version
Build Tag: beta-20170420
Build Time: 2017/04/20 20:23:46
Distribution: CCL
Platform: linux amd64
Go Version: go1.8.1
C Compiler: gcc 6.3.0
Build SHA-1: 9f61dd3e24edd170868a122d4220627230a75178
Build Type: release

the database is configured in secure mode.
the certificates were created as recommended in: https://www.cockroachlabs.com/docs/create-security-certificates.html
cockroach cert create-ca --certs-dir=certs --ca-key=certs/ca.key
cockroach cert create-node 192.168.189.133 $(hostname) --certs-dir=certs --ca-key=certs/ca.key
cockroach cert create-client root --certs-dir=certs --ca-key=certs/ca.key

i copied the ca cert and the client files to the machine i want access the db from and checked the client cert.
openssl verify -verbose -CAfile certs/ca.crt certs/client.root.crt
certs/client.root.crt: OK

the db was started through following command
cockroach start --background --certs-dir=certs --host=192.168.189.133

so i tried to connect to the db with the connection string i got from the dbs output at the startup
psql "postgresql://root@192.168.189.133:26257?sslcert=certs%2Fclient.root.crt&sslkey=certs%2Fclient.root.key&sslmode=verify-full&sslrootcert=certs%2Fca.crt"
ERROR: psql: server certificate for “node” does not match host name “192.168.189.133”

the server cert:
openssl x509 -in certs/node.crt -text -noout
Certificate:
Data:
Version: 3 (0x2)
Serial Number:
23:c3:ef:79:18:69:5e:7f:56:d1:7f:10:86:f3:79:a7
Signature Algorithm: sha256WithRSAEncryption
Issuer: O=Cockroach, CN=Cockroach CA
Validity
Not Before: Apr 22 17:49:40 2017 GMT
Not After : Apr 22 17:49:40 2018 GMT
Subject: O=Cockroach, CN=node
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
Public-Key: (2048 bit)
Modulus:
Exponent: 65537 (0x10001)
X509v3 extensions:
X509v3 Key Usage: critical
Digital Signature, Key Encipherment
X509v3 Extended Key Usage:
TLS Web Server Authentication, TLS Web Client Authentication
X509v3 Subject Alternative Name:
DNS:localhost, DNS:my.testdomain, IP Address:192.168.189.133
Signature Algorithm: sha256WithRSAEncryption

so i switched to dns names instead of ip addresses but i still get an error message:
client cerficates not verified
if i install the cockroach client on the remote machine i am able to connect
cockroach sql --certs-dir=certs --host 192.168.189.133

what i am missing here? is this feature not supported with the psql client?
i am thankful for every reply

cheers!

Pinging certificate expert @marc for help, as I don’t know what to say.

The issue is that psql expects the server address in the Common Name, but we store the user ID there (node.crt is a dual-purpose certificate: client and server), so it does not match. And for some reason, psql does not look at the DNS field.

The solution is to use sslmode=verify-ca which verifies the server certificate chain but does not check the hostname returned.

You can find more details about sslmode at: https://www.postgresql.org/docs/9.1/static/libpq-ssl.html

hello marc, thank you for your quick answer.
ialready tried changing the sslmode to verify-ca but the same error message occurs psql: ERROR: client cerficates not verified
any ideas?

is there a way to separate the connection mode between the client-db and interdb communication. so that it is possible to connect to the db with username and passwort but the nodes are still communication through tls?

Sorry about that, my answer was from memory. The specific error returned is ours (for once, it’s convenient to have a typo) and looks like a mistake on my part when implementing this. This may be broken until we have a fix, but I’ll post more here when I dig into it further.

For the connection mode, if you select secure mode, everything will be over SSL.
However, you can choose to have password-based user authentication (for any user other than root). See https://www.cockroachlabs.com/docs/create-and-manage-users.html

So you have two options for now:

  1. use password-based authentication for non-root users (if you need to prepare DB/tables/users with root, you can do it with cockroach sql)
  2. use cockroach sql for all client interaction

I’ve filed issue 15285 for this and will try to get to it today.
In the meantime, do any of the two options listed above work for you?

thank you for your answer
if i am securing the cluster i can’t connect with username+password from a remote machine. neither with the cockroach client nor psql.
psql: ERROR: cleartext connections are not permitted (makes sense)
although it is possible to connect with username and pwd if am on the local machine.

the remote access is needed for an application which is using cockroach as database. thus i am not able to use the cockroach client.

To connect with username/password, you still need CA certificates.

Here is an example:

# Client cert only for root:
$ ls ~/.cockroach-certs
ca.crt          ca.key          client.root.crt client.root.key node.crt        node.key

# Create user foo with password: by default, this runs as root.
$ ./cockroach user set foo --password
Enter password:
Confirm password:
INSERT 1

# Create a DB and grant all permissions to foo: this runs as root.
$ ./cockroach sql --user=root
# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.
root@:26257/> show databases;
+--------------------+
|      Database      |
+--------------------+
| crdb_internal      |
| information_schema |
| pg_catalog         |
| system             |
+--------------------+
(4 rows)
root@:26257/> create database foo;
CREATE DATABASE
root@:26257/> grant all on database foo to foo;
GRANT

# Connect as user foo using the cockroach sql command. It does not find client certificates for foo, so it prompts for a password. It still uses the CA cert though.
$ ./cockroach sql --user=foo
Enter password:
Confirm password:
# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.
foo@:26257/> show databases;
+--------------------+
|      Database      |
+--------------------+
| crdb_internal      |
| foo                |
| information_schema |
| pg_catalog         |
+--------------------+
(4 rows)
foo@:26257/> set database=foo;
SET
foo@:26257/foo> create table bar (k int, v int);
CREATE TABLE

# Connect as user foo using psql:
$ psql 'postgresql://foo@vintner:26257?sslmode=verify-ca&sslrootcert=%2FUsers%2Fmarc%2F.cockroach-certs%2Fca.crt'
Password:
psql (9.6.1, server 9.5.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

foo=> show databases;
      Database
--------------------
 crdb_internal
 foo
 information_schema
 pg_catalog
(4 rows)
foo=> show "session_user";
 session_user
--------------
 foo
(1 row)

if you don’t care about checking the server certificates against the CA, you can also run with sslmode of allow, prefer, or require. This speaks SSL to the server, but doesn’t check the CA.

$ psql 'postgresql://foo@vintner:26257?sslmode=allow'
Password:
psql (9.6.1, server 9.5.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

foo=> show databases;
      Database
--------------------
 crdb_internal
 foo
 information_schema
 pg_catalog
(4 rows)

thank you for your help! it works. i forgot to remove the root certs from the connection string

Glad it’s working. We do need to clarify our documentation for existing clients talking to a secure cockroach cluster.

In the meantime, the fix to the client cerficates not verified error is going in today and will be available in the next release. That will allow you to connect using sslmode=verify-ca when using client certificates.

do you know why there is a difference between the cockroach client and psql. they should be both verified the same way and using the same certificates. or is there a difference who is knocking on the door?
i’m just curious.

The cockroach sql client is written by us and has slightly different logic (you can find the code for ours here)

One of the things we do differently is try to find the certificates automatically inside the certs dir as opposed to having you specify them all individually.

We probably should allow things like using a password even if you have client certificates present, and support SSL connections without caring about CA verification.

It’s important to know that cockroach sql is mainly support to be a command-line interface to cockroach, but real programs should use drivers. Using those, the manually-specified URL should work the same way as in postgres.

I’ve filed 15318 to bring the cockroach sql behavior closer to psql especially for the security settings.
I’m targeting it for 1.1 as 1.0 is probably too close to get this done in time.