Реализация аудита изменения данных в СУБД PostgreSQL

Аудит изменений базы данных — это комплекс мероприятий, направленных на сохранение информации обо всех действиях, происходящих в базе данных. Аудит является одним из способов обеспечения информационной безопасности.

Файлы регистрации или «логи» — файл с записями о событиях в хронологическом порядке, простейшее средство обеспечения журналирования и аудита.

Рассмотрим способы ведения аудита изменения данных в уже существующей системе на основе СУБД PostgreSQL.

Аудит можно реализовать посредством добавления столбцов в логгируемые таблицы с помощью триггеров на операции INSERT, UPDATE, DELETE. Это могло бы сработать, если бы был заложен в архитектуру БД изначально, но если у нас к моменту внедрения логгирования было написано множество функций, то в каждую пришлось бы вносить изменения.

Поэтому будем хранить логи отдельно от данных. В схеме logs создадим копию структуры таблицы и несколько служебных полей.

На каждую логгируемую таблицу добавляется триггер, который выполняет необходимую работу по сохранению изменившихся данных.

Рассмотрим следующий триггер и его конструкции:

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 специальная переменная, существующая только при срабатывании триггерной функции, в ней хранится идентификатор таблицы, которая вызвала триггер.

С ее помощью мы генерируем имя таблицы, в которую будут записаны логи.

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

now(),$2,session_user;';

Для вставки самих данных используется динамический SQL.

На место переменной $1 подставляются данные из строки, на которой сработал триггер (туда подставляется целиком вся строка, ее необходимо развернуть на отдельные поля — это делается конструкцией (ROW).

* now() — функция, возвращающая время начала транзакции.

session_user — имя пользователя текущего сеанса

IF (TG_OP = 'DELETE')

THEN

EXECUTE query USING OLD,'D';

TG_OP — еще одна переменная, существующая только в триггерных функциях, в ней хранится имя операции, от которой сработал триггер (INSERT, UPDATE, DELETE или TRUNCATE)

OLD, NEW — в этих переменных хранятся старая и новая версия строки.

Дальше, на тот случай, если что-то пойдет не так, предусмотрена достаточно простая обработка ошибок:

EXCEPTION

WHEN SQLSTATE '42P01' OR SQLSTATE '42801' OR SQLSTATE '42804'

THEN

PERFORM logs.create_log_tables(TG_RELID::regclass);

Если была изменена структура таблицы, или же по какой-то причине таблица с логами не была создана, то она создается заново и выполняется попытка записать в нее лог. Во всех остальных случаях при ошибке процедура логгирования игнорируется.

Код функции, подключающей логгирование

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;

Алгоритм подключения логгирования достаточно прост. Для начала на основе имени логгируемой таблицы создается ее клон (если таблица с таким именем уже существовала, старая таблица переименовывается), затем в этот клон добавляются необходимые служебные поля, и на логгируемую таблицу подключается триггер.

Достоинства данного подхода:

• Не нужно вносить изменения в существующие функции.
• Запросы на выборку никак не пострадают по производительности.
• При изменениях в структуре логгируемой таблицы таблица с логами будет автоматически пересоздана.
• Логи можно быстро очистить, удалив старые таблицы.