sql - 在不重新架构的情况下，您将如何跟踪所有 CRUD 操作的历史记录？

Question

出于审计目的，我想捕获某些表中数据值随时间的修改。有很多客户端可以对数据库进行查询，所以在 SQL 前面加一层是不可行的。

Answer 1

在更新删除和插入时创建触发器或规则，将操作的详细信息写入 postgresql 日志文件和/或日志表。

这是我之前准备的此示例同时记录到表和日志

CREATE TABLE debuglog(sql text, ts timestamptz,tbl name,usr text);

CREATE or REPLACE function log_changes() RETURNS trigger as $TLC$
declare
    sql text := current_query();
begin

    --( not shown: code here that blanks out password fileds in sql )

    if tg_op= 'INSERT'  then 
        insert into debuglog values (sql || e'\n -- NEW=' || NEW::text,now(),TG_RELNAME,current_user || coalesce( ' '||inet_client_addr(),'local'));
        raise log 'insert to % by % NEW=%',TG_RELNAME, current_user || coalesce( ' '||inet_client_addr(),'local'),NEW::text;
    elsif tg_op = 'DELETE'  then 
        insert into debuglog values (sql || e'\n -- OLD=' || OLD::text,now(),TG_RELNAME,current_user || coalesce( ' '||inet_client_addr(),'local'));
        raise log 'delete from % by % OLD=%',TG_RELNAME, current_user || coalesce( ' '||inet_client_addr(),'local'),OLD::text;
        return old;
    else
        raise log 'update to % by % OLD=% NEW=%',TG_RELNAME, current_user || coalesce( ' '||inet_client_addr(),'local'),OLD::text,NEW::text;
        insert into debuglog values ( sql || e'\n -- OLD=' || OLD::text || e'\n -- NEW=' || NEW::text,now(),TG_RELNAME,current_user || coalesce( ' '||inet_client_addr(),'local'));
    end if;
    return new;
end $TLC$
language plpgsql;

create trigger after update or insert or delete on logged_table for each row do also log_changes();

我发现向作用于表的 DML 添加 SQL 注释有助于我找到有问题的代码。