| commit | author | age | ||
| e880a1 | 1 | --liquibase formatted sql |
| MH | 2 | |
| 3 | -- ============================================================================ | |
| 4 | --changeset audit-OPERATION-TYPE:1 endDelimiter:--// | |
| 5 | -- ---------------------------------------------------------------------------- | |
| 6 | /* | |
| 7 | A type representing a DML operation. | |
| 8 | */ | |
| 9 | do $$ | |
| 10 | begin | |
| 11 | if not exists(select 1 from pg_type where typname = 'operation') then | |
| 12 | create type "operation" as enum ('INSERT', 'UPDATE', 'DELETE', 'TRUNCATE'); | |
| 13 | end if; | |
| 14 | --more types here... | |
| 15 | end $$; | |
| 16 | --// | |
| 17 | ||
| 18 | -- ============================================================================ | |
| 258f8b | 19 | --changeset audit-TX-CONTEXT-TABLE:1 endDelimiter:--// |
| e880a1 | 20 | -- ---------------------------------------------------------------------------- |
| MH | 21 | /* |
| 258f8b | 22 | A table storing transactions with context data. |
| e880a1 | 23 | */ |
| 258f8b | 24 | create table tx_context |
| e880a1 | 25 | ( |
| 560cd9 | 26 | contextId bigint primary key not null, |
| MH | 27 | txId bigint not null, |
| 7f6e36 | 28 | txTimestamp timestamp not null, |
| MH | 29 | currentUser varchar(63) not null, -- not the uuid, because users can be deleted |
| 30 | assumedRoles varchar not null, -- not the uuids, because roles can be deleted | |
| 31 | currentTask varchar(96) not null, | |
| 32 | currentRequest varchar(512) not null | |
| e880a1 | 33 | ); |
| MH | 34 | |
| 258f8b | 35 | create index on tx_context using brin (txTimestamp); |
| e880a1 | 36 | --// |
| MH | 37 | |
| 38 | -- ============================================================================ | |
| 258f8b | 39 | --changeset audit-TX-JOURNAL-TABLE:1 endDelimiter:--// |
| e880a1 | 40 | -- ---------------------------------------------------------------------------- |
| MH | 41 | /* |
| 258f8b | 42 | A table storing the transaction audit journal for all target tables it's configured for. |
| e880a1 | 43 | */ |
| 258f8b | 44 | create table tx_journal |
| MH | 45 | ( |
| 560cd9 | 46 | contextId bigint not null references tx_context (contextId), |
| 258f8b | 47 | targetTable text not null, |
| MH | 48 | targetUuid uuid not null, -- Assumes that all audited tables have a uuid column. |
| 49 | targetOp operation not null, | |
| 50 | targetDelta jsonb | |
| 51 | ); | |
| 52 | ||
| 53 | create index on tx_journal (targetTable, targetUuid); | |
| 54 | --// | |
| 55 | ||
| 56 | -- ============================================================================ | |
| 57 | --changeset audit-TX-JOURNAL-TRIGGER:1 endDelimiter:--// | |
| 58 | -- ---------------------------------------------------------------------------- | |
| 59 | /* | |
| 60 | Trigger function for transaction audit journal. | |
| 61 | */ | |
| 62 | create or replace function tx_journal_trigger() | |
| e880a1 | 63 | returns trigger |
| MH | 64 | language plpgsql as $$ |
| 560cd9 | 65 | declare |
| MH | 66 | curTask text; |
| 67 | curContextId bigint; | |
| e880a1 | 68 | begin |
| 560cd9 | 69 | curTask := currentTask(); |
| MH | 70 | curContextId := txid_current()+bigIntHash(curTask); |
| 258f8b | 71 | |
| MH | 72 | insert |
| 560cd9 | 73 | into tx_context (contextId, txId, txTimestamp, currentUser, assumedRoles, currentTask, currentRequest) |
| MH | 74 | values (curContextId, txid_current(), now(), |
| 75 | currentUser(), assumedRoles(), curTask, currentRequest()) | |
| 258f8b | 76 | on conflict do nothing; |
| MH | 77 | |
| e880a1 | 78 | case tg_op |
| 258f8b | 79 | when 'INSERT' then insert |
| MH | 80 | into tx_journal |
| 560cd9 | 81 | values (curContextId, |
| 258f8b | 82 | tg_table_name, new.uuid, tg_op::operation, |
| MH | 83 | to_jsonb(new)); |
| 84 | when 'UPDATE' then insert | |
| 85 | into tx_journal | |
| 560cd9 | 86 | values (curContextId, |
| 258f8b | 87 | tg_table_name, old.uuid, tg_op::operation, |
| MH | 88 | jsonb_changes_delta(to_jsonb(old), to_jsonb(new))); |
| 89 | when 'DELETE' then insert | |
| 90 | into tx_journal | |
| 560cd9 | 91 | values (curContextId, |
| 258f8b | 92 | tg_table_name, old.uuid, 'DELETE'::operation, |
| MH | 93 | null::jsonb); |
| 94 | else raise exception 'Trigger op % not supported for %.', tg_op, tg_table_name; | |
| 95 | end case; | |
| e880a1 | 96 | return null; |
| MH | 97 | end; $$; |
| 98 | --// | |
| 99 | ||
| 100 | -- ============================================================================ | |
| 258f8b | 101 | --changeset audit-CREATE-JOURNAL-LOG:1 endDelimiter:--// |
| e880a1 | 102 | -- ---------------------------------------------------------------------------- |
| MH | 103 | /* |
| 258f8b | 104 | Trigger function for transaction audit journal. |
| e880a1 | 105 | */ |
| MH | 106 | |
| 258f8b | 107 | create or replace procedure create_journal(targetTable varchar) |
| e880a1 | 108 | language plpgsql as $$ |
| MH | 109 | declare |
| 258f8b | 110 | createTriggerSQL varchar; |
| e880a1 | 111 | begin |
| 737630 | 112 | targetTable := lower(targetTable); |
| MH | 113 | |
| 258f8b | 114 | createTriggerSQL = 'CREATE TRIGGER ' || targetTable || '_journal' || |
| e880a1 | 115 | ' AFTER INSERT OR UPDATE OR DELETE ON ' || targetTable || |
| 258f8b | 116 | ' FOR EACH ROW EXECUTE PROCEDURE tx_journal_trigger()'; |
| e880a1 | 117 | execute createTriggerSQL; |
| MH | 118 | end; $$; |
| 119 | --// | |
