PostgreSQL 安全管理 pgaudit
1 背景知识
本章主要使用 pgaudit 插件，对数据库进行审计。
2 pgaudit 安装与配置
2.1 下载 pgaudit 源码
1、有网络的情况：上传 pgaudit 源码。（在 I:\tools_softwaer\postgresql\pgaudit\）；
2、无网络的情况下：使用git 命令下载。
cd /soft
git clone https://github.com/pgaudit/pgaudit.git
2.2 checkout 兼容 pg12 的源码
cd pgaudit/
git checkout REL_12_STABLE
//屏幕输出：
branch 'REL_12_STABLE' set up to track 'origin/REL_12_STABLE'.
Switched to a new branch 'REL_12_STABLE'
2.3 checkout 兼容 pg15 的源码
cd pgaudit/
git checkout REL_15_STABLE
2.4 编译和安装
make install USE_PGXS=1 PG_CONFIG=/usr/pg12/bin/pg_config
2.5 数据安装 pgaudit 扩展
vi $PGDATA/postgresql.conf
shared_preload_libraries='...,pgaudit'
pg_ctl restart
CREATE EXTENSION pgaudit;
3 pgaudit 审计参数
| 参数名称 | 值 | 含义 |
|---|---|---|
| pgaudit.log | READ | 审计select/copy to语句 |
| WRITE | 审计INSERT, UPDATE, DELETE, TRUNCATE, and COPY from语句 | |
| FUNCTION | 审计函数调用 | |
| ROLE | 审计DCL语句，包括GRANT, REVOKE, CREATE/ALTER/DROP ROLE | |
| DDL | 审计DDL语句，不包括dcl、truncate | |
| MISC | 审计DISCARD, FETCH, CHECKPOINT, VACUUM等语句 | |
| 多值组合 | 可以使用逗号分隔的列表来提供多种审计，如：write, ddl | |
| NONE | NONE 为默认值，都不审计 | |
| all | 所有都审计 | |
| - | 审计除…以外，如：all,-DDL，代表审计除ddl以外的所有语句 | |
| pgaudit.log_catalog | on/off | 对系统表的查询是否审计，默认值on，建议关掉 |
| pgaudit.log_relation | on/off | on时，审计所有表，该参数已经被对象审计所取代。默认关闭 |
| pgaudit.role | 审计角色。对象审计模式下，允许该角色访问的表将会被审计。可以指定多个审计角色，使不同的审计角色审计不同的内容。如：我们可以定义ddlauditor负责ddl语句的审计，dmlauditor负责dml语句的审计 |
4 非对象模式审计
这种审计方式将会审计全局数据库的SELECT和DDL 等语句。
4.1 设置参数
ALTER SYSTEM SET log_statement ='none';
SELECT pg_reload_conf();
SET pgaudit.log = 'read,ddl';
4.2 打开数据库日志
cd $PGDATA/pg_log/
tail -f postgresql-2023-09-06.csv
//屏幕输出：
4.3 执行相关语句
DROP TABLE t_user;
CREATE TABLE t_user ( id int, c_name varchar(150), c_password char(32), c_info text);
INSERT INTO t_user(id , c_name, c_password, c_info)
VALUES (1, 'postgres', md5('postgres123'), '');
SELECT * FROM t_user;
4.4 观察日志输出
2023-09-06 11:36:02.728 CST,"postgres","postgres",5005,"[local]",64f7f2f9.138d,18,"DROP TABLE",2023-09-06 11:33:13 CST,3/50,1091,LOG,00000,"AUDIT: SESSION,8,1,DDL,DROP TABLE,TABLE,public.t_user,DROP TABLE t_user;,<not logged>",,,,,,,,"log_audit_event, pgaudit.c:774","psql"
2023-09-06 11:36:12.361 CST,"postgres","postgres",5005,"[local]",64f7f2f9.138d,19,"CREATE TABLE",2023-09-06 11:33:13 CST,3/51,1092,LOG,00000,"AUDIT: SESSION,9,1,DDL,CREATE TABLE,TABLE,public.t_user,""CREATE TABLE t_user ( id int, c_name varchar(150), c_password char(32), c_info text);"",<not logged>",,,,,,,,"log_audit_event, pgaudit.c:774","psql"
2023-09-06 11:36:29.343 CST,"postgres","postgres",5005,"[local]",64f7f2f9.138d,20,"SELECT",2023-09-06 11:33:13 CST,3/53,0,LOG,00000,"AUDIT: SESSION,10,1,READ,SELECT,,,SELECT * FROM t_user;,<not logged>",,,,,,,,"log_audit_event, pgaudit.c:774","psql"
5 审计某个对象
1、对象审核模式的审计日志记录是通过角色实现的。
2、对象审计只支持增删改查的审计。
3、不支持TRUNCATE，不支持DDL/DCL等。
4、可以结合非对象审计的审计和对象审计模式下的增删改查的审计，实现 DDL审计+DML审计。
5、pgaudit.role 将某个角色进行审计。
6、对于审计角色将对有权限的表进行审计。
7、例如 pgaudit.role 设置为 auditor ，并将在t_user 表中的SELECT和DELETE权限授予 auditor ，那么针对 t_user 的查询和删除将会被记录。
5.1 设置参数
psql -U postgres -d appdb
ALTER SYSTEM SET log_statement ='none';
pg_ctl restart
psql -U postgres -d appdb
SET pgaudit.role = 'appauditor';
5.2 创建被审计的用户auitor
CREATE ROLE appauditor LOGIN;
5.3 创建表并授权 SELECT
CREATE TABLE t_user ( id int, c_name varchar(150), c_password char(32), c_info text );
GRANT SELECT (c_password) ON public.t_user To auditor;
5.4 使用 auditor 用户查询表
SELECT id, c_password FROM t_user;
SELECT c_name FROM t_user;
5.5 授权UPDAT 权限
GRANT update(c_password, c_name) ON public.t_user TO auditor;
5.6 更新表
\c - auditor
UPDATE t_user SET c_info = 'xxx' WHERE id = 1;
UPDATE t_user SET c_password = md5('test') WHERE id = 1;
6 查看日志输出
2023-09-06 12:59:54.075 CST,"auditor","postgres",5137,"[local]",64f80746.1411,1,"UPDATE",2023-09-06 12:59:50 CST,4/208,0,ERROR,42501,"permission denied for table t_user",,,,,,"UPDATE t_user SET c_info = 'xxx' WHERE id = 1;",,"aclcheck_error, aclchk.c:3492","psql"