pgaudit安装

##pgaudit的tar安装包
https://github.com/pgaudit/pgaudit/releases

##适合postgresql 11版本
https://github.com/pgaudit/pgaudit/archive/refs/tags/1.3.3.tar.gz

安装

1.git clone下载
git clone https://github.com/pgaudit/pgaudit.git

2.将pgaudit移动到postgresql安装路径中contrib路径下
mv pgaudit /data/postgresql-11.4/contrib/

3.编译安装
cd /data/postgresql-11.4/contrib/pgaudit
make install USE_PGXS=1 PG_CONFIG=/usr/local/pgsql/bin/pg_config

##编译成功
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/usr/local/pgsql/include/server -I/usr/local/pgsql/include/internal  -D_GNU_SOURCE   -c -o pgaudit.o pgaudit.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pgaudit.so pgaudit.o  -L/usr/local/pgsql/lib    -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags
/usr/bin/mkdir -p '/usr/local/pgsql/lib'
/usr/bin/mkdir -p '/usr/local/pgsql/share/extension'
/usr/bin/mkdir -p '/usr/local/pgsql/share/extension'
/usr/bin/install -c -m 755  pgaudit.so '/usr/local/pgsql/lib/pgaudit.so'
/usr/bin/install -c -m 644 .//pgaudit.control '/usr/local/pgsql/share/extension/'
/usr/bin/install -c -m 644 .//pgaudit--1.6.1.sql .//pgaudit--1.6--1.6.1.sql  '/usr/local/pgsql/share/extension/'

4.修改postgresql.conf配置文件，添加shared_preload_libraries参数配置，否则会出现错误
vi postgresql.conf
shared_preload_libraries = ‘pgaudit’;

5.重新启动服务，shared_preload_libraries参数修改一定要重启服务，热加载是不可以的
sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /data/pg_data/data -l /data/pg_data/log/pg.log restart

6.创建审计模块
create extension pgaudit;

7.查看安装的模块
select * from pg_catalog.pg_extension

8.查看audit相关配置信息
select * from pg_settings where name like ‘%audit%’;

安装问题

##编译遇到问题

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/usr/local/pgsql/include/server -I/usr/local/pgsql/include/internal  -D_GNU_SOURCE   -c -o pgaudit.o pgaudit.c
pgaudit.c:16:29: fatal error: access/relation.h: No such file or directory
 #include "access/relation.h"
                             ^
compilation terminated.
make: *** [pgaudit.o] Error 1

#include "access/relation.h"

原因：查看pgaudit.c文件中，头文件中有#include “access/relation.h”，然后找不到相关的资源

解决办法：替换pgaudit.c的文件，使用不需要包含头文件的"access/relation.h"，根据PG版本下载了，发现1.3.3里面pgaudit.c并不需要。所以用其替换了后重新编译
https://github.com/pgaudit/pgaudit/archive/refs/tags/1.3.3.tar.gz

##没有配置shared_preload_libraries出现的错误
SQL 错误 [55000]: ERROR: pgaudit must be loaded via shared_preload_libraries

pgaudit的使用

1）pgaudit模式

• session模式
• object模式

2）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.
  -ALL: Include all of the above.

3）pgaudit日志格式

• audit_type:audit的方式
• statement_id:会话id
• substatement_id:主语句中每个子语句的顺序id
• operation type:操作方式，DDL/DCL/DML
• command:操作的命令
• object_type:object类型
• object_name:object类型名称
• statement:执行语句
• parameter:相关参照

4）pgaudit相关配置使用

a. pgaudit.log

• pg_audit是记录在标准postgresql日志中的
• 该参数控制审计的类型

-- 设置审计类型
set pgaudit.log = read,ddl,write

-- 查询 & 删除
select * from aa;
drop table cc;

##审计日志信息

2021-12-10 17:28:12.818 CST [304] LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,select * from pg_settings where name like '%audit%',<not logged>
2021-12-10 17:28:38.099 CST [304] LOG:  AUDIT: SESSION,2,1,READ,SELECT,,,select * from aa,<not logged>
2021-12-10 17:28:38.118 CST [304] LOG:  AUDIT: SESSION,3,1,READ,SELECT,,,"SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 16384 AS oid , 1 AS attnum UNION ALL SELECT 16384, 2 UNION ALL SELECT 16384, 3) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum) ",<not logged>
2021-12-10 17:28:48.858 CST [304] LOG:  execute <unnamed>: drop table cc
2021-12-10 17:28:48.860 CST [304] LOG:  AUDIT: SESSION,4,1,DDL,DROP TABLE,TABLE,public.cc,drop table cc,<not logged>

b. pgaudit.role

• 设置需要审计的用户和角色
• 默认是全部监控

set pgaudit.role = 'postgres,zhangsan,lisi';

c.pgaudit.log_client

• 默认是关闭的
• 客户端打印审计日志

set pgaudit.log_client = on;

d.pgaudit.log_level

• 审计日志级别

级别	描述
DEBUG1 … DEBUG5	调试
INFO	信息
NOTICE	通知信息
WARNING	告警信息
ERROR	错误信息
LOG	日志信息
FATAL	严重错误信息
PANIC	会话中止的错误信息

ps:官方文档信息：https://www.postgresql.org/docs/13/runtime-config-logging.html#RUNTIME-CONFIG-SEVERITY-LEVELS

e.pgaudit.log_parameter

• 指定审计日志记录应包括与语句一起传递的参数