时间戳

首页 > 解决方案 > PgBouncer - Can users in "admin_users" use "auth_query" to connect instead of userlist.txt?

database - PgBouncer - Can users in "admin_users" use "auth_query" to connect instead of userlist.txt?

问题描述

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=#

标签： databasepostgresqlpgbouncer

解决方案

我打赌这是pgBouncer issue #278。

解析该部分时该auth_query设置尚不清楚[database]，因此 pgBouncer 不够聪明，无法知道它应该使用该查询。

重新排序配置文件，使该[pgbouncer]部分位于该部分之前[databases]，看看是否有区别。

推荐阅读