Postgresql – pgbouncer 1.7 with TLS/SSL client and server connections

pgbouncerpostgresqlpostgresql-9.4ssl

With settings borrowed from a similar server running Postgres 9.4.1 & pgbouncer 1.6.1, I have multiple users connecting to the database through pgbouncer on port 6543. I also have a second server running PostgreSQL 9.4.5 where I have verified that all users are only able to connect directly to the databases (on port 5432) using TLS/SSL set to verify-full.

However, I need to create an environment combining these configurations: one where all users connect to the database via TLS/SSL with connection pooling through pgbouncer. That means I need to use the new TLS/SSL functionality in the recently released (as of Dec 18, 2015) pgbouncer 1.7, but aside from documentation of the new TLS parameters, I have not found any examples available demonstrating the new functionality nor had any success on my own establishing a valid connection through pgbouncer using TLS/SSL on my second server.

I have included pertinent excerpts from the postgresql.conf, pg_hba.conf, & pgbouncer.ini from my second server.

postgresql.conf:

ssl = on                                # (change requires restart)
ssl_cert_file = 'server.crt'            # (change requires restart)
ssl_key_file = 'server.key'             # (change requires restart)
ssl_ca_file = 'root.crt'                        # (change requires restart)

pg_hba.conf:

hostssl    all             all             10.10.5.0/24            cert

pgbouncer.ini:

;
; pgbouncer configuration
;
[databases]
mydatabase = host=localhost port=5432 dbname=mydatabase
;
[pgbouncer]
listen_port = 6543
listen_addr = *
admin_users = lalligood, postgres
logfile = /tmp/pgbouncer.log
pidfile = /tmp/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
; USER AUTHENTICATION (old way commented out with new lines below)
;auth_type = md5
;auth_file = pgbouncer/users.txt
auth_type = hba
auth_hba_file = pg_hba.conf
; TLS SETTINGS (NEW STUFF!)
client_tls_sslmode = verify-full
client_tls_key_file = server.key
client_tls_cert_file = server.crt
client_tls_ca_file = root.crt
server_tls_sslmode = verify-full
server_tls_key_file = /tmp/pgb_user.key
server_tls_cert_file = /tmp/pgb_user.crt
server_tls_ca_file = root.crt

pgbouncer starts, however, when I attempt to connect as any user but let's say I want to be user 'lalligood', I get the following error:

ERROR: no such user: lalligood

pgbouncer.log contains the following line for each attempt:

2016-01-13 16:00:36.971 2144 LOG C-0xcad410: 
(nodb)/(nouser)@10.10.5.194:54848 closing because: No such user: 
lalligood (age=0)

I can provide more information if necessary. If anyone has any advice/suggestions on what I might be overlooking to get this working, I greatly appreciate the help!

Best Answer

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.

Related Question