pgbouncer软件安装

本文介绍了postgresql 连接池pgbouncer的软件安装和基本配置。

1，软件下载地址

PgBouncer Downloads

https://www.pgbouncer.org/downloads/

2，依赖软件安装

GNU Make 3.81+
Libevent 2.0+
pkg-config
OpenSSL 1.0.1+ for TLS support
(optional) c-ares as alternative to Libevent’s evdns
(optional) PAM libraries

3，软件安装

$ ./configure --prefix=/usr/local
$ make
$ make install

4，参数文件配置（pgbouncer.ini）

pgbouncer.ini
[databases]
;;;数据库连接字符串(由key=value对组成)
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 = md5
auth_file = /data/pg
listen_addr = *
listen_port = 5766

auth_type=md5  用md5方式验证帐号密码
auth_file=存储帐号密码用的文件，指的是连接池所用到的帐号密码，用户连接进来用的
admin_users=pg 管理连接池的用户名

5，密码文件配置（auth_file）

#md5认证支持明文和md5密码。
[pg10@db01 pgbouncer]$ cat /data/pg
"appuser"    "1qaz@WSX"

6,软件启动

启动：

/usr/local/pgbouncer/bin/pgbouncer -d /data/pgbouncer/pgbouncer.ini

7，配置文件参数说明

配置后端PG数据库顶

host= 数据库IP
port= 数据库端口
dbname= 数据库名
user= 数据库帐号
password= 数据库密码
[pgbouncer]

配置连接池参数

listen_port=  连接池端口，用户连接时需要用这个端口

listen_addr=  允许连接的IP，*代表所有IP

auth_type=md5  用md5方式验证帐号密码

auth_file=存储帐号密码用的文件，指的是连接池所用到的帐号密码，用户连接进来用的

admin_users= 管理连接池的用户名

pool_mode= 指定池的模式，可以有session,transaction,statement三种模式

max_client_conn=  允许连接到pgbouncer上的最大客户端数

default_pool_size= 连接池的默认在大小

8，认证方法

支持pam,hba,md5,plain,trust,any,默认使用md5认证需要配置，密码文件配合使用。

pam
PAM is used to authenticate users, auth_file is ignored. This method is not compatible with databases using the auth_user option. The service name reported to PAM is “pgbouncer”. pam is not supported in the HBA configuration file.
hba
The actual authentication type is loaded from auth_hba_file. This allows different authentication methods for different access paths, for example: connections over Unix socket use the peer auth method, connections over TCP must use TLS.
cert
Client must connect over TLS connection with a valid client certificate. The user name is then taken from the CommonName field from the certificate.
md5
Use MD5-based password check. This is the default authentication method. auth_file may contain both MD5-encrypted and plain-text passwords. If md5 is configured and a user has a SCRAM secret, then SCRAM authentication is used automatically instead.
scram-sha-256
Use password check with SCRAM-SHA-256. auth_file has to contain SCRAM secrets or plain-text passwords.
plain
The clear-text password is sent over the wire. Deprecated.
trust
No authentication is done. The user name must still exist in auth_file.
any
Like the trust method, but the user name given is ignored. Requires that all databases are configured to log in as a specific user. Additionally, the console database allows any user to log in as admin.

9，使用 SCRAM 认证

如果pg服务器端配置为 SCRAM 密码认证，pgbouncer密码文件密码字段需要修改为 SCRAM加密字段。

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

密码文件为明文或者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 ROLEpostgres=# 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=>