背景
穷鬼玩PolarDB RAC一写多读集群系列已经写了几篇:
- 《在Docker容器中用loop设备模拟共享存储》
- 《如何搭建PolarDB容灾(standby)节点》
- 《共享存储在线扩容》
- 《计算节点 Switchover》
- 《在线备份》
- 《在线归档》
- 《实时归档》
- 《时间点恢复(PITR)》
本篇文章介绍一下如何配置读写分离工具pgpool-II for PolarDB? 实验环境依赖 《在Docker容器中用loop设备模拟共享存储》 , 如果没有环境, 请自行参考以上文章搭建环境.
还需要参考如下文档:
DEMO
下面使用实时归档机 pb4 容器进行测试, 在 pb4 中部署pgpool-II.
开源项目地址: https://www.pgpool.net/
pgpool-II 是一款开源的支持读写分离的连接池, 通过pgpool-II, 应用程序访问PolarDB 时, 可以根据SQL的特征自动路由到RW, RO节点, 实现对应用透明的读写分离功能.
1、在pb1 primary节点, 创建一个示例用户, 后面pgpool-II将配置这个用户进行使用.
$ psql postgres=# create user digoal superuser encrypted password 'pwd123' login; CREATE ROLE
2、pb4, 部署pgpool-II 软件
cd /data/ wget https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.5.5.tar.gz -O pgpool-II-4.5.5.tar.gz tar -zxvf pgpool-II-4.5.5.tar.gz cd pgpool-II-4.5.5 ./configure --prefix=/data/tools/pgpool4.5.5 --with-openssl make -j 4 make install
配置动态库和环境变量
sudo vi /etc/ld.so.conf # addd /data/tools/pgpool4.5.5/lib # 更新动态链接库 sudo ldconfig # 设置shell环境变量初始化文件 echo "export PATH=/data/tools/pgpool4.5.5/bin:\$PATH" >> ~/.bashrc # 设置当前环境变量 . ~/.bashrc
3、pb4, 配置pgpool-II
polardb 2节点+1个standby 地址如下:
primary: 172.17.0.2:5432 replica1: 172.17.0.3:5432 standby1: 172.17.0.4:5432
polardb与aurora类似, 共享存储集群模式, 无需pgpool来管理HA.
3.1、配置pgpool.conf - common配置 (开启读写分离, 但pgpool不做failover. 更多复杂配置请参考pgpool-II手册.) :
echo " listen_addresses = '0.0.0.0' port = 9999 unix_socket_directories = '/tmp' pcp_listen_addresses = 'localhost' pcp_port = 9898 pcp_socket_dir = '/tmp' log_destination = 'stderr' logging_collector = on log_directory = '/tmp/pgpool_logs' pid_file_name = '/var/run/pgpool/pgpool.pid' logdir = '/tmp' backend_clustering_mode = 'streaming_replication' load_balance_mode = on sr_check_period = 0 health_check_period = 0 failover_on_backend_shutdown=off failover_on_backend_error=off backend_hostname0 = '172.17.0.2' backend_port0 = '5432' backend_weight0 = 1 backend_application_name0 = 'polardb_primray' backend_flag0 = 'ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER' backend_hostname1 = '172.17.0.3' backend_port1 = '5432' backend_weight1 = 2 backend_application_name1 = 'polardb_replica1' backend_flag1 = 'DISALLOW_TO_FAILOVER' backend_hostname2 = '172.17.0.4' backend_port2 = '5432' backend_weight2 = 2 backend_application_name2 = 'polardb_standby1' backend_flag2 = 'DISALLOW_TO_FAILOVER' " > /data/tools/pgpool4.5.5/etc/pgpool.conf
3.2、配置pcp管理用户的密码文件pcp.conf
# 得到密码pwd123的md5, 你可以换成其他密码 postgres=# select md5('pwd123'); md5 ---------------------------------- 45cb41b32dcfb917ccd8614f1536d6da (1 row)
echo "pcpadm:45cb41b32dcfb917ccd8614f1536d6da" > /data/tools/pgpool4.5.5/etc/pcp.conf
3.3、准备pgpool运行时pid文件目录和日志目录
sudo mkdir -p /var/run/pgpool sudo chown postgres:postgres /var/run/pgpool mkdir /tmp/pgpool_logs
3.4、配置pgpool.conf - 认证配置. 有两种模式可选:
- 代理模式 (适用广、配置简单、安全性较低)
- 双重认证模式 (配置复杂、更安全)
3.4.1、代理模式配置. pgpool 不存储密码, 也不配置pool_hba.conf, 收到客户端发过来的明文密码后, 发送给给PolarDB进行认证. 这个方式好处是配置简单, 添加了数据用户后, 不需要修改pgpool的任何配置; 弊端是pgpool要知道客户端发过来的密码明文, 有泄露数据库密码的安全风险.
如果使用代理模式, 为了提高安全性, 建议客户端和pgpool之间的连接采用SSL连接, 那么传输的数据包将被加密, 攻击者无法得到数据包中的密码的明文.
配置pgpool.conf
echo " enable_pool_hba = off allow_clear_text_frontend_auth = true # 配置了这个true, 当pgpool没有配置密码文件(数据库用户+密码) 或 密码文件中没有对应用户时, 会让客户端传明文密码过来. pool_passwd = '' # 不配置密码文件, 相当于完全代理. 所有的认证都让客户端发明文密码过来. " >> /data/tools/pgpool4.5.5/etc/pgpool.conf
启动pgpool
# 启动pgpool pgpool # 停止pgpool方法如下: pgpool -m fast stop
使用这个配置模式, 用tcpdump可以抓到客户端发送给pgpool的明文密码. 例子如下, 在pb4 pgpool容器上安装tcpdump
sudo apt update sudo apt-get install -y tcpdump
开启一个pb4 shell会话, 抓发送到回环地址9999(pgpool 监听端口)的包:
sudo tcpdump -i lo dst port 9999 -w /tmp/9999.log
开启另一个pb4 shell会话, 连接pgpool执行sql:
psql -h 127.0.0.1 -p 9999 -U digoal -d postgres -c "select client_addr from pg_stat_activity where pid=pg_backend_pid();" -W Password: 输入密码 pwd123 client_addr ------------- 172.17.0.5 (1 row)
退出tcpdump抓包, 查看抓包文件, 可以看到明文密码:
$ strings /tmp/9999.log <kf@ 4kg@ <kh@ 4ki@ user digoal database postgres application_name psql client_encoding UTF8 4kk@ @kl@ pwd123 // 看到了明文密码 dgp7 4km@ 4kn@ 4ko@ 4kp@ 4kq@ 4kr@ 4ks@ 4kt@ 4ku@ 4kv@ 4kw@ 4kx@ 4ky@ 4kz@ ~k{@ Iselect client_addr from pg_stat_activity where pid=pg_backend_pid(); dg'T 4k|@ GHJW dg\T 9k}@ GHJW dgcT 4k~@ GHJ\ GHJ]
观察pgpool load balance是否生效, 每次连接分配了不一样的PolarDB instance, 说明负载均衡生效了:
export PGPASSWORD=pwd123 export PGDATABASE=postgres $ psql -p 9999 -U digoal -w psql (PostgreSQL 15.10 (PolarDB 15.10.2.0 build d4f5477d debug) on aarch64-linux-gnu) Type "help" for help. postgres=# select inet_server_addr(); inet_server_addr ------------------ 172.17.0.2 (1 row) postgres=# \q $ psql -p 9999 -U digoal -w psql (PostgreSQL 15.10 (PolarDB 15.10.2.0 build d4f5477d debug) on aarch64-linux-gnu) Type "help" for help. postgres=# select inet_server_addr(); inet_server_addr ------------------ 172.17.0.4 (1 row) postgres=# \q $ psql -p 9999 -U digoal -w psql (PostgreSQL 15.10 (PolarDB 15.10.2.0 build d4f5477d debug) on aarch64-linux-gnu) Type "help" for help. postgres=# select inet_server_addr(); inet_server_addr ------------------ 172.17.0.3 (1 row) postgres=# \q
演示 client --ssl--> pgpool-II --ssl--> PolarDB
一、配置PolarDB 支持ssl
# pb1 export PGDATA=~/primary # pb2 export PGDATA=~/replica1 # pb3 export PGDATA=~/standby
以上三个节点均需配置
# 1. 修改配置文件 echo "ssl = on" >> ${PGDATA}/postgresql.conf echo "ssl_cert_file = 'server.crt'" >> ${PGDATA}/postgresql.conf echo "ssl_key_file = 'server.key'" >> ${PGDATA}/postgresql.conf # 2. 生成自签名证书(测试环境) openssl req -new -x509 -days 365 -nodes -text \ -out ${PGDATA}/server.crt \ -keyout ${PGDATA}/server.key \ -subj "/CN=pgdbserver" # 3. 调整文件权限 chmod 600 ${PGDATA}/server.{crt,key} chown postgres:postgres ${PGDATA}/server.{crt,key} # 4. 热重载配置 psql -U postgres -c "SELECT pg_reload_conf();" # 5. 验证SSL连接 psql "sslmode=require host=localhost dbname=postgres" -c "\conninfo" You are connected to database "postgres" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
二、配置pgpool-II 支持客户端ssl 连接
export PGPOOL_CONF_DIR=/data/tools/pgpool4.5.5/etc # 生成CA证书(如果不存在) openssl req -new -x509 -days 3650 -nodes -text \ -out ${PGPOOL_CONF_DIR}/ca.crt \ -keyout ${PGPOOL_CONF_DIR}/ca.key \ -subj "/CN=pgpool-ca" # 生成服务器证书 openssl req -new -nodes -text \ -out ${PGPOOL_CONF_DIR}/server.csr \ -keyout ${PGPOOL_CONF_DIR}/server.key \ -subj "/CN=pgpool-server" openssl x509 -req -in ${PGPOOL_CONF_DIR}/server.csr \ -CA ${PGPOOL_CONF_DIR}/ca.crt \ -CAkey ${PGPOOL_CONF_DIR}/ca.key \ -CAcreateserial \ -out ${PGPOOL_CONF_DIR}/server.crt \ -days 36500 chmod 600 ${PGPOOL_CONF_DIR}/server.{crt,key} chown postgres:postgres ${PGPOOL_CONF_DIR}/server.{crt,key} echo "ssl = on ssl_cert = '${PGPOOL_CONF_DIR}/server.crt' ssl_key = '${PGPOOL_CONF_DIR}/server.key'" >> ${PGPOOL_CONF_DIR}/pgpool.conf
重启pgpool
pgpool -m fast stop pgpool
客户端也必须使用ssl连接pgpool, 才能加密网络链路中的数据包. sslmode=require
psql postgresql://digoal@127.0.0.1:9999/postgres?sslmode=require -c "\conninfo" -W Password: You are connected to database "postgres" as user "digoal" on host "127.0.0.1" at port "9999". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) $ psql postgresql://digoal@127.0.0.1:9999/postgres?sslmode=require -W Password: psql (PostgreSQL 15.10 (PolarDB 15.10.3.0 build bbc102d8 debug) on aarch64-linux-gnu) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help. postgres=# create extension sslinfo ; CREATE EXTENSION postgres=# \df *.*ssl* List of functions Schema | Name | Result data type | Argument data types | Type --------+-------------------------+------------------+-----------------------------------------------------+------ public | ssl_cipher | text | | func public | ssl_client_cert_present | boolean | | func public | ssl_client_dn | text | | func public | ssl_client_dn_field | text | text | func public | ssl_client_serial | numeric | | func public | ssl_extension_info | SETOF record | OUT name text, OUT value text, OUT critical boolean | func public | ssl_is_used | boolean | | func public | ssl_issuer_dn | text | | func public | ssl_issuer_field | text | text | func public | ssl_version | text | | func (10 rows) postgres=# select ssl_is_used(); ssl_is_used ------------- t (1 row) postgres=# select ssl_cipher(); ssl_cipher ------------------------ TLS_AES_256_GCM_SHA384 (1 row)
重新抓包
$ sudo tcpdump -i lo dst port 9999 -w /tmp/9999.log tcpdump: listening on lo, link-type EN10MB (Ethernet), snapshot length 262144 bytes
连接
$ psql postgresql://digoal@127.0.0.1:9999/postgres?sslmode=require -c "select client_addr from pg_stat_activity where pid=pg_backend_pid();" -W Password: client_addr ------------- 172.17.0.5 (1 row)
包内现在无法抓到密码了
$ strings /tmp/9999.log H:^c H:`c tVA: 6uE> tVA: Xw'N L0vh H:ic oUsg a>gW Gu|@|
更多参考文章:
- https://www.pgpool.net/docs/latest/en/html/runtime-ssl.html
- https://www.postgresql.org/docs/17/app-psql.html
- https://www.postgresql.org/docs/current/libpq-connect.html
- 《PostgreSQL openssl - multi cn 给SSL证书绑定多个CN hostname - 公网 HOSTNAME + 私网 HSOTNAME》
- 《PostgreSQL 14 preview - ssl相关 : 支持配置 ”SSL吊销证书列表文件 - crl“ 指定目录. 相关参数: ssl_crl_dir , 相关libpq配置: sslcrldir》
- 《PostgreSQL 14 preview - 支持扩展ssl库》
- 《PostgreSQL sslutils插件 - 通过SQL接口管理ssl key,crt,crl》
- 《[珍藏级] PostgreSQL ssl 证书配置 - 防止中间攻击者 - 以及如何使用证书无密码登录配置cert》
- 《PostgreSQL 12 preview ssl 协议版本范围控制参数 ssl_min_protocol_version , ssl_max_protocol_version》
- 《PostgreSQL 会话ssl状态查询 - pg_stat_ssl , sslinfo》
- 《PostgreSQL ssl - Where and when you need a root.crt file》
- 《PostgreSQL 备份链路sslcompression压缩 (openssl)》
- 《PostgreSQL cann't use NULL ciphers(do not encryption) when use hostssl?》
- 《PostgreSQL ssl ciphers performance 比较》
- 《PostgreSQL 如何实现网络压缩传输或加密传输(openssl)》
测试完成后注释这几个配置再继续后面的实验:
vi ${PGPOOL_CONF_DIR}/pgpool.conf # ssl = on # ssl_cert = '${PGPOOL_CONF_DIR}/server.crt' # ssl_key = '${PGPOOL_CONF_DIR}/server.key'
扩展问题: pgbouncer 也有代理模式和双重认证模式, 那么pgbouncer是否存在同样的问题? https://www.pgbouncer.org/config.html 请自行验证.
3.4.2、双重认证模式配置. pgpool 要存储密码. 密文是否加密、使用何种方式加密? 取决于要用什么密码认证方式(password,md5,SCRAM), 而且这个方式要和PolarDB内的配置保持一致. 我管他叫双重认证模式, 因为客户端要和pgpool 认证一次, 然后pgpool又要使用存储在pgpool服务器上的密码和PolarDB认证一次. 当新建了数据库用户时, 也要在pgpool服务器配置存储密码的文件和pool_hba.conf, 复杂度就增加了. 安全在于支持SCRAM这个最高安全模式, 可以消除中间攻击者. md5和password都不安全, 原因可参考文末文章.
下面以最高安全模式SCRAM举例 (注意一些老的client可能不兼容这个模式, 那么建议使用md5模式).
3.4.2.1、PolarDB 配置
为了兼容老的客户端, 并逐渐演变到SCRAM认证. 当pg_hba.conf配置为md5时, 可以兼容md5和SCRAM两种认证, postgresql.conf password_encryption 设置为scram-sha-256时就使用SCRAM认证, 设置为md5则使用md5.
pb1, pb2, pb3, 修改配置如下(可能默认已经是这个配置就不需要改):
vi $PGDATA/postgresql.conf password_encryption = 'scram-sha-256' psql -c "select pg_reload_conf();"
pb1, 查看PolarDB 用户 digoal 密码存储. 如果现在存储的是md5, 则需要刷新一下
postgres=# select usename,passwd from pg_shadow where usename='digoal'; usename | passwd ---------+------------------------------------- digoal | md531a770cec82aa37e217bb6e46c3f9d55 (1 row) # md5就是pwd+username算出来的 postgres=# select md5('pwd123digoal'); md5 ---------------------------------- 31a770cec82aa37e217bb6e46c3f9d55 (1 row)
pb1, 刷新PolarDB 用户 digoal 密码, 确认已使用scram存储.
postgres=# set password_encryption='scram-sha-256'; SET postgres=# alter role digoal encrypted password 'pwd123'; ALTER ROLE postgres=# select usename,passwd from pg_shadow where usename='digoal'; usename | passwd ---------+--------------------------------------------------------------------------------------------------------------------------------------- digoal | SCRAM-SHA-256$4096:9CZEDO6XFdcilseM1ZGSMA==$kBjbpajWAbV5mxUKdMCkCgycxBMMWC5/3XNFracq3jY=:aCrr9W1uRzRy/DOISCkGGXNjJFi9KqkRdFQDD4BELp0= (1 row)
确认 pb1, pb2, pb3 的 pg_hba.conf 都已经配置为md5或scram-sha-256. 如有修改请记得reload配置. 如下:
vi $PGDATA/pg_hba.conf host all all 0.0.0.0/0 md5 psql -c "select pg_reload_conf();"
3.4.2.2、pb4, 配置pgpool scram认证.
配置pgpool.conf
$ vi /data/tools/pgpool4.5.5/etc/pgpool.conf enable_pool_hba = on allow_clear_text_frontend_auth = false pool_passwd = '/data/tools/pgpool4.5.5/etc/pool_passwd'
配置pool_hba.conf
echo " # add host all all 0.0.0.0/0 scram-sha-256 " > /data/tools/pgpool4.5.5/etc/pool_hba.conf
创建用于加密数据库用户密码的密文及文件, pgpool将使用.pgpoolkey
文件中的密文来加密数据库用户密码 以及 解密存储在/data/tools/pgpool4.5.5/etc/pool_passwd
里的密文.
# 随意弄一串字符进去 echo "230923fsekksKLKLKEW922*&^#@" > ~/.pgpoolkey # 修改这个文件的权限, 必须收回group和other 的所有权限 chmod 0600 ~/.pgpoolkey
加密digoal数据库用户的密码pwd123.
$ pg_enc -m -f /data/tools/pgpool4.5.5/etc/pgpool.conf -k /home/postgres/.pgpoolkey -u digoal db password: 输入要加密的digoal用户的密码pwd123 trying to read key from file /home/postgres/.pgpoolkey 或 $ pg_enc -m -f /data/tools/pgpool4.5.5/etc/pgpool.conf -k /home/postgres/.pgpoolkey -u digoal pwd123
pg_enc将加密后的密文存储在/data/tools/pgpool4.5.5/etc/pool_passwd
这个文件里, 使用/home/postgres/.pgpoolkey
进行加解密.
$ cat /data/tools/pgpool4.5.5/etc/pool_passwd digoal:AESoI6ynLOHRoabseURe9Izeg==
/data/tools/pgpool4.5.5/etc/pool_passwd
文件内容说明:
AES开头的 是可以加解密的密文 md5开头的 是md5加密后的密码(md5(pwd+username)) TEXT开头的 是明文密码
3.4.2.3、需要重启pgpool, 启动时记得指定密钥文件
# 停止pgpool: pgpool -m fast stop # 启动pgpool , 并指定用于加解密pool_passwd的密钥文件 pgpool -k /home/postgres/.pgpoolkey
再次使用tcpdump抓包, 使用SCRAM认证方法后, 现在抓不到digoal用户的密码了
$ sudo tcpdump -i lo dst port 9999 -w /tmp/9999.log $ psql ... 连接 pgpool 代理, 完成认证, 执行一些SQL. $ strings /tmp/9999.log user digoal database postgres application_name psql client_encoding UTF8 6SCRAM-SHA-256 n,,n=,r=g0k2zq3Qm5f2KA45tgiur8T8 eg9# Cu]1 Cu]1 lc=biws,r=g0k2zq3Qm5f2KA45tgiur8T8v0syIuNdStoxARpEnnXzFT2W,p=aHZPjKai8pMd/sWzpPHbVB5x8knKMmGb2RbUdGnKzOg= Iselect client_addr from pg_stat_activity where pid=pg_backend_pid(); eg%@ eg0@ eg0C
4、使用pgbench 压测pgpool读写分离
初始化数据
export PGPASSWORD=pwd123 pgbench -i -s 10 -h 127.0.0.1 -p 9999 -U digoal postgres
压测
pgbench -n -r -P 1 -c 8 -j 8 -T 120 -S -h 127.0.0.1 -p 9999 -U digoal postgres
通过另一会话观察压测中的连接数, 因为使用了负载均衡, 可以看到每个节点都分配了连接:
export PGPASSWORD=pwd123 $ psql -h 172.17.0.2 -p 5432 -U digoal -d postgres -c "select count(*) from pg_stat_activity where application_name='pgbench';" count ------- 8 (1 row) $ psql -h 172.17.0.3 -p 5432 -U digoal -d postgres -c "select count(*) from pg_stat_activity where application_name='pgbench';" count ------- 8 (1 row) $ psql -h 172.17.0.4 -p 5432 -U digoal -d postgres -c "select count(*) from pg_stat_activity where application_name='pgbench';" count ------- 8 (1 row)
5、使用pcp管理命令查看pgpool中间件状态
pcp_node_info -U pcpadm -p 9898 -W Password: 输入 pwd123 172.17.0.2 5432 2 0.200000 up unknown primary unknown 0 none none 2024-12-19 17:26:55 172.17.0.3 5432 2 0.400000 up unknown standby unknown 0 none none 2024-12-19 17:26:55 172.17.0.4 5432 2 0.400000 up unknown standby unknown 0 none none 2024-12-19 17:26:55
pcp_node_count -U pcpadm -p 9898 -W Password: 输入 pwd123 3
pcp_pool_status -U pcpadm -h localhost -p 9898 -W Password: 输入 pwd123 ... name : backend_data_directory2 value: desc : data directory for backend #2 name : backend_status2 value: waiting desc : status of backend #2 name : standby_delay2 value: 0 desc : standby delay of backend #2 name : backend_flag2 value: DISALLOW_TO_FAILOVER desc : backend #2 flag name : backend_application_name2 value: polardb_reader2 desc : application_name for backend #2 ...
更多配置说明请参考 pgpool-II 手册.
参考
《穷鬼玩PolarDB RAC一写多读集群系列 | 在Docker容器中用loop设备模拟共享存储》
《穷鬼玩PolarDB RAC一写多读集群系列 | 如何搭建PolarDB容灾(standby)节点》
《穷鬼玩PolarDB RAC一写多读集群系列 | 共享存储在线扩容》
《穷鬼玩PolarDB RAC一写多读集群系列 | 计算节点 Switchover》
《穷鬼玩PolarDB RAC一写多读集群系列 | 在线备份》
《穷鬼玩PolarDB RAC一写多读集群系列 | 在线归档》
《穷鬼玩PolarDB RAC一写多读集群系列 | 实时归档》
《穷鬼玩PolarDB RAC一写多读集群系列 | 时间点恢复(PITR)》
《开源PolarDB|PostgreSQL 应用开发者&DBA 公开课 - 5.6 PolarDB开源版本必学特性 - 安装与使用PostgreSQL开源插件/工具》
《PolarDB 开源版 使用pgpool-II实现透明读写分离》
https://www.postgresql.org/docs/current/auth-password.html
https://www.postgresql.org/docs/current/sql-createrole.html
https://www.postgresql.org/docs/current/runtime-config-connection.html
https://www.pgpool.net/docs/45/en/html/index.html
https://www.pgpool.net/docs/45/en/html/auth-aes-encrypted-password.html
https://www.pgpool.net/docs/45/en/html/pg-enc.html