Postgresql 之 基于表的dml审计

作者：瀚高PG实验室 （Highgo PG Lab）- 波罗
应用场景中有些业务表比较关键，需要对关键业务表的变更进行记录，以下通过在表上创建触发器调用函数的方法对关键业务表dml的操作进行跟踪记录到表table_change_rec中，以便后续审计查询。

跟踪的测试表

CREATE TABLE test (id int primary key, info text, crt_time timestamp(0));

创建hstore extension;

CREATE EXTENSION hstore;

创建通用的存储跟踪记录的记录表

CREATE TABLE table_change_rec (
id serial8 primary key,
relid oid,
table_schema text,
table_name text,
when_tg text,
level text,
op text,
old_rec hstore,
new_rec hstore,
crt_time timestamp without time zone DEFAULT now(),
username text,
client_addr inet,
client_port int);

创建通用的触发器函数

CREATE OR REPLACE FUNCTION dml_trace()
RETURNS trigger
LANGUAGE plpgsql
AS $BODY$
DECLARE
v_new_rec hstore;
v_old_rec hstore;
v_username text := session_user;
v_client_addr inet := inet_client_addr();
v_client_port int := inet_client_port();
BEGIN
case TG_OP
when 'DELETE' then
v_old_rec := hstore(OLD.*);
insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, old_rec, username, client_addr, client_port)
values (tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_old_rec, v_username, v_client_addr, v_client_port);
when 'INSERT' then
v_new_rec := hstore(NEW.*);
insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, new_rec, username, client_addr, client_port)
values (tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_new_rec, v_username, v_client_addr, v_client_port);
when 'UPDATE' then
v_old_rec := hstore(OLD.*);
v_new_rec := hstore(NEW.*);
insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, old_rec, new_rec, username, client_addr, client_port)
values (tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_old_rec, v_new_rec, v_username, v_client_addr, v_client_port);
else
return null;
end case;
RETURN null;
END;
$BODY$ strict;

在测试表上创建触发器.

CREATE TRIGGER tg AFTER DELETE or INSERT or UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE dml_trace();

测试插入, 删除, 更新操作是否被跟踪.

postgres=# insert into test values (1, 'highgo', now());
INSERT 0 1
postgres=# update test set info='HighgoDB' where id=1;
UPDATE 1
postgres=# delete from test where id=1;
DELETE 1
postgres=# select * from table_change_rec;
 id | relid  | table_schema | table_name | when_tg | level |   op   |                             old_r
ec                              |                             new_rec                              |   
       crt_time          | username | client_addr | client_port 
----+--------+--------------+------------+---------+-------+--------+----------------------------------
--------------------------------+------------------------------------------------------------------+---
-------------------------+----------+-------------+-------------
  1 | 106952 | public       | test       | AFTER   | ROW   | INSERT |                                  
                                | "id"=>"1", "info"=>"highgo", "crt_time"=>"2018-04-23 10:37:42"   | 20
18-04-23 10:37:42.387136 | postgres |             |            
  2 | 106952 | public       | test       | AFTER   | ROW   | UPDATE | "id"=>"1", "info"=>"highgo", "crt
_time"=>"2018-04-23 10:37:42"   | "id"=>"1", "info"=>"HighgoDB", "crt_time"=>"2018-04-23 10:37:42" | 20
18-04-23 10:37:51.626954 | postgres |             |            
  3 | 106952 | public       | test       | AFTER   | ROW   | DELETE | "id"=>"1", "info"=>"HighgoDB", "c
rt_time"=>"2018-04-23 10:37:42" |                                                                  | 20
18-04-23 10:38:00.382681 | postgres |             |            
(3 rows)

 postgres=# select id,(each(old_rec)).* from table_change_rec;
  id |   key    |        value        
----+----------+---------------------
  2 | id       | 1
  2 | info     | highgo
  2 | crt_time | 2018-04-23 10:37:42
  3 | id       | 1
  3 | info     | HighgoDB
  3 | crt_time | 2018-04-23 10:37:42
(6 rows)

postgres=select id,(each(new_rec)).* from table_change_rec;
 id |   key    |        value        
----+----------+---------------------
  1 | id       | 1
  1 | info     | highgo
  1 | crt_time | 2018-04-23 10:37:42
  2 | id       | 1
  2 | info     | HighgoDB
  2 | crt_time | 2018-04-23 10:37:42
(6 rows)