密码文件：
认证方法,可设置为any, trust, plain, crypt, md5

连接信息未指定用户名称和密码。

auth_type = trust

pgbouncer.ini
[databases]
;;; 未设置用户密码，需要使用auth_file密码连接数据。
appdb=host=db01 port=5666 dbname=appdb
[pgbouncer]
logfile = /home/pg10/pgbouncer/pgbouncer.log
pidfile = /home/pg10/pgbouncer/pgbouncer.pid
auth_type = trust
;;;pgbouncer密码文件，和数据库用户对应即可。
auth_file = /data/pg
listen_addr = *
listen_port = 5766

密码文件

cat /data/pg
"appuser" "1qaz@WSX"

输入不存在的用户（密码文件不存在，数据不存在。）

密码文件（auth_file）用户不存在；（错误信息：用户不存在）

psql -h db01 -p 5766 appdb -U abc
psql: ERROR: no such user: abc

pgbouncer.log日志：

2021-11-17 21:02:44.049 CST [77537] LOG C-0x15d58c0: (nodb)/(nouser)@172.25.113.97:42630 closing because: no such user: abc (age=0s)
2021-11-17 21:02:44.049 CST [77537] WARNING C-0x15d58c0: (nodb)/(nouser)@172.25.113.97:42630 pooler error: no such user: abc
2021-11-17 21:02:44.049 CST [77537] LOG C-0x15d58c0: (nodb)/(nouser)@172.25.113.97:42630 login failed: db=appdb user=abc

数据库不存在用户，密码文件存在的用户；（错误信息：密码错误）

[pg10@db01 tmp]$ psql -h db01 -p 5766 appdb -U admin
psql: ERROR: password authentication failed for user "admin"
[pg10@db01 tmp]$ psql -h db01 -p 5766 appdb -U admin
psql: ERROR: pgbouncer cannot connect to server

pgbouncer.log日志：

2021-11-17 21:43:47.916 CST [79506] LOG C-0xf6ba10: appdb/admin@172.25.113.97:43832 login attempt: db=appdb user=admin tls=no
2021-11-17 21:43:47.916 CST [79506] LOG S-0xf72850: appdb/admin@172.25.113.97:5666 new connection to server (from 172.25.113.97:49812)
2021-11-17 21:43:47.924 CST [79506] WARNING server login failed: FATAL password authentication failed for user "admin"
2021-11-17 21:43:47.924 CST [79506] LOG C-0xf6ba10: appdb/admin@172.25.113.97:43832 closing because: password authentication failed for user "admin" (age=0s)
2021-11-17 21:43:47.924 CST [79506] WARNING C-0xf6ba10: appdb/admin@172.25.113.97:43832 pooler error: password authentication failed for user "admin"
2021-11-17 21:43:47.924 CST [79506] LOG S-0xf72850: appdb/admin@172.25.113.97:5666 closing because: login failed (age=0s)
2021-11-17 21:43:50.250 CST [79506] LOG C-0xf6ba10: appdb/admin@172.25.113.97:43836 login attempt: db=appdb user=admin tls=no
2021-11-17 21:43:50.250 CST [79506] LOG C-0xf6ba10: appdb/admin@172.25.113.97:43836 closing because: pgbouncer cannot connect to server (age=0s)
2021-11-17 21:43:50.250 CST [79506] WARNING C-0xf6ba10: appdb/admin@172.25.113.97:43836 pooler error: pgbouncer cannot connect to server

源数据库日志：

2021-11-17 21:43:47.924 CST,"admin","appdb",79511,"172.25.113.97:49812",61950713.13697,1,"authentication",2021-11-17 21:43:47 CST,5/344,0,FATAL,28P01,"password authentication failed for user ""admin""","Role ""admin"" does not exist.
Connection matched pg_hba.conf line 95: ""host all all 0.0.0.0/0 scram-sha-256""",,,,,,,,""

密码文件，数据库，已经存在在的用户

不用输入密码即可登录数据库。（trust认证，用户名称正确即可登录）

[pg10@db01 pgbouncer]$ psql -h db01 -p 5766 appdb -U appuser <<客户端不用输入密码。
psql (10.14)
Type "help" for help.
appdb=> ###成功登录###

pgbouncer.log日志：

appdb/appuser@172.25.113.97:42608 login attempt: db=appdb user=appuser tls=no

auth_type = md5

5432数据库建立测试用户和数据库：
create user test01 with password '123456';
create database test01 owner test01;
grant all privileges on database test01 to test01;

pgbouncer.ini
[databases]
;;; 未设置用户密码，需要使用auth_file密码连接数据；
test01=host=db01 port=5432 dbname=test01
[pgbouncer]
logfile = /home/pg10/pgbouncer/pgbouncer.log
pidfile = /home/pg10/pgbouncer/pgbouncer.pid
auth_type = md5
auth_file = /data/pg
listen_addr = *
listen_port = 5766

密码文件存储数据库中对应的用户名称和密码;
/data/pg
"test01" "123456"

客户端登录输入认证密码：

[pg10@db01 tmp]$ psql -h db01 -p5766 -d test01 -U test01
Password for user test01:
psql (10.14)
Type "help" for help.
test01=> \q

连接信息指定用户名称和密码

auth_type = trust

pgbouncer.ini
[databases]
;;; 设置了用户和秘密，需要使用auth_file密码连接数据。
appdb=host=db01 port=5666 user=appuser password=1qaz@WSX dbname=appdb
[pgbouncer]
logfile = /home/pg10/pgbouncer/pgbouncer.log
pidfile = /home/pg10/pgbouncer/pgbouncer.pid
;;;trust不认证密码，只要用户名对即可。
auth_type = trust
auth_file = /data/pg
listen_addr = *
listen_port = 5766

只需要认证auth_file 用户名即可，不用输入密码。

[pg10@db01 tmp]$ psql -h db01 -p 5766 appdb -U admin
psql (10.14)
Type "help" for help.
appdb=> \q

auth_type = md5

pgbouncer.ini
[databases]
;;; 设置了用户和秘密，需要使用auth_file密码连接数据。
appdb=host=db01 port=5666 user=appuser password=1qaz@WSX dbname=appdb
[pgbouncer]
logfile = /home/pg10/pgbouncer/pgbouncer.log
pidfile = /home/pg10/pgbouncer/pgbouncer.pid
;;;md5用户名和密码必须全部正确。
auth_type = md5
auth_file = /data/pg
listen_addr = *
listen_port = 5766

需要验证密码文件（auth_file）第二列的密码；即使用户在数据库中不存在，可以认证成功。

[pg10@db01 tmp]$ psql -h db01 -p 5766 appdb -U admin1
Password for user admin1:

密码文件：

cat /data/pg
"appuser" "1qaz@WSX"
"postgres" "123456"
"admin" "1qaz@WSX"
"admin1" "123456"

数据库使用scram-sha-256密码认证：
userslist.txt通过指定文件AUTH_FILE只包含用于连接到PostgreSQL的用户和口令的信息;该文件中的密码可以是纯文本密码，也可以是使用MD5或SCRAM加密的密码，具体取决于要使用的身份验证方法。

pg_hba.conf：更改scram-sha-256.

更改数据库认证密码类型:

SELECT name,setting,source,enumvals FROM pg_settings WHERE name = 'password_encryption';
ALTER SYSTEM SET password_encryption TO 'scram-sha-256';
SELECT pg_reload_conf();

SELECT name,setting,source,enumvals FROM pg_settings WHERE name = 'password_encryption';

ALTER SYSTEM SET password_encryption TO 'md5';

SELECT pg_reload_conf();

SELECT name,setting,source,enumvals FROM pg_settings WHERE name = 'password_encryption';

更改类型之后要重新设置用户密码。

alter user postgres with superuser password '1qaz@WSX';

pgbouncer 密码文件（auth_file）为明文或者md5则连接数据库失败：

密码文件：
[pg10@db01 data]$ cat /data/pg
"test01" "123456"
[pg10@db01 data]$

连接失败，类型不支持：

[pg10@db01 data]$ psql -h db01 -p5766 -d test01 -U test01
Password for user test01:
psql: ERROR: server login failed: wrong password type

报错信息为密码类型错误.

[pg10@db01 data]$ psql -h db01 -p5766 -d test01 -U test01
psql: ERROR: pgbouncer cannot connect to server
pgbouncer.log日志信息：
2021-11-18 22:47:11.018 CST [127387] LOG C-0x9e0a10: test01/test01@172.25.113.97:55974 login attempt: db=test01 user=test01 tls=no
2021-11-18 22:47:11.018 CST [127387] LOG C-0x9e0a10: test01/test01@172.25.113.97:55974 closing because: client unexpected eof (age=0s)
2021-11-18 22:47:13.453 CST [127387] LOG C-0x9e0a10: test01/test01@172.25.113.97:55978 login attempt: db=test01 user=test01 tls=no
2021-11-18 22:47:13.453 CST [127387] LOG S-0x9e7870: test01/test01@172.25.113.97:5432 new connection to server (from 172.25.113.97:46598)
2021-11-18 22:47:13.454 CST [127387] ERROR S-0x9e7870: test01/test01@172.25.113.97:5432 cannot do SCRAM authentication: wrong password type
2021-11-18 22:47:13.454 CST [127387] LOG C-0x9e0a10: test01/test01@172.25.113.97:55978 closing because: server login failed: wrong password type (age=0s)
2021-11-18 22:47:13.454 CST [127387] WARNING C-0x9e0a10: test01/test01@172.25.113.97:55978 pooler error: server login failed: wrong password type
2021-11-18 22:47:13.454 CST [127387] LOG S-0x9e7870: test01/test01@172.25.113.97:5432 closing because: failed to answer authreq (age=0s)

需要修改用户密码文件密码信息（auth_file）为SCRAM-SHA-256类型新密码：

数据库更改用户密码类型，和重新设置用户密码；

alter user test01 password '123456';
select usename, passwd from pg_shadow order by 1;

postgres=# SELECT name,setting,source,enumvals FROM pg_settings WHERE name = 'password_encryption';

    name         |    setting    |       source       |      enumvals       

(1 row)

postgres=# select usename, passwd from pg_shadow order by 1;

(2 rows)

postgres=# alter user test01 password '123456'; --重新设置密码，SCRAM-SHA-256才生效。
ALTER ROLE
postgres=# select usename, passwd from pg_shadow order by 1;

(2 rows)

修改pgbouncer密码文件（auth_file）：

可见pgbouncer默认密码类型为md5:(如使用SCRAM-SHA-256$，则不能使用明文密码)

[pg10@db01 pgbouncer]$ cat pgbouncer.ini
[databases]
;;; 未设置用户密码，需要使用auth_file密码连接数据。
test01=host=db01 port=5432 dbname=test01
[pgbouncer]
logfile = /home/pg10/pgbouncer/pgbouncer.log
pidfile = /home/pg10/pgbouncer/pgbouncer.pid
auth_type = md5
auth_file = /data/pg
listen_addr = *
listen_port = 5766

修改密码文件密码列为SCRAM-SHA-256类型密码：

[pg10@db01 pgbouncer]$ cat /data/pg
"test01" "SCRAM-SHA-256$4096:bthWsuBz7AFCz2laBzPxbA==$YfZnMNIbx6q0HXCEVW7CRmNZh2VfEjy8L+WJiImG648=:0dxvESqwB/tT/G5goPrRdvfrX39j77p4gPx36jKtj94="
[pg10@db01 pgbouncer]$

连接测试：---连接成功！
[pg10@db01 data]$ psql -h db01 -p5766 -d test01 -U test01
Password for user test01:
psql (10.14)
Type "help" for help.
test01=>

auth_type = any

当配置any,数据连接需要指定用户和密码（user=appuser password=1qaz@WS）。

[databases]
appdb=host=db01 port=5666 user=appuser password=1qaz@WSX dbname=appdb
[pgbouncer]
logfile = /home/pg10/pgbouncer/pgbouncer.log
pidfile = /home/pg10/pgbouncer/pgbouncer.pid
auth_type = any
listen_addr = *
listen_port = 5766

---只需要输入正确的数据名称，不需要认证用户名称和密码，也不需要密码文件（auth_file）
[root@db01 ~]# /home/pg10/soft/bin/psql -h db01 -p 5766 -d appdb
psql (10.14)
Type "help" for help.
appdb=>
---不指定用户名称，即可登录。
[pg10@db01 ~]$ psql -h db01 -p 5766 -d appdb
psql (10.14)
Type "help" for help.
appdb=>

database pgbouncer::
$ psql -p 6543 pgbouncer
Only users listed in configuration parameters admin_users or stats_users
are allowed to login to the console. (Except when auth_mode=any, then
any user is allowed in as a stats_user.)