Errors when attempting to connect to PostgreSQL 9.6 using SSL wildcard server certificate and no client certificates

Solution 1:

The intermediate certificate must be in server.crt i am not sure if you need to add it to root.crt. Please refer to PostgreSQL Documentation

Edit:

I just created a script to generate all you need to setup SSL with full verification. Can you please run it and confirm if it works ?

#!/bin/bash

rm -rf /tmp/pg-ssl
mkdir -p /tmp/pg-ssl

openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=root.yourdomain.com"
chmod og-rwx root.key
openssl x509 -req -in root.csr -text -days 3650 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt


openssl req -new -nodes -text -out intermediate.csr -keyout intermediate.key -subj "/CN=intermediate.yourdomain.com"
chmod og-rwx intermediate.key
openssl x509 -req -in intermediate.csr -text -days 1825 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -CA root.crt -CAkey root.key -CAcreateserial -out intermediate.crt


openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=dbhost.yourdomain.com"
chmod og-rwx server.key
openssl x509 -req -in server.csr -text -days 365 -CA intermediate.crt -CAkey intermediate.key -CAcreateserial -out server.crt

cat server.crt intermediate.crt > bundle.crt 


echo "ssl = true"
echo "ssl_cert_file = '/tmp/pg-ssl/bundle.crt'"
echo "ssl_key_file = '/tmp/pg-ssl/server.key'"

echo "add server ip in hosts file <IP> dbhost.yourdomain.com"
echo "copy root.crt to client"
echo 'connect with psql "postgresql://[email protected]:5432/dev?ssl=true&sslmode=verify-full&sslrootcert=/tmp/pg-ssl/root.crt"'

Make sure to restart the server and copy root.crt to the client that psql can verify server identity. For testing purpose /etc/hosts file on the client must be modified to make CN valid from client perspective.

Solution 2:

On Windows, the default root.crt and root.crl are stored in %APPDATA%\postgresql (this thread pointed me in the right direction).

When I deleted these files, I was able to successfully connect to the remote server via psql without using any ssl parameters (defaults are to auto-negotiate ssl with sslmode=require):

C:\>"Program Files\PostgreSQL\9.6\bin\psql.exe" "postgresql://mydbuser@[REDACTED].org:5432/mydb"
Password:
psql (9.6.5, server 9.6.11)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
n4l_live=> \q

As expected, when I attempt to force sslmode=verify-ca or sslmode=verify-full, psql fails to connect:

C:\>"Program Files\PostgreSQL\9.6\bin\psql.exe" "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-ca"
psql: root certificate file "C:\Users\[USERNAME]\AppData\Roaming/postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

C:\>"Program Files\PostgreSQL\9.6\bin\psql.exe" "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-full"
psql: root certificate file "C:\Users\[USERNAME]\AppData\Roaming/postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

And further, when I attempt to connect via JDBC, I get the same error (because JDBC defaults to sslmode=verify-full):

org.postgresql.util.PSQLException: Could not open SSL root certificate file C:\Users\[USERNAME]\AppData\Roaming\postgresql\root.crt.
    at org.postgresql.ssl.LibPQFactory.<init>(LibPQFactory.java:120)
    at org.postgresql.core.SocketFactoryFactory.getSslSocketFactory(SocketFactoryFactory.java:61)
    at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:33)
    at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:435)
    at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:94)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
    at org.postgresql.Driver.makeConnection(Driver.java:454)
    at org.postgresql.Driver.connect(Driver.java:256)
    ...
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:89)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:541)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:763)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:463)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:209)
Caused by: java.io.FileNotFoundException: C:\Users\[USERNAME]\AppData\Roaming\postgresql\root.crt (The system cannot find the file specified)
    at java.io.FileInputStream.open0(Native Method)
    at java.io.FileInputStream.open(FileInputStream.java:195)
    at java.io.FileInputStream.<init>(FileInputStream.java:138)
    at java.io.FileInputStream.<init>(FileInputStream.java:93)
    at org.postgresql.ssl.LibPQFactory.<init>(LibPQFactory.java:117)
    ... 38 more

When I place only the top-level root certificate (or both top-level root certificates for Path #1 or Path #2) in C:\Users\[USERNAME]\AppData\Roaming\postgresql\root.crt, I am able to successfully connect with Java (no problems using the wildcard certificate for verify-full!):

Connecting with URL: jdbc:postgresql://[REDACTED].org:5432/mydb
PostgreSQL JDBC Driver 42.2.5   
Trying to establish a protocol version 3 connection to [REDACTED].org:5432
converting regular socket connection to ssl
Canonical host name for [REDACTED].org is [REDACTED].org
Server name validation pass for [REDACTED].org, subjectAltName *.[REDACTED].org

Likewise, when I do the same on my Linux psql client:

# cat certs/path_1/3_root_usertrust-selfsigned.crt > ~/.postgresql/root.crt
# psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-full"
Password: ********
psql (9.6.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
n4l_live=> \q

As a sanity check, if only the certificates for Path #1 are in server.crt, but I attempt to verify-full with the root for Path #2:

# cat certs/path_2/4_root_addtrustroot-selfsigned.crt > .postgresql/root.crt
# psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-full"
psql: SSL error: certificate verify failed

And then I also append the root certificate for Path #1:

# cat certs/path_1/3_root_usertrust-selfsigned.crt >> .postgresql/root.crt
# psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-full"
Password: ********
psql (9.6.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
n4l_live=> \q

My misconceptions in my question were that:

PostgreSQL on Windows uses the Windows Certificate Store (FALSE!)
The JDBC PostgreSQL driver uses the default Java keystore (FALSE!)

This was reinforced by the fact that if C:\Users\[USERNAME]\AppData\Roaming\postgresql\root.crt exists, there are no messages indicating where that file is (it was not even on my radar to look in that folder).

In order to add the additional CRLs, I needed to download and convert from DER to PEM:

wget http://crl.usertrust.com/AddTrustExternalCARoot.crl
openssl crl -inform DER -in AddTrustExternalCARoot.crl -outform PEM -out AddTrustExternalCARoot_CRL.pem

wget http://crl.usertrust.com/USERTrustRSACertificationAuthority.crl
openssl crl -inform DER -in USERTrustRSACertificationAuthority.crl -outform PEM -out USERTrustRSACertificationAuthority_CRL.pem

cat USERTrustRSACertificationAuthority_CRL.pem AddTrustExternalCARoot_CRL.pem > root.crl

But then I found that if I copy this root.crl (CRL for intermediate certificates) into ~/.postgresql, my client connections fail with the same error that I started with:

# cp ../data/root.crl ~/.postgresql
# psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-full"
psql: SSL error: certificate verify failed

I was ultimately unable to get CRL working for remote connections, so I deleted root.crl on the clients to simplify the configuration. I now have successful verify-full connections from both psql and Java. For a detailed write-up on CRLs, see this related question.

What I've learned:

The PostgreSQL JDBC driver does not require the intermediate certificates in C:\Users\[USERNAME]\AppData\Roaming\postgresql\root.crt, and will accept the root certificate of either Path #1 or Path #2 for verify-full.
Missing the C:\Users\[USERNAME]\AppData\Roaming\postgresql\root.crl (or ~/.postgresql/root.crl on Linux) is OK
If a root.crl is present on the client, it must contain all of the correct CRLs for each validation path allowed by the server.
If a root.crl is provided, but one or more root CA does not have an associated CRL Distribution Point, the connections may fail with a certificate verify failed message.

I found that (in my case) neither of the root CA certificates had a CRL associated with it, which may be triggering an OpenSSL bug:

# psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=verify-full"
psql: SSL error: certificate verify failed

The equivalent openssl command that confirms this bug is:

# openssl verify -crl_check -CAfile root.crt -CRLfile root.crl server.crt
server.crt: OU = Domain Control Validated, OU = PositiveSSL Wildcard, CN = *.[REDACTED].org
error 3 at 0 depth lookup:unable to get certificate CRL

And if the above bug is indeed responsible for this error, then the reason I was able to use root.crl with my previous Comodo certificate is that the root CA certificate had a CRL Distribution Point, so this bug was never triggered. In the short term, my workaround is to simply delete root.crl, which results in a working connection.

Errors when attempting to connect to PostgreSQL 9.6 using SSL wildcard server certificate and no client certificates

Solution 1:

Solution 2:

Related

Recent Posts