1 -- These three functions are for capturing, getting, and clearing user and workstation information
3 -- Set the User AND workstation in one call. Tis faster. And less calls.
4 -- First argument is user, second is workstation
5 CREATE OR REPLACE FUNCTION auditor.set_audit_info(INT, INT) RETURNS VOID AS $$
6 $_SHARED{"eg_audit_user"} = $_[0];
7 $_SHARED{"eg_audit_ws"} = $_[1];
10 -- Get the User AND workstation in one call. Less calls, useful for joins ;)
11 CREATE OR REPLACE FUNCTION auditor.get_audit_info() RETURNS TABLE (eg_user INT, eg_ws INT) AS $$
12 return [{eg_user => $_SHARED{"eg_audit_user"}, eg_ws => $_SHARED{"eg_audit_ws"}}];
15 -- Clear the audit info, for whatever reason
16 CREATE OR REPLACE FUNCTION auditor.clear_audit_info() RETURNS VOID AS $$
17 delete($_SHARED{"eg_audit_user"});
18 delete($_SHARED{"eg_audit_ws"});
21 CREATE FUNCTION auditor.create_auditor_history ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
24 CREATE TABLE auditor.$$ || sch || $$_$$ || tbl || $$_history (
25 audit_id BIGINT PRIMARY KEY,
26 audit_time TIMESTAMP WITH TIME ZONE NOT NULL,
27 audit_action TEXT NOT NULL,
30 LIKE $$ || sch || $$.$$ || tbl || $$
35 $creator$ LANGUAGE 'plpgsql';
37 CREATE OR REPLACE FUNCTION auditor.create_auditor_func ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
41 SELECT INTO column_list array_agg(a.attname)
42 FROM pg_catalog.pg_attribute a
43 JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
44 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
45 WHERE relkind = 'r' AND n.nspname = sch AND c.relname = tbl AND a.attnum > 0 AND NOT a.attisdropped;
48 CREATE OR REPLACE FUNCTION auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ()
49 RETURNS TRIGGER AS $func$
51 INSERT INTO auditor.$$ || sch || $$_$$ || tbl || $$_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, $$
52 || array_to_string(column_list, ', ') || $$ )
53 SELECT nextval('auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq'),
58 OLD.$$ || array_to_string(column_list, ', OLD.') || $$
59 FROM auditor.get_audit_info();
62 $func$ LANGUAGE 'plpgsql';
66 $creator$ LANGUAGE 'plpgsql';
68 CREATE OR REPLACE FUNCTION auditor.create_auditor_lifecycle ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
72 SELECT INTO column_list array_agg(a.attname)
73 FROM pg_catalog.pg_attribute a
74 JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
75 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
76 WHERE relkind = 'r' AND n.nspname = sch AND c.relname = tbl AND a.attnum > 0 AND NOT a.attisdropped;
79 CREATE VIEW auditor.$$ || sch || $$_$$ || tbl || $$_lifecycle AS
80 SELECT -1 AS audit_id,
85 $$ || array_to_string(column_list, ', ') || $$
86 FROM $$ || sch || $$.$$ || tbl || $$
88 SELECT audit_id, audit_time, audit_action, audit_user, audit_ws,
89 $$ || array_to_string(column_list, ', ') || $$
90 FROM auditor.$$ || sch || $$_$$ || tbl || $$_history;
94 $creator$ LANGUAGE 'plpgsql';
96 -- Corrects all column discrepencies between audit table and core table:
97 -- Adds missing columns
98 -- Removes leftover columns
100 -- Also, ensures all core auditor columns exist.
101 CREATE OR REPLACE FUNCTION auditor.fix_columns() RETURNS VOID AS $BODY$
103 current_table TEXT = ''; -- Storage for post-loop main table name
104 current_audit_table TEXT = ''; -- Storage for post-loop audit table name
105 query TEXT = ''; -- Storage for built query
106 cr RECORD; -- column record object
107 alter_t BOOL = false; -- Has the alter table command been appended yet
108 auditor_cores TEXT[] = ARRAY[]::TEXT[]; -- Core auditor function list (filled inside of loop)
109 core_column TEXT; -- The current core column we are adding
112 WITH audit_tables AS ( -- Basic grab of auditor tables. Anything in the auditor namespace, basically. With oids.
113 SELECT c.oid AS audit_oid, c.relname AS audit_table
114 FROM pg_catalog.pg_class c
115 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
116 WHERE relkind='r' AND nspname = 'auditor'
118 table_set AS ( -- Union of auditor tables with their "main" tables. With oids.
119 SELECT a.audit_oid, a.audit_table, c.oid AS main_oid, n.nspname as main_namespace, c.relname as main_table
120 FROM pg_catalog.pg_class c
121 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
122 JOIN audit_tables a ON a.audit_table = n.nspname || '_' || c.relname || '_history'
125 column_lists AS ( -- All columns associated with the auditor or main table, grouped by the main table's oid.
126 SELECT DISTINCT ON (main_oid, attname) t.main_oid, a.attname
128 JOIN pg_catalog.pg_attribute a ON a.attrelid IN (t.main_oid, t.audit_oid)
129 WHERE attnum > 0 AND NOT attisdropped
131 column_defs AS ( -- The motherload, every audit table and main table plus column names and defs.
135 a.attname AS main_column, -- These two will be null for columns that have since been deleted, or for auditor core columns
136 pg_catalog.format_type(a.atttypid, a.atttypmod) AS main_column_def,
137 b.attname AS audit_column, -- These two will be null for columns that have since been added
138 pg_catalog.format_type(b.atttypid, b.atttypmod) AS audit_column_def
140 JOIN column_lists c USING (main_oid)
141 LEFT JOIN pg_catalog.pg_attribute a ON a.attname = c.attname AND a.attrelid = t.main_oid AND a.attnum > 0 AND NOT a.attisdropped
142 LEFT JOIN pg_catalog.pg_attribute b ON b.attname = c.attname AND b.attrelid = t.audit_oid AND b.attnum > 0 AND NOT b.attisdropped
144 -- Nice sorted output from the above
145 SELECT * FROM column_defs WHERE main_column_def IS DISTINCT FROM audit_column_def ORDER BY main_namespace, main_table, main_column, audit_column
147 IF current_table <> (cr.main_namespace || '.' || cr.main_table) THEN -- New table?
148 FOR core_column IN SELECT DISTINCT unnest(auditor_cores) LOOP -- Update missing core auditor columns
149 IF NOT alter_t THEN -- Add ALTER TABLE if we haven't already
150 query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
153 query:=query || $$,$$;
155 -- Bit of a sneaky bit here. Create audit_id as a bigserial so it gets automatic values and doesn't complain about nulls when becoming a PRIMARY KEY.
156 query:=query || $$ ADD COLUMN $$ || CASE WHEN core_column = 'audit_id bigint' THEN $$audit_id bigserial PRIMARY KEY$$ ELSE core_column END;
158 IF alter_t THEN -- Open alter table = needs a semicolon
159 query:=query || $$; $$;
161 IF 'audit_id bigint' = ANY(auditor_cores) THEN -- We added a primary key...
162 -- Fun! Drop the default on audit_id, drop the auto-created sequence, create a new one, and set the current value
163 -- For added fun, we have to execute in chunks due to the parser checking setval/currval arguments at parse time.
165 EXECUTE $$ALTER TABLE auditor.$$ || current_audit_table || $$ ALTER COLUMN audit_id DROP DEFAULT; $$ ||
166 $$CREATE SEQUENCE auditor.$$ || current_audit_table || $$_pkey_seq;$$;
167 EXECUTE $$SELECT setval('auditor.$$ || current_audit_table || $$_pkey_seq', currval('auditor.$$ || current_audit_table || $$_audit_id_seq')); $$ ||
168 $$DROP SEQUENCE auditor.$$ || current_audit_table || $$_audit_id_seq;$$;
172 -- New table means we reset the list of needed auditor core columns
173 auditor_cores = ARRAY['audit_id bigint', 'audit_time timestamp with time zone', 'audit_action text', 'audit_user integer', 'audit_ws integer'];
174 -- And store some values for use later, because we can't rely on cr in all places.
175 current_table:=cr.main_namespace || '.' || cr.main_table;
176 current_audit_table:=cr.audit_table;
178 IF cr.main_column IS NULL AND cr.audit_column LIKE 'audit_%' THEN -- Core auditor column?
179 -- Remove core from list of cores
180 SELECT INTO auditor_cores array_agg(core) FROM unnest(auditor_cores) AS core WHERE core != (cr.audit_column || ' ' || cr.audit_column_def);
181 ELSIF cr.main_column IS NULL THEN -- Main column doesn't exist, and it isn't an auditor column. Needs dropping from the auditor.
183 query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
186 query:=query || $$,$$;
188 query:=query || $$ DROP COLUMN $$ || cr.audit_column;
189 ELSIF cr.audit_column IS NULL AND cr.main_column IS NOT NULL THEN -- New column auditor doesn't have. Add it.
191 query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
194 query:=query || $$,$$;
196 query:=query || $$ ADD COLUMN $$ || cr.main_column || $$ $$ || cr.main_column_def;
197 ELSIF cr.main_column IS NOT NULL AND cr.audit_column IS NOT NULL THEN -- Both sides have this column, but types differ. Fix that.
199 query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
202 query:=query || $$,$$;
204 query:=query || $$ ALTER COLUMN $$ || cr.audit_column || $$ TYPE $$ || cr.main_column_def;
207 FOR core_column IN SELECT DISTINCT unnest(auditor_cores) LOOP -- Repeat this outside of the loop to catch the last table
209 query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
212 query:=query || $$,$$;
214 -- Bit of a sneaky bit here. Create audit_id as a bigserial so it gets automatic values and doesn't complain about nulls when becoming a PRIMARY KEY.
215 query:=query || $$ ADD COLUMN $$ || CASE WHEN core_column = 'audit_id bigint' THEN $$audit_id bigserial PRIMARY KEY$$ ELSE core_column END;
217 IF alter_t THEN -- Open alter table = needs a semicolon
218 query:=query || $$;$$;
219 IF 'audit_id bigint' = ANY(auditor_cores) THEN -- We added a primary key...
220 -- Fun! Drop the default on audit_id, drop the auto-created sequence, create a new one, and set the current value
221 -- For added fun, we have to execute in chunks due to the parser checking setval/currval arguments at parse time.
223 EXECUTE $$ALTER TABLE auditor.$$ || current_audit_table || $$ ALTER COLUMN audit_id DROP DEFAULT; $$ ||
224 $$CREATE SEQUENCE auditor.$$ || current_audit_table || $$_pkey_seq;$$;
225 EXECUTE $$SELECT setval('auditor.$$ || current_audit_table || $$_pkey_seq', currval('auditor.$$ || current_audit_table || $$_audit_id_seq')); $$ ||
226 $$DROP SEQUENCE auditor.$$ || current_audit_table || $$_audit_id_seq;$$;
232 $BODY$ LANGUAGE plpgsql;
234 -- Update it all routine
235 CREATE OR REPLACE FUNCTION auditor.update_auditors() RETURNS boolean AS $BODY$
241 -- Drop Lifecycle view(s) before potential column changes
244 FROM pg_catalog.pg_class c
245 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
246 WHERE relkind = 'v' AND n.nspname = 'auditor' LOOP
247 EXECUTE $$ DROP VIEW auditor.$$ || auditor_name || $$;$$;
249 -- Fix all column discrepencies
250 PERFORM auditor.fix_columns();
251 -- Re-create trigger functions and lifecycle views
252 FOR table_schema, table_name IN
253 WITH audit_tables AS (
254 SELECT c.oid AS audit_oid, c.relname AS audit_table
255 FROM pg_catalog.pg_class c
256 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
257 WHERE relkind='r' AND nspname = 'auditor'
260 SELECT a.audit_oid, a.audit_table, c.oid AS main_oid, n.nspname as main_namespace, c.relname as main_table
261 FROM pg_catalog.pg_class c
262 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
263 JOIN audit_tables a ON a.audit_table = n.nspname || '_' || c.relname || '_history'
266 SELECT main_namespace, main_table FROM table_set LOOP
268 PERFORM auditor.create_auditor_func(table_schema, table_name);
269 PERFORM auditor.create_auditor_lifecycle(table_schema, table_name);
273 $BODY$ LANGUAGE plpgsql;
275 -- Go ahead and update them all now
276 SELECT auditor.update_auditors();