Geekly Articles each Day
📜 ⬆️ ⬇️

PostgreSQL data change audit

image

There was a need to audit data changes in the existing system.

Requirements:



My first thought was to add _user, _create_date, _delete_date to logged tables.
')
On operations INSERT, UPDATE, DELETE hang triggers working with these fields.
When adding an entry, fill in the _user and _create_date fields.

Instead of updating, make a copy of the updated string (with changed values), and fill in the _delete_date field in the updated row itself.

Instead of deleting the entry, fill in the _delete_date field.

When referring to such a table in the WHERE clause , _delete_date IS NULL must be added.

This mechanism could have worked if it had been incorporated into the database architecture initially, but at the time of introducing logging, we had more than 3000 functions written, each of which would have to be modified.

Then came the idea to keep the logs separate from the data. The idea was as follows:
In the logs scheme, a copy of the table structure is created, plus several service fields.

For each logged table, a trigger is hung, which performs all the dirty work of storing the changed data.

Trigger code
CREATE OR REPLACE FUNCTION logs.tf_log_table() RETURNS trigger AS $BODY$ DECLARE query text; safe_table_name text; BEGIN SELECT quote_ident(nspname||'.'||relname) FROM pg_class cl INNER JOIN pg_namespace nsp ON (cl.relnamespace=nsp.oid) WHERE cl.oid=TG_RELID INTO safe_table_name; query='INSERT INTO logs.'||safe_table_name||' SELECT ($1).*, now(),$2,session_user;'; IF (TG_OP = 'DELETE') THEN EXECUTE query using OLD,'D'; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN EXECUTE query using OLD,'U'; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN EXECUTE query using NEW,'I'; RETURN NEW; END IF; /*           ,   ,      */ EXCEPTION WHEN SQLSTATE '42P01' OR SQLSTATE '42801' OR SQLSTATE '42804' THEN PERFORM logs.create_log_tables(TG_RELID::regclass); IF (TG_OP = 'DELETE') THEN EXECUTE query using OLD,'D'; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN EXECUTE query using OLD,'U'; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN EXECUTE query using NEW,'I'; RETURN NEW; END IF; /*  - ,      */ WHEN OTHERS then IF (TG_OP = 'DELETE') THEN RETURN OLD; ELSE RETURN NEW; END IF; END; $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;



In this trigger there are several constructions, peculiar only plpgsql, I will try to paint them in more detail.

SELECT quote_ident (nspname || '.' || relname)
FROM pg_class cl INNER JOIN pg_namespace nsp ON (cl.relnamespace = nsp.oid)
WHERE cl.oid = TG_RELID INTO safe_table_name;

TG_RELID is a special variable that exists only when the trigger function is triggered. It contains the identifier of the table that caused the trigger.
With its help, we generate the name of the table in which the logs will be written.

query = 'INSERT INTO logs.' || safe_table_name || ' SELECT ($ 1). *, Now (), $ 2, session_user; ';

Dynamic SQL is used to insert the data itself.
The variable $ 1 is replaced with data from the line on which the trigger worked (the whole line is substituted there, it must be expanded into separate fields - this is done by construction (ROW). *
now () is a function that returns the start time of the transaction.
session_user - current session username

IF (TG_OP = 'DELETE')
THEN
EXECUTE query USING OLD, 'D';

TG_OP is another variable that exists only in trigger functions. It contains the name of the operation that triggered the trigger (INSERT, UPDATE, DELETE or TRUNCATE)
OLD, NEW - the old and new versions of the string are stored in these variables.

Further, in case something goes wrong, a fairly simple error handling is provided:
EXCEPTION
WHEN SQLSTATE '42P01' OR SQLSTATE '42801' OR SQLSTATE '42804' THEN
PERFORM logs.create_log_tables (TG_RELID :: regclass);

If the table structure has been changed, or for some reason the table with the logs has not been created, then it is created again and an attempt is made to write a log to it.
In all other cases, if an error occurs, the logging procedure is ignored.

Code of the logging function
 CREATE OR REPLACE FUNCTION logs.create_log_tables(table_oid oid) RETURNS int AS $BODY$ DECLARE log_namespace oid=(SELECT oid from pg_namespace WHERE nspname='logs'); p_relname text; new_tbl_name text; safe_table_name text; BEGIN SELECT relname FROM pg_class WHERE oid=table_oid INTO p_relname; SELECT quote_ident(nspname||'.'||relname) FROM pg_class cl inner join pg_namespace nsp ON (cl.relnamespace=nsp.oid) where cl.oid=table_oid INTO safe_table_name; /*    */ SELECT safe_table_name||'_'||(now()::date)::text||'('||i||')' FROM generate_series(1,10) a(i) WHERE safe_table_name||'_'||(now()::date)::text||'('||i||')' not in(select relname from pg_class where relnamespace=log_namespace and relpersistence='p') ORDER BY i LIMIT 1 INTO new_tbl_name; /*    */ EXECUTE 'ALTER TABLE IF EXISTS logs.'||safe_table_name|| ' RENAME TO '||quote_ident(new_tbl_name)||';'; /*     ,  ,   */ EXECUTE 'create table logs.'||safe_table_name||' (like '||table_oid::regclass||');'; EXECUTE 'ALTER TABLE logs.'||safe_table_name||' ADD COLUMN "'||p_relname||'_timestamp" timestamp with time zone;'; EXECUTE 'ALTER TABLE logs.'||safe_table_name||' ADD COLUMN "'||p_relname||'_operation" char;'; EXECUTE 'ALTER TABLE logs.'||safe_table_name||' ADD COLUMN "'||p_relname||'_user" text;'; /* */ EXECUTE ' DROP TRIGGER IF exists tr_log_table ON '||table_oid::regclass::text||'; CREATE TRIGGER tr_log_table BEFORE UPDATE OR DELETE OR INSERT ON '||table_oid::regclass::text||' FOR EACH ROW EXECUTE PROCEDURE logs.tf_log_table();'; RETURN 0; end; $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;


The logging connection algorithm is quite simple. To begin with, a clone is created based on the name of the logged table (if a table with that name already existed, the old table is renamed), then the necessary utility fields are added to this clone, and a trigger is connected to the logged table.

Advantages of this option:


Source: https://habr.com/ru/post/323618/

More articles:


All Articles