试试PostgreSQL日志审计插件pgaudit？

呆呆的私房菜 2024-09-01

493

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：OCP、PCP
Skill：Oracle、Mysql、PostgreSQL
Platform：CSDN、墨天伦、公众号（呆呆的私房菜）

阅读本文可以了解PostgreSQL审计插件pgaudit的功能和用法。

pgaudit概述

pgaudit是一个PostgreSQL数据库审计扩展插件，它可以提供详细的会话和对象审计日志；
原生PostgreSQL提供了log_statement=all的日志审计功能，但是粒度较粗，无法审计更细粒度的需求；
pgaudit审计插件通过在PostgreSQL 的标准日志记录工具中增加审计钩子来实现，能够记录对数据库的所有读写操作，包括 SELECT、INSERT、UPDATE、DELETE 等 SQL 命令，以及数据定义语言（DDL）操作和系统权限修改等。

pgaudit官网：https://www.pgaudit.org/
github地址：https://github.com/pgaudit/pgaudit

pgaudit版本支持的PostgreSQL主要版本如下：

pgAudit v17.X is intended to support PostgreSQL 17.
pgAudit v16.X is intended to support PostgreSQL 16.
pgAudit v1.7.X is intended to support PostgreSQL 15.
pgAudit v1.6.X is intended to support PostgreSQL 14.
pgAudit v1.5.X is intended to support PostgreSQL 13.
pgAudit v1.4.X is intended to support PostgreSQL 12.

pgaudit配置说明

1. pgaudit 参数的设置只能由超级用户修改；
2. pgaudit 支持在全局、数据库级别和角色级别指定设置；
3. pgaudit 插件必须加载到 shared_preload_libraries 中；
4. pgaudit.log 设置之前必须保证 pgaudit插件已经安装；若插件被误删且需要重新创建时，则pgaudit.log必须先取消设置，否则会引发错误。

参数名称	描述
pgaudit.log	指定会话审计日志将记录哪些语句类
pgaudit.log_catalog	指定在语句中的所有关系都在pg_catalog中的情况下应启用会话日志记录。禁用此设置将减少来自psql和pgadmin等大量查询目录的工具带来的日志噪音。默认值on
pgaudit.log_parameter	指定审计日志记录应该包含与语句传递的参数
pgaudit.log_relation	指定会话审计日志记录是否应该为select或dml语句中引用的每个关系（表、视图等）创建单独的日志项；这是在不使用对象审计日志记录的情况下进行详尽日志记录的有用快捷方式；默认值off
pgaudit.log_rows	指定审计日志记录应该包括语句检索或影响的行。启用后，行字段将包含在参数字段之后；默认值off
pgaudit.log_statement	指定日志记录是否包含语句文本和参数；一般来说审计日志可能不需要这个并且会使日志冗长；默认值on
pgaudit.logstatementonce	指定日志记录是将语句文本和参数包含在语句/子语句组合的第一个日志条目中还是包含在每个条目中；禁用该设置可以减少日志冗长程度
pgaudit.role	指定用于对象审计日志记录的角色；可以通过将它们授予主角色来定义多个审计角色；这允许多个组负责审计日志记录的不同方面
pgaudit.log_client	指定审计日志是否发送到客户端，通常应该禁用，默认值off
pgaudit.log_level	指定用于日志条目的日志级别，默认值log

pgaudit.log的值可以如下：

read：当源为关系（例如表）或查询时记录select和copy；
write：当目标是关系（例如表）时，记录insert、update、delete、truncate和copy；
function：记录函数调用和do块；
role：记录与角色和权限相关的语句，如grant、revoke、create role、alter role、drop role等；
ddl：记录所有ddl（不包括在role类）；
misc：记录其他命令，如discard、fetch、checkpoint、vacuum、set等；
misc_set：记录其他命令，如set role等；
all：包括以上所有内容。
可以使用逗号分隔以上提供的多个类，并且可以通过在类前边加上一个“-”符号来减去类。

pgaudit使用案例

好记性不如烂笔头，实操一下加深印象吧！

# 1. github下载对应源文件并上传文件到安装包下的contrib目录下
https://github.com/pgaudit/pgaudit


# 2. 解压pgaudit并编译安装
cd pg/pg16/postgresql-16.3/contrib
tar xzf pgaudit-16.0.tar.gz
cd pgaudit-16.0
make && make install


# 3. 设置shared_preload_libraries，加载扩展库
postgres=# show shared_preload_libraries;
postgres=# alter system set shared_preload_libraries = 'pgaudit';


# 4. 重启数据库生效
pg_ctl restart


# 5. 创建扩展
postgres=# create extension pgaudit;
CREATE EXTENSION
postgres=# select name, setting from pg_settings where name like 'pgaudit%';
              name              | setting
--------------------------------+---------
 pgaudit.log                    | none
 pgaudit.log_catalog            | on
 pgaudit.log_client             | off
 pgaudit.log_level              | log
 pgaudit.log_parameter          | off
 pgaudit.log_parameter_max_size | 0
 pgaudit.log_relation           | off
 pgaudit.log_rows               | off
 pgaudit.log_statement          | on
 pgaudit.log_statement_once     | off
 pgaudit.role                   |


# 6. 配置开启审计（分为会话审计和对象审计）
# 6.1 配置会话审计
postgres=# set pgaudit.log = 'write,ddl';
postgres=# set pgaudit.log_relation = on;
postgres=# set pgaudit.log_client = on;
postgres=# select name, setting from pg_settings where name like 'pgaudit%';
              name              |  setting
--------------------------------+-----------
 pgaudit.log                    | write,ddl
 pgaudit.log_catalog            | on
 pgaudit.log_client             | on
 pgaudit.log_level              | log
 pgaudit.log_parameter          | off
 pgaudit.log_parameter_max_size | 0
 pgaudit.log_relation           | on
 pgaudit.log_rows               | off
 pgaudit.log_statement          | on
 pgaudit.log_statement_once     | off
 pgaudit.role                   |


postgres=# create table chen1 (id int, name varchar(10));
CREATE TABLE
postgres=# insert into chen1 values (1, 'chen1');
INSERT 0 1
postgres=# select * from chen1;
 id | name
----+-------
  1 | chen1
(1 行记录)


# 观察数据库日志，可以看到create\insert语句都记录到日志中了
2024-08-30 18:24:04.620 CST,"pg16","postgres",2654,"[local]",66d19c7f.a5e,4,"idle",2024-08-30 18:18:39 CST,3/21,0,日志,00000,"语句: create table chen1 (id int, name varchar(10));",,,,,,,,"exec_simple_query, postgres.c:1074","psql","client backend",,0
2024-08-30 18:24:04.644 CST,"pg16","postgres",2654,"[local]",66d19c7f.a5e,5,"CREATE TABLE",2024-08-30 18:18:39 CST,3/21,1067,日志,00000,"AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.chen1,""create table chen1 (id int, name varchar(10));"",<not logged>",,,,,,,,"log_audit_event, pgaudit.c:822","psql","client backend",,0
2024-08-30 18:24:15.772 CST,"pg16","postgres",2654,"[local]",66d19c7f.a5e,6,"INSERT",2024-08-30 18:18:39 CST,3/22,0,日志,00000,"AUDIT: SESSION,2,1,WRITE,INSERT,TABLE,public.chen1,""insert into chen1 values (1, 'chen1');"",<not logged>",,,,,,,,"log_audit_event, pgaudit.c:822","psql","client backend",,0


# 6.2 对象审计日志记录
# 对象审计日志旨在成为pgaudit.log='read,write'的细粒度替代；
# 对象级审计日志是通过角色系统实现的，当审计角色对执行的命令具有权限或从另一个角色继承权限时，将记录一个关系（表、视图等）；
# 这允许我们有效的拥有多个审计角色，即使上下文中只有一个主角色
postgres=# create role auditor with password 'auditor_pwd';
CREATE ROLE
postgres=# set pgaudit.log = '';
SET
postgres=# set pgaudit.role = 'auditor';
SET
postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 行记录)
postgres=# select name, setting from pg_settings where name like 'pgaudit%';
              name              | setting
--------------------------------+---------
 pgaudit.log                    |
 pgaudit.log_catalog            | on
 pgaudit.log_client             | on
 pgaudit.log_level              | log
 pgaudit.log_parameter          | off
 pgaudit.log_parameter_max_size | 0
 pgaudit.log_relation           | on
 pgaudit.log_rows               | off
 pgaudit.log_statement          | on
 pgaudit.log_statement_once     | off
 pgaudit.role                   | auditor
(11 行记录)
postgres=# create table chen2(id int);
CREATE TABLE
postgres=# grant select, delete on public.chen2 to auditor;
GRANT
postgres=# select * from information_schema.role_table_grants where grantee = 'auditor';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
 pg16    | auditor | postgres      | public       | chen2      | SELECT         | NO           | YES
 pg16    | auditor | postgres      | public       | chen2      | DELETE         | NO           | NO
(2 行记录)


postgres=# create table chen2(id int);
CREATE TABLE
postgres=# grant select, delete on public.chen2 to auditor;
GRANT
postgres=# select * from information_schema.role_table_grants where grantee = 'auditor';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
 pg16    | auditor | postgres      | public       | chen2      | SELECT         | NO           | YES
 pg16    | auditor | postgres      | public       | chen2      | DELETE         | NO           | NO
(2 行记录)


postgres=# insert into chen2 values (1);
INSERT 0 1
postgres=# select * from chen2;
 id
----
  1
postgres=# delete from chen2;
DELETE 1
# 可以看到，日志中记录了select和update操作的日志
2024-08-30 18:34:40.817 CST,"pg16","postgres",2654,"[local]",66d19c7f.a5e,11,"SELECT",2024-08-30 18:18:39 CST,3/42,0,日志,00000,"AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.chen2,select * from chen2;,<not logged>",,,,,,,,"log_audit_event, pgaudit.c:822","psql","client backend",,0
2024-08-30 18:35:03.546 CST,"pg16","postgres",2654,"[local]",66d19c7f.a5e,13,"DELETE",2024-08-30 18:18:39 CST,3/44,0,日志,00000,"AUDIT: OBJECT,4,1,WRITE,DELETE,TABLE,public.chen2,delete from chen2;,<not logged>",,,,,,,,"log_audit_event, pgaudit.c:822","psql","client backend",,0

本文内容就到这啦，阅读完本篇，相信你对pgaudit日志审计插件相关知识有了一定的认识了吧！我们下篇再见！

点击上方公众号，关注我吧！

文章转载自呆呆的私房菜，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

试试PostgreSQL日志审计插件pgaudit？

评论