pgBouncer – How to Set Up SSL Connection

pgbouncerpostgresqlssl

I have three Servers, two of whom are running postgres 9.5.3 and the third runs pgbouncer 1.7.2.

i can access one of the two postgresql instance through pgbouncer and it all works perfect. the other postgresql Servers, lets call it P1 is not under my control and i have to connect using ssl so it got and therefor the following line was added to this Servers pga_hba.conf

hostssl    all       all             ###.###.###.###/32               md5

I can connect to P1 from pgbouncer server with psql with no Problem.
But when i connect via pgbouncer to P1 i get this error message

psql: ERROR:  no pg_hba.conf entry for host "XXX.XXX.XXX.XXX", user "myuser", database "mydb", SSL off

here is my pgbouncer.ini

[databases]
my_database = host=xxx.xxx.xxx.xxxx port=5432 dbname=mydb user=myuser password=mypassword client_encoding='UTF8'

[pgbouncer]
listen_addr = *
listen_port = 5432
auth_type = md5 

auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

admin_users = rms_admin 

pool_mode = session

server_reset_query = DISCARD ALL
server_check_query = select 1

default_pool_size = 40

stats_period=300

log_pooler_errors = 1

i've tried add sslmode=require on the connection string to my_database in combination with ignore_startup_parameters but that led to the following error in the pgbouncer log .

ERROR skipping database my_database because of unknown parameter in connstring: sslmode

how can i make pgbouncer build up an ssl Connection?

Best Answer

TLS between pgbouncer and server is not enabled through the connect string, but with server_tls_sslmode, which is disabled by default.

It should be set to at least prefer, and also some of the other server_tls_* parameters might be needed to, depending on the TLS configuration at the other end.

See https://pgbouncer.github.io/config.html#tls-settings for all the details.

Related Question