Рубрики
Без рубрики

Ведение журнала аудита PostgreSQL с использованием триггеров

Узнайте, как реализовать механизм ведения журнала аудита с использованием триггеров PostgreSQL и хранить старые и новые состояния строк в типах столбцов JSON.

Автор оригинала: Vlad Mihalcea.

Вступление

В этой статье мы рассмотрим, как мы можем реализовать механизм ведения журнала аудита с использованием триггеров базы данных PostgreSQL для хранения записей CDC (Сбора данных об изменениях).

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

Таблицы базы данных

Давайте предположим, что мы разрабатываем библиотечное приложение, которое использует следующие две таблицы:

В таблице книга мы будем хранить все книги, предлагаемые нашей библиотекой, а в таблице book_audit_log будут храниться события CDC (Сбор данных об изменении) , которые происходили всякий раз, когда в таблице книга выполнялась инструкция INSERT, UPDATE или DELETE DML.

Таблица book_audit_log создается следующим образом:

CREATE TABLE IF NOT EXISTS book_audit_log (
    book_id bigint NOT NULL, 
    old_row_data jsonb,
    new_row_data jsonb,
    dml_type dml_type NOT NULL,
    dml_timestamp timestamp NOT NULL,
    dml_created_by varchar(255) NOT NULL,
    PRIMARY KEY (book_id, dml_type, dml_timestamp)
) 

В столбце book_id хранится идентификатор связанной записи book таблицы, которая была вставлена, обновлена или удалена текущей выполняемой инструкцией DML.

old_row_data – это столбец JSONB, который фиксирует состояние строки book перед выполнением текущей инструкции INSERT, UPDATE или DELETE.

new_row_data – это столбец JSONB, который будет фиксировать состояние строки book после выполнения текущей инструкции INSERT, UPDATE или DELETE.

В столбце dml_type хранится тип текущего выполняемого оператора DML (например, ВСТАВКА, ОБНОВЛЕНИЕ и УДАЛЕНИЕ). Тип dml_type – это тип перечисления PostgreSQL, который был создан следующим образом:

CREATE TYPE dml_type AS ENUM ('INSERT', 'UPDATE', 'DELETE')

В столбце dml_timestamp хранится текущая метка времени.

В столбце dml_created_by хранится пользователь приложения, создавший текущую инструкцию INSERT, UPDATE или DELETE DML.

Первичный ключ book_audit_log является составным из book_id , dmi_type и dmi_timestamp , поскольку запись book может иметь несколько связанных book_audit_log записей.

Триггеры ведения журнала аудита PostgreSQL

Чтобы записать инструкции INSERT, UPDATE и DELETE DML в таблице book , нам нужно создать функцию триггера, которая выглядит следующим образом:

CREATE OR REPLACE FUNCTION book_audit_trigger_func()
RETURNS trigger AS $body$
BEGIN
   if (TG_OP = 'INSERT') then
       INSERT INTO book_audit_log (
           book_id,
           old_row_data,
           new_row_data,
           dml_type,
           dml_timestamp,
           dml_created_by
       )
       VALUES(
           NEW.id,
           null,
           to_jsonb(NEW),
           'INSERT',
           CURRENT_TIMESTAMP,
           current_setting('var.logged_user')
       );
            
       RETURN NEW;
   elsif (TG_OP = 'UPDATE') then
       INSERT INTO book_audit_log (
           book_id,
           old_row_data,
           new_row_data,
           dml_type,
           dml_timestamp,
           dml_created_by
       )
       VALUES(
           NEW.id,
           to_jsonb(OLD),
           to_jsonb(NEW),
           'UPDATE',
           CURRENT_TIMESTAMP,
           current_setting('var.logged_user')
       );
            
       RETURN NEW;
   elsif (TG_OP = 'DELETE') then
       INSERT INTO book_audit_log (
           book_id,
           old_row_data,
           new_row_data,
           dml_type,
           dml_timestamp,
           dml_created_by
       )
       VALUES(
           OLD.id,
           to_jsonb(OLD),
           null,
           'DELETE',
           CURRENT_TIMESTAMP,
           current_setting('var.logged_user')
       );
       
       RETURN OLD;
   end if;
    
END;
$body$
LANGUAGE plpgsql

Чтобы функция book_audit_trigger_func выполнялась после вставки, обновления или удаления записи книги таблицы, мы должны определить следующий триггер:

CREATE TRIGGER book_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON book
FOR EACH ROW EXECUTE FUNCTION book_audit_trigger_func()

Функцию book_audit_trigger_func можно объяснить следующим образом:

  • переменная TG_OP определяет тип текущего выполняемого оператора DML.
  • ключевое слово NEW также является специальной переменной, которая сохраняет состояние текущей изменяющейся записи после выполнения текущего оператора DML.
  • ключевое слово OLD также является специальной переменной, которая сохраняет состояние текущей изменяющейся записи до выполнения текущего оператора DML.
  • функция to_jsonb PostgreSQL позволяет нам преобразовать строку таблицы в объект JSONB, который будет сохранен в столбцах таблицы old_row_data или new_row_data .
  • значение dml_timestamp равно CURRENT_TIMESTAMP
  • в столбце dml_created_by задано значение переменной сеанса var.logged_user PostgreSQL, которая ранее была задана приложением для текущего зарегистрированного пользователя, например:
Session session = entityManager.unwrap(Session.class);

Dialect dialect = session.getSessionFactory()
    .unwrap(SessionFactoryImplementor.class)
    .getJdbcServices()
    .getDialect();

session.doWork(connection -> {
    update(
        connection,
        String.format(
            "SET LOCAL var.logged_user = '%s'", 
            ReflectionUtils.invokeMethod(
                dialect,
                "escapeLiteral",
                LoggedUser.get()
            )
        )
    );
});

Обратите внимание, что мы использовали УСТАНОВИТЬ ЛОКАЛЬНЫЙ , так как мы хотим, чтобы переменная была удалена после фиксации или отката текущей транзакции. Это особенно полезно при использовании пула соединений .

Время тестирования

При выполнении инструкции INSERT в таблице книга :

INSERT INTO book (
    id,
    author, 
    price_in_cents, 
    publisher, 
    title
) 
VALUES (
    1,
    'Vlad Mihalcea', 
    3990, 
    'Amazon', 
    'High-Performance Java Persistence 1st edition'
)

Мы видим, что в book_audit_log вставлена запись, которая фиксирует инструкцию INSERT, только что выполненную в таблице book :

| book_id | old_row_data | new_row_data                                                                                                                                  | dml_type | dml_timestamp              | dml_created_by |
|---------|--------------|-----------------------------------------------------------------------------------------------------------------------------------------------|----------|----------------------------|----------------|
| 1       |              | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT   | 2020-08-25 13:19:57.073026 | Vlad Mihalcea  |

При обновлении строки книга таблица:

UPDATE book 
SET price_in_cents = 4499 
WHERE id = 1

Мы видим, что новая запись будет добавлена в book_audit_log с помощью book_audit_trigger_ |/:

| book_id | old_row_data                                                                                                                                  | new_row_data                                                                                                                                  | dml_type | dml_timestamp              | dml_created_by |
|---------|-----------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------|----------|----------------------------|----------------|
| 1       |                                                                                                                                               | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT   | 2020-08-25 13:19:57.073026 | Vlad Mihalcea  |
| 1       | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE   | 2020-08-25 13:21:15.006365 | Vlad Mihalcea  |

При удалении строки книга таблица:

DELETE FROM book 
WHERE id = 1

Новая запись добавляется в book_audit_log с помощью book_audit_trigger_ |/:

| book_id | old_row_data                                                                                                                                  | new_row_data                                                                                                                                  | dml_type | dml_timestamp              | dml_created_by |
|---------|-----------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------|----------|----------------------------|----------------|
| 1       |                                                                                                                                               | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT   | 2020-08-25 13:19:57.073026 | Vlad Mihalcea  |
| 1       | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE   | 2020-08-25 13:21:15.006365 | Vlad Mihalcea  |
| 1       | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} |                                                                                                                                               | DELETE   | 2020-08-25 13:21:58.499881 | Vlad Mihalcea  |

Потрясающе, правда?

Вывод

Существует множество способов реализации механизма ведения журнала аудита. Если вы используете Hibernate, очень простое решение-использовать энверы Hibernate .

Если вы не используете режим гибернации или хотите фиксировать события CDC независимо от того, как генерируются инструкции DML, то решение для запуска базы данных, подобное тому, которое представлено в этой статье, довольно просто реализовать. Сохранение старых и новых состояний строк в столбцах JSON-очень хорошая идея, поскольку она позволяет нам повторно использовать одну и ту же функцию, даже если структура исходной таблицы изменится.

Другой вариант-использовать специальную платформу CDC, такую как Debezium , которая извлекает события CDC из PostgreSQL WAL (Журнал предварительной записи). Это решение может быть очень эффективным, так как оно работает асинхронно, поэтому оно не влияет на текущие выполняемые транзакции OLTP. Однако настройка Debezium и запуск его в производство будут намного сложнее, поскольку Debezium также требует Apache Kafka и ZooKeeper.