Postgresql 之 基于表的dml審計

應用場景中有些業務表比較關鍵，需要對關鍵業務表的變更進行記錄，以下通過在表上建立觸發器調用函數的方法對關鍵業務表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

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						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"
18-04-23 10:37:51.626954
3						DELETE	"id"=>"1", "info"=>"HighgoDB", "c
rt_time"=>"2018-04-23 10:37:42"
18-04-23 10:38:00.382681

(3 rows)

postgres=# select id,(each(old_rec)).* from table_change_rec;

id	key	value
	info	highgo
	crt_time	2018-04-23 10:37:42
		HighgoDB

(6 rows)

postgres=select id,(each(new_rec)).* from table_change_rec;