Errors when attempting to connect to PostgreSQL 9.6 with SSL wildcard certificate

by vallismortis   Last Updated August 14, 2019 17:00 PM

I have a PostgreSQL 9.6.11 database on Amazon Linux that has been set up with SSL 2048 bit certificates for remote connections since 2015, and after a recent certificate upgrade (Comodo, now Sectigo), I can no longer connect remotely via SSL.

My goal is to be able to connect to the remote PostgreSQL 9.6 instance 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 certificates and keys set up correctly.

As user postgres:

# cd /var/lib/pgsql96/data

postgresql.conf

This set of ciphers was set up to attempt to force TLSv1.2 for all SSL connections. I've also tried defaults (commented out ssl_ciphers and ssl_prefer_server_ciphers with no difference in results).

ssl = on
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL:!SSLv2:!SSLv3:!TLSv1:!TLSv1.1'
ssl_prefer_server_ciphers = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
ssl_crl_file = 'root.crl'

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

Verified key and certificate permissions.

# ls -l *.key *.crt *.crl
-rw-r----- 1 postgres postgres  963 May 28 20:11 root.crl
-rw-r----- 1 postgres postgres 5644 Aug 14 14:31 root.crt
-rw------- 1 postgres postgres 2313 Aug 14 14:23 server.crt
-r-------- 1 postgres postgres 1679 May 28 19:33 server.key

And for testing verify-full from localhost:

# cp -a root.crt ~/.postgresql/root.crt

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

# 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]

Verified the server certificate against the root certificates (as per this question).

# openssl verify -verbose -CAfile root.crt server.crt
server.crt: OK

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://[email protected]: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://[email protected][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 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.

Cert 1 (active):
    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):
    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):
    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
Cert 4 (active):
    Subject: CN=AddTrust External CA Root,OU=AddTrust External TTP Network,O=AddTrust AB,C=SE
    Issuer: CN=AddTrust External CA Root,OU=AddTrust External TTP Network,O=AddTrust AB,C=SE

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

# 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://[email protected][REDACTED].org:5432/mydb?ssl=true&sslmode=require"
psql: SSL error: certificate verify failed

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

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

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

I must have missed something, since I'm unable to connect remotely via psql or JDBC. I've run out of ideas at this point.

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



Related Questions


openssl keeps creating v1 certificate instead of v3

Updated August 13, 2019 15:00 PM


VPNGate Connections - OpenVPN Certificate Errors

Updated May 09, 2017 17:00 PM