[PGSQL]PostgreSQL9.3中使用事件触发器做DDL审计

在PostgreSQL9.3中,使用事件触发器实现了其它数据库中的DDL触发器的功能。

不过在PostgreSQL9.3中的事件触发器函数仅仅支持TG_EVENT和TG_TAG两个变量,无法得到执行的实际的DDL语句。不过对于“sql_drop”事件的触发器中的函数中可以调用一个函数pg_event_trigger_dropped_objects()获得删除数据库对象的详细信息。大多数知道什么时候删除了哪个数据库对象这些危险操作,对于安全审计来说基本上就够用了。当然我们也期待在PostgreSQL的后续版本中能获得更多的DDL信息。从 http://wiki.postgresql.org/wiki/Event_Triggers 中知道PostgreSQL已经规划了这些内容,只是目前还没有实现。

下面我们创建一个事件触发器用于记录数据库中对象的删除的审计日志:

CREATE TABLE log_drop_objects(
    op_time timestamp, --实际执行的时间
ddl_tag text, --记录实际的ddl类型
    classid Oid,
    objid    Oid,
    objsubid OID,
    object_type text,
    schema_name text,
    object_name text,
    object_identity text
);

CREATE FUNCTION event_trigger_log_drops()
        RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    obj record;
BEGIN
    INSERT INTO log_drop_objects SELECT now(), tg_tag, classid,objid,objsubid,object_type,schema_name,object_name, object_identity FROM pg_event_trigger_dropped_objects();
END
$$;
CREATE EVENT TRIGGER event_trigger_log_drops
   ON sql_drop
   EXECUTE PROCEDURE event_trigger_log_drops();

做测试,先建一张表:

postgres=# create table test01(id int primary key, note varchar(20));
CREATE TABLE

删除测试表的一个字段:

postgres=# alter table test01 drop column note;
ALTER TABLE
postgres=# select ddl_tag, object_type, object_name, object_identity from log_drop_objects;
   ddl_tag   |   object_type    | object_name |       object_identity        
-------------+------------------+-------------+------------------------------
 ALTER TABLE | table column     |             | public.test01.note
 (1 rows)

可以看到,日志表中记录了删除了字段“public.test01.note”。 把测试表删除掉,然后查看日志表:

postgres=# drop table test01;
DROP TABLE
postgres=# select ddl_tag, object_type, object_name, object_identity from log_drop_objects;
   ddl_tag   |   object_type    | object_name |       object_identity        
-------------+------------------+-------------+------------------------------
 ALTER TABLE | table column     |             | public.test01.note
 DROP TABLE  | table            | test01      | public.test01
 DROP TABLE  | table constraint |             | test01_pkey on public.test01
 DROP TABLE  | index            | test01_pkey | public.test01_pkey
 DROP TABLE  | type             | test01      | public.test01
 DROP TABLE  | type             | _test01     | public.test01[]
(6 rows)

从日志中,我们可以清楚看到,删除一张表,实际数据库内部还删除了表的主键约束、主键索引和以表名相同的类型等数据库对象。