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