forked from jcasanov/pg_audit
-
Notifications
You must be signed in to change notification settings - Fork 0
/
pg_audit.array.sql
79 lines (66 loc) · 2.75 KB
/
pg_audit.array.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
-- Table to keep auditing information
CREATE TABLE audit_log (
log_id serial NOT NULL PRIMARY KEY,
log_relid oid NOT NULL,
log_session_user text NOT NULL DEFAULT SESSION_USER,
log_when timestamp with time zone NOT NULL DEFAULT now(),
log_client_addr inet,
log_operation text,
log_columns text[],
log_old_values text[],
log_new_values text[]
);
-- Trigger to use on all the tables we want to track
/*
* To use create a trigger in this way:
* CREATE TRIGGER trigger_name AFTER INSERT OR UPDATE OR DELETE
* ON table_name FOR EACH ROW EXECUTE PROCEDURE logger();
*
* To track TRUNCATE events you can also create a trigger as:
* CREATE TRIGGER trigger_name AFTER TRUNCATE
* ON table_name FOR EACH STATEMENT EXECUTE PROCEDURE logger();
*/
CREATE OR REPLACE FUNCTION logger() RETURNS trigger AS $$
DECLARE
hs_new hstore = NULL;
hs_old hstore = NULL;
BEGIN
-- Check that the trigger for the logger should be AFTER and FOR EACH ROW
IF TG_WHEN = 'BEFORE' THEN
RAISE EXCEPTION 'Trigger for logger should be AFTER';
END IF;
IF TG_LEVEL = 'STATEMENT' AND TG_OP <> 'TRUNCATE' THEN
RAISE EXCEPTION 'Trigger for logger should be FOR EACH ROW';
END IF;
-- Obtain the hstore versions of NEW and OLD, when appropiate
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
SELECT hstore(new.*) INTO hs_new;
END IF;
IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
SELECT hstore(old.*) INTO hs_old;
END IF;
INSERT INTO audit_log.audit_log(log_relid, log_client_addr, log_operation, log_columns, log_old_values, log_new_values)
SELECT TG_RELID, inet_client_addr(), TG_OP, akeys(hs_old), avals(hs_old), avals(hs_new);
RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE OR REPLACE FUNCTION install_logger(schema_name text, table_name text, log_truncate boolean default false) RETURNS boolean AS $$
DECLARE
fq_table_name text = NULL;
BEGIN
SELECT schema_name || '.' || table_name INTO fq_table_name;
-- check if the table exists and if it doesn't get an error
EXECUTE 'SELECT ' || quote_literal(fq_table_name) || '::regclass';
-- drop the trigger if it already exists and re-create it
-- this is easier than checking pg_triggers to see if the trigger exists
EXECUTE 'DROP TRIGGER IF EXISTS auditing_mod_actions ON ' || fq_table_name;
EXECUTE 'CREATE TRIGGER auditing_mod_actions AFTER INSERT OR UPDATE OR DELETE ' ||
' ON ' || fq_table_name || ' FOR EACH ROW EXECUTE PROCEDURE logger();';
IF (log_truncate) THEN
EXECUTE 'DROP TRIGGER IF EXISTS auditing_truncate_actions ON ' || fq_table_name;
EXECUTE 'CREATE TRIGGER auditing_truncate_actions AFTER TRUNCATE ' ||
' ON ' || fq_table_name || ' FOR EACH STATEMENT EXECUTE PROCEDURE logger();';
END IF;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY INVOKER STRICT;