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

Question

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.

Answer 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.

Answer 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.