Database user and auth_query do not work together

[DustinChaloupka]

pgbouncer.ini

[databases]
mydb = host=my_server.com port=5432 dbname=my_db auth_user=pgbouncer user=my_user

[pgbouncer]
pool_mode = transaction
listen_port = 6547
listen_addr = 10.20.30.40
logfile = /var/log/postgresql/pgbouncer.log
unix_socket_dir = /var/run/pgbouncer/pgbouncer-shared-1
auth_type = md5
user = postgres
admin_users = pgbouncer
stats_users = pgbouncer_stat
auth_file = /opt/pgbouncer/auth/userslist
auth_query = SELECT * FROM pgbouncer.get_auth($1)
client_tls_sslmode = allow
client_tls_key_file = /opt/pgbouncer/ssl/server.key
client_tls_cert_file = /opt/pgbouncer/ssl/server.crt
client_tls_ca_file = /etc/ssl/certs/ca-certificates.crt
server_tls_sslmode = require
ignore_startup_parameters = extra_float_digits
max_client_conn = 300
max_db_connections = 500
default_pool_size = 200
min_pool_size = 0
server_reset_query_always = 0
so_reuseport = 1

[peers]
1 = host=/var/run/pgbouncer/pgbouncer-shared-1 port=6547
2 = host=/var/run/pgbouncer/pgbouncer-shared-2 port=6547

It appears that a combination of the peers setup with the "forced" user in the databases section breaks how pgbouncer handles the results of the auth_query.

With the above setup, pgbouncer returns with

server login failed: FATAL password authentication failed for user "my_user"

Removing the peers section gives a successful connection.

Adding the same password that is being returned by the auth_query to /opt/pgbouncer/auth/userslist gives a successful connection as well (with the peers section still present).

[JelteF]

Hmm, that sounds like an annoying bug. Could you check with the master branch if it still occurs there too? I'm hoping this might magically be fixed by the changes to our user management that I merged recently.

[DustinChaloupka]

May have made the issue in haste. I had got it working, and it was after I had excluded the peers section, but after restarting pgbouncer without the peers section, it now does not work still. I attempted to reproduce what I had done to get it working, but cannot figure it out.

Will attempt with the build from the latest on master.

[DustinChaloupka]

Used the latest version from master and it still does not work with the configuration in the description of the issue without the peers section.

[DustinChaloupka]

Some further debug information.

Normally I have been attempting to make a connection with a role different than the one defined in user, eg other_user. The postgres logs showed this (we have a log in the pgbouncer.get_auth function):

pgbouncer@my_db - psql NOTICE:  PgBouncer auth request: other_user
pgbouncer@my_db - psql CONTEXT:  PL/pgSQL function pgbouncer.get_auth(text) line 3 at RAISE
pgbouncer@my_db - psql NOTICE:  PgBouncer auth request: other_user
pgbouncer@my_db - psql CONTEXT:  PL/pgSQL function pgbouncer.get_auth(text) line 3 at RAISE
[unknown]@[unknown] - [unknown] LOG:  connection received: host=10.20.30.40 port=21132
my_user@my_db - [unknown] FATAL:  password authentication failed for user "my_user"
my_user@my_db - [unknown] DETAIL:  Connection matched pg_hba.conf line 127: "host    all         my_user    10.20.30.0/24             md5"

This looks like pgbouncer is only querying for other_user's password and not my_user's.

Saw in some other issues that pgbouncer keeps a list in memory for the users, so tried first authenticating with my_user so pgbouncer would use the auth_query to get the right password and store it. That worked (though I was pretty sure I had tried this earlier and it didn't, but 🤷 ). Then afterward, I was able to authenticate with other_user successfully.

I guess the question is, is authenticating with a different user than the one defined in the databases section in user not supported? I would expect it to be, otherwise there wouldn't really be a reason to have it.

[JelteF]

So there's two stages of authentication that PgBouncer does:

1. First it needs to validate that the user you are logging in as (other_user) provides the correct password to PgBouncer
2. It needs to provide the right password to Postgres for the user PgBouncer is trying to log in as (my_user)

For the first case it will use auth_query to get the password if it's not present in the auth_file. But for the second case I believe that the password needs to be in the auth_file. Using an auth_query to fetch the password from postgres is unlikely to help here, because the password is stored as a SCRAM-256 hash in postgres, meaning that you cannot log in with it.

[JelteF]

So to be clear. If you add a line to the auth_file saying "my_user" "the-password-for-my_user" then I think your setup should work.

[DustinChaloupka]

Yes, adding it to the auth_file does work. I guess this makes sense since pgbouncer needs to be supplied the password for my_user initially somehow. Thanks for the help!