Getting 'E' packet on "auth_conn query" instantly

[ylacancellera]

Hi

I currently have a repeatable issue where users can connect to every db, except one (with appropriate pg privileges).
Users are able to connect to other databases and switch between them with \c, but not to the "broken" one.

I notice I have a "error response from auth_query", I tried debugging it:

2023-10-03 06:47:02.635 UTC [7] NOISE C-0x561ead097b10: some_user/(nouser)@<client_ip>:<client_port> doing auth_conn query
2023-10-03 06:47:02.635 UTC [7] DEBUG pktbuf_dynamic(512): 0x561ead0a24a0
2023-10-03 06:47:02.635 UTC [7] NOISE tls_write: req=108 out=108
2023-10-03 06:47:02.635 UTC [7] DEBUG pktbuf_free(0x561ead0a24a0)
2023-10-03 06:47:02.635 UTC [7] NOISE resync(10): done=0, parse=0, recv=0
2023-10-03 06:47:02.635 UTC [7] NOISE tls_read: req=4096 out=103
2023-10-03 06:47:02.635 UTC [7] NOISE S-0x561ead0a83f0: some_user/_pgbouncer@<pg_ip>:5432 read pkt='E', len=103
2023-10-03 06:47:02.635 UTC [7] LOG S-0x561ead0a83f0: some_user/_pgbouncer@<pg_ip>:5432 closing because: error response from auth_query (age=0s)
2023-10-03 06:47:02.635 UTC [7] LOG C-0x561ead097b10: some_user/(nouser)@<client_ip>:<client_port> closing because: bouncer config error (age=0s)
2023-10-03 06:47:02.635 UTC [7] WARNING C-0x561ead097b10: some_user/(nouser)@<client_ip>:<client_port> pooler error: bouncer config error

I have some questions regarding it:

• is it possible something else than postgres is answering this "E" packet ? (a pgbouncer cache, anything)
• is it weird taht everything is instantaneous here ? It is not on successful logins
• could that be a pg_catalog issue somehow ?
• anything particular logging you would advise on postgres side to dive further ?

[ylacancellera]

Found it.
It was a permission issue on pgbouncer schema. The error is obfuscated unfortunately, it would be really nice to display it as pgbounceruser was getting a proper "permission denied" when trying to use select * from pgbouncer.get_auth(x) directly
EDIT: just to make it clear, "end" users had the correct permissions, only pgbouncer user lacked privileges for some reason

[JelteF]

Hmm, that indeed sounds annoying to debug. We should log the error provided by postgres instead of saying only "error response from auth_query". If you want to contribute a PR for that it's very much appreciated. I think it would be a good first contribution.

[ylacancellera]

Why not actually, it would be fun, I will try over the next days