Last active
January 29, 2024 17:48
-
-
Save kematzy/a2bec88b26655f9fad98116f59bb30de to your computer and use it in GitHub Desktop.
Hasura AuditTrail by Kematzy
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ## Kematzy Hasura AuditTrail | |
-- This is based on the [Hasura/audit-trigger](https://github.com/hasura/audit-trigger). | |
-- | |
-- Changes from the Hasura version: | |
-- 1. Simplified audit table schema with these changes: | |
-- a. Renamed columns to lowerFirst format. | |
-- b. Changed order of columns. | |
-- c. Combined schema & table name into one column. | |
-- d. Stores the record `id` UUID value in the `rowId` column. | |
-- e. Stores only the IP address, and not the Port number. | |
-- 2. Row data is stored in jsonb | |
-- ## Installation | |
-- | |
-- Load `kematzy.hasura.audit_trail.sql` into the database where you want to set up auditing. | |
-- You can do this via psql or any other tool that lets you execute sql on the database. | |
-- | |
-- `$ psql -h <db-host> -p <db-port> -U <db-user> -d <db> -f audit.sql --single-transaction` | |
-- | |
-- ### Setting up triggers | |
-- | |
-- Run the following sql to setup audit on a table | |
-- | |
-- SELECT audit.audit_table('author'); | |
-- | |
-- For a table in a different schema name as follows: | |
-- | |
-- SELECT audit.audit_table('shipping.delivery'); | |
-- | |
-- This sets up triggers on the given table which logs any change (insert/update/delete) | |
-- into the table `audit.audit_trail`. | |
-- | |
-- SELECT * FROM "audit"."audit_trail"; | |
-- | |
-- | |
-- | |
-- SELECT "rowData", "changes", "createdAt" | |
-- FROM "audit"."audit_trail" WHERE "rowId" = '<uuid>' | |
-- ORDER BY "createdAt" DESC; | |
-- | |
-- ## USAGE: | |
-- | |
-- select audit.audit_table('<tableName>', true, true, '{createdAt, createdBy,updatedAt,updatedBy,deletedAt,deletedBy}'); | |
-- ## REMOVE AUDITING: | |
-- | |
-- To remove the auditing of a table, you need to call the following: | |
-- | |
-- -- if you have setup auditing on the `public.clients` table, then execute the following: | |
-- | |
-- DROP TRIGGER IF EXISTS trg_audit_trail_row ON "public"."clients"; | |
-- DROP TRIGGER IF EXISTS trg_audit_trail_stm ON "public"."clients"; | |
-- | |
-- ### REMOVE ALL AUDITING: | |
-- | |
-- | |
-- ## TESTED ON: | |
-- | |
-- 1. Heroku - PostgeSQL 10.6 (Ubuntu) Hobby account [2018-12-07] | |
-- The following are comments preserved from the original file: | |
--> -- An audit history is important on most tables. Provide an audit trigger that logs to | |
--> -- a dedicated audit table for the major relations. | |
--> -- | |
--> -- This file should be generic and not depend on application roles or structures, | |
--> -- as it's being listed here: | |
--> -- | |
--> -- This trigger was originally based on | |
--> -- http://wiki.postgresql.org/wiki/Audit_trigger | |
--> -- but has been completely rewritten. | |
--> -- | |
CREATE SCHEMA audit; | |
REVOKE ALL ON SCHEMA audit FROM public; | |
COMMENT ON SCHEMA audit IS 'Out-of-table audit/history logging tables and trigger functions'; | |
-- Audited data. Lots of information is available, it's just a matter of how much | |
-- you really want to record. See: | |
-- | |
-- http://www.postgresql.org/docs/9.1/static/functions-info.html | |
-- | |
-- Remember, every column you add takes up more audit table space and slows audit | |
-- inserts. | |
-- | |
-- Every index you add has a big impact too, so avoid adding indexes to the | |
-- audit table unless you REALLY need them. | |
-- | |
-- It is sometimes worth copying the audit table, or a coarse subset of it that | |
-- you're interested in, into a temporary table where you CREATE any useful | |
-- indexes and do your analysis. | |
-- | |
CREATE TABLE audit.audit_trail ( | |
-- The unique identifier for each auditable event | |
"event_id" bigserial PRIMARY KEY, | |
-- The DB schema & table name the event occured in | |
"tableName" text NOT NULL, | |
-- Action type; I = insert, D = delete, U = update, T = truncate | |
"action" TEXT NOT NULL CHECK (action IN ('I','D','U', 'T')), | |
-- Record id value of audited row. (NULL on INSERT) | |
"rowId" uuid NULL, | |
-- Record value. Null for statement-level trigger. | |
-- For INSERT this is the NEW tuple. | |
-- For DELETE and UPDATE it is the OLD tuple. | |
"rowData" jsonb, | |
-- Fields changed by UPDATE, during the audit event. [Default: NULL] | |
"changes" jsonb, | |
-- Login/session user who caused the audit event. | |
"pgUser" text, | |
-- the Hasura `Headers` passed | |
"hasuraUser" jsonb, | |
-- Identifier of transaction that made the change. | |
-- (unique when combined with createdAt). | |
"idTransaction" bigint, | |
-- Application name set when audit event occurred. | |
-- Can be changed in-session by client. | |
"appName" text, | |
-- IP address of client that issued query. | |
-- Null for unix domain socket. | |
"clientAddr" inet, | |
-- Timestamp for when the audited event occurred | |
"createdAt" timestamptz NOT NULL | |
); | |
-- | |
REVOKE ALL ON audit.audit_trail FROM public; | |
-- `audit_trail` comments: | |
COMMENT ON TABLE "audit"."audit_trail" IS 'Stores an audit trail of actions on audited tables, from audit.fns_audit_trail()'; | |
COMMENT ON COLUMN "audit"."audit_trail.event_id" IS 'The unique identifier for each auditable event'; | |
COMMENT ON COLUMN "audit"."audit_trail.tableName" IS 'The DB schema & table name the event occured in'; | |
COMMENT ON COLUMN "audit"."audit_trail.action" IS 'Action type; I = insert, D = delete, U = update, T = truncate'; | |
COMMENT ON COLUMN "audit"."audit_trail.rowId" IS 'Record id value of audited row. (NULL on INSERT)'; | |
COMMENT ON COLUMN "audit"."audit_trail.rowData" IS 'Record value. Null for statement-level trigger. For INSERT & UPDATE stores the NEW tuple. For DELETE stores the OLD tuple.'; | |
COMMENT ON COLUMN "audit"."audit_trail.changes" IS 'Fields changed by UPDATE, during the audit event. [Default: NULL]'; | |
COMMENT ON COLUMN "audit"."audit_trail.pgUser" IS 'Login/session user who caused the audit event'; | |
COMMENT ON COLUMN "audit"."audit_trail.hasuraUser" IS 'the Hasura `Headers` passed'; | |
COMMENT ON COLUMN "audit"."audit_trail.idTransaction" IS 'Identifier of transaction that made the change (unique when combined with createdAt)'; | |
COMMENT ON COLUMN "audit"."audit_trail.appName" IS 'Application name set when audit event occurred. Can be changed in-session by client'; | |
COMMENT ON COLUMN "audit"."audit_trail.clientAddr" IS 'IP address of client that issued query. [NULL for UNIX domain socket]'; | |
COMMENT ON COLUMN "audit"."audit_trail.createdAt" IS 'Timestamp for when the audited event occurred'; | |
-- ADD INDEX | |
CREATE INDEX "audit_trail_rowId_idx" ON audit.audit_trail("rowId"); | |
CREATE INDEX "audit_trail_createdAt_idx" ON audit.audit_trail("createdAt"); | |
CREATE INDEX "audit_trail_action_idx" ON audit.audit_trail(action); | |
-- FUNTION: | |
-- the base function that generates the `audit_trail` inserts. | |
-- | |
CREATE OR REPLACE FUNCTION audit.fns_audit_trail() | |
RETURNS TRIGGER AS $body$ | |
DECLARE | |
audit_row audit.audit_trail; | |
excluded_cols text[] = ARRAY[]::text[]; | |
new_r jsonb; | |
old_r jsonb; | |
BEGIN | |
IF TG_WHEN <> 'AFTER' THEN | |
RAISE EXCEPTION 'audit.fns_audit_trail() may only run as an AFTER trigger'; | |
END IF; | |
-- prepare the audited row | |
audit_row = ROW( | |
nextval('audit.audit_trail_event_id_seq'), -- event_id | |
TG_TABLE_SCHEMA::text || '.' || TG_TABLE_NAME::text, -- table_name | |
substring(TG_OP,1,1), -- action | |
NULL, -- row ID | |
NULL, -- rowData | |
NULL, -- row Changes | |
session_user::text, -- pgUser | |
current_setting('hasura.user', 't')::jsonb, -- user information from Hasura GraphQL engine | |
txid_current(), -- transaction ID | |
current_setting('application_name'), -- appName clientapplication | |
inet_client_addr(), -- clientAddr | |
current_timestamp -- action_tstamp_tx | |
); | |
-- grab excluded columns | |
IF TG_ARGV[1] IS NOT NULL THEN | |
excluded_cols = TG_ARGV[1]::text[]; | |
END IF; | |
-- UPDATES | |
IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN | |
old_r = to_jsonb(OLD); | |
new_r = to_jsonb(NEW); | |
audit_row."rowId" = OLD.id; | |
-- include all columns in rowData | |
-- audit_row."rowData" = old_r | |
-- include ONLY NON-EXCLUDED columns in rowData | |
audit_row."rowData" = old_r - excluded_cols; | |
SELECT | |
jsonb_object_agg(new_t.key, new_t.value) - excluded_cols | |
INTO | |
audit_row."changes" | |
FROM jsonb_each(old_r) as old_t | |
JOIN jsonb_each(new_r) as new_t | |
ON (old_t.key = new_t.key AND old_t.value <> new_t.value); | |
ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN | |
audit_row."rowId" = OLD.id; | |
-- audit_row."rowData" = to_jsonb(OLD) - excluded_cols; | |
audit_row."rowData" = to_jsonb(OLD); | |
ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN | |
audit_row."rowId" = NEW.id; | |
-- audit_row."rowData" = to_jsonb(NEW) - excluded_cols; | |
audit_row."rowData" = to_jsonb(NEW); | |
ELSE | |
RAISE EXCEPTION '[audit.fns_audit_trail] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL; | |
RETURN NULL; | |
END IF; | |
INSERT INTO audit.audit_trail VALUES (audit_row.*); | |
RETURN NULL; | |
END; | |
$body$ | |
LANGUAGE 'plpgsql' | |
SECURITY DEFINER | |
SET search_path = pg_catalog, public; | |
COMMENT ON FUNCTION audit.fns_audit_trail() IS '$body$ | |
Track changes to a table at the statement and/or row level. | |
Optional parameters to trigger in CREATE TRIGGER call: | |
param 0: boolean, whether to log the query text. Default ''t''. | |
param 1: text[], columns to ignore in updates. Default []. | |
Updates to ignored cols are omitted from changed_fields. | |
Updates with only ignored cols changed are not inserted | |
into the audit log. | |
Almost all the processing work is still done for updates | |
that ignored. If you need to save the load, you need to use | |
WHEN clause on the trigger instead. | |
No warning or error is issued if ignored_cols contains columns | |
that do not exist in the target table. This lets you specify | |
a standard set of ignored columns. | |
There is no parameter to disable logging of values. Add this trigger as | |
a ''FOR EACH STATEMENT'' rather than ''FOR EACH ROW'' trigger if you do not | |
want to log row values. | |
Note that the user name logged is the login role for the session. The audit trigger | |
cannot obtain the active role because it is reset by the SECURITY DEFINER invocation | |
of the audit trigger itself. | |
$body$'; | |
-- FUNCTION: | |
-- | |
-- | |
CREATE OR REPLACE FUNCTION audit.audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean, ignored_cols text[]) | |
RETURNS void AS $body$ | |
DECLARE | |
stm_targets text = 'INSERT OR UPDATE OR DELETE OR TRUNCATE'; | |
_q_txt text; | |
_ignored_cols_snip text = ''; | |
BEGIN | |
EXECUTE 'DROP TRIGGER IF EXISTS trg_audit_trail_row ON ' || target_table; | |
EXECUTE 'DROP TRIGGER IF EXISTS trg_audit_trail_stm ON ' || target_table; | |
IF audit_rows THEN | |
IF array_length(ignored_cols,1) > 0 THEN | |
_ignored_cols_snip = ', ' || quote_literal(ignored_cols); | |
END IF; | |
_q_txt = 'CREATE TRIGGER trg_audit_trail_row AFTER INSERT OR UPDATE OR DELETE ON ' || target_table || | |
' FOR EACH ROW EXECUTE PROCEDURE audit.fns_audit_trail(' || quote_literal(audit_query_text) || _ignored_cols_snip || ');'; | |
RAISE NOTICE '%',_q_txt; | |
EXECUTE _q_txt; | |
stm_targets = 'TRUNCATE'; | |
ELSE | |
END IF; | |
_q_txt = 'CREATE TRIGGER trg_audit_trail_stm AFTER ' || stm_targets || ' ON ' || target_table || | |
' FOR EACH STATEMENT EXECUTE PROCEDURE audit.fns_audit_trail('|| quote_literal(audit_query_text) || ');'; | |
RAISE NOTICE '%',_q_txt; | |
EXECUTE _q_txt; | |
END; | |
$body$ | |
language 'plpgsql'; | |
COMMENT ON FUNCTION audit.audit_table(regclass, boolean, boolean, text[]) IS '$body$ | |
Add auditing support to a table. | |
Arguments: | |
target_table: Table name, schema qualified if not on search_path | |
audit_rows: Record each row change, or only audit at a statement level | |
audit_query_text: Record the text of the client query that triggered the audit event? | |
ignored_cols: Columns to exclude from update diffs, ignore updates that change only ignored cols. | |
$body$'; | |
-- Pg doesn't allow variadic calls with 0 params, so provide a wrapper | |
CREATE OR REPLACE FUNCTION audit.audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean) | |
RETURNS void AS $body$ | |
SELECT audit.audit_table($1, $2, $3, ARRAY[]::text[]); | |
$body$ LANGUAGE SQL; | |
-- And provide a convenience call wrapper for the simplest case | |
-- of row-level logging with no excluded cols and query logging enabled. | |
CREATE OR REPLACE FUNCTION audit.audit_table(target_table regclass) | |
RETURNS void AS $body$ | |
SELECT audit.audit_table($1, BOOLEAN 't', BOOLEAN 't'); | |
$body$ LANGUAGE 'sql'; | |
COMMENT ON FUNCTION audit.audit_table(regclass) IS '$body$ | |
Add auditing support to the given table. Row-level changes will be logged with full client query text. No cols are ignored. | |
$body$'; | |
-- ## CHANGELOG: | |
-- [v0.0.1][2018-12-07]: Initial version created | |
-- ## LICENCE: | |
-- Copyright (c) 2018, Kematzy [github.com/kematzy] | |
-- The following is "The PostgreSQL License", effectively equivalent to the BSD | |
-- license. | |
-- I, Craig Ringer, cede any copyright on this work to the PostgreSQL Global | |
-- Development Group. | |
-- ------ | |
-- PostgreSQL Audit Trigger Example | |
-- Copyright (c) 2013, PostgreSQL Global Development Group | |
-- Permission to use, copy, modify, and distribute this software and its | |
-- documentation for any purpose, without fee, and without a written agreement | |
-- is hereby granted, provided that the above copyright notice and this | |
-- paragraph and the following two paragraphs appear in all copies. | |
-- IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR | |
-- DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING | |
-- LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS | |
-- DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE | |
-- POSSIBILITY OF SUCH DAMAGE. | |
-- THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, | |
-- INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY | |
-- AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS | |
-- ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO | |
-- PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment