Audit data changes PostgreSQL
There arose a need to audit data changes in the existing system.
Requirements:
the
-
the
- Easy to enable/disable logging for individual tables. the
- to minimize changes to the existing functions of the database. the
- to Minimize the performance degradation.
First thought was to add in loggerhome table field _user, _create_date, _delete_date.
For operations INSERT, UPDATE, DELETE triggers the hang of working with these fields.
When a record is added to fill the field _user and _create_date.
Instead of updating, make a copy of the row being updated (with changed values), and in the updated row to fill in the field _delete_date.
Instead of deleting the recording to fill in the field _delete_date.
When referring to the table in block WHERE you need to add _delete_date IS NULL.
This mechanism could work if it was built in the architecture of the database initially, but by the time of implementation of the logging was written more than 3000 functions, each of which would have had to make changes.
Then came the idea to store the logs separately from the data. The idea was the following:
In the scheme of logs creates a copy of the table structure plus a few service fields.
Each loggorhea table hung a trigger that does all the dirty work to preserve 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 the OLD,'D';
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
EXECUTE query using the OLD'U';
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
EXECUTE query using the NEW'I';
RETURN NEW;
END IF;
/*If the table log is not created or its structure differs from the current one, then re-create it, and again, trying to write down data*/
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 the OLD,'D';
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
EXECUTE query using the OLD'U';
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
EXECUTE query using the NEW'I';
RETURN NEW;
END IF;
/* If something else, ignore the error and return the standard reply*/
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 some designs peculiar only to 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 special variable that exists only when triggered, trigger a function, it stores the ID of the table that caused the trigger.
With her help, we generated the table name, which will be written logs.
query='INSERT INTO logs.'||safe_table_name||' SELECT ($1).*, now(),$2,session_user;';
To insert data using dynamic SQL.
In place of the variable $1 is populated from the data from the line that triggered the trigger (there is substituted the entire the entire string, it must be deployed on separate fields — this is done by construction (ROW).*
now() — a function that returns time the transaction started.
session_user — the username of the current session
IF (TG_OP = 'DELETE')
THEN
EXECUTE query USING the OLD,'D';
TG_OP — another variable that exists only in trigger functions, it holds the name of the operation which triggered the trigger (INSERT, UPDATE, DELETE or TRUNCATE)
OLD, NEW — these variables store the old and new version string.
Further, in case if something goes wrong, provides a fairly simple error handling:
EXCEPTION
WHEN SQLSTATE '42P01' OR SQLSTATE '42801' OR SQLSTATE '42804' THEN
PERFORM logs.create_log_tables(TG_RELID::regclass);
If you have changed the table structure, or for some reason, the table log was not created, it is created again and attempt to record it in the log.
Code for the function connect logging
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;
/*Generate a new name for the table*/
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;
/*Rename the old table with the logs*/
EXECUTE 'ALTER TABLE IF EXISTS logs.'||safe_table_name|| ' RENAME TO '||quote_ident(new_tbl_name)||';';
/*Create a table with the same structure as loggerhead, plus service fields*/
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;';
/*Plug-in trigger*/
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 algorithm of connection logging is quite simple. For starters based on the name loggerodeo table creates a clone of it (if the table with the same name already existed, the old table is renamed), then this clone can be added service fields, and loggerhead the table is connected to the trigger.
Advantages of this option are:
the
-
the
- you do Not need to change anything in existing functionality. the
- select Query does not suffer in performance. the
- changes in the structure bloggerwave table table logs will be automatically re-generated.
the Logs can be quickly cleaned by removing the old table.
Комментарии
Отправить комментарий