How would you track the history of all CRUD operations without re-architecting?

Question

I would like to capture modifications to data values over time in certain tables for auditing purposes. There are many clients that can make queries to the database, so putting a layer in front of SQL would not be feasible.

Answer 1

create triggers or rules on update delete and insert that write details of the operation to the postgresql log file and/or to a logging table.

here's one I prepared earlier this example logs to both a table and to the log

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();

I find that adding SQL comments to the DML that acts on the table helps me locate problematic code.