search

vitabaks / postgresql_cluster

PostgreSQL High-Availability Cluster (based on "Patroni" and DCS "etcd" or "consul"). Automating with Ansible.
MIT License
1.27k stars 340 forks source link

Can't connect to db, using SSL certificate over pgbouncer. #582

Closed davideferrero closed 1 month ago

davideferrero commented 2 months ago

Hi everyone!

We just finished configuring the PostgreSQL cluster, and everything looks great! We have set up the SSL connection for the cluster. However, when we try to connect to the VIP at port 5000, we go through pgbouncer, and it doesn't allow SSL connections. Instead, if we connect directly to the node at port 5432, we establish the SSL connection without any issues.

❯ PGSSLMODE=require psql --host pgdebian2.test.it.lan --port 5000 --user testuser --db test --password Password: psql: error: connection to server at "pgdebian2.test.it.lan" (192.168.43.51), port 5000 failed: server does not support SSL, but SSL was required

Thanks a lot! :)

n-borges commented 2 months ago

Hey Davide!

you can add the following lines to /etc/pgbouncer/pgbouncer.ini to enable tls:

client_tls_sslmode = allow
client_tls_key_file = <key file path>
client_tls_cert_file = <cert file path>

It would be nice to add these configurations via playbook variables, i wonder if this is supported

vitabaks commented 2 months ago

Feel free to create a PR.

n-borges commented 2 months ago

Feel free to create a PR.

I will happily work on it in the next few days