-- An audit history is important on most tables. Provide an audit trigger that logs to -- a dedicated audit table for the major relations. -- -- This file should be generic and not depend on application roles or structures, -- as it's being listed here: -- -- https://wiki.postgresql.org/wiki/Audit_trigger_91plus -- -- This trigger was originally based on -- http://wiki.postgresql.org/wiki/Audit_trigger -- but has been completely rewritten. -- -- Should really be converted into a relocatable EXTENSION, with control and upgrade files. CREATE OR REPLACE FUNCTION SELECTED_SCHEMA_NAME.if_modified_func() RETURNS TRIGGER AS $$ DECLARE audit_row SELECTED_SCHEMA_NAME.logged_actions; include_values boolean; log_diffs boolean; h_old hstore; h_new hstore; user_row record; excluded_cols text[] = ARRAY[]::text[]; pk_val_query text; BEGIN IF TG_WHEN <> 'AFTER' THEN RAISE EXCEPTION 'SELECTED_SCHEMA_NAME.if_modified_func() may only run as an AFTER trigger'; END IF; audit_row = ROW( nextval('SELECTED_SCHEMA_NAME.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 NULL, NULL, NULL, -- app_user_id, app_user_type, app_ip_address current_timestamp, -- action_tstamp_tx statement_timestamp(), -- action_tstamp_stm clock_timestamp(), -- action_tstamp_clk txid_current(), -- transaction ID current_setting('application_name'), -- client application 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, NULL, -- row_id, row_data, changed_fields 'f' -- statement_only ); IF NOT TG_ARGV[0]::boolean IS DISTINCT FROM 'f'::boolean THEN audit_row.client_query = NULL; END IF; IF ((TG_ARGV[1] IS NOT NULL) AND (TG_LEVEL = 'ROW')) THEN pk_val_query = 'SELECT $1.' || quote_ident(TG_ARGV[1]::text); IF (TG_OP IS DISTINCT FROM 'DELETE') THEN EXECUTE pk_val_query INTO audit_row.row_id USING NEW; END IF; IF audit_row.row_id IS NULL THEN EXECUTE pk_val_query INTO audit_row.row_id USING OLD; END IF; END IF; IF TG_ARGV[2] IS NOT NULL THEN excluded_cols = TG_ARGV[2]::text[]; END IF; CREATE TEMP TABLE IF NOT EXISTS "_app_user" (user_id integer, user_type text, ip_address inet); IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN audit_row.row_data = hstore(OLD.*) - excluded_cols; audit_row.changed_fields = (hstore(NEW.*) - audit_row.row_data) - excluded_cols; IF audit_row.changed_fields = hstore('') THEN -- All changed fields are ignored. Skip this update. RETURN NULL; END IF; ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN audit_row.row_data = hstore(OLD.*) - excluded_cols; ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN audit_row.row_data = hstore(NEW.*) - excluded_cols; ELSIF (TG_LEVEL = 'STATEMENT' AND TG_OP IN ('INSERT','UPDATE','DELETE','TRUNCATE')) THEN audit_row.statement_only = 't'; ELSE RAISE EXCEPTION '[SELECTED_SCHEMA_NAME.if_modified_func] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL; RETURN NULL; END IF; -- inject app_user data into audit BEGIN PERFORM n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname like 'pg_temp_%' AND c.relname = '_app_user'; IF FOUND THEN FOR user_row IN SELECT * FROM _app_user LIMIT 1 LOOP audit_row.app_user_id = user_row.user_id; audit_row.app_user_type = user_row.user_type; audit_row.app_ip_address = user_row.ip_address; END LOOP; END IF; END; -- end app_user data EXECUTE 'CREATE TABLE IF NOT EXISTS SELECTED_SCHEMA_NAME.logged_actions_' || quote_ident(TG_TABLE_NAME::TEXT) || '( CHECK (table_name = ' || quote_literal(TG_TABLE_NAME::TEXT) || ') ) INHERITS (SELECTED_SCHEMA_NAME.logged_actions)'; EXECUTE 'INSERT INTO SELECTED_SCHEMA_NAME.logged_actions_' || quote_ident(TG_TABLE_NAME::TEXT) || ' VALUES ($1.*)' USING audit_row; RETURN NULL; END; $$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public; COMMENT ON FUNCTION SELECTED_SCHEMA_NAME.if_modified_func() IS $$ 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'. param 1: text, primary_key_column of audited table if bigint. param 2: text[], columns to ignore in updates. Default []. Updates to ignored cols are omitted from changed_fields. Updates with only ignored cols changed are not inserted into the audit log. Almost all the processing work is still done for updates that ignored. If you need to save the load, you need to use WHEN clause on the trigger instead. No warning or error is issued if ignored_cols contains columns that do not exist in the target table. This lets you specify a standard set of ignored columns. There is no parameter to disable logging of values. Add this trigger as a 'FOR EACH STATEMENT' rather than 'FOR EACH ROW' trigger if you do not want to log row values. 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. $$; CREATE OR REPLACE FUNCTION SELECTED_SCHEMA_NAME.get_primary_key_column(target_table text) RETURNS text AS $$ DECLARE _pk_query_text text; _pk_column_name text; BEGIN _pk_query_text = 'SELECT a.attname ' || 'FROM pg_index i ' || 'JOIN pg_attribute a ON a.attrelid = i.indrelid ' || ' AND a.attnum = ANY(i.indkey) ' || 'WHERE i.indrelid = ' || quote_literal(target_table::TEXT) || '::regclass ' || 'AND i.indisprimary ' || 'AND format_type(a.atttypid, a.atttypmod) = ' || quote_literal('bigint'::TEXT) || 'LIMIT 1'; EXECUTE _pk_query_text INTO _pk_column_name; raise notice 'Value %', _pk_column_name; return _pk_column_name; END; $$ LANGUAGE plpgsql; COMMENT ON FUNCTION SELECTED_SCHEMA_NAME.get_primary_key_column(text) IS $$ Get primary key column name if single PK and type bigint. Arguments: target_table: Table name, schema qualified if not on search_path $$; CREATE OR REPLACE FUNCTION SELECTED_SCHEMA_NAME.audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean, ignored_cols text[]) RETURNS void AS $$ DECLARE stm_targets text = 'INSERT OR UPDATE OR DELETE OR TRUNCATE'; _q_txt text; _pk_column_name text; _pk_column_snip text; _ignored_cols_snip 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); EXECUTE 'CREATE TABLE IF NOT EXISTS SELECTED_SCHEMA_NAME.logged_actions_' || quote_ident(target_table::TEXT) || '( CHECK (table_name = ' || quote_literal(target_table::TEXT) || ') ) INHERITS (SELECTED_SCHEMA_NAME.logged_actions)'; IF audit_rows THEN _pk_column_name = SELECTED_SCHEMA_NAME.get_primary_key_column(target_table::TEXT); IF _pk_column_name IS NOT NULL THEN _pk_column_snip = ', ' || quote_literal(_pk_column_name); ELSE _pk_column_snip = ', NULL'; END IF; IF array_length(ignored_cols,1) > 0 THEN _ignored_cols_snip = ', ' || quote_literal(ignored_cols); END IF; _q_txt = 'CREATE TRIGGER audit_trigger_row AFTER INSERT OR UPDATE OR DELETE ON ' || quote_ident(target_table::TEXT) || ' FOR EACH ROW EXECUTE PROCEDURE SELECTED_SCHEMA_NAME.if_modified_func(' || quote_literal(audit_query_text) || _pk_column_snip || _ignored_cols_snip || ');'; RAISE NOTICE '%',_q_txt; EXECUTE _q_txt; stm_targets = 'TRUNCATE'; ELSE END IF; _q_txt = 'CREATE TRIGGER audit_trigger_stm AFTER ' || stm_targets || ' ON ' || target_table || ' FOR EACH STATEMENT EXECUTE PROCEDURE SELECTED_SCHEMA_NAME.if_modified_func('|| quote_literal(audit_query_text) || ');'; RAISE NOTICE '%',_q_txt; EXECUTE _q_txt; END; $$ LANGUAGE plpgsql; COMMENT ON FUNCTION SELECTED_SCHEMA_NAME.audit_table(regclass, boolean, boolean, text[]) IS $$ 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? ignored_cols: Columns to exclude from update diffs, ignore updates that change only ignored cols. $$; -- Pg doesn't allow variadic calls with 0 params, so provide a wrapper CREATE OR REPLACE FUNCTION SELECTED_SCHEMA_NAME.audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean) RETURNS void AS $$ SELECT SELECTED_SCHEMA_NAME.audit_table($1, $2, $3, ARRAY[]::text[]); $$ LANGUAGE SQL; -- And provide a convenience call wrapper for the simplest case -- of row-level logging with no excluded cols and query logging enabled. -- CREATE OR REPLACE FUNCTION SELECTED_SCHEMA_NAME.audit_table(target_table regclass) RETURNS void AS $$ SELECT SELECTED_SCHEMA_NAME.audit_table($1, BOOLEAN 't', BOOLEAN 't'); $$ LANGUAGE SQL; COMMENT ON FUNCTION SELECTED_SCHEMA_NAME.audit_table(regclass) IS $$ Add auditing support to the given table. Row-level changes will be logged with full client query text. No cols are ignored. $$;