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 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@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 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@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 —
smime.p7s
Description: S/MIME cryptographic signature
