Статья:

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

Конференция: XVI Студенческая международная научно-практическая конференция «Технические и математические науки. Студенческий научный форум»

Секция: Технические науки

Выходные данные
Козлов Д.А., Карнаухов Д.Д. Реализация аудита изменения данных в СУБД PostgreSQL // Технические и математические науки. Студенческий научный форум: электр. сб. ст. по мат. XVI междунар. студ. науч.-практ. конф. № 5(16). URL: https://nauchforum.ru/archive/SNF_tech/5(16).pdf (дата обращения: 24.05.2024)
Лауреаты определены. Конференция завершена
Эта статья набрала 29 голосов
Мне нравится
Дипломы
лауреатов
Сертификаты
участников
Дипломы
лауреатов
Сертификаты
участников
на печатьскачать .pdfподелиться

Реализация аудита изменения данных в СУБД 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;

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

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

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

 

Список литературы:
1. Новиков Б. А. Основы технологий баз данных: учеб. пособие / Б. А. Новиков, Е. А. Горшкова; под ред. Е. В. Рогова. — М.: ДМК Пресс, 2019. — 240 с.