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
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 

(3 rows)

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

(6 rows)

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

(6 rows)