[HACKERS] postgresql v9.5 and SSL: LOG: could not accept SSL connection: tlsv1alert iso-8859-1 ca - Mailing list pgsql-hackers

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]@[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@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
theresome kind of debug option that will tell me a) what certs/keys/ca certs/crls have been picked up, and b) whether
thesehave been validated by postgresql as functional? Obviously I can (and have) run the certs through openssl, but
thattells 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@sql02:~$ /usr/bin/pg_isready -t 0 -d 'postgresql://sql01:5432?user=repmgr&sslmode=verify-ca'
sql01:5432 - no response

postgres@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
specificallyfailed 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
value0xc00a       Unknown value 0xa5       Unknown value 0xa3       Unknown value 0xa1       Unknown value 0x9f
Unknownvalue 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
value0x85       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
Unknownvalue 0xc027       Unknown value 0xc023       Unknown value 0xc013       Unknown value 0xc009       Unknown
value0xa4       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
Unknownvalue 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
Unknownvalue 0x9c       Unknown value 0x3c       TLS_RSA_WITH_AES_128_CBC_SHA       Unknown value 0x96       Unknown
value0x41       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@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
clientare both at their defaults. 

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

Regards,
Graham
—