审核触发器

来自 PostgreSQL Wiki
跳转到导航跳转到搜索

库代码片段

通用审核触发器功能

适用于 PostgreSQL

8.4+

使用书面形式

PL/pgSQL

依赖于

无


以下是记录表更改到审核日志表的通用触发器功能示例。它将记录旧记录和新记录、受影响的表、进行更改的用户以及每个更改的时间戳。

请参阅触发器源后的说明，了解有关时间戳时区、触发器限制以及如何将值更改记录为 json 等信息。

您可以在 PostgreSQL 9.1+ 审计触发器代码片段页面中使用适用于 PostgreSQL 9.1+ 的功能更强大的审计触发器。

触发器

-- create a schema named "audit"
create schema audit;
revoke create on schema audit from public;

create table audit.logged_actions (
    schema_name text not null,
    table_name text not null,
    user_name text,
    action_tstamp 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);

revoke all on audit.logged_actions from public;

-- You may wish to use different permissions; this lets anybody
-- see the full audit data. In Pg 9.0 and above you can use column
-- permissions for fine-grained control.
grant select on audit.logged_actions to public;

create index logged_actions_schema_table_idx 
on audit.logged_actions(((schema_name||'.'||table_name)::TEXT));

create index logged_actions_action_tstamp_idx 
on audit.logged_actions(action_tstamp);

create index logged_actions_action_idx 
on audit.logged_actions(action);

--
-- Now, define the actual trigger function:
--

CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS trigger AS $body$
DECLARE
    v_old_data TEXT;
    v_new_data TEXT;
BEGIN
    /*  If this actually for real auditing (where you need to log EVERY action),
        then you would need to use something like dblink or plperl that could log outside the transaction,
        regardless of whether the transaction committed or rolled back.
    */

    /* This dance with casting the NEW and OLD values to a ROW is not necessary in pg 9.0+ */

    if (TG_OP = 'UPDATE') then
        v_old_data := ROW(OLD.*);
        v_new_data := ROW(NEW.*);
        insert into audit.logged_actions (schema_name,table_name,user_name,action,original_data,new_data,query) 
        values (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,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,user_name,action,original_data,query)
        values (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,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,user_name,action,new_data,query)
        values (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_new_data, current_query());
        RETURN NEW;
    else
        RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - Other action occurred: %, at %',TG_OP,now();
        RETURN NULL;
    end if;

EXCEPTION
    WHEN data_exception THEN
        RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
    WHEN unique_violation THEN
        RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
    WHEN others THEN
        RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
END;
$body$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, audit;

--
-- To add this trigger to a table, use:
-- CREATE TRIGGER tablename_audit
-- AFTER INSERT OR UPDATE OR DELETE ON tablename
-- FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func();
--

不适用于表内审计

不应用触发器来设置表中的“上次更新”和“谁更新”列。此操作应由一个单独的BEFORE INSERT OR UPDATE OR DELETE ... FOR EACH ROW触发器完成。您可能希望记录没有内置审计时间/谁列的表上的历史记录，或向您不想生成完整审计日志的表添加时间/谁数据，因此这种分离是有意义的。更重要的是，设置时间/谁数据需要BEFORE触发器，但此审计日志记录触发器必须作为AFTER触发器运行，以确保它在其他触发器作用后捕获行的最终状态。

限制

对于可使用 Pg 进行审计的触发器，存在限制。然而，使用触发器无法审计的大多数内容仍然可以在系统日志中进行跟踪，更重要的是，可以使用且应该将这些内容置于普通用户的权限之外。

如果您的生产应用使用没有超级用户权限的角色登录到数据库，或者CREATEUSER CREATEDB权限，如果该角色不拥有这些表，而你确保其只拥有所需的最低限度权限授予了，你就可以高枕无忧。

限制：无法审计 SELECT

此审计触发器不捕获选择活动。审计的唯一方式选择PostgreSQL 是通过系统日志，选择因为不支持触发器，选择而且如果不支持触发器中的自主事务，审计将无法在回滚时防止审计数据丢失。

限制：无法审计系统表

你无法在系统目录表上使用这样的审计触发器，因此无法审计创建角色或者创建表等等。

由于生产应用程序角色永远不应该拥有创建用户或者创建数据库权限，而且通常不应该拥有创建除了临时表架构以外，对任何架构的权限，因此在实践中不会有问题。

限制：无法审计 DDL

PostgreSQL 没有挂钩来触发 DDL（修改表语句等）。

由于只有表的所有者或超级用户才能对表执行 DDL，因此这对于生产审计日志不会有问题。

限制：无法可靠地审计超级用户或所有者

这应该是显而易见的，但似乎被忽视了：你无法使用数据库内审计来安全地跟踪超级用户、拥有被审计表的角色或拥有审计表的角色的操作。审计日志会起作用，但可以被这些用户简单地篡改。

你的生产应用程序应使用非特权角色运行，并且不应拥有其使用的任何表的所有权。使用不同的角色创建它们并授予生产应用程序角色只拥有其需要的权限。

关于时间戳如何工作的详细信息

该action_tstamp时间戳使用current_timestamp，因为它在交易开始时是当前的，因此与同一个事务中每次调用audit.if_modified_func的时间戳将相同。如果你愿意，可以添加一个附加列来记录statement_timestamp()以记录来自客户端的当前顶级语句开始的时间，或者甚至clock_timestamp()如果你希望它在日志记录插入时是真实时间。

请注意带时区的时间戳时间始终以 UTC 存储，默认情况下以当地时间显示。你可以使用在时间区域在查询中，或者使用将时区设为 = 'UTC'作为每个会话的 GUC。请参阅Pg 文档。

以 JSON 形式审计值

对于 PostgreSQL 9.2，或与极好的 json_91 附加组件 搭配使用的 PostgreSQL 9.1，您可以以结构化 json 而非纯文本的形式记录表中的旧值和新值，从而能够更有效地查询审计历史记录。只需更改以下字段的类型：v_old_data, v_new_data, original_data和new_data从TEXT更改为json，然后用ROW(OLD.*)和ROW(NEW.*)分别替换row_to_json(OLD)和row_to_json(NEW)。

此外，您可以使用 增强型审计触发器 91plus 来审计 hstore 值。

使触发器更灵活

如果您想省略全部查询文本，或者针对一些表格而不是其他表格而省略数据，这样做并不难。PostgreSQL 触发器可以在CREATE TRIGGER期间传递的参数。每次从该CREATE TRIGGERstatement 执行该特定触发器实例时都会传递这些参数。

例如，如果您定义了一个触发器mytrigger(log_query_text boolean, log_values boolean)那么在CREATE TRIGGERstatement 中，您可以说EXECUTE PROCEDURE mytrigger(false, true)这些参数将传递给触发器函数调用。触发器可以使用IF或者CASE语句基于参数来更改其行为。

可以轻松地扩展上述触发器函数，以便您在不同的调用中打开和关闭查询文本记录、值记录等功能。

演示/示例代码

 -- Tested with a table named "t"
 drop table if exists t;
 create table t (x int not null primary key, y text);

 -- needs to be applied to all tables that we want to monitor

 -- this is a test trigger to show how we can audit all changes to the relevant tables, including inserts
 CREATE TRIGGER t_if_modified_trg 
 AFTER INSERT OR UPDATE OR DELETE ON t
 FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func();


 -- Some sample updates, deletes, and inserts to illustrate the points
 select * from t; select * from audit.logged_actions;

 insert into t (x,y) values (1,'asdf'),(2,'werwer'),(3,null);

 select * from t; select * from audit.logged_actions;

 -- You may have noticed that the times output in the prior query are in your local time. 
 -- They're stored as UTC, but Pg is converting them for display according to the 'timezone' GUC.
 SHOW timezone;

 -- See?
 SET timezone = 'UTC';
 SELECT * FROM audit.logged_actions;
 RESET timezone;
 -- Another way to achieve the same effect:
 SELECT *, action_tstamp AT TIME ZONE 'UTC' AS action_tstamp_utc FROM audit.logged_actions;

 update t set y='eeeeee' where x=2;
 select * from t; select * from audit.logged_actions;

 update t set y='yuyuyuy' where x=3;
 select * from t; select * from audit.logged_actions;

 delete from t where x=1;
 select * from t; select * from audit.logged_actions;

 -- should be a pk violation
 update t set x=4 where x=2;
 select * from t; select * from audit.logged_actions;

原始代码由 bricklen 编写，由 ringerc 修改，用以将表内审计与审计记录分开

从 "https://wiki.postgresql.ac.cn/index.php?title=Audit_trigger&oldid=17940" 检索