0

I am trying to authenticate the users listed in "admin_users" by using the "auth_query" instead of hardcoding the passwords in userlist.txt... but I don't think this is possible in the latest version of pgbouncer (v1.8.1).

Please someone tell me I'm wrong!

Below is an example of the problem, and I can find no workaround. In fact, I've got a bug and an enhancement logged with the project... but I'd love for someone to point out some silly mistake I've made:

https://github.com/pgbouncer/pgbouncer/issues/302 (Crashing bug) https://github.com/pgbouncer/pgbouncer/issues/303 (Enhancement request)

EXAMPLE

pgbouncer.ini

[databases]
* = host=localhost port=5432 auth_user=pgbouncer

[pgbouncer]
auth_query = SELECT uname, phash FROM pgbouncer.user_lookup($1);
admin_users = postgres,gclough
userlist.txt

"pgbouncer" "password"

Logins work to both the database (port 5432) and pgbouncer (port 6432):

[root@localhost pgbouncer]# /usr/pgsql-9.6/bin/psql -h 127.0.0.1 -U gclough -p 5432 postgres
Password for user gclough: 
psql (9.6.9)
Type "help" for help.

postgres=# \q

[root@localhost pgbouncer]# /usr/pgsql-9.6/bin/psql -h 127.0.0.1 -U gclough -p 6432 postgres
Password for user gclough: 
psql (9.6.9)
Type "help" for help.

postgres=# \q

But if I try to login to pgbouncer, then it fails:

[root@localhost pgbouncer]# /usr/pgsql-9.6/bin/psql -h 127.0.0.1 -U gclough -p 6432 pgbouncer
psql: ERROR:  No such user: gclough

Unless I put my user into userlist.txt:

"pgbouncer" "password"
"gclough" "trustno1"

Then it works:

[root@localhost pgbouncer]# /usr/pgsql-9.6/bin/psql -h 127.0.0.1 -U gclough -p 6432 pgbouncer
Password for user gclough: 
psql (9.6.9, server 1.8.1/bouncer)
Type "help" for help.

pgbouncer=#
Greg Clough
  • 1
  • 2
  • Turn on statement logging and see if the `auth_user` can connect and if the `auth_query` is run against the database. – Laurenz Albe Jun 14 '18 at 06:32
  • @LaurenzAlbe, I turned on full logging in PostgreSQL, and when I connect to "postgres" via PgBouncer it makes contact with the database and verifies the password. `< 2018-06-14 10:04:02.483 BST > LOCATION: exec_simple_query, postgres.c:1163 < 2018-06-14 10:04:02.484 BST > LOG: 00000: duration: 0.797 ms parse : SELECT uname, phash FROM pgbouncer.user_lookup($1); < 2018-06-14 10:04:02.484 BST > LOCATION: exec_parse_message, postgres.c:1427` When connecting to "pgbouncer" both with and without an entry in userlist.txt, no activity is registered on the database at all. – Greg Clough Jun 14 '18 at 09:06

1 Answers1

0

My money is on the bet that this is pgBouncer issue #278.

The auth_query setting is not yet known when the [database] section is parsed, so pgBouncer is not smart enough to know that it should use that query.

Reorder the configuration file such that the [pgbouncer] section is before the [databases] section and see if that makes a difference.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • That sparked like an "aha!" moment... but unfortunately reordering the ini and restarting didn't help. I have a gut feeling that there is a special switch in the code that changes the authentication path when connecting to the "pgbouncer" database. I'll keep looking. – Greg Clough Jun 15 '18 at 13:31
  • After reordering, do any authentication queries get sent to PostgreSQL? – Laurenz Albe Jun 15 '18 at 13:40
  • No, I've set `log_min_duration_statement=0`, and `log_connections=on`... but it doesn't even touch the target database when you try to connect to the `pgbouncer` database. – Greg Clough Jun 16 '18 at 16:36