PostgreSQL 审计功能实现：跟踪数据库变更的最佳实践

-- 创建审计schema（如果已存在可以跳过）
CREATE SCHEMA IF NOT EXISTS audit;

-- 创建通用审计触发器函数
CREATE OR REPLACE FUNCTION audit.create_audit_trigger(
    origin_schema TEXT,     -- 原始表所在schema
    origin_table TEXT,      -- 原始表名
    audit_schema TEXT DEFAULT 'audit'  -- 审计表所在schema，默认为audit
)
RETURNS void AS $$
DECLARE
    backup_table TEXT;
    trigger_name TEXT;
    trigger_func_name TEXT;
BEGIN
    -- 构造备份表名
    backup_table := origin_table || '_bak';
    -- 构造触发器名
    trigger_name := origin_table || '_audit';
    -- 构造触发器函数名
    trigger_func_name := origin_table || '_audit_func';
    
    -- 创建备份表
    EXECUTE format('
        DROP TABLE IF EXISTS %I.%I;
        CREATE TABLE %I.%I AS 
        SELECT 
            ''''::text as operation,
            now()::timestamp operation_time,
            ''''::text user_name,
            ''''::text old_content,
            * 
        FROM %I.%I
        WHERE 1=0;
    ', audit_schema, backup_table, audit_schema, backup_table, origin_schema, origin_table);

    -- 创建触发器函数
    EXECUTE format('
        CREATE OR REPLACE FUNCTION %I.%I() RETURNS TRIGGER AS $func$
        BEGIN
            IF (TG_OP = ''DELETE'') THEN
                INSERT INTO %I.%I 
                SELECT TG_OP, now(), current_user, '''', OLD.*;
                RETURN OLD;
            ELSIF (TG_OP = ''UPDATE'') THEN
                INSERT INTO %I.%I 
                SELECT TG_OP, now(), current_user, row_to_json(OLD.*), NEW.*;
                RETURN NEW;
            ELSIF (TG_OP = ''INSERT'') THEN
                INSERT INTO %I.%I 
                SELECT TG_OP, now(), current_user, '''', NEW.*;
                RETURN NEW;
            END IF;
            RETURN NULL;
        END;
        $func$ LANGUAGE plpgsql;
    ', audit_schema, trigger_func_name, audit_schema, backup_table, audit_schema, backup_table, audit_schema, backup_table);

    -- 创建触发器
    EXECUTE format('
        DROP TRIGGER IF EXISTS %I ON %I.%I;
        CREATE TRIGGER %I
            AFTER INSERT OR UPDATE OR DELETE ON %I.%I
            FOR EACH ROW EXECUTE PROCEDURE %I.%I();
    ', trigger_name, origin_schema, origin_table, trigger_name, origin_schema, origin_table, audit_schema, trigger_func_name);
    
END;
$$ LANGUAGE plpgsql;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.

-- 为 example.test_info 表创建审计
SELECT audit.create_audit_trigger('example', 'test_info');

-- 为 other_schema.employee 表创建审计，并指定审计表存放在 custom_audit schema中
SELECT audit.create_audit_trigger('other_schema', 'employee', 'custom_audit');
1.
2.
3.
4.
5.

-- 查询最近的操作记录
SELECT operation, operation_time, user_name, id, name 
FROM audit.test_info_bak 
ORDERBY operation_time DESC
LIMIT 100;

-- 查询特定用户的操作
SELECT*FROM audit.test_info_bak 
WHERE user_name ='postgres'
ORDERBY operation_time DESC;

-- 查询特定记录的变更历史
SELECT operation, operation_time, user_name, old_content, name, status
FROM audit.test_info_bak 
WHERE id =123
ORDERBY operation_time;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.