Postgresql审计插件pgaudit使用说明

最近由于审计，rds的审计成了问题，因为很多权限没开放出来，但是很多云厂商提供了pgaudit插件，这里简单介绍下吧。

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

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 v1.3.X is intended to support PostgreSQL 11.

pgAudit v1.2.X is intended to support PostgreSQL 10.

pgAudit v1.1.X is intended to support PostgreSQL 9.6.

pgAudit v1.0.X is intended to support PostgreSQL 9.5.

编译安装：

#git克隆，这里以1.4.1为例
git clone https://github.com/pgaudit/pgaudit.git
#或者直接下载zip包
https://github.com/pgaudit/pgaudit/archive/refs/tags/1.4.1.zip
#解压
unzip pgaudit-1.4.1.zip
#安装，因为我这里配置$PGHOME，PATH等环境变量，可以直接安装
make install USE_PGXS=1
或者指定PG_CONFIG
make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-13/bin/pg_config


gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pgaudit.o pgaudit.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pgaudit.so pgaudit.o  -L/opt/pgsql/lib    -Wl,--as-needed -Wl,-rpath,'/opt/pgsql/lib',--enable-new-dtags  
/usr/bin/mkdir -p '/opt/pgsql/lib'
/usr/bin/mkdir -p '/opt/pgsql/share/extension'
/usr/bin/mkdir -p '/opt/pgsql/share/extension'
/usr/bin/install -c -m 755  pgaudit.so '/opt/pgsql/lib/pgaudit.so'
/usr/bin/install -c -m 644 .//pgaudit.control '/opt/pgsql/share/extension/'
/usr/bin/install -c -m 644 .//pgaudit--1.4.1.sql .//pgaudit--1.4--1.4.1.sql  '/opt/pgsql/share/extension/'

#修改shared_preload_libraries加入pgaudit后重启实例，其他插件请忽略
shared_preload_libraries = 'pg_stat_monitor,repmgr,pglogical,shutdown_db,pg_pathman,pg_stat_statements,pgaudit'
#进入数据库，使用超级用户创建插件
create extension pgaudit ;

参数说明：

<15:43:31><db:postgres><user:postgres><pid:25390>=# select name,setting from pg_settings where name ~ 'pgaudit';
            name            │      setting      
────────────────────────────┼───────────────────
 pgaudit.log                │ WRITE,ROLE,DDL
 pgaudit.log_catalog        │ off
 pgaudit.log_client         │ on
 pgaudit.log_level          │ log
 pgaudit.log_parameter      │ off
 pgaudit.log_relation       │ off
 pgaudit.log_statement_once │ off
 pgaudit.role               │ aaaaaaaaaaaaaaaaa
 
#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_parameter
指定审计日志记录包括语句一起传递的参数。当参数出现时，它们将以CSV格式包含在语句文本之后，默认off

#pgaudit.log_relation
指定会话审计日志是否应该为SELECT或DML语句中引用的每个关系对象(TABLE、VIEW等)创建单独的日志条目。这是在不使用对象审计日志记录的情况下进行详尽日志记录的一种有用的快捷方式。

#pgaudit.log_statement_once
翻译出来太难理解，有兴趣的可以自己实验一下，默认off

#pgaudit.role
最容易迷惑的就是这个参数，最开始以为是指定role的，其实是指定记录具有相关对象权限的role，一会看实验就明白了，没有默认值。

实例：

#配置pgaudit.log为WRITE,ROLE,DDL，总之根据自己需求配置即可，这个配置是全局的，就算其他库没有创建插件，也会有审计日志。
<16:14:50><db:postgres><user:postgres><pid:27628>=# create table t1(a int);
CREATE TABLE
<16:15:00><db:postgres><user:postgres><pid:27628>=# insert into t1 values (1);
INSERT 0 1
<16:15:08><db:postgres><user:postgres><pid:27628>=# update t1  set a=2;
UPDATE 1
<16:15:20><db:postgres><user:postgres><pid:27628>=# delete from t1;
DELETE 1
<16:15:25><db:postgres><user:postgres><pid:27628>=# drop table t1;
DROP TABLE

#审计日志如下：
2022-03-28 16:15:00.704 CST,"postgres","postgres",27628,"[local]",62416e32.6bec,3,"CREATE TABLE",2022-03-28 16:13:38 CST,1/3638,196851,LOG,00000,"AUDIT: SESSION,2,1,DDL,CREATE TABLE,,,create table t1(a int);,<not logged>",,,,,,,,,"psql"
2022-03-28 16:15:08.135 CST,"postgres","postgres",27628,"[local]",62416e32.6bec,4,"INSERT",2022-03-28 16:13:38 CST,1/3639,0,LOG,00000,"AUDIT: SESSION,3,1,WRITE,INSERT,,,insert into t1 values (1);,<not logged>",,,,,,,,,"psql"
2022-03-28 16:15:20.594 CST,"postgres","postgres",27628,"[local]",62416e32.6bec,5,"UPDATE",2022-03-28 16:13:38 CST,1/3640,0,LOG,00000,"AUDIT: SESSION,4,1,WRITE,UPDATE,,,update t1  set a=2;,<not logged>",,,,,,,,,"psql"
2022-03-28 16:15:25.224 CST,"postgres","postgres",27628,"[local]",62416e32.6bec,6,"DELETE",2022-03-28 16:13:38 CST,1/3641,0,LOG,00000,"AUDIT: SESSION,5,1,WRITE,DELETE,,,delete from t1;,<not logged>",,,,,,,,,"psql"
2022-03-28 16:15:32.957 CST,"postgres","postgres",27628,"[local]",62416e32.6bec,7,"DROP TABLE",2022-03-28 16:13:38 CST,1/3642,196855,LOG,00000,"AUDIT: SESSION,6,1,DDL,DROP TABLE,,,drop table t1;,<not logged>",,,,,,,,,"psql"

#就算不没安装插件的库，也会有记录，如下：
<16:15:32><db:postgres><user:postgres><pid:27628>=# \c logic logic 
You are now connected to database "logic" as user "logic".
<16:16:48><db:logic><user:logic><pid:27628>=> \dx
                 List of installed extensions
  Name   │ Version │   Schema   │         Description          
─────────┼─────────┼────────────┼──────────────────────────────
 plpgsql │ 1.0     │ pg_catalog │ PL/pgSQL procedural language
(1 row)

<16:16:49><db:logic><user:logic><pid:27628>=> create table t1(a int);
CREATE TABLE
<16:16:53><db:logic><user:logic><pid:27628>=> insert into t1 values (1);
INSERT 0 1
<16:17:07><db:logic><user:logic><pid:27628>=>  update t1  set a=2;
UPDATE 1
<16:17:09><db:logic><user:logic><pid:27628>=>  delete from t1;

2022-03-28 16:16:53.961 CST,"logic","logic",27867,"[local]",62416ef0.6cdb,1,"CREATE TABLE",2022-03-28 16:16:48 CST,5/2333,196856,LOG,00000,"AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,create table t1(a int);,<not logged>",,,,,,,,,"psql"
2022-03-28 16:17:07.483 CST,"logic","logic",27867,"[local]",62416ef0.6cdb,2,"INSERT",2022-03-28 16:16:48 CST,5/2334,0,LOG,00000,"AUDIT: SESSION,2,1,WRITE,INSERT,,,insert into t1 values (1);,<not logged>",,,,,,,,,"psql"
2022-03-28 16:17:09.937 CST,"logic","logic",27867,"[local]",62416ef0.6cdb,3,"UPDATE",2022-03-28 16:16:48 CST,5/2335,0,LOG,00000,"AUDIT: SESSION,3,1,WRITE,UPDATE,,,update t1  set a=2;,<not logged>",,,,,,,,,"psql"
2022-03-28 16:17:12.024 CST,"logic","logic",27867,"[local]",62416ef0.6cdb,4,"DELETE",2022-03-28 16:16:48 CST,5/2336,0,LOG,00000,"AUDIT: SESSION,4,1,WRITE,DELETE,,,delete from t1;,<not logged>",,,,,,,,,"psql"

pgaudit.role是容易混淆的一个参数，实例如下：

#修改参数为以下设置，全局审计DDL,并设置pgaudit.role为hank,连接logic库，创建表
<16:24:03><db:postgres><user:postgres><pid:28433>=# show pgaudit.log;
 pgaudit.log 
─────────────
 DDL
(1 row)

<16:24:07><db:postgres><user:postgres><pid:28433>=# show pgaudit.role;
 pgaudit.role 
──────────────
 hank
(1 row)
<16:24:10><db:postgres><user:postgres><pid:28433>=# \c logic logic 

<16:25:45><db:logic><user:logic><pid:28433>=> create table t1 (a int);
CREATE TABLE
<16:25:53><db:logic><user:logic><pid:28433>=> \z t1
                            Access privileges
 Schema │ Name │ Type  │ Access privileges │ Column privileges │ Policies 
────────┼──────┼───────┼───────────────────┼───────────────────┼──────────
 logic  │ t1   │ table │                   │                   │ 
(1 row)
#插入一条记录，因为全局设置的是DDL，所以审计不会有记录
<16:26:01><db:logic><user:logic><pid:28433>=> insert into t1 values (1);
INSERT 0 1
#但是把表的所有权限赋予hank后，再插入一条记录，虽然全局是DDL,但是还是会有DML审计日志。
<16:26:47><db:logic><user:logic><pid:28433>=> grant all on t1  to hank;
GRANT
<16:27:24><db:logic><user:logic><pid:28433>=> insert into t1 values (2);
INSERT 0 1
2022-03-28 16:27:30.886 CST,"logic","logic",28570,"[local]",62417109.6f9a,2,"INSERT",2022-03-28 16:25:45 CST,5/2445,0,LOG,00000,"AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,logic.t1,insert into t1 values (2);,<not logged>",,,,,,,,,"psql"

#官方的例子也有说明，只是颗粒度更细一些，精确到了具体的列和具体的操作，所以这里不是我想要的针对role的审计，而是针对对象有相关权限role的审计，一定要注意。

另外把一个role赋予另外一个审计配置的role,作用效果和上面例子一样，这种是在pgaudit.role不能修改的情况下使用，如下：

#其他保持不变，配置pgaudit.role为auditor，这里假设我们pgaudit.role参数不能修改，有些云没有开放该参数修改权限，但是我们可以把要审计的role赋予auditor

<16:38:35><db:logic><user:logic><pid:29416>=> show pgaudit.role ;
 pgaudit.role 
──────────────
 auditor
(1 row)

<16:40:14><db:logic><user:logic><pid:29416>=> show pgaudit.log ;
 pgaudit.log 
─────────────
 DDL
(1 row)
<16:37:26><db:logic><user:postgres><pid:29416>=# \c  logic logic 
You are now connected to database "logic" as user "logic".
<16:37:30><db:logic><user:logic><pid:29416>=> create table t1 (a int);
CREATE TABLE
<16:37:48><db:logic><user:logic><pid:29416>=> insert into t1 values (1);
INSERT 0 1
<16:38:02><db:logic><user:logic><pid:29416>=> \z  t1
                            Access privileges
 Schema │ Name │ Type  │ Access privileges │ Column privileges │ Policies 
────────┼──────┼───────┼───────────────────┼───────────────────┼──────────
 logic  │ t1   │ table │                   │                   │ 
(1 row)

<16:38:07><db:logic><user:logic><pid:29416>=>  grant all on t1  to hank;
GRANT
<16:38:14><db:logic><user:logic><pid:29416>=> insert into t1 values (2);
INSERT 0 1
<16:38:18><db:logic><user:logic><pid:29416>=> \z t1
                             Access privileges
 Schema │ Name │ Type  │  Access privileges  │ Column privileges │ Policies 
────────┼──────┼───────┼─────────────────────┼───────────────────┼──────────
 logic  │ t1   │ table │ logic=arwdDxt/logic↵│                   │ 
        │      │       │ hank=arwdDxt/logic  │                   │ 
(1 row)
#可见除了create,也有一条insert在审计日志中
2022-03-28 16:37:48.447 CST,"logic","logic",29477,"[local]",624173ca.7325,1,"CREATE TABLE",2022-03-28 16:37:30 CST,1/3971,196869,LOG,00000,"AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,create table t1 (a int);,<not logged>",,,,,,,,,"psql"
2022-03-28 16:38:18.247 CST,"logic","logic",29477,"[local]",624173ca.7325,2,"INSERT",2022-03-28 16:37:30 CST,1/3975,0,LOG,00000,"AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,logic.t1,insert into t1 values (2);,<not logged>",,,,,,,,,"psql"

根据需要也可以设置数据库级别或者role级别的参数：

#如下，全局关闭审计，只设置hank用户审计所有操作，可以看到审计日志已记录hank的相关操作
postgres=# show pgaudit.log;
 pgaudit.log 
-------------
 NONE
postgres=# alter role hank SET pgaudit.log = 'ALL';
ALTER ROLE
postgres=# \c  hank hank 
hank=> create table t1 (a int);
CREATE TABLE
hank=> insert into t1 values (1);
INSERT 0 1
hank=> delete from t1 ;
DELETE 1
hank=> drop table t1;
DROP TABLE

2022-03-30 09:43:57.384 CST,"hank","hank",20549,"[local]",6243b5d3.5045,1,"CREATE TABLE",2022-03-30 09:43:47 CST,5/3809,196882,LOG,00000,"AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,hank.t1,create table t1 (a int);,<not logged>",,,,,,,,,"psql"
2022-03-30 09:44:07.771 CST,"hank","hank",20549,"[local]",6243b5d3.5045,2,"INSERT",2022-03-30 09:43:47 CST,5/3810,0,LOG,00000,"AUDIT: SESSION,2,1,WRITE,INSERT,,,insert into t1 values (1);,<not logged>",,,,,,,,,"psql"
2022-03-30 09:44:12.589 CST,"hank","hank",20549,"[local]",6243b5d3.5045,3,"DELETE",2022-03-30 09:43:47 CST,5/3811,0,LOG,00000,"AUDIT: SESSION,3,1,WRITE,DELETE,,,delete from t1 ;,<not logged>",,,,,,,,,"psql"
2022-03-30 09:44:16.684 CST,"hank","hank",20549,"[local]",6243b5d3.5045,9,"DROP TABLE",2022-03-30 09:43:47 CST,5/3812,196885,LOG,00000,"AUDIT: SESSION,4,6,DDL,DROP TABLE,TABLE,hank.t1,drop table t1;,<not logged>",,,,,,,,,"psql"

#使用postgres用户，则没有记录审计日志
hank=> \c postgres postgres 
psql (13.6, server 12.6)
You are now connected to database "postgres" as user "postgres".
postgres=# \dt t1
Did not find any relation named "t1".
postgres=# create table t1 (a int);
CREATE TABLE
postgres=# drop table t1;
DROP TABLE

#相关设置审计命令如下：
ALTER SYSTEM SET pgaudit.log = '…' 或者修改postgresql.conf 文件，此设置为实例全局有效
ALTER DATABASE xxx SET pgaudit.log = '…'  此设置对单个库有效
ALTER ROLE xxx SET pgaudit.log = '…'   此设置对单个role有效

参考：

https://github.com/pgaudit/pgaudit/blob/master/README.md