Dbvisualizer and crdb using ssl connections

I am struggling to get dbvisualizer to connect to crdb using ssl and certificates.
I did find: https://forum.cockroachlabs.com/t/connecting-to-an-ssl-secure-server-using-jdbc-java-and-client-certificate-authentication/400

and converted by keys and certificates:
openssl x509 -in ca.crt -inform pem -outform der -out ca.der
openssl pkcs8 -topk8 -inform PEM -outform DER -in client.root.key -out client.root.pk8 -nocrypt
openssl x509 -in client.root.crt -inform pem -outform der -out client.root.der
and referenced them in the connection details

In the debug log I get:
2018-08-27 16:01:57.375 INFO 500 [ExecutorRunner-pool-2-thread-11 - H.ᅣᄡ] Exception while connecting cdb secure

*** Database Properties ****************************

Alias: cdb secure

Notes: [0 characters]

Settings Format: Database URL

URL: jdbc:postgresql://localhost:26257/system

Driver: PostgreSQL

Userid: root

Preferred Database Type: POSTGRESQL

Current Database Type: POSTGRESQL

Preferred Profile: auto

Current Profile: null (not connected?)

Connection State(s): DISCONNECTED

--- Object Type Settings ---------------------------

None

--- Overridden User Properties ---------------------

postgresql.ConnectionModeMigrated = true

postgresql.SQLCommanderMostRecentCatalog = zabbix

postgresql.SQLCommanderMostRecentSchema = public

postgresql.TransactionIsolation = 0

--- Overridden Driver Properties -------------------

sslrootcert = /Users/ronr/certs/ca.der

ApplicationName = dbvis

ssl = true

user = root

sslpassword = ""

sslcert = /Users/ronr/certs/client.root.der

sslmode = require

sslkey = /Users/ronr/certs/client.root.pk8

loggerLevel = OFF

*** End Database Properties ************************

*** Driver Information *****************************

Name: PostgreSQL

DefaultDriver: true

DefaultClass: org.postgresql.Driver

Paths:

Path: /Applications/dbvis/DbVisualizer 10.0.13.app/Contents/java/app/jdbc/postgresql/postgresql.jar

Class: org.postgresql.Driver

*** End Driver Information *************************

*** Current Connection Message *********************

Disconnected.

*** End Current Connection Message *****************

2018-08-27 16:01:57.375 INFO 500 [ExecutorRunner-pool-2-thread-11 - H.ᅣᄡ] Exception while connecting cdb secure

org.postgresql.util.PSQLException: SSL error: Received fatal alert: bad_certificate

at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:67)

at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:359)

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:148)

Any ideas about how to get this working? dbVisualizer is a very nice SQL tool with briljant graphing options in it…

Could you try with the original certificates? When I was testing DBeaver with LibPQFactory, I used the pk8 key but left the certificates as they were. See the screenshot in this PR for the full configuration.

Confirmed that it works using the original pem-encoded certificates. I selected PostgreSQL and overrode the ssl parameters but left the factory at the default:

      <Alias>blah</Alias>
      <Notes />
      <Url />
      <Driver>PostgreSQL</Driver>
      <Userid>root</Userid>
      <Profile>auto</Profile>
      <Type>postgresql</Type>
      <Password>rSk+E2+Snss=</Password>
      <ServerInfoFormat>1</ServerInfoFormat>
      <AutoDetectType>true</AutoDetectType>
      <Properties>
        <Property key="sslrootcert">/home/marc/.cockroach-certs/ca.crt</Property>
        <Property key="sslmode">verify-full</Property>
        <Property key="sslkey">/home/marc/.cockroach-certs/client.root.key.pk8</Property>
        <Property key="sslcert">/home/marc/.cockroach-certs/client.root.crt</Property>
        <Property key="dbvis.ConnectionModeMigrated">true</Property>
        <Property key="dbvis.TransactionIsolation">0</Property>
      </Properties>
      <UrlFormat>0</UrlFormat>
      <UrlVariables>
        <Driver>
          PostgreSQL
          <UrlVariable UrlVariableName="Server">localhost</UrlVariable>
          <UrlVariable UrlVariableName="Port">26257</UrlVariable>
          <UrlVariable UrlVariableName="Database">postgres</UrlVariable>
        </Driver>
      </UrlVariables>

Thanks for your reply @marc

good to know it works and happy to see that the cli will soon also generate the pk8 files. Doing so would certainly make things easier.
I still get:
2018-08-27 16:41:12.145 INFO 532 [ExecutorRunner-pool-2-thread-2 - H.ᅣᄡ] Exception while connecting cdb secure

*** Database Properties ****************************

Alias: cdb secure

Notes: [0 characters]

Settings Format: Database URL

URL: jdbc:postgresql://localhost:26257/system

Driver: PostgreSQL

Userid: root

Preferred Database Type: POSTGRESQL

Current Database Type: POSTGRESQL

Preferred Profile: auto

Current Profile: null (not connected?)

Connection State(s): DISCONNECTED

--- Object Type Settings ---------------------------

None

--- Overridden User Properties ---------------------

postgresql.ConnectionModeMigrated = true

postgresql.SQLCommanderMostRecentCatalog = zabbix

postgresql.SQLCommanderMostRecentSchema = public

postgresql.TransactionIsolation = 0

--- Overridden Driver Properties -------------------

sslrootcert = /Users/ronr/certs/ca.crt

ApplicationName = dbvis

ssl = true

user = root

sslpassword =

sslcert = /Users/ronr/certs/client.root.crt

sslmode = require

sslkey = /Users/ronr/certs/client.root.pk8

loggerLevel = OFF

*** End Database Properties ************************

*** Driver Information *****************************

Name: PostgreSQL

DefaultDriver: true

DefaultClass: org.postgresql.Driver

Paths:

Path: /Applications/dbvis/DbVisualizer 10.0.13.app/Contents/java/app/jdbc/postgresql/postgresql.jar

Class: org.postgresql.Driver

*** End Driver Information *************************

*** Current Connection Message *********************

Disconnected.

*** End Current Connection Message *****************

2018-08-27 16:41:12.151 INFO 532 [ExecutorRunner-pool-2-thread-2 - H.ᅣᄡ] Exception while connecting cdb secure

**org.postgresql.util.PSQLException: SSL error: Received fatal alert: bad_certificate**

at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:67) 

And I still have no clue why I get this error.

dbvis.xml shows:

cdb secure

jdbc:postgresql://localhost:26257/system
PostgreSQL
root
auto
postgresql
rSk+E2+Snss=
0

/Users/ronr/certs/ca.crt
require
root
true
/Users/ronr/certs/client.root.pk8
/Users/ronr/certs/client.root.crt
“”
true
zabbix
public
0

0


PostgreSQL
ronrmb03.local
26257
system



false

22





crdb 2.0.5
dbvis 10.0.13

Ah. odd. I’m on dbvis 10.0.14 but I doubt there’s much difference.

You could check with the built-in sql client: cockroach sql --url 'postgres://root@localhost:26257/?sslmode=require&sslrootcert=/Users/ronr/certs/ca.crt&sslcert=/Users/ronr/certs/client.root.crt&sslkey=/Users/ronr/certs/client.root.key'

If that still fails then there’s a problem with the certificates, otherwise we need to dig further.

Turns out 10.0.14 picked up a newer JDBC postgresql driver (42.2.2): https://www.dbvis.com/releasenotes/10.0.

The previous version used the 42.1.1 driver release May 2017. It’s had a few certificate-related changes, but nothing that would explain this (that I can see). Still, it’s worth a try.

Nevermind, that worked as well:

*** Database Properties ****************************
Alias: blah
Notes: [0 characters]
Settings Format: Server Info
URL: jdbc:postgresql://localhost:26257/postgres
Driver: PostgreSQL
Userid: root
Preferred Database Type: AUTO
Current Database Type: POSTGRESQL
Preferred Profile: auto
Current Profile: null (not connected?)
Connection State(s): CONNECTING
--- Object Type Settings ---------------------------
None
--- Overridden User Properties ---------------------
postgresql.TransactionIsolation = 0
--- Overridden Driver Properties -------------------
sslrootcert = /home/marc/.cockroach-certs/ca.crt
ApplicationName = dbvis
sslmode = require
loggerLevel = OFF
sslkey = /home/marc/.cockroach-certs/client.root.key.pk8
sslcert = /home/marc/.cockroach-certs/client.root.crt
*** End Database Properties ************************
*** Driver Information *****************************
Name: PostgreSQL
DefaultDriver: true
DefaultClass: org.postgresql.Driver
Paths:
  Path: /tmp/DbVisualizer/jdbc/postgresql/postgresql.jar
    Class: org.postgresql.Driver
*** End Driver Information *************************
*** Current Connection Message *********************
Connecting. Wait...
*** End Current Connection Message *****************

I must be making some foolish error:

so I re-created the certs directory and the my-safe-directory as listed in https://www.cockroachlabs.com/docs/stable/secure-a-cluster.html
Converted:
openssl pkcs8 -topk8 -inform PEM -outform DER -in client.root.key -out client.root.key.pk8 -nocrypt

chmod 600 client.root.key.pk8

client ronr@ronrmb03:~/certs
$>ls -l
total 48
-rw-r--r--  1 ronr  staff  1111 Aug 27 17:01 ca.crt
-rw-r--r--  1 ronr  staff  1103 Aug 27 17:01 client.root.crt
-rw-------  1 ronr  staff  1675 Aug 27 17:01 client.root.key
-rw-------  1 ronr  staff  1216 Aug 27 17:03 client.root.key.pk8
-rw-r--r--  1 ronr  staff  1168 Aug 27 17:01 node.crt
-rw-------  1 ronr  staff  1679 Aug 27 17:01 node.key
client ronr@ronrmb03:~/certs

$>cockroach sql --url ‘postgres://root@localhost:26257/?sslmode=require&sslrootcert=/Users/ronr/certs/ca.crt&sslcert=/Users/ronr/certs/client.root.crt&sslkey=/Users/ronr/certs/client.root.key.pk8’

Welcome to the cockroach SQL interface.

All statements must be terminated by a semicolon.

To exit: CTRL + D.

Error: tls: failed to find any PEM data in key input
Failed running “sql”:cry:

You need to give cockroach the PEM-encoded key file (client.root.key), not the DER-encoded key file (client.root.key.pk8). The latter is only for JDBC drivers.

Hah …
now it is working in dbvis … (both 10.0.13 and 10.0.14)
I guess I must have crippled one of the certificate files while playing with openssl :frowning:
thanks for you help @marc (and also Roger from dbvis)
PostgreSQL
9.5.0
PostgreSQL JDBC Driver
42.2.2

and also:
cockroach sql --url ‘postgres://root@localhost:26257/?sslmode=require&sslrootcert=/Users/ronr/certs/ca.crt&sslcert=/Users/ronr/certs/client.root.crt&sslkey=/Users/ronr/certs/client.root.key’

Welcome to the cockroach SQL interface.

All statements must be terminated by a semicolon.

To exit: CTRL + D.

Server version: CockroachDB CCL v2.0.5 (x86_64-apple-darwin13, built 2018/08/13 17:55:48, go1.10) (same version as client)

Cluster ID: c4257215-3ccf-43d7-842e-c75cb89eed18

Enter ? for a brief introduction.

warning: no current database set. Use SET database = to change, CREATE DATABASE to make a new database.
root@localhost:26257/>

problem solved. :smiley:

Ronald,

How is your experience using dbvisualizer now that it’s working for you?

I am using dbvis since many years. In my opinion it is the best SQL database browsing tool. Currently it has -obviously- poor support for crdb but it is working, for me good enough, what is missing now, is easy to add using a custom profile.
Look at things like monitoring SQL and graphing. No other tool I checked has these.
I use it for Oracle, postgres, mysql, ms$sql and now also for crdb. If you can help Roger -from dbvis- to write the queries to populate the objects tree, I think he will happily be adding those to the basic config. It already has quite a list of supported databases and platforms …