PG 安全之审计

大家好，今天大表哥要和大家分享的是PG 安全之审计方面的内容。

由于所在公司是金融行业的原因，生产使用的数据库要具备审计功能并且适当打开审计功能，才能满足公司的合规要求。 对于传统的商业软件像是ORACLE的企业版是自带audit 功能的。开源的数据库像是mysql,mongo 的percona 版本也是有相应的审计插件。 当然了，企业版的mysql和mongo 也是同样自带审计功能。

熟悉PG的同学们都知道，PG原生的log参数是十分丰富的。

log_statement  ： 用来记录操作语句的类型： 目前只支持这4种 none, ddl, mod, all

log_line_prefix:  设置日志打印的前缀信息：

log_connections：记录连接登录的信息

log_disconnections：记录连接登出的信息

复制

但是用 log_statement 仅仅可以满足一些有限的审计行为： ddl, mod…

业界广泛使用的第三方审计插件 pgaudit. 来源于业界著名的第二象限公司。 很多 cloud 云厂商像是 AWS, 阿里云的 polar DB 都使用这个审计插件。

官网地址：https://www.pgaudit.org/#section_three
(Logo是个绿帽子的大象，寓意还是十分有趣的。。。不知道是否知道中国的典故。。。)

下载源码的时候，需要注意一下和PG 实例的版本的兼容性的问题。

目前的测试环境是 PG 12.3， 我们下载一下相应的PG版本 pgAudit v1.4.X ：

github 地址： https://github.com/pgaudit/pgaudit/blob/master/README.md

wget https://github.com/pgaudit/pgaudit/archive/refs/tags/1.4.3.tar.gz

复制

解压源码压缩包：

tar -xvf pgaudit-1.4.3.tar.gz

复制

源码编译前，确保环境变量 pg_config的版本

INFRA [postgres@wqdcsrv3084 ~]# pg_config |grep VERSION
VERSION = PostgreSQL 12.3


复制

编译源代码：

INFRA [postgres@wqdcsrv3065 pgaudit]# mv pgaudit-1.4.3 pgaudit
INFRA [postgres@wqdcsrv3065 pgaudit]# cd pgaudit-1.4.3/
INFRA [postgres@wqdcsrv3065 pgaudit]# make install USE_PGXS=1 PG_CONFIG=/opt/postgreSQL/pg12/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/postgreSQL/pg12/include/postgresql/server -I/opt/postgreSQL/pg12/include/postgresql/internal  -D_GNU_SOURCE   -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/postgreSQL/pg12/lib    -Wl,--as-needed -Wl,-rpath,'/opt/postgreSQL/pg12/lib',--enable-new-dtags 
/usr/bin/mkdir -p '/opt/postgreSQL/pg12/lib/postgresql'
/usr/bin/mkdir -p '/opt/postgreSQL/pg12/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/postgreSQL/pg12/share/postgresql/extension'
/usr/bin/install -c -m 755  pgaudit.so '/opt/postgreSQL/pg12/lib/postgresql/pgaudit.so'
/usr/bin/install -c -m 644 .//pgaudit.control '/opt/postgreSQL/pg12/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//pgaudit--1.4.3.sql .//pgaudit--1.4--1.4.1.sql .//pgaudit--1.4.1--1.4.2.sql .//pgaudit--1.4.2--1.4.3.sql  '/opt/postgreSQL/pg12/share/postgresql/extension/'


复制

编译成功后，可以在 ${PG_HOME}/lib/postgresql 下面查询 : pgaudit.so

INFRA [postgres@wqdcsrv3065 postgresql]# ls -l | grep pgaudit
-rwxr-xr-x 1 postgres postgres  38288 Sep  5 18:51 pgaudit.so

复制

安装成功后， 我们需要修改 PG参数 shared_preload_libraries 预加载插件：

postgres=# show shared_preload_libraries ;
shared_preload_libraries
--------------------------
(1 row)
postgres=# alter system set shared_preload_libraries = 'pgaudit';
ALTER SYSTEM

复制

参数 shared_preload_libraries的 作用域是 postmaster 需要重启数据库 ：

日志中可以看到 2022-09-05 21:19:37.730 CST [127316] LOG: parameter “shared_preload_libraries” cannot be changed without restarting the server

postgres=# select context from pg_settings where name = 'shared_preload_libraries';
  context  
------------
postmaster
(1 row)

复制

重启数据库，参数生效：

INFRA [postgres@wqdcsrv3065 log]# /opt/postgreSQL/pg12/bin/pg_ctl -D /pgdata/postgreSQL/1998 restart
INFRA [postgres@wqdcsrv3065 log]# psql -h /tmp -p 1998
psql (12.3)
Type "help" for help.
postgres=# show shared_preload_libraries ;
shared_preload_libraries
--------------------------
pgaudit
(1 row)

复制

我们创建 extension：

postgres=# create extension pgaudit;
CREATE EXTENSION
postgres=# \dx
                   List of installed extensions
  Name   | Version |   Schema   |           Description          
---------+---------+------------+---------------------------------
pgaudit | 1.4.3   | public     | provides auditing functionality
plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

复制

PGAUDIT 提供的审计选项具体如下：

* 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 的审计主要分为2大类：

1）连接session 的操作审计
2）对象级别的审计，通过创建role 来实现，

我们先测试一下连接session 审计 ， 开启DDL的审计信息：

postgres=# alter system set pgaudit.log = 'ddl';
 
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# show pgaudit.log;
pgaudit.log
-------------
ddl
(1 row)

复制

我们设置一下日志的格式：

postgres=# alter system set log_line_prefix='%m %u %d %a %r [%p]: ';
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

复制

我们尝试执行一些DDL语句:

INFRA [postgres@wqdcsrv3065 log]# psql -h /tmp -p 1998 -U jason -d postgres
psql (12.3)
Type "help" for help.
postgres=> create table t_jason(id int);
CREATE TABLE
postgres=> insert into t_jason select generate_series(1,10000);
INSERT 0 10000
postgres=> delete  from t_jason;
DELETE 10000
postgres=# drop table t_jason;
DROP TABLE

复制

查看审计日志： 只有 create 和 drop table 的DDL 被审计

INFRA [postgres@wqdcsrv3065 log]# tail -f postgresql-2022-09-05.log
2022-09-05 22:29:18.598 CST postgres postgres psql [local] [18232]: LOG:  AUDIT: SESSION,2,1,DDL,CREATE TABLE,TABLE,public.t_jason,create table t_jason(id int);,<not logged>
2022-09-05 22:29:46.836 CST postgres postgres psql [local] [18232]: LOG:  AUDIT: SESSION,3,1,DDL,DROP TABLE,TABLE,public.t_jason,drop table t_jason;,<not logged>


复制

b) 对象级别的审计，通过创建role 来实现， 原理就是把想要审计的对象的具体操作赋权给一个 role， 然后在设置 pgaudit.role 。

目前只能支持， SELECT, INSERT, UPDATE and DELETE 这4中类型， 相对于 read, write 来说更细粒度了

创建角色: audit_account

postgres=# create role audit_account password 'audit_account';
CREATE ROLE

复制

设置对象 t_jason 的 insert, update, delete.select 为审计行为

postgres=# alter system set pgaudit.role = 'audit_account';
ALTER SYSTEM
postgres=# grant select,insert,update,delete on t_jason to audit_account;
GRANT
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# \dp t_jason
                                   Access privileges
Schema |  Name   | Type  |      Access privileges      | Column privileges | Policies
--------+---------+-------+-----------------------------+-------------------+----------
public | t_jason | table | postgres=arwdDxt/postgres  +|                   |
        |         |       | audit_account=arwd/postgres |                   |
(1 row)
postgres=# show pgaudit.role;
pgaudit.role 
---------------
audit_account
(1 row)

复制

我们对表t_jason 进行一些 增删改查

INFRA [postgres@wqdcsrv3065 log]# psql -h /tmp -p 1998 -U audit_account -d postgres
psql (12.3)
Type "help" for help.
postgres=# insert into t_jason select generate_series(1,10000);
INSERT 0 10000
postgres=# delete from t_jason where id < 5000;
DELETE 4999
            
 
postgres=# update t_jason set id = 1000+ id ;
UPDATE 5001
postgres=# select * from t_jason limit 2;
  id 
------
6000
6001
(2 rows)

复制

查看审计日志：表的 select, insert,update,delete 均被审计

2022-09-05 23:14:19.716 CST postgres postgres psql [local] [32031]: LOG:  AUDIT: OBJECT,1,1,WRITE,INSERT,TABLE,public.t_jason,"insert into t_jason select generate_series(1,10000);",<not logged>
2022-09-05 23:14:43.257 CST postgres postgres psql [local] [32031]: LOG:  AUDIT: OBJECT,2,1,WRITE,DELETE,TABLE,public.t_jason,delete from t_jason where id < 5000;,<not logged>
2022-09-05 23:15:05.757 CST postgres postgres psql [local] [32031]: LOG:  AUDIT: OBJECT,3,1,WRITE,UPDATE,TABLE,public.t_jason,update t_jason set id = 1000+ id ;,<not logged>
2022-09-05 23:15:19.601 CST postgres postgres psql [local] [32031]: LOG:  AUDIT: OBJECT,4,1,READ,SELECT,TABLE,public.t_jason,select * from t_jason limit 2;,<not logged>

复制

写到最后：

1）如果你的公司只有简单的审计需求： 例如审计DDL，审计账号的登录，登出，那么你可以直接用PG原生支持的log参数实现。

2）如果公司合规要求审计力度更细致，例如表级别的审计，像是ERP或者SAP的核心敏感表 salary 或者 公司资产相关的表 进行全方位的审计。那么就要考虑使用pgaudit 或者其他第三方审计插件或者服务了。

Have a fun 😃 !