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/[email protected]: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.