postgresql v9.5 and SSL: LOG: could not accept SSL connection: tlsv1 alert unknown ca

Hi all,

I have a working postgresql v9.3 installation running on out-of-the-box Ubuntu Trusty, and it works fine. The job at hand: replace the server with postgresql v9.5 on out-of-the-box Ubuntu Xenial, but this does not work fine.

I am getting the problem described on this page: http://www.pontifier.com/?p=23

2017-11-08 22:43:39 UTC [2553-1] [unknown](at)[unknown] LOG: could not accept SSL connection: tlsv1 alert unknown ca

To start with, the certs on the postgresql server validate without a problem, they are signed with SHA265:

root(at)sql01:/var/lib/postgresql/9.5/main# openssl verify -CAfile root.crt server.crt
server.crt: OK

The server.crt contains a cert signed by two intermediates, in turn signed by the root.

The postgresql server has an ssl configuration as follows:

ssl = true # (change requires restart)
ssl_cert_file = '/var/lib/postgresql/9.5/main/server.crt' # (change requires restart)
ssl_key_file = '/var/lib/postgresql/9.5/main/server.key' # (change requires restart)
ssl_ca_file = '/var/lib/postgresql/9.5/main/root.crt'
ssl_crl_file = '/var/lib/postgresql/9.5/main/root.crl'

If I place bogus values in ssl_cert_file postgresql complains as expected. If I place what I believe to be valid values, postgresql is silent on the issue in the log files.

First question - apart from the quoted message in the logfile, the logfile is completely silent on the state of SSL. Is there some kind of debug option that will tell me a) what certs/keys/ca certs/crls have been picked up, and b) whether these have been validated by postgresql as functional? Obviously I can (and have) run the certs through openssl, but that tells me openssl is happy, not that postgresql is happy.

Digging deeper, I’m trying the pg_isready tool to test if the server is ready. Unfortunately this gives inconsistent results:

postgres(at)sql02:~$ /usr/bin/pg_isready -t 0 -d 'postgresql://sql01:5432?user=repmgr&sslmode=verify-ca'
sql01:5432 - no response

postgres(at)sql02:~$ /usr/bin/psql -d 'postgresql://sql01:5432?user=repmgr&sslmode=verify-ca'
psql: SSL error: certificate verify failed

In the pg_isready case, the error is discarded and replaced with the inaccurate message “no response”. In the psql case, the error is too vague to be useful - it tells us a certificate verification failed, but didn’t tell us what specifically failed about the verification.

Sniffing the connection with ssldump gives us the following:

New TCP connection #8: 172.29.231.43(60178) <-> 172.29.228.240(5432)
8 1 0.0039 (0.0039) C>S Handshake
ClientHello
Version 3.3
cipher suites
Unknown value 0xc030
Unknown value 0xc02c
Unknown value 0xc028
Unknown value 0xc024
Unknown value 0xc014
Unknown value 0xc00a
Unknown value 0xa5
Unknown value 0xa3
Unknown value 0xa1
Unknown value 0x9f
Unknown value 0x6b
Unknown value 0x6a
Unknown value 0x69
Unknown value 0x68
TLS_DHE_RSA_WITH_AES_256_CBC_SHA
TLS_DHE_DSS_WITH_AES_256_CBC_SHA
TLS_DH_RSA_WITH_AES_256_CBC_SHA
TLS_DH_DSS_WITH_AES_256_CBC_SHA
Unknown value 0x88
Unknown value 0x87
Unknown value 0x86
Unknown value 0x85
Unknown value 0xc032
Unknown value 0xc02e
Unknown value 0xc02a
Unknown value 0xc026
Unknown value 0xc00f
Unknown value 0xc005
Unknown value 0x9d
Unknown value 0x3d
TLS_RSA_WITH_AES_256_CBC_SHA
Unknown value 0x84
Unknown value 0xc02f
Unknown value 0xc02b
Unknown value 0xc027
Unknown value 0xc023
Unknown value 0xc013
Unknown value 0xc009
Unknown value 0xa4
Unknown value 0xa2
Unknown value 0xa0
Unknown value 0x9e
TLS_DHE_DSS_WITH_NULL_SHA
Unknown value 0x40
Unknown value 0x3f
Unknown value 0x3e
TLS_DHE_RSA_WITH_AES_128_CBC_SHA
TLS_DHE_DSS_WITH_AES_128_CBC_SHA
TLS_DH_RSA_WITH_AES_128_CBC_SHA
TLS_DH_DSS_WITH_AES_128_CBC_SHA
Unknown value 0x9a
Unknown value 0x99
Unknown value 0x98
Unknown value 0x97
Unknown value 0x45
Unknown value 0x44
Unknown value 0x43
Unknown value 0x42
Unknown value 0xc031
Unknown value 0xc02d
Unknown value 0xc029
Unknown value 0xc025
Unknown value 0xc00e
Unknown value 0xc004
Unknown value 0x9c
Unknown value 0x3c
TLS_RSA_WITH_AES_128_CBC_SHA
Unknown value 0x96
Unknown value 0x41
Unknown value 0xc011
Unknown value 0xc007
Unknown value 0xc00c
Unknown value 0xc002
TLS_RSA_WITH_RC4_128_SHA
TLS_RSA_WITH_RC4_128_MD5
Unknown value 0xc012
Unknown value 0xc008
TLS_DHE_RSA_WITH_3DES_EDE_CBC_SHA
TLS_DHE_DSS_WITH_3DES_EDE_CBC_SHA
TLS_DH_RSA_WITH_3DES_EDE_CBC_SHA
TLS_DH_DSS_WITH_3DES_EDE_CBC_SHA
Unknown value 0xc00d
Unknown value 0xc003
TLS_RSA_WITH_3DES_EDE_CBC_SHA
Unknown value 0xff
compression methods
NULL
8 2 0.0057 (0.0017) S>C Handshake
ServerHello
Version 3.3
session_id[0]=

cipherSuite Unknown value 0xc030
compressionMethod NULL
8 3 0.0057 (0.0000) S>C Handshake
Certificate
8 4 0.0057 (0.0000) S>C Handshake
ServerKeyExchange
8 5 0.0057 (0.0000) S>C Handshake
CertificateRequest
certificate_types rsa_sign
certificate_types dss_sign
certificate_types unknown value
Not enough data. Found 163 bytes (expecting 32767)
ServerHelloDone
8 6 0.0062 (0.0004) C>S Alert
level fatal
value unknown_ca
8 0.0063 (0.0000) C>S TCP RST

Running psql through strace reveals that all certificate files are being read successfully:

open("/var/lib/postgresql/.postgresql/root.crt", O_RDONLY) = 5
open("/var/lib/postgresql/.postgresql/root.crl", O_RDONLY) = 5
open("/var/lib/postgresql/.postgresql/postgresql.crt", O_RDONLY) = 5
open("/var/lib/postgresql/.postgresql/postgresql.key", O_RDONLY) = 5

Openssl does this:

postgres(at)sql02:~$ openssl s_client -CAfile .postgresql/root.crt -key .postgresql/postgresql.key -cert .postgresql/postgresql.crt -connect sql01:5432
CONNECTED(00000003)
140691649681048: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 305 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
PSK identity: None
PSK identity hint: None
SRP username: None
Start Time: 1510184399
Timeout : 300 (sec)
Verify return code: 0 (ok)
—

The openssl seems to suggest something to do with ciphers - 0000 - but the ciphers on the server and the ciphers on the client are both at their defaults.

Does anyone have any experience with postgresql and SSL on Ubuntu xenial? Does this work at all?

Regards,
Graham
—