The userlist.txt file contains the accounts for connecting from your client to PgBouncer. The [databases]
section contains the login information for connection from PgBouncer to the backend PostgreSQL server. You appear to be mixing these two up.
When you connect from your client to PgBouncer you need to provide a user name and password from userlist.txt and a database name of something
in this case.
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.
Best Answer
PgBouncer speaks the normal PostgreSQL protocol. So a client application connects to PgBouncer normally, except by specifying the host and port where PgBouncer runs instead of the host and port where the PostgreSQL server runs. (The default port of PgBouncer is 6432.)
If PgBouncer has already established connections to the PostgreSQL backend, then opening a new connection from the client to PgBouncer is very fast. That is one of several reasons to use PgBouncer.