PostgreSQL 触发器应用 - use trigger audit record which column modified, insert, delete.

3 minute read

背景

这个需求需要记录数据被 插入, 删除, 更新的审计, 注意更新的审计包括哪些字段发生了变更.

不需要的审计, 注释对应的触发器代码即可.

触发器代码如下 :

create extension hstore;  
  
CREATE TABLE table_change_rec (  
  id serial8 primary key,  
  relid oid,  
  table_schema name,  
  table_name name,  
  when_tg text,  
  level text,  
  op text,  
  old_rec hstore,  
  new_rec hstore,  
  diff_old_rec text,  
  diff_new_rec text,  
  crt_time timestamp without time zone DEFAULT now(),  
  username name,  
  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_diff_new_rec text;  
  v_diff_old_rec text;  
  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.*);  
    select array_agg(o)::text,array_agg(n)::text into v_diff_old_rec,v_diff_new_rec from (select row_number() over() as rn,o from each(v_old_rec) as o) as o join  
    (select row_number() over() as rn,n from each(v_new_rec) as n) as n on o.rn=n.rn and o<>n;  
    insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, old_rec, new_rec, diff_old_rec, diff_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_diff_old_rec, v_diff_new_rec, v_username, v_client_addr, v_client_port);  
  else  
    return null;  
  end case;  
  RETURN null;  
END;  
$BODY$ strict;  

测试 :

postgres=# create table test (id int, c1 int, c2 text, c3 timestamp);  
CREATE TABLE  
postgres=# CREATE TRIGGER tg AFTER DELETE or INSERT or UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE dml_trace();  
CREATE TRIGGER  
postgres=# truncate table_change_rec ;  
TRUNCATE TABLE  
postgres=# insert into test values (1,1,'digoal',now());  
INSERT 0 1  
postgres=# insert into test values (2,2,'digoal',now());  
INSERT 0 1  
postgres=# update test set id=null,c1=null,c2=null,c3=null where id=1;  
UPDATE 1  
postgres=# update test set c1=2,c3=now() where id=2;  
UPDATE 1  
postgres=# delete from test;  
DELETE 2  

查看审计

postgres=# select * from table_change_rec;  
-[ RECORD 1 ]+-------------------------------------------------------------------------  
id           | 11  
relid        | 16593  
table_schema | public  
table_name   | test  
when_tg      | AFTER  
level        | ROW  
op           | INSERT  
old_rec      |   
new_rec      | "c1"=>"1", "c2"=>"digoal", "c3"=>"2014-12-15 03:43:12.135139", "id"=>"1"  
diff_old_rec |   
diff_new_rec |   
crt_time     | 2014-12-15 03:43:12.135139  
username     | postgres  
client_addr  |   
client_port  |   
-[ RECORD 2 ]+-------------------------------------------------------------------------  
id           | 12  
relid        | 16593  
table_schema | public  
table_name   | test  
when_tg      | AFTER  
level        | ROW  
op           | INSERT  
old_rec      |   
new_rec      | "c1"=>"2", "c2"=>"digoal", "c3"=>"2014-12-15 03:43:14.803063", "id"=>"2"  
diff_old_rec |   
diff_new_rec |   
crt_time     | 2014-12-15 03:43:14.803063  
username     | postgres  
client_addr  |   
client_port  |   

diff的内容, 只有更新的字段被记录

-[ RECORD 3 ]+-------------------------------------------------------------------------  
id           | 13  
relid        | 16593  
table_schema | public  
table_name   | test  
when_tg      | AFTER  
level        | ROW  
op           | UPDATE  
old_rec      | "c1"=>"1", "c2"=>"digoal", "c3"=>"2014-12-15 03:43:12.135139", "id"=>"1"  
new_rec      | "c1"=>NULL, "c2"=>NULL, "c3"=>NULL, "id"=>NULL  
diff_old_rec | {"(c1,1)","(c2,digoal)","(c3,\"2014-12-15 03:43:12.135139\")","(id,1)"}  
diff_new_rec | {"(c1,)","(c2,)","(c3,)","(id,)"}  
crt_time     | 2014-12-15 03:43:18.832179  
username     | postgres  
client_addr  |   
client_port  |   

diff的内容: 只有更新的字段被记录, 未更新的字段不会被记录

-[ RECORD 4 ]+-------------------------------------------------------------------------  
id           | 14  
relid        | 16593  
table_schema | public  
table_name   | test  
when_tg      | AFTER  
level        | ROW  
op           | UPDATE  
old_rec      | "c1"=>"2", "c2"=>"digoal", "c3"=>"2014-12-15 03:43:14.803063", "id"=>"2"  
new_rec      | "c1"=>"2", "c2"=>"digoal", "c3"=>"2014-12-15 03:43:35.977674", "id"=>"2"  
diff_old_rec | {"(c3,\"2014-12-15 03:43:14.803063\")"}  
diff_new_rec | {"(c3,\"2014-12-15 03:43:35.977674\")"}  
crt_time     | 2014-12-15 03:43:35.977674  
username     | postgres  
client_addr  |   
client_port  |   
-[ RECORD 5 ]+-------------------------------------------------------------------------  
id           | 15  
relid        | 16593  
table_schema | public  
table_name   | test  
when_tg      | AFTER  
level        | ROW  
op           | DELETE  
old_rec      | "c1"=>NULL, "c2"=>NULL, "c3"=>NULL, "id"=>NULL  
new_rec      |   
diff_old_rec |   
diff_new_rec |   
crt_time     | 2014-12-15 03:43:39.352938  
username     | postgres  
client_addr  |   
client_port  |   
-[ RECORD 6 ]+-------------------------------------------------------------------------  
id           | 16  
relid        | 16593  
table_schema | public  
table_name   | test  
when_tg      | AFTER  
level        | ROW  
op           | DELETE  
old_rec      | "c1"=>"2", "c2"=>"digoal", "c3"=>"2014-12-15 03:43:35.977674", "id"=>"2"  
new_rec      |   
diff_old_rec |   
diff_new_rec |   
crt_time     | 2014-12-15 03:43:39.352938  
username     | postgres  
client_addr  |   
client_port  |   

参考

1. http://blog.163.com/digoal@126/blog/static/163877040201252575529358/

2. http://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html

3. http://www.postgresql.org/docs/9.3/static/hstore.html

4. http://blog.163.com/digoal@126/blog/static/1638770402014721981288/

5. http://blog.163.com/digoal@126/blog/static/16387704020121118112533410/

6. http://www.postgresql.org/docs/9.2/static/sql-createaggregate.html

Flag Counter

digoal’s 大量PostgreSQL文章入口