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
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
pgbouncer.ini from my second server.
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)
hostssl all all 10.10.5.0/24 cert
; ; 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!