Connecting to an SSL secure server using JDBC/JAVA and client certificate authentication


(christian) #1

(This is not a question but rather my notes on connecting to an SSL server using JDBC and client certificates).
As a side note, client certificates are mandatory for user root (and recommended anyways in various cases).

Given a running, secured cluster, and assuming a client certificate has been created as described in

https://www.cockroachlabs.com/docs/create-security-certificates.html

The only catch for connecting using JDBC is that the postgresql driver doesn’t read PEM certs of keys.
Rather we’ll convert the cert to the DER format and the key to pks8:

openssl x509 -in maxroach.cert -inform pem -outform der -out maxroach.der
openssl pkcs8 -topk8 -inform PEM -outform DER -in maxroach.key -out maxroach.key.pk8 -nocrypt

We’ll then use those files in the cockroach URL:

  String url = "jdbc:postgresql://cockroach-host:26257/lefty?user=maxroach"
      + "&sslcert=path%to%2Fmaxroach.der"
      + "&sslkey=path%to%2Fmaxroach.key.pk8"
      + "&sslmode=require&ssl=true";

  // Connect to the database.
  conn = DriverManager.getConnection(url);

Also note that in my experience cockroach wouldn’t recognize the user from the certificate so we just included the ?user=maxroach part in the URL.


Dbvisualizer and crdb using ssl connections
(Marc) #2

Thanks for the details.
You should also be able to specify the user at the beginning of the URL:

"postgresq://maxroach@cockroach-host:26257/lefty"

The user does indeed need to be specified somewhere in the URL (using <user>@ or ?user=<user>) because it’s technically separate from the authentication method. ie: with password authentication, we couldn’t just guess the user.


(christian) #3

I’ve tried user@host, don’t think the postgreSQL driver supports that (java.net.UnknownHostException).


(Matt Jibson) #4

We do this same process in our java drive tests: https://github.com/cockroachdb/cockroach/blob/master/pkg/acceptance/java_test.go

Java is way more difficult than any other driver I’ve seen for this process, so it should probably be documented somewhere. I’m opening an issue to improve the docs. (https://github.com/cockroachdb/docs/issues/935)


#5

Another nugget of information – if you are using IBM Java set the system property:

-Dcom.ibm.jsse2.overrideDefaultTLS=true

This gives the same behaviour as Oracle Java and will allow TLSv1.2.

The postgresql JDBC driver passes “TLS” to the SSLContext.getInstance method.


(Rich Loveland) #6

Realize it’s pretty late to bump this thread, but FYI we have updated the docs for connecting to a secure cluster with JDBC here: Build a Java app with CockroachDB

In the linked example we were able to get the cluster to recognize the user from the certificate without adding it to the connection string.