PostgreSQL 如何进行审计？

推荐答案

在 PostgreSQL 中，可以通过以下几种方式进行审计：

1. 使用 pg_audit 扩展：

   • pg_audit 是一个开源的 PostgreSQL 扩展，专门用于审计数据库活动。它能够记录详细的 SQL 语句、执行时间、用户信息等。
   • 安装 pg_audit 扩展后，可以通过配置 pg_audit.log 参数来指定需要审计的操作类型（如 SELECT, INSERT, UPDATE, DELETE 等）。

2. 使用 pg_stat_activity 视图：

   • pg_stat_activity 是 PostgreSQL 提供的一个系统视图，可以实时监控当前数据库连接的活动情况。
   • 通过查询 pg_stat_activity，可以获取当前执行的 SQL 语句、用户信息、连接时间等。

3. 使用 log_statement 参数：

   • 在 postgresql.conf 配置文件中，可以通过设置 log_statement 参数来记录所有执行的 SQL 语句。
   • 该参数可以设置为 none, ddl, mod, 或 all，分别表示不记录、只记录 DDL 语句、记录修改数据的语句、或记录所有语句。

4. 使用触发器：

   • 可以通过在表上创建触发器来记录对表的操作（如 INSERT, UPDATE, DELETE）。
   • 触发器可以将操作记录到一个专门的审计表中，以便后续查询和分析。

本题详细解读

1. 使用 pg_audit 扩展

pg_audit 是一个功能强大的审计工具，能够记录详细的数据库操作信息。以下是使用 pg_audit 的步骤：

• 安装 pg_audit：

  CREATE EXTENSION pg_audit;

• 配置 pg_audit.log： 在 postgresql.conf 中配置 pg_audit.log 参数，例如：

  pg_audit.log = 'all, -misc'

  这将记录所有操作，除了 misc 类别的操作。

• 查看审计日志： 审计日志通常会被记录到 PostgreSQL 的日志文件中，可以通过查看日志文件来获取审计信息。

2. 使用 pg_stat_activity 视图

pg_stat_activity 视图提供了当前数据库连接的活动信息。可以通过以下 SQL 查询来监控数据库活动：

SELECT datname, usename, state, query, query_start 
FROM pg_stat_activity;

该查询将返回当前数据库连接的数据库名、用户名、连接状态、执行的 SQL 语句以及查询开始时间。

3. 使用 log_statement 参数

log_statement 参数可以记录所有执行的 SQL 语句。配置方法如下：

• 在 postgresql.conf 中设置：

  log_statement = 'all'

• 重启 PostgreSQL 服务以使配置生效。

4. 使用触发器

触发器可以用于记录对表的操作。以下是一个简单的触发器示例：

• 创建审计表：

  // www.javascriptcn.com code example
  CREATE TABLE audit_log (
      id SERIAL PRIMARY KEY,
      table_name TEXT,
      operation TEXT,
      old_data JSONB,
      new_data JSONB,
      user_name TEXT,
      operation_time TIMESTAMP
  );

• 创建触发器：

  // www.javascriptcn.com code example
  CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$
  BEGIN
      IF (TG_OP = 'INSERT') THEN
          INSERT INTO audit_log (table_name, operation, new_data, user_name, operation_time)
          VALUES (TG_TABLE_NAME, 'INSERT', row_to_json(NEW), current_user, now());
      ELSIF (TG_OP = 'UPDATE') THEN
          INSERT INTO audit_log (table_name, operation, old_data, new_data, user_name, operation_time)
          VALUES (TG_TABLE_NAME, 'UPDATE', row_to_json(OLD), row_to_json(NEW), current_user, now());
      ELSIF (TG_OP = 'DELETE') THEN
          INSERT INTO audit_log (table_name, operation, old_data, user_name, operation_time)
          VALUES (TG_TABLE_NAME, 'DELETE', row_to_json(OLD), current_user, now());
      END IF;
      RETURN NULL;
  END;
  $$ LANGUAGE plpgsql;

• 将触发器附加到目标表：

  CREATE TRIGGER audit_trigger
  AFTER INSERT OR UPDATE OR DELETE ON your_table
  FOR EACH ROW EXECUTE FUNCTION audit_trigger();

通过以上方法，可以在 PostgreSQL 中实现不同级别的审计功能。