You also need to tell us how you attempted to connect (which command and which options), and the log entries in the postgres and pgbouncer logs from around that time.

You also need to tell us how you attempted to connect (which command and which options), and the log entries in the postgres and pgbouncer logs from around that time.

psql commands

and tail -f db log file

first connection attempt denied by pgbouncer hence nothing in pg log file

pgbouncer log

This setup

[databases]
test = dbname=test host=127.0.0.1 port=5432
test = auth_user=pgbouncer

is not correct. You have two entries for the test database. You need to combine those into one.

This setup

[databases]
test = dbname=test host=127.0.0.1 port=5432
test = auth_user=pgbouncer

is not correct. You have two entries for the test database. You need to combine those into one.

I've change configuration and remove test = auth_user=pgbouncer line and restart/reload pgbouncer but still the same error.

I tried your setup and any user into users section cannot login. Looking at the postgres logs, pgbouncer does not try to connect and execute auth_query when user is from users section. Other users (that is not in users section) work fine. It seems a bug to me.

I tried your setup and any user into users section cannot login. Looking at the postgres logs, pgbouncer does not try to connect and execute auth_query when user is from users section. Other users (that is not in users section) work fine. It seems a bug to me.

You are right, I tried too after your comment and It works fine after remove users in [users] section.

Yes, I just went through this today, and have some more details. In my specific case I set client_tls_sslmode = prefer in the [pgbouncer] section. What happens then is that any user that has settings specified in the [users] section ignores that, uses the default of no SSL, does not even attempt to fetch user info via the auth_query and reports that password auth failed.

I find that the statement in the docs "Only a few settings are available here." followed by just the two pool_mode and max_user_connections is misleading. In fact, adding client_tls_sslmode=prefer to the user's entry fixes the problem. (That is all that is necessary; I did not repeat the settings for cert/key locations and it worked.)

[EDIT] I also noticed that changes to this did not seem to take effect with a reload, but required actual quit & restart of pgbouncer.

Addendum to above: reviewing logs from after out config changes, I see:

ERROR skipping user ... because of unknown parameter in settings: client_tls_sslmode

So in fact it "works" by ignoring the setting where we try to limit the pool size for that user, so there seems to be NO WAY to have TLS and per-user settings.

Spent better half of the day on this and can confirm that with following config configuration will not let postgre user to connect to DB, unless it is listed in userlist.txt

[databases]

pgbench = host=127.0.0.1 port=5431 auth_user=pgbouncer pool_size=4
postgres = host=127.0.0.1 port=5431 auth_user=pgbouncer pool_size=0

[users]
postgres = max_user_connections=2

[pgbouncer]
listen_addr = *
listen_port = 5432

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

unix_socket_dir = /var/run/pgbouncer

auth_type = md5
auth_query = SELECT p_user, p_password FROM pgbouncer.auth($1)
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = session
server_reset_query = DISCARD ALL

stats_users = pgbouncer_stats
admin_users = pgbouncer_admin

When [users] section is removed and pgbouncer is restarted (reload will not work for this), postgres user is able to log in via pgbouncer.

This pretty much hard blocks us to use [users] all together and there is no way for us to add connection limits to particular users.

It has become apparent, also through other reports, that the [users] section support is pretty buggy. It's good to have this reproducible report. The whole thing probably needs a deeper analysis and rewrite.

@eulerto @petere Here is a PR to fix this issue: #706

@eulerto @petere Here is a PR to fix this issue: #706

@petere can this PR be merged in? I have the same issue

@veshant The #706 PR is not in a state where it can be merged.