why am I getting "FATAL: bouncer config error" on wrong username?

I'm trying to set up simple authentication with pgbouncer using scram-sha-256.

Here is my pgbouncer.ini:

[databases]
* = host=localhost port=5432 auth_user=pgbouncer

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/users.txt
auth_query = SELECT uname, phash from pgbouncer.user_lookup($1)
admin_users = pgbouncer
stats_users = pgbouncer
default_pool_size = 20
max_client_conn = 1000
pool_mode = transaction
logfile = /var/log/pgbouncer/pgbouncer.log

users.txt (contains only password for pgbouncer user):

"pgbouncer"     "pgbouncer"

user_lookup function, straight from https://www.pgbouncer.org/config.html:

CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)
RETURNS record AS $$
BEGIN
    SELECT usename, passwd FROM pg_catalog.pg_shadow
    WHERE usename = i_username INTO uname, phash;
    RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public, pgbouncer;
GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer;

Now I'm trying to login with postgres user, everything is fine:

[root@repmgr1 pgbouncer]# psql -U postgres -p 6432 -h localhost -d postgres
Password for user postgres:
psql (15.7)
Type "help" for help.

Now I try to access DB with wrong username:

[root@repmgr1 pgbouncer]# psql -U wrong_user -p 6432 -h localhost -d postgres
psql: error: connection to server at "localhost" (::1), port 6432 failed: FATAL:  bouncer config error

pgbouncer.log:

2024-07-17 22:50:39.483 MSK [22172] LOG S-0x55c67b0dc930: postgres/pgbouncer@127.0.0.1:5432 closing because: auth_query response contained null user name (age=34s)
2024-07-17 22:50:39.483 MSK [22172] LOG C-0x55c67b0d3f40: postgres/(nouser)@[::1]:57790 closing because: bouncer config error (age=0s)
2024-07-17 22:50:39.483 MSK [22172] WARNING C-0x55c67b0d3f40: postgres/(nouser)@[::1]:57790 pooler error: bouncer config error

My question is: is this correct behaviour of pgbouncer? Should I use more complex user_lookup() function so it returns NOT NULL on wrong username?

Right now anyone can bruteforce pgbouncer and get all the valid login names because they will be prompted with password prompt.

Solution

Too me the password is the thing you don't want to make it easy to brute force. As long as the password is good, I don't really care if they can enumerate the user name.

But maybe you an file a bug with pgbouncer about this, it is at least arguably a bug. At least, I can't think of a reason for it to make this distinction.

Anyway, it is almost trivial to work around it on your end. Just select the name back to you.

SELECT $1::name, phash from pgbouncer.user_lookup($1)

Or you could make the analogous change in the function definition, returning the input user name unconditionally.