Mercurial > lada > lada-server

comparison db_schema/audit.sql @ 1298:ba47994c1665

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add audit-trail table/trigger/views to database.
author Raimund Renkert <raimund.renkert@intevation.de>
date Fri, 24 Feb 2017 14:27:10 +0100
parents
children c2df9c848e9d
comparison
equal deleted inserted replaced
1297:65166d1158fe 1298:ba47994c1665
1 -- Based on https://github.com/xdimedrolx/audit-trigger/
2 --
3 -- which is licensed by "The PostgreSQL License", effectively equivalent to the BSD
4 -- license.
5
6 SET search_path TO land;
7 CREATE TABLE audit_trail(
8 id bigserial primary key,
9 table_name varchar(50) not null,
10 tstamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
11 action varchar(1) NOT NULL CHECK (action IN ('I','D','U', 'T')),
12 object_id integer not null,
13 row_data JSONB,
14 changed_fields JSONB
15 );
16
17 CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b jsonb)
18 RETURNS jsonb AS
19 $BODY$
20 SELECT COALESCE(
21 (
22 SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
23 FROM jsonb_each(a)
24 WHERE NOT ('{' || to_json(key) || ':' || value || '}')::jsonb <@ b
25 )
26 , '{}')::jsonb;
27 $BODY$
28 LANGUAGE sql IMMUTABLE STRICT;
29 COMMENT ON FUNCTION jsonb_delete_left(jsonb, jsonb) IS 'delete matching pairs in second argument from first argument';
30 DROP OPERATOR IF EXISTS - (jsonb, jsonb);
31 CREATE OPERATOR - ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = jsonb);
32 COMMENT ON OPERATOR - (jsonb, jsonb) IS 'delete matching pairs from left operand';
33
34
35 CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b text[])
36 RETURNS jsonb AS
37 $BODY$
38 SELECT COALESCE(
39 (
40 SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
41 FROM jsonb_each(a)
42 WHERE key <> ALL(b)
43 )
44 , '{}')::jsonb;
45 $BODY$
46 LANGUAGE sql IMMUTABLE STRICT;
47 COMMENT ON FUNCTION jsonb_delete_left(jsonb, text[]) IS 'delete keys in second argument from first argument';
48 DROP OPERATOR IF EXISTS - (jsonb, text[]);
49 CREATE OPERATOR - ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = text[]);
50 COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand';
51
52
53 CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b text)
54 RETURNS jsonb AS
55 $BODY$
56 SELECT COALESCE(
57 (
58 SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
59 FROM jsonb_each(a)
60 WHERE key <> b
61 )
62 , '{}')::jsonb;
63 $BODY$
64 LANGUAGE sql IMMUTABLE STRICT;
65 COMMENT ON FUNCTION jsonb_delete_left(jsonb, text) IS 'delete key in second argument from first argument';
66 DROP OPERATOR IF EXISTS - (jsonb, text);
67 CREATE OPERATOR - ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = text);
68 COMMENT ON OPERATOR - (jsonb, text) IS 'delete key from left operand';
69
70
71 CREATE OR REPLACE FUNCTION if_modified_func() RETURNS TRIGGER AS $body$
72 DECLARE
73 audit_row land.audit_trail;
74 include_values boolean;
75 log_diffs boolean;
76 h_old jsonb;
77 h_new jsonb;
78 excluded_cols text[] = ARRAY[]::text[];
79 BEGIN
80 IF TG_WHEN <> 'AFTER' THEN
81 RAISE EXCEPTION 'land.if_modified_func() may only run as an AFTER trigger';
82 END IF;
83
84 -- Do nothing on insert and delete.
85 IF (TG_OP = 'INSERT' OR TG_OP = 'DELETE') THEN
86 RETURN NULL;
87 END IF;
88
89 audit_row = ROW(
90 nextval('land.audit_trail_id_seq'), -- id
91 TG_TABLE_NAME::varchar, -- table_name
92 current_timestamp, -- tstamp
93 substring(TG_OP,1,1), -- action
94 OLD.id, -- object_id
95 NULL, NULL -- row_data, changed_fields
96 );
97
98 IF TG_ARGV[1] IS NOT NULL THEN
99 excluded_cols = TG_ARGV[1]::text[];
100 END IF;
101
102 IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
103 audit_row.row_data = row_to_json(OLD)::JSONB;
104 audit_row.changed_fields = row_to_json(NEW)::JSONB - audit_row.row_data - excluded_cols;
105 IF audit_row.changed_fields = '{}'::jsonb THEN
106 -- All changed fields are ignored. Skip this update.
107 RETURN NULL;
108 END IF;
109 ELSE
110 RAISE EXCEPTION '[land.if_modified_func] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL;
111 RETURN NULL;
112 END IF;
113 INSERT INTO land.audit_trail VALUES (audit_row.*);
114 RETURN NULL;
115 END;
116 $body$
117 LANGUAGE plpgsql
118 SECURITY DEFINER
119 SET search_path = land, public;
120
121 CREATE OR REPLACE FUNCTION audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean, ignored_cols text[]) RETURNS void AS $body$
122 DECLARE
123 stm_targets text = 'INSERT OR UPDATE OR DELETE OR TRUNCATE';
124 _q_txt text;
125 _ignored_cols_snip text = '';
126 BEGIN
127 EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_row ON ' || quote_ident(target_table::TEXT);
128 EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_stm ON ' || quote_ident(target_table::TEXT);
129
130 IF audit_rows THEN
131 IF array_length(ignored_cols,1) > 0 THEN
132 _ignored_cols_snip = ', ' || quote_literal(ignored_cols);
133 END IF;
134 _q_txt = 'CREATE TRIGGER audit_trigger_row AFTER INSERT OR UPDATE OR DELETE ON ' ||
135 quote_ident(target_table::TEXT) ||
136 ' FOR EACH ROW EXECUTE PROCEDURE land.if_modified_func(' ||
137 quote_literal(audit_query_text) || _ignored_cols_snip || ');';
138 RAISE NOTICE '%',_q_txt;
139 EXECUTE _q_txt;
140 stm_targets = 'TRUNCATE';
141 ELSE
142 END IF;
143
144 _q_txt = 'CREATE TRIGGER audit_trigger_stm AFTER ' || stm_targets || ' ON ' ||
145 target_table ||
146 ' FOR EACH STATEMENT EXECUTE PROCEDURE land.if_modified_func('||
147 quote_literal(audit_query_text) || ');';
148 RAISE NOTICE '%',_q_txt;
149 EXECUTE _q_txt;
150
151 END;
152 $body$
153 language 'plpgsql';
154
155 COMMENT ON FUNCTION audit_table(regclass, boolean, boolean, text[]) IS $body$
156 Add auditing support to a table.
157
158 Arguments:
159 target_table: Table name, schema qualified if not on search_path
160 audit_rows: Record each row change, or only audit at a statement level
161 audit_query_text: Record the text of the client query that triggered the audit event?
162 ignored_cols: Columns to exclude from update diffs, ignore updates that change only ignored cols.
163 $body$;
164
165 -- Pg doesn't allow variadic calls with 0 params, so provide a wrapper
166 CREATE OR REPLACE FUNCTION audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean) RETURNS void AS $body$
167 SELECT audit_table($1, $2, $3, ARRAY[]::text[]);
168 $body$ LANGUAGE SQL;
169
170 -- And provide a convenience call wrapper for the simplest case
171 -- of row-level logging with no excluded cols and query logging enabled.
172 --
173 CREATE OR REPLACE FUNCTION audit_table(target_table regclass) RETURNS void AS $body$
174 SELECT audit_table($1, BOOLEAN 't', BOOLEAN 't');
175 $body$ LANGUAGE 'sql';
176
177 COMMENT ON FUNCTION audit_table(regclass) IS $body$
178 Add auditing support to the given table. Row-level changes will be logged with full client query text. No cols are ignored.
179 $body$;
180
181 CREATE INDEX probe_id_ndx ON audit_trail(cast("row_data"->>'probe_id' AS int));
182 CREATE INDEX messung_id_ndx ON audit_trail(cast("row_data"->>'messung_id' AS int));
183
184 -- View for probe audit trail
185 CREATE OR REPLACE VIEW audit_trail_probe AS
186 SELECT
187 id,
188 table_name,
189 object_id,
190 tstamp,
191 cast(row_data ->> 'messungs_id' AS integer) AS messungs_id,
192 coalesce(cast(row_data ->> 'probe_id' AS integer),
193 (SELECT probe_id FROM messung WHERE id = cast(
194 row_data ->> 'messungs_id' AS integer))) AS probe_id,
195 row_data,
196 changed_fields
197 FROM audit_trail;
198
199
200 -- View for messung audit trail
201 CREATE OR REPLACE VIEW audit_trail_messung AS
202 SELECT audit_trail.id,
203 audit_trail.table_name,
204 audit_trail.tstamp,
205 audit_trail.action,
206 audit_trail.object_id,
207 audit_trail.row_data,
208 audit_trail.changed_fields,
209 cast(row_data ->> 'messungs_id' AS int) AS messungs_id
210 FROM audit_trail;
211
212
213 SELECT audit_table('probe', true, false, '{tree_modified, letzte_aenderung}'::text[]);
214 SELECT audit_table('messung', true, false, '{tree_modified, letzte_aenderung, status}'::text[]);
215 SELECT audit_table('messwert', true, false, '{tree_modified, letzte_aenderung}'::text[]);
216 SELECT audit_table('kommentar_p', true, false, '{tree_modified, letzte_aenderung}'::text[]);
217 SELECT audit_table('kommentar_m', true, false, '{tree_modified, letzte_aenderung}'::text[]);
218 SELECT audit_table('zusatz_wert', true, false, '{tree_modified, letzte_aenderung}'::text[]);
219 SELECT audit_table('ortszuordnung', true, false, '{tree_modified, letzte_aenderung}'::text[]);
220
221 SET search_path TO public;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)