I am trying to configure pgbouncer as connection pooler for my postgres instances. For my use case I tried to work with auth_query authentication method to let users connect to postgres instances without explicitly adding the credentials in userlist.txt.
Here is my pgbouncer.ini:
[databases]
; PostgreSQL database named 'dbone' running on 'postgres1' service
pokemons = host=postgres1 port=5432 dbname=pokemon
super = host=postgres2 port=5432 dbname=supersaiyans
[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_query = select usename, passwd FROM user_search($1)
auth_user = pgbuser
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
admin_users = postgres
Here is my userlist.txt:
"pgbuser" "pgbouncer"
Here is the function and auth_user for pgbouncer:
CREATE OR REPLACE FUNCTION user_search(uname TEXT) RETURNS TABLE (usename name, passwd text) as
$$
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 '[^@]*')
$$
LANGUAGE sql SECURITY DEFINER;
CREATE ROLE pgbuser WITH LOGIN PASSWORD 'pgbouncer';
GRANT EXECUTE ON FUNCTION user_search(text) TO pgbuser;
Here I created a non super user role pgbuser which could execute the function on postgres and verify the users.
Then i created a db users with view access on specific database. I checked manually logging in with these users and i could see the data i was permitted to view.
Issue: When i login to pgadmin using the host and port of pgbouncer and enter the credentials of my created db users. I could login successfully. To verify that login was working i also entered wrong passwords. I can login and see the databases but when i try to view the contents I get an SASL error:
Logs:
pgbouncer-1 | 2024-03-30 11:10:13.531 UTC [1] LOG S-0x7f8133c733e0: pokemons/[email protected]:5432 new connection to server (from 192.168.16.3:35530)
pgbouncer-1 | 2024-03-30 11:10:13.540 UTC [1] LOG C-0x7f8133c7d3c0: pokemons/[email protected]:56156 login attempt: db=pokemons user=ash tls=no
pgbouncer-1 | 2024-03-30 11:10:13.550 UTC [1] LOG S-0x7f8133c73690: pokemons/[email protected]:5432 new connection to server (from 192.168.16.3:35544)
pgbouncer-1 | 2024-03-30 11:10:16.337 UTC [1] LOG C-0x7f8133c7d670: (nodb)/(nouser)@192.168.16.1:56168 no such user: ash
pgbouncer-1 | 2024-03-30 11:10:16.337 UTC [1] LOG C-0x7f8133c7d670: (nodb)/[email protected]:56168 login attempt: db=dbone user=ash tls=no
pgbouncer-1 | 2024-03-30 11:10:16.337 UTC [1] LOG C-0x7f8133c7d920: (nodb)/(nouser)@192.168.16.1:56178 no such user: ash
pgbouncer-1 | 2024-03-30 11:10:16.337 UTC [1] LOG C-0x7f8133c7d920: (nodb)/[email protected]:56178 login attempt: db=dbone user=ash tls=no
pgbouncer-1 | 2024-03-30 11:10:16.347 UTC [1] ERROR C-0x7f8133c7d670: (nodb)/[email protected]:56168 password authentication failed
pgbouncer-1 | 2024-03-30 11:10:16.347 UTC [1] LOG C-0x7f8133c7d670: (nodb)/[email protected]:56168 closing because: SASL authentication failed (age=0s)
pgbouncer-1 | 2024-03-30 11:10:16.347 UTC [1] WARNING C-0x7f8133c7d670: (nodb)/[email protected]:56168 pooler error: SASL authentication failed
pgbouncer-1 | 2024-03-30 11:10:16.348 UTC [1] ERROR C-0x7f8133c7d920: (nodb)/[email protected]:56178 password authentication failed
Here once the login attempt is successful but after that when i click on databases I get authentication failed errors.
pgadmin gets the list of "available" databases by sending a query to the database it is actually connected to. But the databases actually available to it are determined by pgbouncer's config, which it can't see. So the introspection capabilities of pgadmin are going to broken when connecting through pgbouncer unless you are very careful, which you weren't.