更新时间：2026-08-06 GMT+08:00
操作步骤
传统审计使用指导
- root用户通过客户端登录CN节点。
- 修改审计对象GUC参数audit_system_object的值为12，表示审计TABLE和USER对象的CREATE、DROP、ALTER操作。
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "audit_system_object=12"
- root用户登录客户端查看audit_system_object值返回12即修改成功。
gaussdb=# SHOW audit_system_object; audit_system_object --------------------------- 12 (1 row) - root用户创建审计管理员auditadmin和普通用户audit_t1，auditadmin用于查询审计日志，创建普通用户audit_t1用于执行SQL操作。
gaussdb=# CREATE USER auditadmin WITH AUDITADMIN password '********'; CREATE ROLE gaussdb=# CREATE USER audit_t1 password '********'; CREATE ROLE
- 使用audit_t1登录数据库，创建数据表t1并插入数据然后添加一列name，再删除t1。
gaussdb=# CREATE TABLE t1 (id int); CREATE TABLE gaussdb=# INSERT INTO t1 values(123); INSERT 0 1 gaussdb=# ALTER TABLE t1 ADD COLUMN NAME varchar(20); ALTER TABLE gaussdb=# DROP TABLE t1; DROP TABLE
- 登录root用户删除普通用户audit_t1。
gaussdb=# DROP USER audit_t1; DROP ROLE - 使用auditadmin登录数据库查询审计日志。
按起止时间查询审计日志，并查询用户audit_t1执行的ddl_table操作，root用户的user操作，例如：
gaussdb=# SELECT * FROM gs_query_audit('2024-03-23 15:50:00','2024-03-23 16:30:00') WHERE username='audit_t1' AND type='ddl_table'; time | type | result | userid | username | database | client_conninfo | object_name | detail_info | node_name | thread_id | local_port | remote_port ------------------------+-----------+--------+--------+----------+----------+-------------------+-------------+----------------------------------------- ----+-----------+---------------------------------+------------+------------- 2024-03-23 15:51:15+08 | ddl_table | ok | 20839 | audit_t1 | gaussdb | gsql@192.168.0.99 | t1 | CREATE TABLE t1(id int); | dn_6001 | 281431669195952@764495475555777 | 8000 | 36548 2024-03-23 16:02:48+08 | ddl_table | ok | 20839 | audit_t1 | gaussdb | gsql@192.168.0.99 | t1 | ALTER TABLE t1 ADD COLUMN NAME varchar(2 0); | dn_6001 | 281430353822896@764496168035630 | 8000 | 36628 2024-03-23 16:03:23+08 | ddl_table | ok | 20839 | audit_t1 | gaussdb | gsql@192.168.0.99 | t1 | drop table t1; | dn_6001 | 281430353822896@764496203155439 | 8000 | 36636 (3 rows) gaussdb=# SELECT * FROM gs_query_audit('2024-03-23 15:50:00','2024-03-23 16:30:00') WHERE username='root' and type='user' and object_name='audit_t1'; time | type | result | userid | username | database | client_conninfo | object_name | detail_info | node_name | thread_id | local_port | remote_port ------------------------+----------+--------+--------+----------+----------+-------------------+-------------+------------------------------------------ -+-----------+---------------------------------+------------+------------- 2024-03-23 15:50:47+08 | user | ok | 16725 | root | gaussdb | gsql@192.168.0.99 | audit_t1 | CREATE USER audit_t1 password '********'; | dn_6001 | 281431669195952@764495447019065 | 8000 | 36482 2024-03-23 16:04:57+08 | user | ok | 16725 | root | gaussdb | gsql@192.168.0.99 | audit_t1 | drop user audit_t1; | dn_6001 | 281431669195952@764496297236538 | 8000 | 36648 (2 rows)
- 恢复GUC参数audit_system_object。
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "audit_system_object=67121159"
- 使用root用户登录数据库，删除用户auditadmin。
gaussdb=# DROP USER auditadmin; DROP ROLE
统一审计使用指导
- root用户执行以下命令开启统一审计开关。
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "enable_security_policy=on" gs_guc reload -Z coordinator -Z datanode -N all -I all -c "unified_audit_location=0"
- 操作系统root用户进行rsyslog配置。
在操作系统后台服务配置文件/etc/rsyslog.conf中添加：
local0.* /var/log/localmessages
重启rsyslog服务使配置生效。
sudo systemctl restart rsyslog
- 初始用户登录数据库，配置资源标签，创建审计策略。配置资源标签及创建审计策略需要有安全策略管理员权限，对于安全策略管理员的相关操作请参见《开发指南》中“数据库安全 > 用户及权限 > 管理员”章节，审计策略参数请参见《参考》中“SQL参考 > SQL语法 > C > CREATE AUDIT POLICY”章节的相关描述。
-- 初始化资源 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登录数据库，执行如下操作，触发审计策略。
-- ACCESS 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; -- PRIVILEGES GRANT INSERT ON TABLE table_security_auditing TO user002; REVOKE INSERT ON TABLE table_security_auditing FROM user002;
- 使用操作系统root用户查看审计日志/var/log/localmessages。
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_auditing], 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_auditing], 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_auditing], 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_auditing], 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]
- 修改GUC参数unified_audit_location的值，将统一审计日志记录到审计日志文件。
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "unified_audit_location=1" gs_guc reload -Z coordinator -Z datanode -N all -I all -c "audit_enabled=on"
- 再次使用用户user001登录数据库，执行如下操作，触发审计策略。
-- ACCESS 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; -- PRIVILEGES GRANT INSERT ON TABLE table_security_auditing TO user002; REVOKE INSERT ON TABLE table_security_auditing FROM user002;
- 初始用户登录数据库，使用系统函数gs_query_unified_audit查询统一审计策略。查询审计策略需要有审计管理员权限，对于审计管理员的相关操作请参见《开发指南》中“数据库安全 > 用户及权限 > 管理员”章节，gs_query_unified_audit函数的相关信息请参见《参考》中“SQL参考 > 函数和操作符 > 安全函数”章节。
1gaussdb=# SELECT * FROM gs_query_unified_audit(sysdate-1,sysdate+1) WHERE type='audit_policy';
查询结果如下：
time | type | result | userid | username | database | client_conninfo | object_name | detail_info | node_name | thread_id | local_port | remot e_port | policy_id | unified_audit_type | unified_audit_policy ------------------------+--------------+--------+--------+----------+----------+-----------------+------------------------------------ -----+--------------------------------------------------------------+-----------+---------------------------------+------------+------ -------+-----------+--------------------+---------------------------------------------------------------------- 2025-04-26 14:49:54+08 | audit_policy | ok | 16761 | user001 | postgres | gsql@[local] | table: [public.table_security_audit ing] | insert into table_security_auditing values(1,'22'); | datanode | 139686500628224@798965394386913 | 17778 | 46946 | 16785 | access | INSERT 2025-04-26 14:49:54+08 | audit_policy | ok | 16761 | user001 | postgres | gsql@[local] | table: [public.table_security_audit ing] | update table_security_auditing set name=234123 where id=1; | datanode | 139686500628224@798965394390539 | 17778 | 46946 | 16785 | access | UPDATE 2025-04-26 14:49:54+08 | audit_policy | ok | 16761 | user001 | postgres | gsql@[local] | table: [public.table_security_audit ing] | delete from table_security_auditing where id=1; | datanode | 139686500628224@798965394391655 | 17778 | 46946 | 16785 | access | DELETE 2025-04-26 14:49:55+08 | audit_policy | ok | 16761 | user001 | postgres | gsql@[local] | table: [public.table_security_audit ing] | truncate table table_security_auditing; | datanode | 139686500628224@798965395709651 | 17778 | 46946 | 16785 | access | TRUNCATE 2025-04-26 14:50:00+08 | audit_policy | ok | 16761 | user001 | postgres | gsql@[local] | public.table_security_auditing | GRANT INSERT ON TABLE table_security_auditing TO user002; | datanode | 139686500628224@798965400073107 | 17778 | 46946 | 16770 | privilege | GRANT ON TABLE postgres.public.table_security_auditing TO user002 2025-04-26 14:50:01+08 | audit_policy | ok | 16761 | user001 | postgres | gsql@[local] | public.table_security_auditing | revoke insert on table table_security_auditing from user002; | datanode | 139686500628224@798965401697885 | 17778 | 46946 | 16770 | privilege | REVOKE ON TABLE postgres.public.table_security_auditing FROM user002 (6 rows)上述查询记录展示了用户user001的详细执行SQL语句及执行的相关信息，其中policy_id表示审计策略ID，unified_audit_type表示审计策略是privilege或access类型，unified_audit_policy为审计策略的详细信息。
- 如不需要继续对特定资源进行审计，可移除审计策略。
drop audit policy audit_security_priall; drop audit policy audit_security_accall;
