PG数据库如何通过插件pgaudit做审计

一 pgaudit和postgresql版本兼容性匹配列表

pgAudit v1.5.X is intended to support PostgreSQL 13

pgAudit v1.4.X is intended to support PostgreSQL 12

pgAudit v1.3.X is intended to support PostgreSQL 11

二 安装插件pgaudit-1.4.1

下载zip包上传后解压

https://github.com/pgaudit/pgaudit/archive/refs/tags/1.4.1.zip

unzip pgaudit-1.4.1.zip

指定pg_config安装

cd pgaudit-1.4.1/

make install USE_PGXS=1 PG_CONFIG=/var/postgresql/soft/pg12.8/bin/pg_config

注意：pg_config替换成对应的绝对路径

扩展插件

参数文件postgresql.conf参数shared_preload_libraries新增pgaudit

参考pg_auto_failover高可用架构维护操作如下

https://vip.kingdee.com/article/296690452542608128

cat postgresql.conf |grep shared_preload_libraries

重启数据库服务

systemctl restart postgresql

扩展pgaudit

create extension pgaudit

三 参数说明

select name,setting from pg_settings where name ~ 'pgaudit'

pgaudit.log指定要审计的操作如下

READ: SELECT and COPY when the source is a relation or a query.

WRITE: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.

FUNCTION: Function calls and DO blocks.

ROLE: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.

DDL: All DDL that is not included in the ROLE class.

MISC: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM, SET.

MISC_SET: Miscellaneous SET commands, e.g. SET ROLE.

ALL: Include all of the above.

pgaudit.log_catalog

指定所有对象都在pg_catalog中时，是否记录到审计日志。如果禁用此设置，将减少psql和pgadmin等工具在大量查询时的干扰。

pgaudit.log_client

指定日志消息对客户端(如psql)是否可见，默认值是off。

注意: pgaudit.log_level仅在pgaudit.log_client打开时才启用。

pgaudit.log_level

指定用于审计的日志级别，不允许error,fatal和panic。

注意：pgaudit.log_level只在pgaudit.log_client打开时启用，否则将使用默认值log。

pgaudit.log_level 取值如下