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

ssl ssl-certificate configuration postgresql ssl-certificate-errors

10,215

Solution 1

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.

Solution 2

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.

10,215

Author by

vallismortis

Software architect and full-stack software developer for a Michigan bioinformatics company.

Updated on September 18, 2022

Comments

vallismortis over 1 year

I have a PostgreSQL 9.6.11 database on Amazon Linux that has been configured with a 2048-bit SSL wildcard server certificate and password-based (no client certificates) remote connections since January 2012. After a recent certificate upgrade (Comodo, now Sectigo), I can no longer establish remote psql or JDBC connections to this database over SSL.

My goal is to be able to connect to this PostgreSQL database remotely via psql and also via JDBC.

Starting from the server key (which hasn't changed since I had remote access working), I have attempted to cover the full series of steps to verify that I have my keys, certificates, firewall and database set up correctly.

I must have missed something, since I'm unable to connect remotely via psql or JDBC.

What have I missed that could be causing these remote connections to fail?

Troubleshooting Steps

As user postgres:

# cd /var/lib/pgsql96/data

postgresql.conf

I had tried to limit the set of ciphers to attempt to force TLSv1.2 for all SSL connections. Since there was no difference in client behavior, I commented out ssl_ciphers and ssl_prefer_server_ciphers to allow the defaults.

ssl = on
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL:!SSLv2:!SSLv3:!TLSv1:!TLSv1.1'
#ssl_prefer_server_ciphers = on
ssl_cert_file = 'server.crt' # wildcard cert plus intermediate certs
ssl_key_file = 'server.key' # private key
#ssl_ca_file = 'root.crt' # commented out - do not require client certs
#ssl_crl_file = 'root.crl' # commented out - no client certificates

pg_hba.conf

This file was set up to allow only the public IP address of the localhost and the remote host I am testing. I don't want to require client certificates, only encryption with a required password.

hostssl       all     all     11.222.11.222/32      password # localhost
hostssl       all     all     34.84.31.82/32        password # remote host

I checked the certification paths via ssltest and found that there are two paths available (Path #1 and Path #2):

From the documentation on PostgreSQL 9.6 Secure TCP/IP Connections with SSL:

The first certificate in server.crt must be the server's certificate because it must match the server's private key. The certificates of "intermediate" certificate authorities can also be appended to the file. Doing this avoids the necessity of storing intermediate certificates on clients, assuming the root and intermediate certificates were created with v3_ca extensions. This allows easier expiration of intermediate certificates.

It is not necessary to add the root certificate to server.crt. Instead, clients must have the root certificate of the server's certificate chain.

Assembling and verifying the certificate chain for Path #1

# ls -l
-rw------- 1 postgres postgres 2313 Aug 15 00:26 1_wildcard_server.crt
-rw------- 1 postgres postgres 2167 Aug 15 00:27 2_intermediate_sectigo.crt
-rw------- 1 postgres postgres 2094 Aug 15 00:27 3_root_usertrust-selfsigned.crt

I checked the fingerprints of each individual certificate in Path #1 to confirm their identity:

# openssl x509 -in 1_wildcard_server.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=8C:69:06:8E:81:31:30:6E:DA:DD:C2:1C:38:83:73:67:97:3D:DB:37:78:B8:49:D7:7E:32:A8:3F:1F:8B:08:AB

# openssl x509 -in 2_intermediate_sectigo.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=7F:A4:FF:68:EC:04:A9:9D:75:28:D5:08:5F:94:90:7F:4D:1D:D1:C5:38:1B:AC:DC:83:2E:D5:C9:60:21:46:76

# openssl x509 -in 3_root_usertrust-selfsigned.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=E7:93:C9:B0:2F:D8:AA:13:E2:1C:31:22:8A:CC:B0:81:19:64:3B:74:9C:89:89:64:B1:74:6D:46:C3:D4:CB:D2

And also examined the text versions of the certificates to confirm that the intermediate and root certificates have the v3_ca extension (the wildcard server certificate does not have this extension):

# openssl x509 -in 1_wildcard_server.crt -text
...
X509v3 Basic Constraints: critical
    CA:FALSE
...

# openssl x509 -in 2_intermediate_sectigo.crt -text
...
X509v3 Basic Constraints: critical
    CA:TRUE
...

# openssl x509 -in 3_root_usertrust-selfsigned.crt -text
...
X509v3 Basic Constraints: critical
    CA:TRUE
...

Verify the certificate chain for Path #1 with OpenSSL:

# openssl verify -verbose -CAfile 3_root_usertrust-selfsigned.crt -untrusted 2_intermediate_sectigo.crt 1_wildcard_server.crt
1_wildcard_server.crt: OK

Create the bundled server and intermediate certificate for Path #1:

# cat 1_wildcard_server.crt > server.crt
# cat 2_intermediate_sectigo.crt >> server.crt

Create the bundled intermediate and root certificates (those with the v3_ca extension) for Path #1 (although this would only be needed when requiring client certificates):

# cat 2_intermediate_sectigo.crt > root.crt
# cat 3_root_usertrust-selfsigned.crt >> root.crt

Assembling and verifying the certificate chain for Path #2

# ls -l
-rw------- 1 postgres postgres 2313 Aug 15 00:26 1_wildcard_server.crt
-rw------- 1 postgres postgres 2167 Aug 15 00:27 2_intermediate_sectigo.crt
-rw------- 1 postgres postgres 1956 Aug 15 00:35 3_intermediate_usertrust.crt
-rw------- 1 postgres postgres 1521 Aug 15 00:27 4_root_addtrustroot-selfsigned.crt

I checked the fingerprints of each individual certificate in Path #2 to confirm their identity:

# openssl x509 -in 1_wildcard_server.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=8C:69:06:8E:81:31:30:6E:DA:DD:C2:1C:38:83:73:67:97:3D:DB:37:78:B8:49:D7:7E:32:A8:3F:1F:8B:08:AB

# openssl x509 -in 2_intermediate_sectigo.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=7F:A4:FF:68:EC:04:A9:9D:75:28:D5:08:5F:94:90:7F:4D:1D:D1:C5:38:1B:AC:DC:83:2E:D5:C9:60:21:46:76

# openssl x509 -in 3_intermediate_usertrust.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=1A:51:74:98:0A:29:4A:52:8A:11:07:26:D5:85:56:50:26:6C:48:D9:88:3B:EA:69:2B:67:B6:D7:26:DA:98:C5

# openssl x509 -in 4_root_addtrustroot-selfsigned.crt -noout -sha256 -fingerprint
SHA256 Fingerprint=68:7F:A4:51:38:22:78:FF:F0:C8:B1:1F:8D:43:D5:76:67:1C:6E:B2:BC:EA:B4:13:FB:83:D9:65:D0:6D:2F:F2

And also examined the text versions of the certificates to confirm that the intermediate and root certificates have the v3_ca extension (the wildcard server certificate does not have this extension):

# openssl x509 -in 1_wildcard_server.crt -text
...
X509v3 Basic Constraints: critical
    CA:FALSE
...

# openssl x509 -in 2_intermediate_sectigo.crt -text
...
X509v3 Basic Constraints: critical
    CA:TRUE
...

# openssl x509 -in 3_intermediate_usertrust.crt -text
...
X509v3 Basic Constraints: critical
    CA:TRUE
...

# openssl x509 -in 4_root_addtrustroot-selfsigned.crt -text
...
X509v3 Basic Constraints: critical
    CA:TRUE
...

Verify the certificate chain for Path #2 with OpenSSL (from this article):

# openssl verify -verbose -CAfile 4_root_addtrustroot-selfsigned.crt -untrusted 3_intermediate_usertrust.crt 2_intermediate_sectigo.crt
2_intermediate_sectigo.crt: OK

Create the bundled server and intermediate certificate for Path #2:

# cat 1_wildcard_server.crt > server.crt
# cat 2_intermediate_sectigo.crt >> server.crt
# cat 3_intermediate_usertrust.crt >> server.crt

Create the bundled intermediate and root certificates (those with the v3_ca extension) for Path #2 (although this would only be needed when requiring client certificates):

# cat 2_intermediate_sectigo.crt > root.crt
# cat 3_intermediate_usertrust.crt >> root.crt
# cat 4_root_addtrustroot-selfsigned.crt >> root.crt

Verified key and certificate permissions (as in this question).

# ls -l *.key *.crt *.crl-bash-4.2$ ls -l *.key *.crt *.crl
-rw-r----- 1 postgres postgres  963 Aug 14 21:12 root.crl
-rw-r--r-- 1 postgres postgres 1521 Aug 15 01:27 root.crt
-rw-r--r-- 1 postgres postgres 6436 Aug 15 01:27 server.crt
-rw------- 1 postgres postgres 1679 May 28 19:33 server.key

Confirmed that the server key is ok, following instructions from Comodo.

# openssl version
OpenSSL 1.0.2k-fips  26 Jan 2017
# openssl rsa -check -noout -in server.key
RSA key ok

Confirmed that the certificate and private key moduli are identical.

# openssl rsa -modulus -noout -in server.key
Modulus=[REDACTED]

# openssl x509 -modulus -noout -in server.crt
Modulus=[REDACTED]

Tested the CRL and verified the issuer:

# openssl crl -in root.crl -text
Certificate Revocation List (CRL):
        Version 2 (0x1)
    Signature Algorithm: sha1WithRSAEncryption
        Issuer: /C=SE/O=AddTrust AB/OU=AddTrust External TTP Network/CN=AddTrust External CA Root
        Last Update: May 28 00:12:38 2019 GMT
        Next Update: Jun  1 00:12:38 2019 GMT
        CRL extensions:
            X509v3 Authority Key Identifier:
                keyid:AD:BD:98:7A:34:B4:26:F7:FA:C4:26:54:EF:03:BD:E0:24:CB:54:1A
            X509v3 CRL Number:
                5275
Revoked Certificates:
    Serial Number: 537B76564F297F14DC6943E922AD2C79
        Revocation Date: Dec 14 15:58:30 2015 GMT
    Serial Number: 46EAF096054CC5E3FA65EA6E9F42C664
        Revocation Date: Dec 14 15:58:30 2015 GMT
    Serial Number: 3ACDAB9C759886BCAF74E5DF81A9F4E8
        Revocation Date: Dec 14 15:58:30 2015 GMT
    Serial Number: 79174AA9141736FE15A7CA9F2CFF4588
        Revocation Date: Apr 30 20:03:54 2018 GMT
    Serial Number: 74C18753F7EEB4EA238D8416B5AC7646
        Revocation Date: Oct  9 09:11:57 2018 GMT
    Signature Algorithm: sha1WithRSAEncryption
         38:3a:7d:3e:ee:be:48:e7:93:c3:91:0a:c3:47:46:11:87:83:
[TRIMMED]
         5f:16:1a:38
-----BEGIN X509 CRL-----
MIICnTCCAYUCAQEwDQYJKoZIhvcNAQEFBQAwbzELMAkGA1UEBhMCU0UxFDASBgNV
[TRIMMED]
iEx7Li7fLtVPxbIU4aqaKU+15QEE37eJWRccBnuhqJqEDM+ML+k67Hj1yeLaXxYa
OA==
-----END X509 CRL-----

Started PostgreSQL service.

# service postgresql96 start
Starting postgresql96 service:                             [  OK  ]

Verified there were no errors in the log files.

# cat ../pgstartup.log
LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".

# cat pg_log/postgresql-Wed.log
LOG:  database system was shut down at 2019-08-14 15:01:03 UTC
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

Successfully connected to database via SSL from localhost with sslmode require and with verify-full (using FQDN).

# psql "postgresql://mydbuser@localhost:5432/mydb?ssl=true&sslmode=require"
psql (9.6.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
mydb=> \q

# 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.
mydb=> \q

Retrieved and visually verified the SSL certificate chain remotely via Java using a combination of this method, this method, this method and this method. This confirms that port 5432 is open for this host on the firewall and that the certificate and chain are retrievable via JDBC.

Supported Protocols: 5
 SSLv2Hello
 SSLv3
 TLSv1
 TLSv1.1
 TLSv1.2
Enabled Protocols: 3
 TLSv1
 TLSv1.1
 TLSv1.2
Enabled Cipher suites: 43
 TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384
 TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384
 TLS_RSA_WITH_AES_256_CBC_SHA256
 TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384
 TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384
 TLS_DHE_RSA_WITH_AES_256_CBC_SHA256
 TLS_DHE_DSS_WITH_AES_256_CBC_SHA256
 TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA
 TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA
 TLS_RSA_WITH_AES_256_CBC_SHA
 TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA
 TLS_ECDH_RSA_WITH_AES_256_CBC_SHA
 TLS_DHE_RSA_WITH_AES_256_CBC_SHA
 TLS_DHE_DSS_WITH_AES_256_CBC_SHA
 TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256
 TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256
 TLS_RSA_WITH_AES_128_CBC_SHA256
 TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256
 TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256
 TLS_DHE_RSA_WITH_AES_128_CBC_SHA256
 TLS_DHE_DSS_WITH_AES_128_CBC_SHA256
 TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA
 TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA
 TLS_RSA_WITH_AES_128_CBC_SHA
 TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA
 TLS_ECDH_RSA_WITH_AES_128_CBC_SHA
 TLS_DHE_RSA_WITH_AES_128_CBC_SHA
 TLS_DHE_DSS_WITH_AES_128_CBC_SHA
 TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384
 TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
 TLS_RSA_WITH_AES_256_GCM_SHA384
 TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384
 TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384
 TLS_DHE_RSA_WITH_AES_256_GCM_SHA384
 TLS_DHE_DSS_WITH_AES_256_GCM_SHA384
 TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
 TLS_RSA_WITH_AES_128_GCM_SHA256
 TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256
 TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256
 TLS_DHE_RSA_WITH_AES_128_GCM_SHA256
 TLS_DHE_DSS_WITH_AES_128_GCM_SHA256
 TLS_EMPTY_RENEGOTIATION_INFO_SCSV
Cert 1 (active):
    Thumbprint SHA1   : B5:2D:43:A8:0F:C6:C3:39:1F:2D:BB:9C:30:A5:4B:8D:DF:5F:9B:F8
    Fingerprint SHA256: 8c69068e8131306edaddc21c38837367973ddb3778b849d77e32a83f1f8b08ab
    Subject: CN=*.[REDACTED].org,OU=PositiveSSL Wildcard,OU=Domain Control Validated
    Issuer: CN=Sectigo RSA Domain Validation Secure Server CA,O=Sectigo Limited,L=Salford,ST=Greater Manchester,C=GB
Cert 2 (active):
    Thumbprint SHA1   : 33:E4:E8:08:07:20:4C:2B:61:82:A3:A1:4B:59:1A:CD:25:B5:F0:DB
    Fingerprint SHA256: 7fa4ff68ec04a99d7528d5085f94907f4d1dd1c5381bacdc832ed5c960214676
    Subject: CN=Sectigo RSA Domain Validation Secure Server CA,O=Sectigo Limited,L=Salford,ST=Greater Manchester,C=GB
    Issuer: CN=USERTrust RSA Certification Authority,O=The USERTRUST Network,L=Jersey City,ST=New Jersey,C=US
Cert 3 (active):
    Thumbprint SHA1   : 33:E4:E8:08:07:20:4C:2B:61:82:A3:A1:4B:59:1A:CD:25:B5:F0:DB
    Fingerprint SHA256: 7fa4ff68ec04a99d7528d5085f94907f4d1dd1c5381bacdc832ed5c960214676
    Subject: CN=Sectigo RSA Domain Validation Secure Server CA,O=Sectigo Limited,L=Salford,ST=Greater Manchester,C=GB
    Issuer: CN=USERTrust RSA Certification Authority,O=The USERTRUST Network,L=Jersey City,ST=New Jersey,C=US
Cert 4 (active):
    Thumbprint SHA1   : EA:B0:40:68:9A:0D:80:5B:5D:6F:D6:54:FC:16:8C:FF:00:B7:8B:E3
    Fingerprint SHA256: 1a5174980a294a528a110726d5855650266c48d9883bea692b67b6d726da98c5
    Subject: CN=USERTrust RSA Certification Authority,O=The USERTRUST Network,L=Jersey City,ST=New Jersey,C=US
    Issuer: CN=AddTrust External CA Root,OU=AddTrust External TTP Network,O=AddTrust AB,C=SE

I confirmed that the above root certificates (AddTrust External CA Root) are both in the default Java keystore as recommended here (and also confirmed that they are in the Windows keystore by default as well):

C:\Windows\System32>"C:\Program Files\Java\jdk1.8.0_212\jre\bin\keytool.exe" -keystore "C:\Program Files\Java\jdk1.8.0_212\jre\lib\security\cacerts" -storepass
changeit -list
Keystore type: jks
Keystore provider: SUN

Your keystore contains 95 entries
....
usertrustrsaca [jdk], Aug 25, 2016, trustedCertEntry,
Certificate fingerprint (SHA1): 2B:8F:1B:57:33:0D:BB:A2:D0:7A:6C:51:F7:0E:E9:0D:DA:B9:AD:8E
....
addtrustexternalca [jdk], Aug 25, 2016, trustedCertEntry,
Certificate fingerprint (SHA1): 02:FA:F3:E2:91:43:54:68:60:78:57:69:4D:F5:E4:5B:68:85:18:68
....

Attempted to connect to PostgreSQL via openssl from remote host (as in this question).

# openssl version
OpenSSL 1.1.0h  27 Mar 2018
# openssl s_client -connect [REDACTED].org:5432 -state -msg -showcerts -debug
CONNECTED(00000003)
SSL_connect:before/connect initialization
write to 0x2070760 [0x20fe520] (289 bytes => 289 (0x121))
0000 - 16 03 01 01 1c 01 00 01-18 03 03 0c 53 44 0c a3   ............SD..
[TRIMMED]
0110 - 03 01 03 02 03 03 02 01-02 02 02 03 00 0f 00 01   ................
0120 - 01                                                .
>>> TLS 1.2  [length 0005]
    16 03 01 01 1c
>>> TLS 1.2 Handshake [length 011c], ClientHello
    01 00 01 18 03 03 0c 53 44 0c a3 e2 21 36 f2 b0
[TRIMMED]
    01 05 02 05 03 04 01 04 02 04 03 03 01 03 02 03
    03 02 01 02 02 02 03 00 0f 00 01 01
SSL_connect:SSLv2/v3 write client hello A
read from 0x2070760 [0x2103a80] (7 bytes => 0 (0x0))
139650021263184:error:140790E5:SSL routines:ssl23_write:ssl handshake failure:s23_lib.c:177:
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 0 bytes and written 289 bytes
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
    Protocol  : TLSv1.2
    Cipher    : 0000
    Session-ID:
    Session-ID-ctx:
    Master-Key:
    Key-Arg   : None
    Krb5 Principal: None
    PSK identity: None
    PSK identity hint: None
    Start Time: 1565797370
    Timeout   : 300 (sec)
    Verify return code: 0 (ok)
---

# tail pg_log/postgresql-Wed.log
LOG:  invalid length of startup packet

Attempted to connect remotely via Windows psql client (9.6.5).

C:\Program Files\PostgreSQL\9.6\bin>psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=require"
psql: SSL error: certificate verify failed

For some reason, the psql client is sending an TLSv1 alert, "unknown ca":

# tail pg_log/postgresql-Wed.log
LOG:  could not accept SSL connection: tlsv1 [alert][15] unknown ca

Additionally, if I specify the root certificate:

C:\Program Files\PostgreSQL\9.6\bin>psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=require&sslrootcert=root.crt"
psql: SSL error: certificate verify failed

or if I leave that parameter blank:

C:\Program Files\PostgreSQL\9.6\bin>psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=require&sslrootcert="
psql: SSL error: certificate verify failed

However, if I specify a non-existent file for that parameter, I am able to connect successfully (with sslmode=require):

C:\Program Files\PostgreSQL\9.6\bin>psql "postgresql://mydbuser@[REDACTED].org:5432/mydb?ssl=true&sslmode=require&sslrootcert=x"
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

From the specification:

unknown_ca

  A valid certificate chain or partial chain was received, but the
  certificate was not accepted because the CA certificate could not
  be located or couldn't be matched with a known, trusted CA.  This
  message is always fatal.

Attempt to connect via Java client with postgresql-42.2.5.jar.

javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
  at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)
  at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1946)
  at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:316)
  at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:310)
  at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1639)
  at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:223)
  at sun.security.ssl.Handshaker.processLoop(Handshaker.java:1037)
  at sun.security.ssl.Handshaker.process_record(Handshaker.java:965)
  at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1064)
  at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1367)
  at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1395)
  at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1379)
  at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:40)
  ... 36 more
Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
  at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:397)
  at sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:302)
  at sun.security.validator.Validator.validate(Validator.java:262)
  at sun.security.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:330)
  at sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:237)
  at sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:132)
  at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1621)
  ... 44 more
Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
  at sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141)
  at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:126)
  at java.security.cert.CertPathBuilder.build(CertPathBuilder.java:280)
  at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:392)
  ... 50 more

For some reason, the Java client is producing an SSLv3 alert, "certificate unknown", even though it is not one of the enabled protocols:

# tail pg_log/postgresql-Wed.log
LOG:  could not accept SSL connection: sslv3 alert certificate unknown

From the specification:

certificate_unknown

  Some other (unspecified) issue arose in processing the
  certificate, rendering it unacceptable.

The PostgreSQL JDBC driver documentation indicates that the Java client (by default) will attempt to use verify-full for the server certificate, which may be the reason a different error is produced here than with the psql client:

The simplest being ssl=true, passing this into the driver will cause the driver to validate both the SSL certificate and verify the hostname (same as verify-full). Note this is different than libpq which defaults to a non-validating SSL connection.

The above failures (and the success when specifying a non-existent root certificate with sslmode=require) seem to point to some problem with verifying the CA for the certificate.

Note: I used the script provided in this answer to generate new certificates and performed the above tests again, with identical results.

vallismortis over 4 years

I used this script to generate new certificates, and found that I was getting the same results. However, I found (accidentally) that providing a non-existent file for sslrootcert (e.g., sslrootcert=x) with sslmode=require enables me to connect remotely. However, providing the root.crt file for that parameter still results in psql: SSL error: certificate verify failed (even when attempting to connect locally). I believe this eliminates the remote clients as the source of the problem. I believe this is narrowing in the source of the problem, but I'm not quite there yet.
vallismortis over 4 years

I sincerely appreciate the time you took to put this script together, and although I was able to find a solution that uses my certificate chain, your comments and suggestions regarding the sslrootcert parameter were what led me in the right direction.