pgbouncer
auth_query in pgbouncer with azure postgresql instance
Hello,
I have an azure Postgresql instance and a Linux VM which I installed pgbouncer 1.15.0.
My main objective is to implement password rotation for the database.
What I'm trying to do is to use auth_user and auth_query, following is the configuration I made:
pgbouncer.ini
[database]
* = host=... post=... auth_user=postgres@myserver
[pgbouncer]
auth_file = /etc/pgbouncer/userlist.txt
auth_type = md5
auth_query = SELECT usename, passwd FROM user_search($1)
userlist.txt:
"postgres" "password as clear text"
On Postgresql side I ran created a role name postgres and the following 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;
I made sure that postgres have access and can run the query, and I'm trying to log in with another user than postgres.
the error I'm getting is the following:
WARNING C-0x5602e53bed80: dbname/(nouser)@ip:port pooler error: no such user.
After my research, I noticed that this error normally contains the username, which might point that pgbouncer isn't parsing the username correctly, however in the logs it show:
got var: user=myuser@myserver
And I also noticed from logs that it's trying to get postgres password from auth_file as plain text, however auth_type = md5, and if I store the password in md5 format it won't work and will give me login failed error, following is the logs:
DEBUG S-0x5602e53c5ce0: dbname/postgres@myserver@ip:port calling login_answer
DEBUG S-0x5602e53c5ce0: dbname/postgres@myserver@ip:port S: req cleartext password
DEBUG S-0x5602e53c5ce0: dbname/postgres@myserver@ip:port P: send clear password
Thanks in advance.
@smartouss The problem is pg_shadow is storing the usernames without the @myserver if you query pg_shadow manually you will see it.
You can use this auth_query to get it to match:
WITH myuser as (SELECT $1 as fullusername) SELECT fullusername as usename, passwd FROM pg_catalog.pg_shadow INNER JOIN myuser ON usename = substring(fullusername from '[^@]*')
Also as mentioned in #325 there is something with Azure and md5 auth not working. I worked around it by using the attached patch. It makes the md5 auth work with pgshadow by eliminating the @myserver part of the username. And it stores the cleartext password in memory for later plain authentication against Azure.
It's a bit hackish patch, but works for us.
