Audit trigger 91plus
From PostgreSQL wiki
Here is an example of a generic trigger function used for recording changes to tables into an audit log table. It records quite a bit more detail than the older Audit trigger and does so in a more structured manner.
Row values are recorded as hstore fields rather than as flat text. This allows much more sophisticated querying against the audit history and allows the audit system to record only changed fields for updates.
Auditing can be done coarsely at a statement level or finely at a row level. Control is per-audited-table.
The information recorded is:
- Change type - Insert, Update, Delete or Ttruncate.
- client IP/port if not a UNIX socket
- Session user name ("real" user name, not "set role" name)
- transaction, statement, and wall clock timestamps
- Top-level statement that caused the change
- The row value before the change (or after in the case of INSERT)
- In the case of UPDATE, the new values of any changed columns. The new value can be reconstructed using row_value || changed_fields
- The transaction ID of the tx that made the change
- The application_name
- Target schema and table, by OID and name.
This trigger can not track:
- DDL like ALTER TABLE
- Changes to system catalogs
Changes by the table owner and superusers are tracked, but can be trivially tampered with.
If you want this audit log to be trustworthy, your app should run with a role that has at most USAGE to the audit schema and SELECT rights to audit.logged_actions. Most importantly, your app must not connect with a superuser role and must not own the tables it uses. Create your app's schema with a different user to the one your app runs as, and GRANT your app the minimum rights it needs.
You can obtain the latest version of the audit trigger from GitHub.
A copy - hopefully up to date - appears below.
-- This trigger was originally based on -- http://wiki.postgresql.org/wiki/Audit_trigger -- but has been completely rewritten. CREATE EXTENSION IF NOT EXISTS hstore; CREATE SCHEMA audit; REVOKE ALL ON SCHEMA audit FROM public; COMMENT ON SCHEMA audit IS 'Out-of-table audit/history logging tables and trigger functions'; -- -- Audited data. Lots of information is available, it's just a matter of how much -- you really want to record. See: -- -- http://www.postgresql.org/docs/9.1/static/functions-info.html -- -- Remember, every row you add takes up more audit table space and slows audit inserts. -- -- Every index you add has a big impact too, so avoid adding indexes to the audit table -- unless you REALLY need them. -- CREATE TABLE audit.logged_actions ( event_id bigserial PRIMARY KEY, schema_name text NOT NULL, table_name text NOT NULL, relid oid NOT NULL, session_user_name text, action_tstamp_tx TIMESTAMP WITH TIME ZONE NOT NULL, action_tstamp_stm TIMESTAMP WITH TIME ZONE NOT NULL, action_tstamp_clk TIMESTAMP WITH TIME ZONE NOT NULL, transaction_id bigint, application_name text, client_addr inet, client_port integer, client_query text NOT NULL, action TEXT NOT NULL CHECK (action IN ('I','D','U', 'T')), row_data hstore, changed_fields hstore, statement_only BOOLEAN NOT NULL ); REVOKE ALL ON audit.logged_actions FROM public; COMMENT ON TABLE audit.logged_actions IS 'History of auditable actions on audited tables, from audit.if_modified_func()'; COMMENT ON COLUMN audit.logged_actions.event_id IS 'Unique identifier for each auditable event'; COMMENT ON COLUMN audit.logged_actions.schema_name IS 'Database schema audited table for this event is in'; COMMENT ON COLUMN audit.logged_actions.table_name IS 'Non-schema-qualified table name of table event occured in'; COMMENT ON COLUMN audit.logged_actions.relid IS 'Table OID. Changes with drop/create. Get with ''tablename''::regclass'; COMMENT ON COLUMN audit.logged_actions.session_user_name IS 'Login / session user whose statement caused the audited event'; COMMENT ON COLUMN audit.logged_actions.action_tstamp_tx IS 'Transaction start timestamp for tx in which audited event occurred'; COMMENT ON COLUMN audit.logged_actions.action_tstamp_stm IS 'Statement start timestamp for tx in which audited event occurred'; COMMENT ON COLUMN audit.logged_actions.action_tstamp_clk IS 'Wall clock time at which audited event''s trigger call occurred'; COMMENT ON COLUMN audit.logged_actions.transaction_id IS 'Identifier of transaction that made the change. May wrap, but unique paired with action_tstamp_tx.'; COMMENT ON COLUMN audit.logged_actions.client_addr IS 'IP address of client that issued query. Null for unix domain socket.'; COMMENT ON COLUMN audit.logged_actions.client_port IS 'Remote peer IP port address of client that issued query. Undefined for unix socket.'; COMMENT ON COLUMN audit.logged_actions.client_query IS 'Top-level query that caused this auditable event. May be more than one statement.'; COMMENT ON COLUMN audit.logged_actions.application_name IS 'Application name set when this audit event occurred. Can be changed in-session by client.'; COMMENT ON COLUMN audit.logged_actions.action IS 'Action type; I = insert, D = delete, U = update, T = truncate'; COMMENT ON COLUMN audit.logged_actions.row_data IS 'Record value. Null for statement-level trigger. For INSERT this is the new tuple. For DELETE and UPDATE it is the old tuple.'; COMMENT ON COLUMN audit.logged_actions.changed_fields IS 'New values of fields changed by UPDATE. Null except for row-level UPDATE events.'; COMMENT ON COLUMN audit.logged_actions.statement_only IS '''t'' if audit event is from an FOR EACH STATEMENT trigger, ''f'' for FOR EACH ROW'; CREATE INDEX logged_actions_relid_idx ON audit.logged_actions(relid); CREATE INDEX logged_actions_action_tstamp_tx_stm_idx ON audit.logged_actions(action_tstamp_stm); CREATE INDEX logged_actions_action_idx ON audit.logged_actions(action); CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS $body$ DECLARE audit_row audit.logged_actions; include_values BOOLEAN; log_diffs BOOLEAN; h_old hstore; h_new hstore; BEGIN IF TG_WHEN <> 'AFTER' THEN RAISE EXCEPTION 'audit.if_modified_func() may only run as an AFTER trigger'; END IF; audit_row = ROW( NEXTVAL('audit.logged_actions_event_id_seq'), -- event_id TG_TABLE_SCHEMA::text, -- schema_name TG_TABLE_NAME::text, -- table_name TG_RELID, -- relation OID for much quicker searches session_user::text, -- session_user_name current_timestamp, -- action_tstamp_tx statement_timestamp(), -- action_tstamp_stm clock_timestamp(), -- action_tstamp_clk txid_current(), -- transaction ID (SELECT setting FROM pg_settings WHERE name = 'application_name'), inet_client_addr(), -- client_addr inet_client_port(), -- client_port current_query(), -- top-level query or queries (if multistatement) from client substring(TG_OP,1,1), -- action NULL, NULL, -- row_data, changed_fields 'f' -- statement_only ); IF NOT TG_ARGV::BOOLEAN IS DISTINCT FROM 'f'::BOOLEAN THEN audit_row.client_query = NULL; END IF; IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN audit_row.row_data = hstore(OLD.*); audit_row.changed_fields = hstore(NEW.*) - audit_row.row_data; ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN audit_row.row_data = hstore(OLD.*); ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN audit_row.row_data = hstore(NEW.*); ELSIF (TG_LEVEL = 'STATEMENT' AND TG_OP IN ('INSERT','UPDATE','DELETE','TRUNCATE')) THEN audit_row.statement_only = 't'; ELSE RAISE WARNING '[audit_if_modified_func] - Other action occurred: %, at %',TG_OP,clock_timestamp(); RETURN NULL; END IF; INSERT INTO audit.logged_actions VALUES (audit_row.*); RETURN NULL; END; $body$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public; COMMENT ON FUNCTION audit.if_modified_func() IS $body$ Track changes TO a TABLE at the statement AND/OR row level. Optional parameters TO TRIGGER IN CREATE TRIGGER call: param 0: BOOLEAN, whether TO log the query text. DEFAULT 't'. There IS no parameter TO disable logging of VALUES. Instead ADD this TRIGGER AS a 'FOR EACH STATEMENT' rather than 'FOR EACH ROW' TRIGGER. Note that the user name logged IS the login role FOR the session. The audit TRIGGER cannot obtain the active role because it IS reset BY the SECURITY DEFINER invocation of the audit TRIGGER its self. $body$; CREATE OR REPLACE FUNCTION audit.audit_table(target_table regclass, audit_rows BOOLEAN, audit_query_text BOOLEAN) RETURNS void AS $body$ DECLARE stm_targets text = 'INSERT OR UPDATE OR DELETE OR TRUNCATE'; _q_txt text; BEGIN EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_row ON ' || quote_ident(target_table::text); EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_stm ON ' || quote_ident(target_table::text); IF audit_rows THEN _q_txt = 'CREATE TRIGGER audit_trigger_row AFTER INSERT OR UPDATE OR DELETE ON ' || quote_ident(target_table::text) || ' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(' || quote_literal(audit_query_text) || ');'; RAISE NOTICE '%',_q_txt; EXECUTE _q_txt; stm_targets = 'TRUNCATE'; ELSE END IF; _q_txt = 'CREATE TRIGGER audit_trigger_stm AFTER ' || stm_targets || ' ON ' || quote_ident(target_table::text) || ' FOR EACH STATEMENT EXECUTE PROCEDURE audit.if_modified_func('|| quote_literal(audit_query_text) || ');'; RAISE NOTICE '%',_q_txt; EXECUTE _q_txt; END; $body$ LANGUAGE 'plpgsql'; COMMENT ON FUNCTION audit.audit_table(regclass, BOOLEAN, BOOLEAN) IS $body$ ADD auditing support TO a TABLE. Arguments: target_table: TABLE name, schema qualified IF NOT ON search_path audit_rows: Record each row CHANGE, OR only audit at a statement level audit_query_text: Record the text of the client query that triggered the audit event? $body$; CREATE OR REPLACE FUNCTION audit.audit_table(target_table regclass) RETURNS void AS $$ SELECT audit.audit_table($1, BOOLEAN 't', BOOLEAN 't'); $$ LANGUAGE 'sql'; COMMENT ON FUNCTION audit.audit_table(regclass) IS $body$ ADD auditing support TO the given TABLE. Row-level changes will be logged WITH diffs BETWEEN updates AND FULL client query text $body$;
The table will now have audit events recorded at a row level for every insert/update/delete, and at a statement level for truncate. Query text will always be logged.
If you want finer control use audit.audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean) or CREATE TRIGGER the audit trigger yourself.
Ignoring some changes
In PostgreSQL 9.0 and above, you can add an optional WHEN clause to invocations of the trigger. This is great for auditing, as it lets you exclude some changes without even paying the cost of invoking the trigger.
A common case is to ignore a frequently updated field that isn't worth auditing. Unfortunately, you have to test for a change in all the fields you ARE interested in, there isn't an easy way to say "if anything except fields <x> and <y> have changed between OLD and NEW":
CREATE TRIGGER tablename_audit_insert_delete AFTER INSERT OR DELETE ON sometable FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); CREATE TRIGGER tablename_audit_update_selective AFTER UPDATE ON sometable FOR EACH ROW WHEN ( OLD.non_ignored IS DISTINCT FROM NEW.non_ignored ) EXECUTE PROCEDURE audit.if_modified_func();
... but of course, you can do a lot more. You'll often need to separate your INSERT, DELETE and UPDATE trigger definitions rather than doing them all in one trigger definition, though.
Note the use of IS DISTINCT FROM rather than =. Think about the effect of NULL.
Time zone concerns
TIMESTAMP WITH TIME ZONE fields are always stored in UTC and are displayed in local time by default. You can control display using AT TIME ZONE in queries, or with SET timezone = 'UTC' as a per-session GUC. See the Pg docs.
Loosely based on Audit trigger by bricklen, completely rewritten by ringerc for more audit detail, hstore logging, and more.