pgAudit 的目标是为 PostgreSQL 用户提供生成审计日志的能力。
pgAudit通过标准 PostgreSQL 日志记录工具提供详细的会话和对象审计日志。
审计结果展示
原始SQL：
DO $$
BEGIN
EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;
标准日志记录：
LOG: statement: DO $$
BEGIN
EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;
pgaudit日志记录：
在动态创建表的情况下，查找感兴趣的表可能需要一些代码知识。这并不理想，因为最好只搜索表名。这就是 pgAudit 的用武之地。对于相同的输入，它将在日志中产生以下输出。
AUDIT: SESSION,33,1,FUNCTION,DO,,,"DO $$
BEGIN
EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;"
AUDIT: SESSION,33,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)
不仅记录了DO块，而且子语句 2 包含CREATE TABLE带有语句类型、对象类型、完整的对象名和完整SQL语句，以便于搜索。
在记录SELECT和DML语句时，可以将 pgAudit 配置为为语句中引用的每个关系记录一个单独的条目。无需解析即可找到涉及特定表的所有语句。
db2=# set pgaudit.log_relation='on';
db2=# select 1 from t1,ttt;
INFO: AUDIT: SESSION,7,1,READ,SELECT,TABLE,public.t1,"select 1 from t1,ttt;",<not logged>
INFO: AUDIT: SESSION,7,1,READ,SELECT,TABLE,public.ttt,"select 1 from t1,ttt;",<not logged>
?column?
----------
(0 rows)
安装pgaudit插件
以PostgreSQL 13安装pgaudit 1.5.0为例
下载并编译插件
wget https://github.com/pgaudit/pgaudit/archive/1.5.0.tar.gz
tar -zxf 1.5.0.tar.gz
cd pgaudit-1.5.0
make install USE_PGXS=1 pg_config=/opt/pgsql/bin/pg_config
添加动态库
postgresql.conf中修改shared_preload_libraries参数，启动时加载pgaudit库
shared_preload_libraries='pgaudit'
重启数据库后，创建pgaudit扩展
$ psql
psql (13.6)
Type "help" for help.
postgres=# create extension pgaudit;
开启会话审计功能
例：开启test1用户的审计模式记录所有该用户的DML和DDL操作，同时都记录DML中引用的对象以及SQL语句中的参数值。
postgres=# alter role test1 set pgaudit.log='read,ddl';
ALTER ROLE
postgres=# alter role test1 set pgaudit.log_parameter='on';
ALTER ROLE
postgres=# \c - test1
You are now connected to database "postgres" as user "test1".
postgres=>DO $$
declare
a varchar(10) :='aaa';
b int :=1;
BEGIN
EXECUTE 'CREATE TABLE import' ||a|| 'ant_table (id INT)';
select name into a from t1 where id= b;
END $$;
postgres=>
[postgres@mogdb1 log]$ tail -f postgresql-Thu.csv
2021-09-16 21:50:38.196 CST,,,17714,"[local]",61434bae.4532,1,"",2021-09-16 21:50:38 CST,,0,LOG,00000,"connection received: host=[local]",,,,,,,,,"","not initialized"
2021-09-16 21:50:38.197 CST,"test1","postgres",17714,"[local]",61434bae.4532,2,"authentication",2021-09-16 21:50:38 CST,4/699,0,LOG,00000,"connection authorized: user=test1 database=postgres application_name=psql",,,,,,,,,"","client backend"
2021-09-16 21:50:41.406 CST,"test1","postgres",17714,"[local]",61434bae.4532,3,"idle",2021-09-16 21:50:38 CST,4/700,0,LOG,00000,"statement: DO $$
declare
a varchar(10) :='aaa';
b int :=1;
BEGIN
EXECUTE 'CREATE TABLE import' ||a|| 'ant_table (id INT)';
select name into a from t1 where id= b;
END $$;",,,,,,,,,"psql","client backend"
2021-09-16 21:50:41.410 CST,"test1","postgres",17714,"[local]",61434bae.4532,4,"DO",2021-09-16 21:50:38 CST,4/700,1165,LOG,00000,"AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,test1.importaaaant_table,CREATE TABLE importaaaant_table (id INT),<none>",,,,,,,,,"psql","client backend"
2021-09-16 21:50:41.410 CST,"test1","postgres",17714,"[local]",61434bae.4532,5,"DO",2021-09-16 21:50:38 CST,4/700,1165,LOG,00000,"AUDIT: SESSION,1,2,READ,SELECT,,,select name from t1 where id= b,"",,1,""",,,,,,,,,"psql","client backend"
开启对象审计功能
对象审计模式只支持增删改查的审计。
对象审计模式是基于角色实现的，通过pgaudit.role参数来定义用于对象审计的角色。当审计角色具有执行命令的权限或有成员角色继承权限时，将审计访问的对象。
db2=> \c db2 postgres
You are now connected to database "db2" as user "postgres".
db2=# alter database db2 set pgaudit.role='user3';
ALTER DATABASE
db2=# set pgaudit.log_client=on;
db2=# set pgaudit.log_level=info;
db2=# create table public.t1 (id int,name varchar(10),comment varchar(100));
CREATE TABLE
db2=# select *from public.t1;
id | name | comment
----+------+---------
(0 rows)
db2=# grant all on table public.t1 to user3;
GRANT
db2=# insert into public.t1 values (1,'postgres','postgres');
INFO: AUDIT: OBJECT,1,1,WRITE,INSERT,TABLE,public.t1,"insert into public.t1 values (1,'postgres','postgres');",<not logged>