Fix PostgreSQL Client Certificate TLS Error

It looks like OpenSSL's s_client tool added Postgres support using the -starttls in 1.1.1, so you can now use the full power of OpenSSL's command line tools without additional helper scripts:

openssl s_client -starttls postgres -connect my.postgres.host:5432 # etc...

References:

• Git commit
• s_client manpage

I figured it out... I was (partially?) guilty of trying to use too many new features in pgbouncer 1.7.

There are the TLS/SSL settings & then there is the HBA access controls. (TLS/SSL doesn't need HBA access controls & vice-versa to work). Also, since pgbouncer & the database are on the same box, there is no need for the extra overhead of TLS/SSL between pgbouncer & the database.

Simplifying to just use more commonly used user authentication settings proved to be the fix.

First, postgresql.conf & pg_hba.conf were left untouched as show above.

pgbouncer.ini, however, is this:

;
; pgbouncer configuration
;
[databases]
mydatabase = host=localhost port=5432 dbname=mydatabase
;
[pgbouncer]
listen_port = 6543
listen_addr = *
admin_users = lalligood, postgres
auth_type = cert
auth_file = pgbouncer/users.txt
logfile = /var/lib/pgsql/pgbouncer.log
pidfile = /var/lib/pgsql/pgbouncer.pid
ignore_startup_parameters = application_name
server_reset_query = DISCARD ALL;
pool_mode = session
max_client_conn = 1000
default_pool_size = 300
log_pooler_errors = 0
; Improve compatibility with Java/JDBC connections
ignore_startup_parameters = extra_float_digits
; TLS settings
client_tls_sslmode = verify-full
client_tls_key_file = server.key
client_tls_cert_file = server.crt
client_tls_ca_file = root.crt

So the specific changes are auth_type = cert & auth_file = pgbouncer/users.txt (changing/removing the HBA references) & stripping out the 4 server_tls_... lines at the end.

The users authenticate to both pgbouncer and the postgres database using the SSL cert.

This also means that I have to put together a list of users that will be going through pgbouncer in ./pgbouncer/users.txt. The format should be just like this (for each user):

"lalligood" ""

since pgbouncer will not be verifying any connections based on a password.

So what all this means is that TLS/SSL authentication/connectivity through pgbouncer works. But it also leaves me with the feeling that auth_type = hba / auth_hba_file = pg_hba.conf is suspect at best; not working properly at worst.