SSL connection to Postgres with self-signed certificate


#1

I’m trying to evaluate Metabase and to do so I need to connect to a PostgreSQL 9.6 database over SSL using a self-signed certificate. I first tried with the macOS client, but the error message was too vague, so I then downloaded the .jar file so that I could watch the terminal while attempting to connect.

The cert and key that I use function with other clients and I’ve chmod 0600'd them.

Into the “Additional JDBC connection string options” field, I have tried the following:

sslmode=require&sslcert=/Users/clayh/.postgresql/postgresql.crt&sslkey=/Users/clayh/.postgresql/postgresql.key

This leads to the error:

Apr 13, 2018 9:55:45 AM org.postgresql.Driver connect
SEVERE: Connection error: 
org.postgresql.util.PSQLException: Could not read SSL key file /Users/clayh/.postgresql/postgresql.key.
	at org.postgresql.ssl.jdbc4.LazyKeyManager.getPrivateKey(LazyKeyManager.java:250)
	at sun.security.ssl.AbstractKeyManagerWrapper.getPrivateKey(SSLContextImpl.java:1240)
	at sun.security.ssl.ClientHandshaker.serverHelloDone(ClientHandshaker.java:824)
	at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:348)
	at sun.security.ssl.Handshaker.processLoop(Handshaker.java:1026)
	at sun.security.ssl.Handshaker.process_record(Handshaker.java:961)
	at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1062)
	at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1375)
	at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1403)
	at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1387)
	at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:62)
	at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:359)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:148)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)
	at org.postgresql.Driver.makeConnection(Driver.java:450)
	at org.postgresql.Driver.connect(Driver.java:252)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at clojure.java.jdbc$get_connection.invokeStatic(jdbc.clj:364)
	at clojure.java.jdbc$get_connection.invoke(jdbc.clj:226)
	at clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1014)
	at clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:996)
	at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1090)
	at clojure.java.jdbc$query.invoke(jdbc.clj:1047)
	at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1063)
	at clojure.java.jdbc$query.invoke(jdbc.clj:1047)
	at metabase.driver.generic_sql$can_connect_QMARK_.invokeStatic(generic_sql.clj:220)
	at metabase.driver.generic_sql$can_connect_QMARK_.invoke(generic_sql.clj:217)
	at metabase.driver$fn__25827$G__25580__25834.invoke(driver.clj:51)
	at metabase.driver$can_connect_with_details_QMARK_$fn__25949.invoke(driver.clj:461)
	at clojure.core$binding_conveyor_fn$fn__4676.invoke(core.clj:1938)
	at clojure.lang.AFn.call(AFn.java:18)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.io.IOException: extra data given to DerValue constructor
	at sun.security.util.DerValue.init(DerValue.java:384)
	at sun.security.util.DerValue.<init>(DerValue.java:294)
	at javax.crypto.EncryptedPrivateKeyInfo.<init>(EncryptedPrivateKeyInfo.java:84)
	at org.postgresql.ssl.jdbc4.LazyKeyManager.getPrivateKey(LazyKeyManager.java:196)
	... 36 more

04-13 09:55:45 ERROR metabase.driver :: Failed to connect to database: org.postgresql.util.PSQLException: Could not read SSL key file /Users/clayh/.postgresql/postgresql.key.
04-13 09:55:45 DEBUG metabase.middleware :: POST /api/setup/validate 400 (123 ms) (0 DB calls).
{:errors {:dbname "org.postgresql.util.PSQLException: Could not read SSL key file /Users/clayh/.postgresql/postgresql.key."}}

Then I tried with the option:

sslmode=require&sslcert=/Users/clayh/.postgresql/postgresql.crt&sslkey=/Users/clayh/.postgresql/postgresql.key&sslfactory=org.postgresql.ssl.NonValidatingFactory

which leads to this error:

Apr 13, 2018 9:57:30 AM org.postgresql.Driver connect
SEVERE: Connection error: 
org.postgresql.util.PSQLException: FATAL: connection requires a valid client certificate
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:438)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:222)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)
	at org.postgresql.Driver.makeConnection(Driver.java:450)
	at org.postgresql.Driver.connect(Driver.java:252)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at clojure.java.jdbc$get_connection.invokeStatic(jdbc.clj:364)
	at clojure.java.jdbc$get_connection.invoke(jdbc.clj:226)
	at clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1014)
	at clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:996)
	at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1090)
	at clojure.java.jdbc$query.invoke(jdbc.clj:1047)
	at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1063)
	at clojure.java.jdbc$query.invoke(jdbc.clj:1047)
	at metabase.driver.generic_sql$can_connect_QMARK_.invokeStatic(generic_sql.clj:220)
	at metabase.driver.generic_sql$can_connect_QMARK_.invoke(generic_sql.clj:217)
	at metabase.driver$fn__25827$G__25580__25834.invoke(driver.clj:51)
	at metabase.driver$can_connect_with_details_QMARK_$fn__25949.invoke(driver.clj:461)
	at clojure.core$binding_conveyor_fn$fn__4676.invoke(core.clj:1938)
	at clojure.lang.AFn.call(AFn.java:18)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:748)

04-13 09:57:30 ERROR metabase.driver :: Failed to connect to database: org.postgresql.util.PSQLException: FATAL: connection requires a valid client certificate
04-13 09:57:30 DEBUG metabase.middleware :: POST /api/setup/validate 400 (151 ms) (0 DB calls).
{:errors {:dbname "org.postgresql.util.PSQLException: FATAL: connection requires a valid client certificate"}}

Any idea what’s going on here or how to get this to work?


#2

I would try adding your self signed cert as a trusted one in the Java keystore. See MongoDb SSL Connection with Self Signed Certificate issue for a similar story with MongoDB.