use auth_user instead of user option in the default db config

[kruftik]

[jflambert]

hi @kruftik can you give me a bit more info as to what problem this would solve for you

[kruftik]

@jflambert , pgbouncer is able to dynamically 'create' database configs during the 1st user connect to them. moreover, there is a feature of transparent authentication by the postgres itself via 'auth_query' so that there is no need to configure each user-database pair in pgbouncer config.

if we want to authentificate in pgbouncer by means of auth_query, we must set username and password pgbouncer will use to connect to postgresql to execute the auth_query. the former is configured by the 'auth_user' parameter instead of 'user' one. pgbouncer has is able to auto 'create' database configs during the 1st user connect to them. moreover, there is a feature of transparent authentication by the postgres itself via 'auth_query' so that there is no need to configure each user-database pair in pgbouncer config.

if we want to authentificate in pgbouncer by means of auth_query, we must set username and password pgbouncer will use to authentificate itself in postgresql to execute the auth_query. the former is configured by the 'auth_user' parameter instead of 'user' one.

it should be noted, in the simple case of one user-one db configuration there is no any difference between 'user' / 'auth_user' parameters.

[cameronmccord2]

I +1 this change. We have multiple users hitting pgbouncer to get to postgres but when user=your_pgbouncer_user is in the databases line the only user postgres sees is your_pgbouncer_user. We're overriding pgbouncer.ini just to remove that one key and everything else works as expected. We love this image though!

before:

[databases]
yourdb = host=yourhost port=5432 user=your_pgbouncer_user

results in:

SELECT current_user; --- your_pgbouncer_user

After

[databases]
yourdb = host=yourhost port=5432

results in:

SELECT current_user; --- the_user_you_called_pgbouncer_with

We had read-only users hitting pgbouncer but were getting write-access because the your_pgbouncer_user had write access

[jflambert]

fair enough. I was worried about replacing user with auth_user for existing deployments, but since I introduced patch releases it's less of a concern.

[jflambert]

@kruftik @cameronmccord2 latest and 1.21.0-p2 images have been pushed to dockerhub. Please confirm.

[kruftik]

@jflambert I have asked my ex-colleagues to check but I do believe everything is good:)

[Dasio]

It broke our deployments

ERROR C-0x7f419c107390: (nodb)/(nouser)@<ip>:51292 auth_type=any requires forced user
LOG C-0x7f419c107390: (nodb)/(nouser)@<ip>:51292 closing because: bouncer config error (age=0s)

Fixed for us by switching to edoburu/pgbouncer:1.20.1-p0

[jflambert]

@Dasio I'm sorry to hear this. based on earlier comments it seemed like this change wouldn't break any existing deployments. If I read between the lines, @kruftik himself doesn't use pgbouncer anymore, and I was hoping to get feedback from @cameronmccord2.

I'm not against reverting this PR if we can't find a common ground.

[Dasio]

I don't know exactly what went wrong, just wanted to give heads up, that it can break. Maybe our setup is wrong.

We use it along with GCP CloudSQL proxy and IAM users

AUTH_TYPE=any we tried AUTH_TYPE=trust but didn't fixed it