密码文件：
认证方法,可设置为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 | password_encryption | scram-sha-256 | configuration file | {md5,scram-sha-256} |
(1 row)
postgres=# select usename, passwd from pg_shadow order by 1;
| usename | passwd |
|---|---|
| pg10 | |
| test01 | md5a1e41a7c7c50af10ea8fad2b76087168 |
(2 rows)
postgres=# alter user test01 password '123456'; --重新设置密码，SCRAM-SHA-256才生效。
ALTER ROLE
postgres=# select usename, passwd from pg_shadow order by 1;
| usename | passwd |
|---|---|
| pg10 | |
| test01 | SCRAM-SHA-256$4096:bthWsuBz7AFCz2laBzPxbA==$YfZnMNIbx6q0HXCEVW7CRmNZh2VfEjy8L+WJiImG648=:0dxvESqwB/tT/G5goPrRdvfrX39j77p4gPx36jKtj94= |
(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.)
