In many of my earlier applications/frameworks av always implemented table audit facility via java/python code...
granted this worked ok... unless the (malicious or otherwise) user accessed the apps DB via other means eg terminal, ide, et al...
Recently av been doing this via DB triggers (...silence please.. i know this is not DB agnostic...) and had quite a challenge creating a 'universal' trigger function that will be used by all auditable tables...
The following is the solution, of course assembled from several blogs and sites and some fiddling on SQLFiddle......
Prerequisites (for those who wish to use this function as it is):
- Audited table must capture a login id(bigint)
- Must have an AFTER trigger calling this fxn
- Table p_audit must be created in advance
CREATE OR REPLACE FUNCTION if_modified_func() returns TRIGGER AS $$
DECLARE
pk bigint;
pk_name varchar(100);
pk_datatype varchar(50);
BEGIN
IF TG_WHEN <> 'AFTER' THEN
RAISE EXCEPTION 'Audit Function may only run as an AFTER trigger';
END IF;
--first we identify the pk field of the db
EXECUTE
'(SELECT
pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
FROM pg_index, pg_class, pg_attribute
WHERE pg_class.oid = ''' || TG_TABLE_NAME || '''::regclass
AND indrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = any(pg_index.indkey)
AND indisprimary)'
INTO pk_name, pk_datatype;
--TO GET record_id start here => http://www.sqlfiddle.com/#!15/64e41/2/0
if TG_OP = 'UPDATE' then
EXECUTE 'SELECT $1.'||pk_name INTO pk USING NEW;
INSERT INTO p_audit (transaction_id, p_login_id, table_name, action, client_addr, client_port, client_query,
record_id, db_user, action_timestamp, data_before, data_after, updated_cols, narrative)
VALUES(txid_current(), NEW.login_id, TG_TABLE_NAME, 'U', inet_client_addr(), inet_client_port(), current_query(),
pk, CURRENT_USER, CURRENT_TIMESTAMP, hstore(OLD.*), hstore(NEW.*), akeys(hstore(NEW.*) - hstore(OLD.*)), NULL);
return NEW;
elsif TG_OP = 'DELETE' then
EXECUTE 'SELECT $1.'||pk_name INTO pk USING OLD;
INSERT INTO p_audit (transaction_id, p_login_id, table_name, action, client_addr, client_port, client_query,
record_id, db_user, action_timestamp, data_before)
VALUES(txid_current(), OLD.login_id, TG_TABLE_NAME, 'D', inet_client_addr(), inet_client_port(), current_query(),
pk, CURRENT_USER, CURRENT_TIMESTAMP, hstore(OLD.*));
return OLD;
elsif TG_OP = 'INSERT' then
EXECUTE 'SELECT $1.'||pk_name INTO pk USING NEW;
INSERT INTO p_audit (transaction_id, p_login_id, table_name, action, client_addr, client_port, client_query,
record_id, db_user, action_timestamp, data_before, data_after, updated_cols, narrative)
VALUES(txid_current(), NEW.login_id, TG_TABLE_NAME, 'I', inet_client_addr(), inet_client_port(), current_query(),
pk, CURRENT_USER, CURRENT_TIMESTAMP, NULL, hstore(NEW.*), NULL, NULL);
return NEW;
end if;
END;
$$ language plpgsql;
Finaly...
if you think this is coool....... just say it
=====
Graphically u may present it like this
