AntDB使用pgaudit实现日志审计

pgaudit 是作为 postgresql 的一个 extension 形式存在的，通过标准postgresql日志工具提供详细的会话和/或对象审计日志记录。

pgaudit 的目标是为AntDB生成审计日志。

编译安装pgaudit

获取源码

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

注意：根据pg版本checkout到合适的branch

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.

[[email protected] pgaudit]$ git branch -a
* master
  remotes/origin/HEAD -> origin/master
  remotes/origin/REL9_5_STABLE
  remotes/origin/REL9_6_STABLE
  remotes/origin/REL_10_STABLE
  remotes/origin/REL_11_STABLE
  remotes/origin/master

编译安装

AntDB 是一个分布式数据库，pgaudit编译时报错，需要打pgaudit_for_adb.patch

[[email protected] pgaudit]$ make USE_PGXS=1          
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -ggdb3 -DADB -fPIC -I. -I./ -I/data/adb3.1/app/include/postgresql/server -I/data/adb3.1/app/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pgaudit.o pgaudit.c -MMD -MP -MF .deps/pgaudit.Po
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -ggdb3 -DADB -fPIC -shared -o pgaudit.so pgaudit.o  -L/data/adb3.1/app/lib    -Wl,--as-needed -Wl,-rpath,'/data/adb3.1/app/lib',--enable-new-dtags  
[[email protected] pgaudit]$ make install USE_PGXS=1
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -ggdb3 -DADB -fPIC -I. -I./ -I/data/adb3.1/app/include/postgresql/server -I/data/adb3.1/app/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pgaudit.o pgaudit.c -MMD -MP -MF .deps/pgaudit.Po
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -ggdb3 -DADB -fPIC -shared -o pgaudit.so pgaudit.o  -L/data/adb3.1/app/lib    -Wl,--as-needed -Wl,-rpath,'/data/adb3.1/app/lib',--enable-new-dtags  
/bin/mkdir -p '/data/adb3.1/app/lib/postgresql'
/bin/mkdir -p '/data/adb3.1/app/share/postgresql/extension'
/bin/mkdir -p '/data/adb3.1/app/share/postgresql/extension'
/bin/install -c -m 755  pgaudit.so '/data/adb3.1/app/lib/postgresql/pgaudit.so'
/bin/install -c -m 644 .//pgaudit.control '/data/adb3.1/app/share/postgresql/extension/'
/bin/install -c -m 644 .//pgaudit--1.2.sql  '/data/adb3.1/app/share/postgresql/extension/'

至此，安装完成。

使用pgaudit

step 1: AntDB集群配置使用pgaudit

连接adbmgr，deploy到所有host机器上，然后添加shared_preload_libraries= ‘pgaudit,pg_stat_statements’

postgres=# stop all mode f;
NOTICE:  172.10.20.176, pg_ctl  stop -D /data/adb3.1/pgxc_data/db1 -Z datanode -m fast -o -i -w -c -W
NOTICE:  172.10.20.176, pg_ctl  stop -D /data/adb3.1/pgxc_data/db2 -Z datanode -m fast -o -i -w -c -W
NOTICE:  waiting max 90 seconds for datanode master to stop ...

NOTICE:  172.10.20.176, pg_ctl  stop -D /data/adb3.1/pgxc_data/coord1 -Z coordinator -m fast -o -i -w -c -W
NOTICE:  waiting max 90 seconds for coordinator master to stop ...

WARNING:  try stop coordinator master coord1 again
NOTICE:  172.10.20.176, pg_ctl  stop -D /data/adb3.1/pgxc_data/coord1 -Z coordinator -m fast -o -i -w -c -t 3
NOTICE:  172.10.20.176, agtm_ctl  stop -D /data/adb3.1/pgxc_data/gtm -m fast -o -i -w -c -W
NOTICE:  waiting max 90 seconds for gtm master to stop ...

     operation type      | nodename | status | description 
-------------------------+----------+--------+-------------
 stop datanode master    | db1      | t      | success
 stop datanode master    | db2      | t      | success
 stop coordinator master | coord1   | t      | success
 stop gtm master         | gtm      | t      | success
(4 rows)

postgres=# deploy all password '123';
ERROR:  on host "localhost1" the agent still running, please stop it before deploy
HINT:  try "monitor agent all;" for more information
postgres=# stop agent all ;
  nodename  | status | description 
------------+--------+-------------
 localhost1 | t      | success
(1 row)

postgres=# deploy all password '123';
  hostname  | status |  description   
------------+--------+----------------
 localhost1 | t      | skip localhost
(1 row)
postgres=# start agent all password '123';
  hostname  | status | description 
------------+--------+-------------
 localhost1 | t      | success
(1 row)
postgres=# set coordinator all (shared_preload_libraries= 'pgaudit,pg_stat_statements');
NOTICE:  parameter "shared_preload_libraries" cannot be changed without restarting the server
SET PARAM
postgres=# set datanode all (shared_preload_libraries= 'pgaudit,pg_stat_statements');                         
NOTICE:  parameter "shared_preload_libraries" cannot be changed without restarting the server
SET PARAM
postgres=# start all ;
NOTICE:  172.10.20.176, agtm_ctl  start -D /data/adb3.1/pgxc_data/gtm -o -i -w -c -W -l /data/adb3.1/pgxc_data/gtm/logfile
NOTICE:  waiting max 90 seconds for gtm master to start ...

NOTICE:  172.10.20.176, pg_ctl  start -D /data/adb3.1/pgxc_data/coord1 -Z coordinator -o -i -w -c -W -l /data/adb3.1/pgxc_data/coord1/logfile
NOTICE:  waiting max 90 seconds for coordinator master to start ...

NOTICE:  172.10.20.176, pg_ctl  start -D /data/adb3.1/pgxc_data/db1 -Z datanode -o -i -w -c -W -l /data/adb3.1/pgxc_data/db1/logfile
NOTICE:  172.10.20.176, pg_ctl  start -D /data/adb3.1/pgxc_data/db2 -Z datanode -o -i -w -c -W -l /data/adb3.1/pgxc_data/db2/logfile
NOTICE:  waiting max 90 seconds for datanode master to start ...

      operation type      | nodename | status | description 
--------------------------+----------+--------+-------------
 start gtm master         | gtm      | t      | success
 start coordinator master | coord1   | t      | success
 start datanode master    | db1      | t      | success
 start datanode master    | db2      | t      | success

step 2: 开始使用

连接coordinator，创建extension：

psql (4.0.0 eb87028865 based on PG 10.7)
Type "help" for help.

postgres=# create extension pgaudit ;              
CREATE EXTENSION
postgres=# select name,setting from pg_settings where name like 'pgaudit%';
            name            | setting 
----------------------------+---------
 pgaudit.log                | none
 pgaudit.log_catalog        | on
 pgaudit.log_client         | off
 pgaudit.log_level          | log
 pgaudit.log_parameter      | off
 pgaudit.log_relation       | off
 pgaudit.log_statement_once | off
 pgaudit.role               | 
(8 rows)

上述几个参数，根据实际需要进行设置。本次实验使用的参数如下：

pgaudit.log = 'all, -misc'
pgaudit.log_catalog = on
pgaudit.log_client = on
pgaudit.log_level = log
pgaudit.log_parameter = on
pgaudit.log_relation = on
pgaudit.log_statement_once = on

将几个参数写到coordinator和datanode 节点的postgresql.conf里：

[[email protected] ~]$ cat >> pgxc_data/coord1/postgresql.conf <<EOF
 pgaudit.log = 'all, -misc'
 pgaudit.log_catalog = on
 pgaudit.log_client = on
 pgaudit.log_level = log
 pgaudit.log_parameter = on
 pgaudit.log_relation = on
 pgaudit.log_statement_once = on
 EOF
 [[email protected] ~]$ cat >> pgxc_data/db1/postgresql.conf <<EOF
 pgaudit.log = 'all, -misc'
 pgaudit.log_catalog = on
 pgaudit.log_client = on
 pgaudit.log_level = log
 pgaudit.log_parameter = on
 pgaudit.log_relation = on
 pgaudit.log_statement_once = on
 EOF
 [[email protected] ~]$ cat >> pgxc_data/db2/postgresql.conf <<EOF
 pgaudit.log = 'all, -misc'
 pgaudit.log_catalog = on
 pgaudit.log_client = on
 pgaudit.log_level = log
 pgaudit.log_parameter = on
 pgaudit.log_relation = on
 pgaudit.log_statement_once = on
 EOF
[[email protected] ~]$ adbmgr
psql (4.0.0 eb87028865 based on PG 10.7)
Type "help" for help.

postgres=# stop all mode f;
NOTICE:  172.10.20.176, pg_ctl  stop -D /data/adb3.1/pgxc_data/db1 -Z datanode -m fast -o -i -w -c -W
NOTICE:  172.10.20.176, pg_ctl  stop -D /data/adb3.1/pgxc_data/db2 -Z datanode -m fast -o -i -w -c -W
NOTICE:  waiting max 90 seconds for datanode master to stop ...

NOTICE:  172.10.20.176, pg_ctl  stop -D /data/adb3.1/pgxc_data/coord1 -Z coordinator -m fast -o -i -w -c -W
NOTICE:  waiting max 90 seconds for coordinator master to stop ...

WARNING:  try stop coordinator master coord1 again
NOTICE:  172.10.20.176, pg_ctl  stop -D /data/adb3.1/pgxc_data/coord1 -Z coordinator -m fast -o -i -w -c -t 3
NOTICE:  172.10.20.176, agtm_ctl  stop -D /data/adb3.1/pgxc_data/gtm -m fast -o -i -w -c -W
NOTICE:  waiting max 90 seconds for gtm master to stop ...

     operation type      | nodename | status | description 
-------------------------+----------+--------+-------------
 stop datanode master    | db1      | t      | success
 stop datanode master    | db2      | t      | success
 stop coordinator master | coord1   | t      | success
 stop gtm master         | gtm      | t      | success
(4 rows)

postgres=# start all;
NOTICE:  172.10.20.176, agtm_ctl  start -D /data/adb3.1/pgxc_data/gtm -o -i -w -c -W -l /data/adb3.1/pgxc_data/gtm/logfile
NOTICE:  waiting max 90 seconds for gtm master to start ...

NOTICE:  172.10.20.176, pg_ctl  start -D /data/adb3.1/pgxc_data/coord1 -Z coordinator -o -i -w -c -W -l /data/adb3.1/pgxc_data/coord1/logfile
NOTICE:  waiting max 90 seconds for coordinator master to start ...

NOTICE:  172.10.20.176, pg_ctl  start -D /data/adb3.1/pgxc_data/db1 -Z datanode -o -i -w -c -W -l /data/adb3.1/pgxc_data/db1/logfile
NOTICE:  172.10.20.176, pg_ctl  start -D /data/adb3.1/pgxc_data/db2 -Z datanode -o -i -w -c -W -l /data/adb3.1/pgxc_data/db2/logfile
NOTICE:  waiting max 90 seconds for datanode master to start ...

      operation type      | nodename | status | description 
--------------------------+----------+--------+-------------
 start gtm master         | gtm      | t      | success
 start coordinator master | coord1   | t      | success
 start datanode master    | db1      | t      | success
 start datanode master    | db2      | t      | success
(4 rows)

查看生成的日志

连接coord进行日常操作：

[adb3.1@intel176 ~]$ coord1
psql (4.0.0 eb87028865 based on PG 10.7)
Type "help" for help.

postgres=# create table customer (id int ,name varchar(22));
CREATE TABLE
postgres=# insert into customer values (1,'user-01');
INSERT 0 1
postgres=#

查看对应日志文件：

2019-02-28 21:35:44.958 CST,"adb3.1","postgres",169007,"172.10.20.176:36972",5c77e39d.2942f,1,"CREATE TABLE",2019-02-28 21:35:25 CST,3/4,707,LOG,00000,"AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.customer,""create table customer (id int ,name varchar(22));"",<none>",,,,,,,,,"psql"
2019-02-28 21:35:58.395 CST,"adb3.1","postgres",169007,"172.10.20.176:36972",5c77e39d.2942f,2,"SELECT",2019-02-28 21:35:25 CST,3/5,0,LOG,00000,"AUDIT: SESSION,2,1,READ,SELECT,TABLE,pg_catalog.pg_class,""SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'f', 'v', 'p') AND substring(pg_catalog.quote_ident(c.relname),1,2)='cu' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,2)='cu' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,2) = substring('cu',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'f', 'v', 'p') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,2)='cu' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,2) = substring('cu',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,2) = substring('cu',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
LIMIT 1000"",<none>",,,,,,,,,"psql"
2019-02-28 21:35:58.395 CST,"adb3.1","postgres",169007,"172.10.20.176:36972",5c77e39d.2942f,3,"SELECT",2019-02-28 21:35:25 CST,3/5,0,LOG,00000,"AUDIT: SESSION,2,1,READ,SELECT,TABLE,pg_catalog.pg_namespace,<previously logged>,<previously logged>",,,,,,,,,"psql"
2019-02-28 21:35:58.395 CST,"adb3.1","postgres",169007,"172.10.20.176:36972",5c77e39d.2942f,4,"SELECT",2019-02-28 21:35:25 CST,3/5,0,LOG,00000,"AUDIT: SESSION,2,1,READ,SELECT,TABLE,pg_catalog.pg_class,<previously logged>,<previously logged>",,,,,,,,,"psql"
2019-02-28 21:35:58.395 CST,"adb3.1","postgres",169007,"172.10.20.176:36972",5c77e39d.2942f,5,"SELECT",2019-02-28 21:35:25 CST,3/5,0,LOG,00000,"AUDIT: SESSION,2,1,READ,SELECT,TABLE,pg_catalog.pg_namespace,<previously logged>,<previously logged>",,,,,,,,,"psql"
2019-02-28 21:35:58.395 CST,"adb3.1","postgres",169007,"172.10.20.176:36972",5c77e39d.2942f,6,"SELECT",2019-02-28 21:35:25 CST,3/5,0,LOG,00000,"AUDIT: SESSION,2,1,READ,SELECT,TABLE,pg_catalog.pg_namespace,<previously logged>,<previously logged>",,,,,,,,,"psql"
2019-02-28 21:35:58.395 CST,"adb3.1","postgres",169007,"172.10.20.176:36972",5c77e39d.2942f,7,"SELECT",2019-02-28 21:35:25 CST,3/5,0,LOG,00000,"AUDIT: SESSION,2,1,READ,SELECT,TABLE,pg_catalog.pg_namespace,<previously logged>,<previously logged>",,,,,,,,,"psql"
2019-02-28 21:35:58.395 CST,"adb3.1","postgres",169007,"172.10.20.176:36972",5c77e39d.2942f,8,"SELECT",2019-02-28 21:35:25 CST,3/5,0,LOG,00000,"AUDIT: SESSION,2,1,READ,SELECT,TABLE,pg_catalog.pg_namespace,<previously logged>,<previously logged>",,,,,,,,,"psql"
2019-02-28 21:36:07.603 CST,"adb3.1","postgres",169007,"172.10.20.176:36972",5c77e39d.2942f,9,"INSERT",2019-02-28 21:35:25 CST,3/6,0,LOG,00000,"AUDIT: SESSION,3,1,WRITE,INSERT,TABLE,public.customer,""insert into customer values (1,'user-01');"",<none>",,,,,,,,,"psql"

参考：https://github.com/pgaudit/pgaudit

AntDB使用pgaudit实现日志审计

编译安装pgaudit

继续阅读

AntDB重分布流程记录

AntDB读写分离探索AntDB读写分离探索

pg9.6单纯写性能探索安装与配置

AntDB容器化设计概述AntDB容器化设计概述环境介绍AntDB容器化设计说明总结参考

AGTM对antdb性能测试场景（一）

AntDB单机版安装指南

AntDB上使用表空间

Oracle语法兼容对比 Oracle vs AntDB vs Postgresql

AntDB日常调试GDB脚本

AntDB的Cluster Plan与PGXC的Remote Query Plan区别

ADBMGR设置节点GUC参数ADBMGR设置节点GUC参数实现过程介绍小结

AntDB 分布式数据库如何合理选择分片键AntDB 如何合理选择分片键

AntDB 性能测试篇（一）：Jmeter工具

AntDB内存管理之内存上下文

管理员应了解的 SIEM解决方案七大功能！

EventLog Analyzer：保障网络安全的强大日志审计利器