Postgres 的审计跟踪

引言

本文介绍如何在PostgresSQL中实现审计表。它与其他一些数据库有所不同。

背景

Cedric Baelemans 在他的文章 Microsoft SQL的审计跟踪生成器 中介绍了使用审计表或他所说的影子表。我决定为PostgresSQL做同样的事情，看看在该数据库中是如何实现的。

Using the Code

大多数数据库将为每种数据库操作类型设置一个触发器。使用这种实现方式，您需要大量的触发器和代码来完成工作。使用PostgresSQL的这种实现方式，您只需要每个表一个函数。下面我将展示主表和审计表的两个表定义。

//
// The primary table 
//
CREATE TABLE MinUser (
	User_Id   			UUID NOT NULL PRIMARY KEY,
	User_Name 			varchar (50) NOT NULL,
	User_Password			varchar (50) NOT NULL,
	User_Email			varchar (50),
	User_Role 			varchar (50),
	UNIQUE (User_Name)
) WITH (OIDS=FALSE);
//
// The Audit table 
//
CREATE TABLE MinUser_Audit (
	AuditUser_Id   			Serial PRIMARY KEY,
        operation 		        char(1)   NOT NULL,
        stamp            		timestamp NOT NULL,
        userid            		text      NOT NULL,
	User_Id   			UUID NOT NULL,
	User_Name 			varchar (50) NOT NULL,
	User_Password			varchar (50) NOT NULL,
	User_Email			varchar (50),
	User_Role 			varchar (50)
) WITH (OIDS=FALSE);

在PostgresSQL中，当触发触发器时会调用一个函数。我将其设置为对每个表的更新、插入和删除操作触发相同的函数。在函数中，我检查操作是什么，然后写入审计表。这个函数与PostgresSQL附带的文档中的函数相同，除了我的表有一个序列键，这导致示例代码失败。

CREATE OR REPLACE FUNCTION MinUser_audit() RETURNS TRIGGER AS $usr_audit$
    BEGIN
        --
        -- Create a row in MinUser_Audit to reflect the operation performed on MinUser,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'D', now(), user, OLD.*);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'U', now(), user, NEW.*);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'I', now(), user, NEW.*);
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$usr_audit$ LANGUAGE plpgsql;

文档中的原始代码有一个SELECT语句，用于将旧数据获取到审计记录中。下面显示代码以显示差异。由于我正在使用序列键，因此必须使用DEFAULT关键字才能使序列正常工作，或者独立地移动每一列。这可以通过执行OLD.User_Id和OLD.User_Name等操作来实现所有数据成员。

INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;

函数中使用了几个特殊的运算符。含义如下：

• NEW      数据类型 RECORD；变量保存 INSERT/UPDATE 操作的新数据库行。
• OLD       数据类型 RECORD；变量保存 UPDATE/DELETE 操作的旧数据库行。
• TG_OP   数据类型 text；一个字符串，表示 INSERT、UPDATE 或 DELETE，用于指示触发触发器所针对的操作。

有关完整的关键字集，请参阅PostgresSQL文档。下一步是使其触发。为此，您必须添加一个触发器。调用CREATE TRIGGER SQL 时，必须说明是在更改之前还是之后，以及要触发此操作的类型。在这个特定的应用程序中，我希望它为INSERT、UPDATE和DELETE操作触发。

CREATE TRIGGER MinUser_auditt AFTER INSERT OR UPDATE OR DELETE ON MinUser 
    FOR EACH ROW EXECUTE PROCEDURE MinUser_audit();

现在，您可以当然使用为每个操作和表设置单独触发器的实现，但这会创建大量的函数和触发器。这种实现只需要您想要进行审计跟踪的每个表一个。

关注点

在Code Project中有几篇关于触发器的文章，如果您对触发器感兴趣，应该阅读这些文章。还有不同的实现方式。一种实现方式会像我一样复制记录，另一种只会复制更改的项目。每种方式都有其优点。您也不必对每个表进行审计，只需对您想要查看更改的表进行审计即可。

历史

2009年1月20日 第一篇文章