· 9 years ago · Dec 13, 2016, 05:56 PM
1CREATE EXTENSION IF NOT EXISTS hstore;
2
3CREATE SCHEMA audit;
4REVOKE ALL ON SCHEMA audit FROM public;
5
6COMMENT ON SCHEMA audit IS 'Out-of-table audit/history logging tables and trigger functions';
7
8--
9-- Audited data. Lots of information is available, it's just a matter of how much
10-- you really want to record. See:
11--
12-- http://www.postgresql.org/docs/9.1/static/functions-info.html
13--
14-- Remember, every column you add takes up more audit table space and slows audit
15-- inserts.
16--
17-- Every index you add has a big impact too, so avoid adding indexes to the
18-- audit table unless you REALLY need them. The hstore GIST indexes are
19-- particularly expensive.
20--
21-- It is sometimes worth copying the audit table, or a coarse subset of it that
22-- you're interested in, into a temporary table where you CREATE any useful
23-- indexes and do your analysis.
24--
25CREATE TABLE audit.logged_actions (
26 event_id bigserial primary key,
27 schema_name text not null,
28 table_name text not null,
29 relid oid not null,
30 session_user_name text,
31 action_tstamp_tx TIMESTAMP WITH TIME ZONE NOT NULL,
32 action_tstamp_stm TIMESTAMP WITH TIME ZONE NOT NULL,
33 action_tstamp_clk TIMESTAMP WITH TIME ZONE NOT NULL,
34 transaction_id bigint,
35 application_name text,
36 client_addr inet,
37 client_port integer,
38 client_query text,
39 action TEXT NOT NULL CHECK (action IN ('I','D','U', 'T')),
40 row_data hstore,
41 changed_fields hstore,
42 statement_only boolean not null
43);
44
45REVOKE ALL ON audit.logged_actions FROM public;
46
47COMMENT ON TABLE audit.logged_actions IS 'History of auditable actions on audited tables, from audit.if_modified_func()';
48COMMENT ON COLUMN audit.logged_actions.event_id IS 'Unique identifier for each auditable event';
49COMMENT ON COLUMN audit.logged_actions.schema_name IS 'Database schema audited table for this event is in';
50COMMENT ON COLUMN audit.logged_actions.table_name IS 'Non-schema-qualified table name of table event occured in';
51COMMENT ON COLUMN audit.logged_actions.relid IS 'Table OID. Changes with drop/create. Get with ''tablename''::regclass';
52COMMENT ON COLUMN audit.logged_actions.session_user_name IS 'Login / session user whose statement caused the audited event';
53COMMENT ON COLUMN audit.logged_actions.action_tstamp_tx IS 'Transaction start timestamp for tx in which audited event occurred';
54COMMENT ON COLUMN audit.logged_actions.action_tstamp_stm IS 'Statement start timestamp for tx in which audited event occurred';
55COMMENT ON COLUMN audit.logged_actions.action_tstamp_clk IS 'Wall clock time at which audited event''s trigger call occurred';
56COMMENT ON COLUMN audit.logged_actions.transaction_id IS 'Identifier of transaction that made the change. May wrap, but unique paired with action_tstamp_tx.';
57COMMENT ON COLUMN audit.logged_actions.client_addr IS 'IP address of client that issued query. Null for unix domain socket.';
58COMMENT ON COLUMN audit.logged_actions.client_port IS 'Remote peer IP port address of client that issued query. Undefined for unix socket.';
59COMMENT ON COLUMN audit.logged_actions.client_query IS 'Top-level query that caused this auditable event. May be more than one statement.';
60COMMENT ON COLUMN audit.logged_actions.application_name IS 'Application name set when this audit event occurred. Can be changed in-session by client.';
61COMMENT ON COLUMN audit.logged_actions.action IS 'Action type; I = insert, D = delete, U = update, T = truncate';
62COMMENT 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.';
63COMMENT ON COLUMN audit.logged_actions.changed_fields IS 'New values of fields changed by UPDATE. Null except for row-level UPDATE events.';
64COMMENT ON COLUMN audit.logged_actions.statement_only IS '''t'' if audit event is from an FOR EACH STATEMENT trigger, ''f'' for FOR EACH ROW';
65
66CREATE INDEX logged_actions_relid_idx ON audit.logged_actions(relid);
67CREATE INDEX logged_actions_action_tstamp_tx_stm_idx ON audit.logged_actions(action_tstamp_stm);
68CREATE INDEX logged_actions_action_idx ON audit.logged_actions(action);
69
70CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS $body$
71DECLARE
72 audit_row audit.logged_actions;
73 include_values boolean;
74 log_diffs boolean;
75 h_old hstore;
76 h_new hstore;
77 excluded_cols text[] = ARRAY[]::text[];
78BEGIN
79 IF TG_WHEN <> 'AFTER' THEN
80 RAISE EXCEPTION 'audit.if_modified_func() may only run as an AFTER trigger';
81 END IF;
82
83 audit_row = ROW(
84 nextval('audit.logged_actions_event_id_seq'), -- event_id
85 TG_TABLE_SCHEMA::text, -- schema_name
86 TG_TABLE_NAME::text, -- table_name
87 TG_RELID, -- relation OID for much quicker searches
88 session_user::text, -- session_user_name
89 current_timestamp, -- action_tstamp_tx
90 statement_timestamp(), -- action_tstamp_stm
91 clock_timestamp(), -- action_tstamp_clk
92 txid_current(), -- transaction ID
93 current_setting('application_name'), -- client application
94 inet_client_addr(), -- client_addr
95 inet_client_port(), -- client_port
96 current_query(), -- top-level query or queries (if multistatement) from client
97 substring(TG_OP,1,1), -- action
98 NULL, NULL, -- row_data, changed_fields
99 'f' -- statement_only
100 );
101
102 IF NOT TG_ARGV[0]::boolean IS DISTINCT FROM 'f'::boolean THEN
103 audit_row.client_query = NULL;
104 END IF;
105
106 IF TG_ARGV[1] IS NOT NULL THEN
107 excluded_cols = TG_ARGV[1]::text[];
108 END IF;
109
110 IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
111 audit_row.row_data = hstore(OLD.*) - excluded_cols;
112 audit_row.changed_fields = (hstore(NEW.*) - audit_row.row_data) - excluded_cols;
113 IF audit_row.changed_fields = hstore('') THEN
114 -- All changed fields are ignored. Skip this update.
115 RETURN NULL;
116 END IF;
117 ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN
118 audit_row.row_data = hstore(OLD.*) - excluded_cols;
119 ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN
120 audit_row.row_data = hstore(NEW.*) - excluded_cols;
121 ELSIF (TG_LEVEL = 'STATEMENT' AND TG_OP IN ('INSERT','UPDATE','DELETE','TRUNCATE')) THEN
122 audit_row.statement_only = 't';
123 ELSE
124 RAISE EXCEPTION '[audit.if_modified_func] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL;
125 RETURN NULL;
126 END IF;
127 INSERT INTO audit.logged_actions VALUES (audit_row.*);
128 RETURN NULL;
129END;
130$body$
131LANGUAGE plpgsql
132SECURITY DEFINER
133SET search_path = pg_catalog, public;
134
135
136COMMENT ON FUNCTION audit.if_modified_func() IS $body$
137Track changes to a table at the statement and/or row level.
138
139Optional parameters to trigger in CREATE TRIGGER call:
140
141param 0: boolean, whether to log the query text. Default 't'.
142
143param 1: text[], columns to ignore in updates. Default [].
144
145 Updates to ignored cols are omitted from changed_fields.
146
147 Updates with only ignored cols changed are not inserted
148 into the audit log.
149
150 Almost all the processing work is still done for updates
151 that ignored. If you need to save the load, you need to use
152 WHEN clause on the trigger instead.
153
154 No warning or error is issued if ignored_cols contains columns
155 that do not exist in the target table. This lets you specify
156 a standard set of ignored columns.
157
158There is no parameter to disable logging of values. Add this trigger as
159a 'FOR EACH STATEMENT' rather than 'FOR EACH ROW' trigger if you do not
160want to log row values.
161
162Note that the user name logged is the login role for the session. The audit trigger
163cannot obtain the active role because it is reset by the SECURITY DEFINER invocation
164of the audit trigger its self.
165$body$;
166
167
168
169CREATE OR REPLACE FUNCTION audit.audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean, ignored_cols text[]) RETURNS void AS $body$
170DECLARE
171 stm_targets text = 'INSERT OR UPDATE OR DELETE OR TRUNCATE';
172 _q_txt text;
173 _ignored_cols_snip text = '';
174BEGIN
175 EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_row ON ' || quote_ident(target_table::TEXT);
176 EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_stm ON ' || quote_ident(target_table::TEXT);
177
178 IF audit_rows THEN
179 IF array_length(ignored_cols,1) > 0 THEN
180 _ignored_cols_snip = ', ' || quote_literal(ignored_cols);
181 END IF;
182 _q_txt = 'CREATE TRIGGER audit_trigger_row AFTER INSERT OR UPDATE OR DELETE ON ' ||
183 quote_ident(target_table::TEXT) ||
184 ' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(' ||
185 quote_literal(audit_query_text) || _ignored_cols_snip || ');';
186 RAISE NOTICE '%',_q_txt;
187 EXECUTE _q_txt;
188 stm_targets = 'TRUNCATE';
189 ELSE
190 END IF;
191
192 _q_txt = 'CREATE TRIGGER audit_trigger_stm AFTER ' || stm_targets || ' ON ' ||
193 target_table ||
194 ' FOR EACH STATEMENT EXECUTE PROCEDURE audit.if_modified_func('||
195 quote_literal(audit_query_text) || ');';
196 RAISE NOTICE '%',_q_txt;
197 EXECUTE _q_txt;
198
199END;
200$body$
201language 'plpgsql';
202
203COMMENT ON FUNCTION audit.audit_table(regclass, boolean, boolean, text[]) IS $body$
204Add auditing support to a table.
205
206Arguments:
207 target_table: Table name, schema qualified if not on search_path
208 audit_rows: Record each row change, or only audit at a statement level
209 audit_query_text: Record the text of the client query that triggered the audit event?
210 ignored_cols: Columns to exclude from update diffs, ignore updates that change only ignored cols.
211$body$;
212
213-- Pg doesn't allow variadic calls with 0 params, so provide a wrapper
214CREATE OR REPLACE FUNCTION audit.audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean) RETURNS void AS $body$
215SELECT audit.audit_table($1, $2, $3, ARRAY[]::text[]);
216$body$ LANGUAGE SQL;
217
218-- And provide a convenience call wrapper for the simplest case
219-- of row-level logging with no excluded cols and query logging enabled.
220--
221CREATE OR REPLACE FUNCTION audit.audit_table(target_table regclass) RETURNS void AS $body$
222SELECT audit.audit_table($1, BOOLEAN 't', BOOLEAN 't');
223$body$ LANGUAGE 'sql';
224
225COMMENT ON FUNCTION audit.audit_table(regclass) IS $body$
226Add auditing support to the given table. Row-level changes will be logged with full client query text. No cols are ignored.
227$body$;