设置统一审计策略

背景信息

传统审计会产生大量的审计日志，且不支持定制化的访问对象和访问来源配置，不方便数据库安全管理员对审计日志的分析。而统一审计策略支持绑定资源标签、配置数据来源输出审计日志，可以提升安全管理员对数据库监控的效率。

操作步骤

执行以下命令开启统一审计开关。

gs_guc reload -Z coordinator -N all -I all -c "enable_security_policy=on"

操作系统root用户进行rsyslog配置。
在操作系统后台服务配置文件/etc/rsyslog.conf中添加：
```
local0.* /var/log/localmessages  
```
重启rsyslog服务使配置生效。
```
sudo systemctl restart rsyslog
```

安全策略管理员登录数据库，配置资源标签，对于安全策略管理员的相关操作参考管理员章节，审计策略参数参考SQL语法描述。

-- 初始化资源
DROP TABLE IF EXISTS table_security_auditing;
CREATE TABLE table_security_auditing(id int,name char(10));
create user user001 password '********';
create user user002 password '********';
grant all privileges to user001;

-- 新建资源标签
DROP RESOURCE LABEL IF EXISTS rl_security_auditing;
CREATE RESOURCE LABEL rl_security_auditing ADD TABLE(table_security_auditing);

-- 创建审计策略，审计用户user001在资源标签rl_security_auditing上的DDL、DML操作
CREATE AUDIT POLICY audit_security_priall PRIVILEGES all on LABEL(rl_security_auditing) FILTER ON ROLES(user001);
CREATE AUDIT POLICY audit_security_accall ACCESS all on LABEL(rl_security_auditing) FILTER ON ROLES(user001);

使用用户user001登录数据库，执行如下操作, 触发审计策略。

-- DML
insert into table_security_auditing values(1,'22');
update table_security_auditing set name=234123 where id=1;
delete from table_security_auditing where id=1;
truncate table table_security_auditing;
-- DDL
GRANT INSERT ON TABLE table_security_auditing TO user002;
revoke insert on table table_security_auditing from user002;

使用操作系统root用户查看审计日志/var/log/localmessage。

Oct  9 15:38:11 localhost PGAUDIT: AUDIT EVENT: user name: [user001], app_name: [gsql], client_ip: [local], access type: [INSERT], policy id: [16423], table: [public.table_security_auditi           ng], result: [OK]
Oct  9 15:38:11 localhost PGAUDIT: AUDIT EVENT: user name: [user001], app_name: [gsql], client_ip: [local], access type: [UPDATE], policy id: [16423], table: [public.table_security_auditi           ng], result: [OK]
Oct  9 15:38:11 localhost PGAUDIT: AUDIT EVENT: user name: [user001], app_name: [gsql], client_ip: [local], access type: [DELETE], policy id: [16423], table: [public.table_security_auditi           ng], result: [OK]
Oct  9 15:38:12 localhost PGAUDIT: AUDIT EVENT: user name: [user001], app_name: [gsql], client_ip: [local], access type: [TRUNCATE], policy id: [16423], table: [public.table_security_audi           ting], result: [OK]
Oct  9 15:49:41 localhost PGAUDIT: AUDIT EVENT: user name: [user001], app_name: [gsql], client_ip: [local], privilege type: [GRANT ON TABLE postgres.public.table_security_auditing TO user002], poy id: [16408], result: [OK]
Oct  9 15:49:53 localhost PGAUDIT: AUDIT EVENT: user name: [user001], app_name: [gsql], client_ip: [local], privilege type: [REVOKE ON TABLE postgres.public.table_security_auditing FROM user002],licy id: [16408], result: [OK]

如不需要继续对特定资源进行审计，可移除审计策略。

drop audit policy audit_security_priall;  
drop audit policy audit_security_accall;

统一审计日志字段说明

Oct  9 15:38:12 localhost PGAUDIT: AUDIT EVENT: user name: [user001], app_name: [gsql], client_ip: [local], access type: [TRUNCATE], policy id: [16423], table: [public.table_security_auditing], result: [OK]

以如上TRUNCATE操作触发的审计日志为例，字段说明如下:

注意： 在使用DATABASE LINK功能的场景下，客户端发起的DATABASE LINK请求，实际的发送方是服务端，发送端ip地址等相关的属性将是服务端的值。