审计日志

PostgreSQL 提供了事件触发器。事件触发器对一个特定数据库来说是全局的，并且可以捕捉该数据库中发生的 DDL 事件。Redrock Postgres 通过对 PostgreSQL 进行了如下改进，从而让用户可以通过事件触发器，实现对 DDL 操作的审计：

在 Redrock Postgres 中，角色和表空间都是数据库级别的对象，事件触发器也支持作用于角色和表空间对象的 DDL 命令，详情可参见事件触发器触发矩阵；
支持在命令结束处（对应ddl_command_end事件）捕捉删除对象的操作；
提供函数 pg_ddl_command_deparse 将DDL命令从pg_ddl_command类型的内部格式反向解析为 SQL 语句；

下面将以示例的方式介绍如何审计数据库中发生的 DDL 和 DML 操作，您可以根据业务情况对示例中的操作命令进行相应修改。

创建审计日志记录表

使用超级用户或者数据库拥有者登录数据库，创建审计日志记录表，用于记录事件：

CREATE SCHEMA IF NOT EXISTS audit;
REVOKE ALL ON SCHEMA audit FROM public;

-- create table for audit logging
CREATE TABLE IF NOT EXISTS audit.logged_actions(
  event_time      timestamp,
  username        text,
  object_type     text,
  schema_name     text,
  object_identity text,
  application_name text,
  client_addr     inet,
  client_port     integer,
  command_tag     text,
  command         text
);

-- grant privileges to all user
GRANT INSERT ON TABLE audit.logged_actions TO PUBLIC;

其中表audit.logged_actions包括的列如下：

名称	描述
`event_time`	操作执行的时间
`username`	执行该操作的用户
`object_type`	对象的类型。可能的类型是 `table`, `index`, `sequence`, `view`, `materialized view`, `foreign table`, `aggregate`, `function`, `type`, `cast`, `collation`, `rule`, `trigger`, `schema`, `role`, `tablespace`, `foreign data wrapper`, `server`, `user mapping`, `extension`, `policy`, `publication` 以及 `subscription`。
`schema_name`	该对象所属的模式的名称（如果有），如果没有则为`NULL`。没有引号修饰。
`object_identity`	对象标识的文本表现形式，用模式限定。如果必要，出现在该标识中的每一个标识符会被引号修饰。
`application_name`	执行该操作的客户端应用名称
`client_addr`	执行该操作的客户端网络地址
`client_port`	执行该操作的客户端网络端口
`command_tag`	命令标签。可能的值请参见支持事件触发器的命令标签。
`command`	执行的 SQL 命令

审计 DDL 操作

我们可以通过事件触发器，捕捉该数据库中发生的 DDL 事件，并记录事件信息到审计记录表中。

创建 DDL 事件触发器

-- create function for event trigger
CREATE OR REPLACE FUNCTION audit.ddl_trigger_func()
  RETURNS event_trigger AS $$
BEGIN
  INSERT INTO audit.logged_actions
    SELECT now(), current_user,
           object_type, schema_name, object_identity,
           current_setting('application_name'),
           inet_client_addr(), inet_client_port(),
           command_tag, pg_ddl_command_deparse(command)
      FROM pg_event_trigger_ddl_commands();
END;
$$ LANGUAGE plpgsql;

-- create ddl_command_end event trigger
CREATE EVENT TRIGGER audit_event_trigger
  ON ddl_command_end
  EXECUTE PROCEDURE audit.ddl_trigger_func();

执行完以上命令后，您的 DDL 操作将会记录在表audit.logged_actions中。

配置 DDL 操作的审计

您可以通过修改 DDL 事件触发器函数定义，对 DDL 操作的审计进行配置。参照上面表audit.logged_actions的定义，您可以根据 DDL 操作的执行用户、影响的对象类型、对象所属模式、对象名称、命令标签，以及客户端应用名称、网络地址、网络端口等信息，在 DDL 事件触发器函数定义中添加筛选条件，对 DDL 操作的审计进行配置。比如，我们可以将上面的事件触发器函数audit.ddl_trigger_func的定义修改如下，限定审计的 DDL 操作涉及的对象类型：

CREATE OR REPLACE FUNCTION audit.ddl_trigger_func()
  RETURNS event_trigger AS $$
BEGIN
  INSERT INTO audit.logged_actions
    SELECT now(), current_user,
           object_type, schema_name, object_identity,
           current_setting('application_name'),
           inet_client_addr(), inet_client_port(),
           command_tag, pg_ddl_command_deparse(command)
      FROM pg_event_trigger_ddl_commands();
      WHERE object_type IN ('table', 'role');
END;
$$ LANGUAGE plpgsql;

在上面的例子中，我们将 DDL 操作涉及的对象类型限定在table和role。

审计 DML 操作

我们可以创建一个通用触发器函数，用于将对表的更改记录到审计日志表中。它将记录 SQL 语句、受影响的表信息、进行更改的用户以及每个更改发生的时间戳。

创建触发器函数

CREATE OR REPLACE FUNCTION audit.if_modified_func()
  RETURNS TRIGGER AS $$
BEGIN
  IF TG_WHEN <> 'AFTER' OR TG_LEVEL <> 'STATEMENT' THEN
    RAISE EXCEPTION 'audit.if_modified_func() may only run as statement level AFTER trigger';
  END IF;

  INSERT INTO audit.logged_actions VALUES (
    now(), current_user, 'table', TG_TABLE_SCHEMA, TG_TABLE_NAME,
    current_setting('application_name'),
    inet_client_addr(), inet_client_port(),
    TG_OP, current_query());
END;
$$ LANGUAGE plpgsql;

审计表上的 DML 操作

在需要进行审计的表上创建触发器，用于审计表数据修改操作：

CREATE TRIGGER trigger_audit_testtbl
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON testtbl
  FOR EACH STATEMENT
  EXECUTE PROCEDURE audit.if_modified_func();

上面的 SQL 命令对表testtbl上的 INSERT、UPDATE、DELETE 和 TRUNCATE 操作进行了审计。您需要根据自己的实际情况，修改 SQL 命令中的表名和触发器名称。另外，您也可以选择只对表上的部分操作进行审计，比如，只审计表上的 DELETE 和 TRUNCATE 操作。