Postgresql审计：pgaudit安装使用

Postgresql:10
pgaudit:v1.2

编译安装

1、clone插件

git clone https://github.com/pgaudit/pgaudit.git

2、Change to pgAudit directory:

cd pgaudit

3、Checkout postgresql 10 branch

git checkout REL_10_STABLE

4、编译安装

make install USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/10/bin/pg_config

备注：PG_CONFIG路径

5、修改postgresql.conf配置

修改添加：shared_preload_libraries = 'pgaudit'

6、重启postgresql

systemctl restart postgresql@10-main.service

7、create extension

postgres=# create extension pgaudit;

参数配置说明

可配置参数

postgres=# select name,setting from pg_settings where name like '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.

postgres=# set pgaudit.log = READ,DDL;

pgaudit.log显示如下：

pgaudit.log_client:

客户端打印审计日志，例如psql

pgaudit.log_level:

日志级别：Postgresql Log Level，与pgaudit.log_client配合使用。

pgaudit.log_parameter:

指定审计日志记录应包括与语句一起传递的参数。当参数存在时，它们将包含在CSV语句文本之后的格式中，默认值为off。

pgaudit.role:

配置日志审计主角色，可以通过配置多个角色，分别负责不通对象的审计。

set pgaudit.role = 'postgresq,nexttao';

grant select
   on public.test
   to nexttao;

参考

https://github.com/pgaudit/pgaudit