作者：IT邦德
中國DBA聯(lián)盟(ACDU)成員，10余年DBA工作經(jīng)驗(yàn)，
Oracle、PostgreSQL ACE
CSDN博客專家及B站知名UP主，全網(wǎng)粉絲10萬+
擅長主流Oracle、MySQL、PG、高斯及Greenplum備份恢復(fù)，
安裝遷移，性能優(yōu)化、故障應(yīng)急處理
微信:jem_db
QQ交流群:587159446
公眾號(hào)：IT邦德

前言

PostgreSQL運(yùn)維的使用總結(jié)，只談使用經(jīng)驗(yàn)，不聊原理

📣 1.DBA那些辛酸的過往

數(shù)據(jù)庫問題排查一天，被 Diss 排查問題慢…
核心表誤刪除數(shù)據(jù)，手足無措，看是哪個(gè)家伙寫的，竟然是…
客戶線上部署PG，應(yīng)該如何完善體系化，讓客戶dis嗎？
故障排查，問題還沒有找到，頭頂?shù)臒魠s早已照亮了整層樓…
PostgreSQL運(yùn)維，一些必要的插件安裝是非常重要的

📣 2.安全加固插件

? passwordcheck插件

PostgreSQL數(shù)據(jù)庫密碼復(fù)雜度設(shè)置可以通過安裝passwordcheck擴(kuò)展插件來實(shí)現(xiàn)，該插件默認(rèn)的密碼復(fù)雜度規(guī)則是密碼長度必須大于等于8、必須包含字母和非字母、密碼不能包含用戶名。如果這些規(guī)則仍然不能滿足你的密碼強(qiáng)度要求，那么還可以安裝cracklib以及字典來提高密碼強(qiáng)度。

? 2.1 修改規(guī)則

默認(rèn)密碼復(fù)雜度規(guī)則：
1.密碼長度必須大于等于8
2.必須包含字母和非字母
3.密碼不能包含用戶名

可根據(jù)實(shí)際需要更改最小密碼長度，默認(rèn)值為8，
建議更改為10或更大。
注意：此處的#號(hào)并非注釋，不要去掉。

cd /pgccc/soft/postgresql-15.6/contrib/
cd passwordcheck
編輯修改passwordcheck.c文件中的MIN_PWD_LENGTH

/* passwords shorter than this
will be rejected */
#define MIN_PWD_LENGTH 10

? 2.2 編譯passwordcheck

–使用make命令編譯安裝插件。
cd /pgccc/soft/postgresql-15.6/contrib/passwordcheck
make && make install

? 2.3 測試檢查

–修改參數(shù)
alter system set shared_preload_libraries=‘passwordcheck’;
–重啟PG
pg_ctl restart
–密碼校驗(yàn)
postgres=# ALTER USER postgres WITH PASSWORD ‘postgres’;
ERROR: password is too short
postgres=# create user pgtest password ‘pgtest1234’;
ERROR: password must not contain user name
postgres=# create user pgtest password ‘pg12345678’;
CREATE ROLE

📣 3.審計(jì)插件

? pgaudit插件

能夠提供詳細(xì)的會(huì)話和對(duì)象審計(jì)日志，是PG的一個(gè)擴(kuò)展插件。pgAudit通過標(biāo)準(zhǔn)PostgreSQL日志記錄工具提供詳細(xì)的會(huì)話和/或?qū)ο髮徍巳罩居涗洝?/p>

? 3.1 pgaudit安裝

官網(wǎng)：
https://www.pgaudit.org/下載安裝包一定要注意pgaudit與PG版本的對(duì)應(yīng)
wget https://codeload.github.com/pgaudit/pgaudit/tar.gz/refs/tags/1.6.2 -O pgaudit-1.6.2.tar.gztar -xzvf 1.6.2.tar.gz
cd pgaudit-1.6.2/
make install USE_PGXS=1安裝插件
alter system set shared_preload_libraries='pgaudit';
pg_ctl restart
create extension pgaudit;
\dx
select name,setting from pg_settings where name like 'pgaudit%';
select * from pg_available_extensions where name like '%audit%';
show shared_preload_libraries;

? 3.2 開啟審計(jì)

1.會(huì)話審計(jì)日志記錄
會(huì)話審計(jì)日志提供用戶在后端執(zhí)行的所有語句的詳細(xì)日志。
使用pgaudit.log設(shè)置啟用會(huì)話日志記錄。set pgaudit.log = 'write, ddl';
set pgaudit.log_relation = on;
set pgaudit.log_client=on;
SELECT pg_reload_conf();
select name,setting,source from pg_settings where name like 'pgaudit%';postgres=> create table t_jeames(id int);
postgres=> insert into t_jeames select generate_series(1,10000);
postgres=> delete  from t_jeames;
postgres=# drop table t_jeames;

2.對(duì)象審計(jì)日志記錄
通過創(chuàng)建role 來實(shí)現(xiàn)， 
原理就是把想要審計(jì)的對(duì)象的具體操作賦權(quán)給一個(gè) role， 
然后在設(shè)置 pgaudit.role 。目前只能支持， SELECT, INSERT, UPDATE and DELETE 這4中類型，
相對(duì)于 read, write 來說更細(xì)粒度了--創(chuàng)建角色: audit_account
create role audit_account password 'audit_account';
設(shè)置對(duì)象 t_jeames 的insert, update, delete.select 為審計(jì)行為postgres=# alter system set pgaudit.role = 'audit_account';
postgres=# grant select,insert,update,delete on t_jeames to audit_account;
postgres=# select pg_reload_conf();--修改數(shù)據(jù)
postgres=# delete from t_jeames where id < 5000;
postgres=# update t_jeames set id = 1000+ id ;
postgres=# select * from t_jeames limit 2;

📣 4.遠(yuǎn)程訪問插件

? postgres_fdw插件

通過 postgres_fdw訪問遠(yuǎn)程PostgreSQL數(shù)據(jù)庫表。
步驟如下：
1.在源端（本地庫）創(chuàng)建 postgres_fdw 插件
2.創(chuàng)建 foreign server 外部服務(wù)(即：指連接外部數(shù)據(jù)源的連接信息)
3.創(chuàng)建映射用戶（映射用戶指定了訪問外部表的本地用戶和遠(yuǎn)程用戶信息）
4.創(chuàng)建外部表（外部表的表定義建議和遠(yuǎn)端表結(jié)構(gòu)一致）

? 4.1 創(chuàng)建安裝插件

本地庫做如下的操作：
–編譯安裝
[postgres@centos79 ~]$ cd /pgccc/soft/postgresql-15.6/contrib
[postgres@centos79 contrib]$ cd postgres_fdw
[postgres@centos79 postgres_fdw]$ make install
–再次確認(rèn)插件
[root@centos79 ~]# cd /pgccc/pgsql-15/share/extension

–postgres 超級(jí)用戶登錄 PostgreSQL
[postgres@centos79 ~]$ psql
postgres=# CREATE EXTENSION postgres_fdw;
postgres=# \dx

? 4.2 權(quán)限配置

本地庫做如下的操作：
若使用超級(jí)用戶使用postgres_fdw可以跳過
普通用戶使用postgres_fdw需要單獨(dú)授權(quán)
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO pgtest;

? 4.3 創(chuàng)建外部服務(wù)

本地庫做如下的操作：
外部服務(wù)定義了遠(yuǎn)端PostgreSQL數(shù)據(jù)庫的IP、端口、數(shù)據(jù)庫連接信息
CREATE SERVER fdwpg2 FOREIGN DATA WRAPPER
postgres_fdw OPTIONS (host ‘pg2host’, port ‘5432’, dbname ‘devdb’);

? 4.4 創(chuàng)建映射用戶

本地庫做如下的操作：
映射用戶指定了連接源端 PostgreSQL 數(shù)據(jù)庫的用戶名和密碼信息
CREATE USER MAPPING FOR pgtest
SERVER fdwpg2 OPTIONS (user 'pg2user', password 'pg2user');FOR:配置的用戶為本地的數(shù)據(jù)庫用戶
OPTIONS ：配置的是遠(yuǎn)端PostgreSQL數(shù)據(jù)庫的用戶和密碼

? 4.5 創(chuàng)建外部表

–遠(yuǎn)端數(shù)據(jù)庫創(chuàng)建測試表
CREATE TABLE pg2_fdw (id int4 ,info text) ;
INSERT INTO pg2_fdw (id , info ) VALUES (1, ‘a(chǎn)’),(2, ‘b’);

—?jiǎng)?chuàng)建外部表(本地庫)
CREATE FOREIGN TABLE ft_fdw (
id int4,
info text
) SERVER fdwpg2 OPTIONS (schema_name ‘pg2user’, table_name ‘pg2_fdw’);

通過外部表訪問遠(yuǎn)端數(shù)據(jù)表
注意：遠(yuǎn)端數(shù)據(jù)庫pg_hba.conf文件需要允許本地庫訪問策略
select * from pg2_fdw;

📣 5.推薦插件

📣 6.總結(jié)

PostgreSQL 提供使用 extension 的方式來擴(kuò)展數(shù)據(jù)庫的功能,您可以發(fā)現(xiàn)，PostgreSQL的許多功能也都通過插件的形式完成，也正是由于使用的插件的形式，使得這些插件功能基本不受PostgreSQL核心升級(jí)的影響，這也是PostgreSQL數(shù)據(jù)庫能夠持續(xù)發(fā)展的一個(gè)重要的原因