password in [databases] connect string sometimes doesn't work for SCRAM

[jeffjanes]

If the database server has a SCRAM verifier in pg_shadow, and the [databases] connection string contains the corresponding plain text password, and the user_list.txt also contains a SCRAM verifier for a different password, authentication fails even when the password is given correctly.

If the verifier stored in either pg_shadow or user_list.txt is md5, things work (assuming the auth type settings allow md5). It is only the double use of SCRAM which fails.

If I use the exact same verifier in user_list.txt as in pg_shadow, then it works. But if I use different verifiers which both verify the same password, then it fails. So I think somewhere in the SCRAM code, one call must be stomping on the parameters for another call, but I can't figure out where this might be happening.

This report is based on my examination of a stack overflow question. I've verified it in both 1.20.1 and 1.16.0.

ini file:

[databases]
jjanes = host=127.0.0.1 user=jjanes password=abc

[pgbouncer]
auth_file = user_list.txt
ignore_startup_parameters = extra_float_digits,options
logfile = pgbouncer_6432.log
pidfile = pgbouncer_6432.pid
listen_addr = 127.0.0.1
listen_port = 6432
auth_type=md5

user_list.txt

"jjanes"    "SCRAM-SHA-256$4096:LG+7fmZAOMP7g0mHPWRnEA==$CjHttDgX/BTUDerbr3jV7oAzCU5zONfDSWI9OmMUUls=:TNs1Iq+B47hM+f1zlZ3pLAMd+AXIOx6Ih/1bm8JqEIg="

[JelteF]

That does seem like a bug indeed. Feel free to create a PR to fix it. As a workaround you should be able to put the actual password in the user_list.txt and that should make it work.

[megakoresh]

This is perhaps one of the most obscure bugs I have ever encountered, but I just hit the same problem. I can confirm it, but I also was not able apply the workaround @JelteF suggested - I am still getting "authentication failed for user postgres" with that. Only thing that helped was to set these parameters on the docker compose (I am running postgres in docker).

      POSTGRES_HOST_AUTH_METHOD: md5
      POSTGRES_INITDB_ARGS: --auth-host=md5

This switches postgres user back to using md5 by default like before version 14 and then it works. Alternatively, changing the userlist.txt encryption to md5 also works.

I guess using pgbouncer as a proxy is not exactly common use-case, since this only happens when the userlist.txt has different users than the ones in the database, but still I guess worth looking into.

[tobwen]

I'm now experiencing the same problem after an update to v1.22.0-1.pgdg120+1