PostgreSQL审计插件之pgAudit

pgAudit插件地址

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';
SET 
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 $$; 
DO
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;
SET
db2=# set pgaudit.log_level=info;
SET
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>
INSERT 0 1