Ask about auth_query, auth_user

[vinhnd20]

I'm learning how to use auth_query, auth_user without using userlist.txt.
Below are my steps:

• Create user user1:

postgres=# create user user1 password 'user1pass';
CREATE ROLE

• Create database db1:

postgres=# create database db1 owner user1;
CREATE DATABASE

• After that, I create function:

CREATE OR REPLACE FUNCTION user_search(uname TEXT) RETURNS TABLE (usename name, passwd text) as
$$
  SELECT usename, passwd FROM pg_shadow WHERE usename=$1;
$$
LANGUAGE sql SECURITY DEFINER;

• And this is my pgbouncer.ini:

[databases]
db1 = host=localhost dbname=db1 auth_user=user1

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 16432
auth_type = md5
auth_query = SELECT usename, passwd FROM user_search($1)

• But when I try to connect to it, it doesn't work? Did I do something wrong somewhere? Is there any way to get what I want?

PGPASSWORD=user1pass psql -h 127.0.0.1 -p 16432 -U user1 db1
psql: error: connection to server at "127.0.0.1", port 16432 failed: FATAL:  password authentication failed

Is there a way to avoid using userlist.txt?

[JelteF]

You need to add auth_user to your `pgbouncer.ini.

[vinhnd20]

You need to add auth_user to your `pgbouncer.ini.

@JelteF
You mean now I need to add auth_user like this or something ?
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 16432
auth_user= user1
auth_type = md5
auth_query = SELECT usename, passwd FROM user_search($1)

[crvt4722]

I'm also encountering a problem like that

[JelteF]

You need to add auth_user to your `pgbouncer.ini.

@JelteF You mean now I need to add auth_user like this or something ?

Yes

[JelteF]

Oh I now see you have set auth_user in the database line. That should work as well. Do you have logs from Postgres and PgBouncer during this time.

[vinhnd20]

@JelteF
This is my logs when I use the following command:

vinh@vinh:~/Desktop/Work/Work-1810-2210/pg_bouncer$ PGPASSWORD=user1pass psql -h 127.0.0.1 -p 16432 -U user1 db1
psql: error: connection to server at "127.0.0.1", port 16432 failed: FATAL:  password authentication failed

• Logs:

.1, tls: OpenSSL 3.0.8 7 Feb 2023
2023-10-24 15:17:09.320 +07 [9646] LOG C-0x557bb2213ed0: db1/user1@127.0.0.1:50926 login attempt: db=db1 user=user1 tls=no
2023-10-24 15:17:09.321 +07 [9646] LOG C-0x557bb2213ed0: db1/user1@127.0.0.1:50926 closing because: password authentication failed (age=0s)
2023-10-24 15:17:09.321 +07 [9646] WARNING C-0x557bb2213ed0: db1/user1@127.0.0.1:50926 pooler error: password authentication failed

[vinhnd20]

@JelteF
If now I want to use userlist.txt, which user I should put in this file ? postgres or user1 ?

[eulerto]

@vinhnd20 see #975 (comment) .

[emelsimsek]

@vinhnd20 It looks like auth_user is the user who runs theauth_queryfor authenticating other users. You can create a dedicated auth_user for this purpose. It does not have to be the username in the connection string.

Now this user should be able to authenticate itself in order to run auth_query. Hence you need to specify this user in auth_file.

The improvement is that you only have to have one line in auth_file, instead of listing all the Postgres users.

In summary you still need an auth_file with the auth_user and its password in it.

psql -c "CREATE USER pgbouncer_auth_user WITH ENCRYPTED PASSWORD 'yourpass';"

pgbouncer.ini
auth_file = userlist.txt

userlist.txt
"pgbouncer_auth_user" "yourpasswd"

Now connect using the database user you want.

[garry-t]

not so helpful. I also stuck with problem. After I read guide , In my understanding no need any auth_file anymore if I specified config like below:

auth_type = md5
auth_user = pgbouncer
auth_dbname = test-db
auth_query = SELECT usename, passwd FROM user_search($1)

user pgbouncer is db owner.
For login I use same user
psql -U pgbouncer -p 6432 -h 127.0.0.1 test-db
But I getting same error.
WARNING C-0x56297658ea70: test-db/pgbouncer@127.0.0.1:39624 pooler error: password authentication failed
I followed @emelsimsek suggestion, but add only pgbouncer user to userlist.txt still doesn't work

[garry-t]

@vinhnd20 did you find solution for yourself ?

[eulerto]

@garry-t see my previous comment. You need the auth_file. How will PgBouncer know the credentials to log into Postgres and execute the auth_query? auth_file should provide the auth_user credentials.

[garry-t]

Ok, yep.
So for those who will struggle as me. You need to have following in config:

[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /run/pgbouncer/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
unix_socket_dir = /var/run/pgbouncer
auth_type = md5
auth_user = pgbouncer
auth_dbname = test_db
auth_query = SELECT usename, passwd FROM user_search($1)
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
stats_users = postgres
ignore_startup_parameters = extra_float_digits,geqo,search_path

inside auth_file I specified user pgbouncer and password. And off course created search function for this db in config and assigned permissions to auth_user.

Ticket can be closed in my opinion.