PostgreSQL：记录数据库中的每一处改动

数据丢失对任何公司来说都可能是一场灾难。虽然许多人依赖频繁的备份和实时复制，但这并不能保证数据不会被覆盖并永远丢失。即使在较短的备份间隔期间，信息仍然可以被写入和删除，而复制仅仅保留了数据库的最新版本——丢失了其中的变化历史。

简而言之: 学会如何在PostgreSQL中实现强大的数据库审计，以便跟踪每一个变更，通过使用SET LOCAL和current_setting来识别执行操作的用户，并在事务开始时添加自定义行为以便进行日志记录、监控或验证。请访问GitHub仓库获得更多详细信息。

审计是一种记录数据库表中任何更改或删除的方法。通过这样做，可以恢复数据库中任何对象的状态，从而有效地为每次修改创建历史记录。实现审计有多种方式，其中最简单的是在应用层面进行。在此方法中，应用程序会在任何写入或删除操作之前或之后保存当前数据状态到一个单独的表里，通常称为“审计日志”或“版本记录”。

例如，在 Ruby 生态系统中，一些流行的 gem，例如 paper_trail 和 audited，通过跟踪每次更改和删除，并为每个记录生成版本来处理这种情况。然而，这种方法也存在一些局限性。

虽然说应用层审计是个不错的开端，但它也有一些不足之处。

1. 人为失误：如果手动操作，开发人员可能会忘记记录这些变更。

2. ORM 的限制：对象关系映射器 (ORM) 可能会错过更改。比如在 Ruby on Rails 中的 ActiveRecord，批量更新（如 update_all）不会像单条记录更新那样触发常规回调机制。

3. 直接 SQL 访问：代码直接通过 SQL 进行的更改会完全跳过 ORM 层，这也就意味着没有任何审计日志会被记录下来。

4. 数据库访问控制：如果有人可以直接访问数据库（比如 psql 控制台），就能做出未被追踪的改动。

要解决这些问题，最好直接在数据库里管理审计日志。

你可以利用 PostgreSQL 内置的功能，让数据库自行处理审计。这需要创建一个触发器函数，每当有 INSERT、UPDATE 和 DELETE 操作时，就把这些操作记录到一个专用日志表中。以下是如何实现的方法。

第一步：创建审计方案和表格

审核表随时间可能会变得非常庞大，这使得备份实际数据变得缓慢。最好将审核表定义在同一个数据库的单独模式中。这样我们就可以选择做备份整个数据库，包括审核表，或者分别备份数据和审核表的内容。

    -- 创建名为 "audit" 的模式  
    CREATE SCHEMA audit;  
    -- 从公共角色中撤销在审计模式中的创建权限  
    REVOKE CREATE ON SCHEMA audit FROM public;  

    CREATE TABLE audit.日志操作 (  
        schema_name TEXT NOT NULL,  
        table_name TEXT NOT NULL,  
        record_id INTEGER NOT NULL,  
        user_name TEXT,  
        操作时间戳 TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,  
        action TEXT NOT NULL CHECK (action IN ('I', 'D', 'U')),  
        original_data TEXT,  
        new_data TEXT,  
        query TEXT  
    ) WITH (fillfactor=100);  -- 设置填充因子为100

    -- 从公共角色中撤销对审计日志操作的所有权限  
    REVOKE ALL ON audit.日志操作 FROM public;  
    -- 向公共角色授予审计日志操作的查询权限  
    GRANT SELECT ON audit.日志操作 TO public;

这个 logged_actions 表将存储每个更改的详细信息。action 列将记录操作的类型（I 表示插入，D 表示删除，U 表示更新）。您可以在经常被查询的列上添加索引以提高性能。

创建名为 logged_actions_schema_table_idx 的索引
在 audit.logged_actions 上 (模式名与表名的组合::TEXT);

创建名为 logged_actions_action_tstamp_idx 的索引
在 audit.logged_actions 上 (操作时间戳);

创建名为 logged_actions_action_idx 的索引
在 audit.logged_actions 上 (操作);

步骤2：定义触发器函数如下。

以下触发器函数将在每当目标表中的任何一行被修改时，插入一条记录到审计日志的logged_actions表中。

    CREATE OR REPLACE FUNCTION audit.log_current_action() RETURNS trigger AS $body$  
    DECLARE  
        v_old_data TEXT;  
        v_new_data TEXT;  
    BEGIN  
        IF (TG_OP = 'UPDATE') THEN  
            v_old_data := ROW(OLD.*);  
            v_new_data := ROW(NEW.*);  
            INSERT INTO audit.logged_actions   
            (schema_name, table_name, record_id, user_name, action, original_data, new_data, query)  
            VALUES   
            (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, NEW.id, session_user::TEXT, substring(TG_OP,1,1), v_old_data, v_new_data, current_query());  
            RETURN NEW;  
        ELSIF (TG_OP = 'DELETE') THEN  
            v_old_data := ROW(OLD.*);  
            INSERT INTO audit.logged_actions   
            (schema_name, table_name, record_id, user_name, action, original_data, query)  
            VALUES   
            (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, OLD.id, session_user::TEXT, substring(TG_OP,1,1), v_old_data, current_query());  
            RETURN OLD;  
        ELSIF (TG_OP = 'INSERT') THEN  
            v_new_data := ROW(NEW.*);  
            INSERT INTO audit.logged_actions   
            (schema_name, table_name, record_id, user_name, action, new_data, query)  
            VALUES   
            (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, NEW.id, session_user::TEXT, substring(TG_OP,1,1), v_new_data, current_query());  
            RETURN NEW;  
        ELSE  
            RAISE WARNING '[AUDIT.LOG_CURRENT_ACTION] - 其他操作发生: %, 时间: %t', TG_OP, now();  
            RETURN NULL;  
        END IF;  
    EXCEPTION  
        WHEN data_exception THEN  
            RAISE WARNING '[AUDIT.LOG_CURRENT_ACTION] - 数据异常';  
            RETURN NULL;  
        WHEN unique_violation THEN  
            RAISE WARNING '[AUDIT.LOG_CURRENT_ACTION] - 唯一性冲突';  
            RETURN NULL;  
        WHEN unknown THEN  
            RAISE WARNING '[AUDIT.LOG_CURRENT_ACTION] - 其他异常';  
            RETURN NULL;  
    END;  
    $body$  
    LANGUAGE plpgsql  
    SECURITY DEFINER  
    SET search_path = pg_catalog, audit;

第三步：将触发器关联到表。

将此触发器添加到您希望审计的每个表中：

-- 创建名为tablename_audit的触发器，用于在对表进行插入、更新或删除操作后记录当前操作。
CREATE TRIGGER tablename_audit  
AFTER INSERT OR UPDATE OR DELETE ON tablename  
-- 对每一行执行相应的函数
FOR EACH ROW EXECUTE FUNCTION audit.log_current_action();

在 Ruby on Rails 中，你可以使用以下代码片段如下所示为数据库中的每个表自动完成这个过程：

# 代码片段示例

实际内容根据具体需求编写。

(ActiveRecord::Base.connection.tables - ["schema_migrations", "ar_internal_metadata"]).each do |table_name|
  ActiveRecord::Base.connection.execute(<<-SQL)
    CREATE TRIGGER #{table_name}_audit
    AFTER INSERT OR UPDATE OR DELETE ON #{table_name}
    FOR EACH ROW EXECUTE FUNCTION audit.log_current_action();
  SQL
end

为了确保不会忘记给新创建的表添加触发器，扩展 ActiveRecord 的 create_table 和 drop_table 方法的功能。

    定义模块 TableWithTrigger  
      def create_table(table_name, **options)  
        # 创建表  
        super(table_name, **options) do |t|  
          yield(t) if block_given?  
        end  
        add_trigger(table_name)  
      end  

      def drop_table(table_name, **options)  
        remove_trigger(table_name)  
        # 删除表  
        super(table_name, **options)  
      end  

      private  

      # 添加触发器  
      def add_trigger(table_name)  
        execute <<-SQL  
          CREATE TRIGGER #{table_name}_trigger  
          AFTER INSERT OR UPDATE OR DELETE ON #{table_name}  
          FOR EACH ROW EXECUTE FUNCTION audit.log_current_action();  
        SQL  
      end  

      # 删除触发器  
      def remove_trigger(table_name)  
        execute <<-SQL  
          DROP TRIGGER IF EXISTS #{table_name}_trigger;  
        SQL  
      end  
    end  

    # 当活动记录加载时  
    ActiveSupport.on_load(:active_record) do  
      # 前置模块 TableWithTrigger  
      ActiveRecord::ConnectionAdapters::SchemaStatements.prepend(TableWithTrigger)  
    end

数据库级别审计的一个挑战是追踪哪个用户进行了操作。相比之下，应用级别的审计中用户身份很容易获取，而在数据库级别审计中，session_user 默认情况下反映的是数据库用户而非实际的应用程序用户。

PostgreSQL 的 SET LOCAL 和 current_setting 提供了一种在数据库连接期间设置会话特定变量来跟踪应用程序用户的方法。这种方法可以无缝地应用于像 Rails 这样的应用程序。

PostgreSQL 允许你在事务中使用 SET LOCAL 设置自定义会话变量。这些变量仅在当前事务中有效，确保它们不会在其他数据库操作中保留。例如，你可以使用 SET LOCAL 在事务中设置自定义会话变量。

设定变量

• 应用上下文：在一个 Rails 应用中，该变量可以在控制器的 before_action 回调中设置，用于追踪已验证的用户。
• 数据库上下文：该变量可以在审计触发程序中使用 current_setting() 获取。

Rails 示例工作流程

1. 在Around动作中设置当前用户：在你的ApplicationController中添加一个around_action来，为每个请求设置当前用户。

    class ApplicationController < ActionController::Base  
      允许现代浏览器 versions: :modern  

      环绕动作 :设置用户  

      私有  

      def 设置用户  
        当前设置用户为当前用户 do  
          yield  
        end  
      end  
    end

2. 在 ActiveRecord 中设置当前用户ID

模块 CustomTransactionBehavior  
定义方法 begin_db_transaction  
begin_db_transaction  
super  
internal_execute("SET LOCAL app.current_user_id to '#{Current.user&.id || 'Guest'}';", "TRANSACTION", allow_retry: true, materialize_transactions: false)  
end  

当 ActiveSupport 加载 :active_record 时 do  
ActiveRecord::ConnectionAdapters::PostgreSQL::DatabaseStatements.prepend(CustomTransactionBehavior)  
end

SET LOCAL 命令在当前会话中为 app.current_user 设置了一个值。

3. 在 PostgreSQL 中访问变量，修改审计触发器功能来读取 app.current_user 变量：

    CREATE OR REPLACE FUNCTION audit.记录当前操作() RETURNS trigger AS $
    DECLARE
        用户名 TEXT;
        旧数据 TEXT;
        新数据 TEXT;
    BEGIN
        用户名 := current_setting('app.current_user_id', false);

        IF (TG_OP = 'UPDATE') THEN
            旧数据 := ROW(OLD.*);
            新数据 := ROW(NEW.*);
            INSERT INTO audit.logged_actions (
                schema_name, table_name, record_id, user_name, action, original_data, 新数据, query
            ) VALUES (
                TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.id, 用户名, 'U', 旧数据, 新数据, current_query()
            );
            返回新记录;
        ELSIF (TG_OP = 'DELETE') THEN
            旧数据 := ROW(OLD.*);
            INSERT INTO audit.logged_actions (
                schema_name, table_name, record_id, user_name, action, 旧数据, query
            ) VALUES (
                TG_TABLE_SCHEMA, TG_TABLE_NAME, OLD.id, 用户名, 'D', 旧数据, current_query()
            );
            返回旧记录;
        ELSIF (TG_OP = 'INSERT') THEN
            新数据 := ROW(NEW.*);
            INSERT INTO audit.logged_actions (
                schema_name, table_name, record_id, user_name, action, 新数据, query
            ) VALUES (
                TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.id, 用户名, 'I', 新数据, current_query()
            );
            返回新记录;
        ELSE
            RAISE WARNING '未知的操作: %', TG_OP;
            返回空值;
        END IF;
    END;
    $ LANGUAGE plpgsql SECURITY DEFINER;

-- 该函数用于记录对数据库的更新、插入和删除操作

在 PostgreSQL 中使用 SET LOCAL，并通过应用程序范围的控制器的 around_action 发布 ActiveRecord，可以在数据库审计中提供应用程序用户的细粒度跟踪，确保每个事务都与实际发起操作的用户相关联。这种方法是安全的，因为变量的作用域仅限于事务，并且可以轻松集成到应用程序逻辑中。然而，这种方法需要一致的实现以避免遗漏用户上下文，并且可能无法与不支持按事务设置的连接池解决方案（如 pgbouncer）一起使用。

current_setting 的第二个参数是 missing_ok，它决定了当设置不存在时的行为表现。要使它成为可选参数而不是必填项，可以将 missing_ok 设为 true。

user_name := 获取当前设置('app.current_user_id', true);

审计对写入操作性能的影响

直接在 PostgreSQL 中添加审计会影响写入性能的几个方面：

1. 增加的I/O: 每次写操作（INSERT、UPDATE或DELETE）现在都会触发对审计表audit.logged_actions的额外写入，从而增加了磁盘I/O负载。
2. 更高的CPU负载: 触发函数会在每次插入、更新或删除操作时运行，消耗CPU资源并减慢响应速度，特别是在处理高流量表时尤为明显。
3. 存储增长: 每次记录变动时审计表都会随之增长，需要精心管理磁盘并定期维护（vacuuming和归档）以避免膨胀。
4. 事务复杂性: 每个事务中都包含审计。回滚操作也会撤销相应的审计记录，增加了复杂性并可能会导致延迟。

如何在审计需求和性能之间找到平衡：

• 仅对关键表选择性地应用触发器。
• 限制日志数据只保留必要的列。
• 归档旧日志，并定期清理以防止表膨胀。
• 对审计表进行分区以提高查询性能并便于维护。

总之，虽然审计会影响写入速度，但通过仔细配置和维护可以管理这些影响，确保不会过度加载数据库，同时保持审计日志有效。

如果你的应用程序是数据密集型的，考虑采用其他方法来减少数据库级别的审计影响。

1. 应用层面审计: 使用ORM工具（如Ruby中的paper_trail）来跟踪应用代码的变化。这种方式避免了数据库触发器，但可能会错过直接的SQL变更。
2. 基于日志的审计: 利用PostgreSQL的预写日志（WAL）来追踪变更，避免了触发器的开销。虽然这种方式高效，但解析和管理日志较复杂。
3. 事件溯源: 将每个变更存储为一个“事件”，从而内置了一个审计轨迹。这种方法适合高吞吐量系统，但需要改变数据管理策略。
4. 外部日志服务: 使用如ELK栈的工具来收集和处理审计日志，处理大量日志而不影响写性能。

根据您的应用性能和审计需求进行选择。

谢谢读我！