— 测试数据库对象操作
CREATE TABLE fgedu_test_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
value INTEGER
);

ALTER TABLE fgedu_test_table ADD COLUMN description TEXT;

CREATE INDEX idx_fgedu_test_table_name ON fgedu_test_table(name);

DROP TABLE fgedu_test_table;

— 查看审计记录
SELECT * FROM fgedu_object_audit_log ORDER BY operation_time DESC;

audit_id | event_type | object_type | object_name | schema_name | command | user_name |
operation_time | client_ip
———-+——————-+————-+———————–+————-+——————————————-+———–+————————-+———–
4 | ddl_command_end | table | public.fgedu_test_table | public | DROP TABLE fgedu_test_table; |
fgedu | 2026-04-07 10:00:00.000 | 127.0.0.1
3 | ddl_command_end | index | public.idx_fgedu_test_table_name | public | CREATE INDEX
idx_fgedu_test_table_name ON fgedu_test_table(name); | fgedu | 2026-04-07 10:00:00.000 | 127.0.0.1
2 | ddl_command_end | table | public.fgedu_test_table | public | ALTER TABLE fgedu_test_table ADD
COLUMN description TEXT; | fgedu | 2026-04-07 10:00:00.000 | 127.0.0.1
1 | ddl_command_end | table | public.fgedu_test_table | public | CREATE TABLE fgedu_test_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
value INTEGER
); | fgedu | 2026-04-07 10:00:00.000 | 127.0.0.1

— 创建高级审计表
CREATE TABLE fgedu_advanced_object_audit (
audit_id SERIAL PRIMARY KEY,
event_type VARCHAR(50) NOT NULL,
object_type VARCHAR(50) NOT NULL,
object_name VARCHAR(100) NOT NULL,
schema_name VARCHAR(100) NOT NULL,
command TEXT NOT NULL,
user_name VARCHAR(100) NOT NULL,
user_role VARCHAR(100),
operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
client_ip VARCHAR(50),
fgapplication_name VARCHAR(100),
session_id INTEGER
);

— 创建高级事件触发器函数
CREATE OR REPLACE FUNCTION fgedu_advanced_object_audit_trigger()
RETURNS EVENT_TRIGGER AS $$
DECLARE
v_ddl_command RECORD;
v_user_name VARCHAR;
v_user_role VARCHAR;
v_client_ip VARCHAR;
v_fgapplication_name VARCHAR;
v_session_id INTEGER;
v_command TEXT;
BEGIN
— 获取操作人
SELECT current_user INTO v_user_name;

— 获取用户角色
SELECT string_agg(rolname, ‘, ‘) INTO v_user_role
FROM pg_roles r
JOIN pg_auth_members m ON r.oid = m.roleid
WHERE m.member = (SELECT oid FROM pg_roles WHERE rolname = current_user);

— 获取客户端信息
SELECT inet_client_addr()::text INTO v_client_ip;
SELECT fgapplication_name INTO v_fgapplication_name FROM pg_stat_activity WHERE pid = pg_backend_pid();
SELECT pg_backend_pid() INTO v_session_id;

— 获取完整的DDL命令
SELECT current_query() INTO v_command;

— 审计DDL命令
FOR v_ddl_command IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
— 插入审计记录
INSERT INTO fgedu_advanced_object_audit (
event_type,
object_type,
object_name,
schema_name,
command,
user_name,
user_role,
client_ip,
fgapplication_name,
session_id
) VALUES (
tg_event,
v_ddl_command.object_type,
v_ddl_command.object_identity,
v_ddl_command.schema_name,
v_command,
v_user_name,
v_user_role,
v_client_ip,
v_fgapplication_name,
v_session_id
);

— 权限控制：禁止非超级用户删除表
IF v_ddl_command.object_type = ‘table’ AND tg_tag = ‘DROP TABLE’ AND NOT current_user = ‘postgres’
THEN
RAISE EXCEPTION ‘Only superusers can drop tables’;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 创建高级事件触发器
CREATE EVENT TRIGGER fgedu_advanced_object_audit
ON ddl_command_start
EXECUTE FUNCTION fgedu_advanced_object_audit_trigger();

— 测试高级审计
CREATE TABLE fgedu_secure_table (
id SERIAL PRIMARY KEY,
sensitive_data TEXT
);

— 测试权限控制（非超级用户执行）
DROP TABLE fgedu_secure_table;

— 预期结果：ERROR: Only superusers can drop tables

— 创建集成表
CREATE TABLE fgedu_object_audit_integration (
id SERIAL PRIMARY KEY,
audit_id INTEGER REFERENCES fgedu_object_audit_log(audit_id),
integration_type VARCHAR(50) NOT NULL,
status VARCHAR(20) DEFAULT ‘pending’,
error_message TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
processed_at TIMESTAMP
);

— 创建集成触发器函数
CREATE OR REPLACE FUNCTION fgedu_object_audit_integration_trigger()
RETURNS TRIGGER AS $$
BEGIN
— 插入集成记录
INSERT INTO fgedu_object_audit_integration (audit_id, integration_type)
VALUES (NEW.audit_id, ‘monitoring_system’);

— 这里可以添加发送到外部系统的逻辑
— 例如使用dblink或外部程序调用

— 模拟集成处理
UPDATE fgedu_object_audit_integration
SET status = ‘success’, processed_at = CURRENT_TIMESTAMP
WHERE audit_id = NEW.audit_id;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

— 为审计表创建集成触发器
CREATE TRIGGER fgedu_object_audit_integration_trigger
AFTER INSERT ON fgedu_object_audit_log
FOR EACH ROW EXECUTE FUNCTION fgedu_object_audit_integration_trigger();

— 测试集成
CREATE TABLE fgedu_integration_test (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);

— 查看集成记录
SELECT * FROM fgedu_object_audit_integration ORDER BY created_at DESC;

id | audit_id | integration_type | status | error_message | created_at | processed_at
—-+———-+——————+———+—————+—————————-+————————-
1 | 5 | monitoring_system | success | | 2026-04-07 10:00:00.000000 | 2026-04-07 10:00:00.000000