PostgreSQL: Фіксуйте кожну зміну у вашій базі даних - javascript.org.ua

Втрата даних може мати катастрофічні наслідки для будь-якої компанії. Хоча багато хто покладається на часті резервні копії та реплікацію в реальному часі, ці заходи не гарантують, що дані не будуть переписані та втрачено назавжди. Навіть між двома близькими інтервалами резервного копіювання інформація може бути записана та видалена, а реплікація просто зберігає останню версію бази даних — втрачаючи історію змін між цими інтервалами.

TL;DR: Дізнайтесь, як впровадити надійне аудиту бази даних у PostgreSQL для відстеження кожної зміни, ідентифікації користувачів, які виконують операції, за допомогою SET LOCAL і current_setting, а також додавання власної поведінки до транзакцій для ведення журналу, моніторингу чи перевірки. Повне рішення можна знайти на цьому репозиторії GitHub.

Журнал аудиту

Аудит — це техніка фіксації будь-яких змін або видалень у таблиці бази даних. Завдяки цьому можна відновити стан будь-якого об'єкта в базі даних, ефективно створюючи історію кожної модифікації. Існує кілька способів впровадити аудит, найпростіший з яких — на рівні додатку. Тут, до або після будь-якої операції запису чи видалення, додаток зберігає поточний стан даних в окремій таблиці, часто званій "audits" або "versions".

Наприклад, у екосистемі Ruby популярні гемми, такі як paper_trail та audited, вирішують це, відстежуючи кожну зміну/видалення та створюючи версії кожного запису. Проте є деякі обмеження.

Обмеження аудиту на рівні додатку

Хоча аудит на рівні додатку є хорошим початком, у нього є кілька недоліків:

Помилки з боку людини: Якщо реалізовано вручну, розробники можуть забути записувати зміни.
Обмеження ORM: Об'єктно-реляційні мапери (ORM) можуть пропускати зміни. Наприклад, у ActiveRecord (що використовується в Ruby on Rails) масові оновлення (наприклад, update_all) не викликають звичайні механізми зворотних викликів, які викликаються при оновленнях одного запису.
Прямий доступ до SQL: Зміни, зроблені за допомогою коду безпосередньо через SQL, повністю оминають рівень ORM, що означає, що жодні журнали аудиту не записуються.
Контроль доступу до бази даних: Якщо хтось має прямий доступ до бази даних (наприклад, через psql console), вони можуть вносити незареєстровані зміни.

Щоб вирішити ці проблеми, найкраще здійснювати аудит безпосередньо на рівні бази даних.

Найкраще рішення: аудит на рівні бази даних

Використовуючи вбудовані функціональності PostgreSQL, можна налаштувати базу даних для самостійного ведення аудиту, що зробить цей процес надійним. Налаштування включає створення тригер-функції, яка записує кожну операцію INSERT, UPDATE або DELETE в окрему таблицю. Ось як це можна реалізувати.

Крок 1: Створіть схему та таблицю для аудиту

Таблиця аудиту може зростати в розмірах з часом, що ускладнює її резервне копіювання. Тому доцільно визначити її в окремій схемі в межах тієї ж бази даних. Таким чином, ми можемо вибрати, чи резервувати всю базу даних разом з аудитом, чи резервувати дані та аудит окремо.

-- Створити схему з назвою "audit"  
CREATE SCHEMA audit;  
REVOKE CREATE ON SCHEMA audit FROM public;  

CREATE TABLE audit.logged_actions (  
 schema_name TEXT NOT NULL,  
 table_name TEXT NOT NULL,  
 record_id INTEGER NOT NULL,  
 user_name TEXT,  
 action_tstamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,  
 action TEXT NOT NULL CHECK (action IN ('I', 'D', 'U')),  
 original_data TEXT,  
 new_data TEXT,  
 query TEXT  
) WITH (fillfactor=100);  

REVOKE ALL ON audit.logged_actions FROM public;  
GRANT SELECT ON audit.logged_actions TO public;

Ця таблиця logged_actions зберігатиме деталі кожної зміни. Колонка action записуватиме тип операції (I — вставка, D — видалення, U — оновлення).
Ви можете додавати індекси на часто запитувані стовпці для оптимізації продуктивності:

CREATE INDEX logged_actions_schema_table_idx  
ON audit.logged_actions(((schema_name || '.' || table_name)::TEXT));  

CREATE INDEX logged_actions_action_tstamp_idx   
ON audit.logged_actions(action_tstamp);  

CREATE INDEX logged_actions_action_idx   
ON audit.logged_actions(action);

Крок 2: Опис функції тригера

Наступна функція тригера вставляє запис у таблицю audit.logged_actions кожного разу, коли рядок у цільовій таблиці змінюється.

CREATE OR REPLACE FUNCTION audit.log_current_action() RETURNS trigger AS $body$  
DECLARE  
 v_old_data TEXT;  
 v_new_data TEXT;  
BEGIN  
 IF (TG_OP = 'UPDATE') THEN  
 v_old_data := ROW(OLD.*);  
 v_new_data := ROW(NEW.*);  
 INSERT INTO audit.logged_actions   
 (schema_name, table_name, record_id, user_name, action, original_data, new_data, query)  
 VALUES   
 (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, NEW.id, session_user::TEXT, substring(TG_OP,1,1), v_old_data, v_new_data, current_query());  
 RETURN NEW;  
 ELSIF (TG_OP = 'DELETE') THEN  
 v_old_data := ROW(OLD.*);  
 INSERT INTO audit.logged_actions   
 (schema_name, table_name, record_id, user_name, action, original_data, query)  
 VALUES   
 (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, OLD.id, session_user::TEXT, substring(TG_OP,1,1), v_old_data, current_query());  
 RETURN OLD;  
 ELSIF (TG_OP = 'INSERT') THEN  
 v_new_data := ROW(NEW.*);  
 INSERT INTO audit.logged_actions   
 (schema_name, table_name, record_id, user_name, action, new_data, query)  
 VALUES   
 (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, NEW.id, session_user::TEXT, substring(TG_OP,1,1), v_new_data, current_query());  
 RETURN NEW;  
 ELSE  
 RAISE WARNING '[AUDIT.LOG_CURRENT_ACTION] - Сталася інша операція: %, в %', TG_OP, now();  
 RETURN NULL;  
 END IF;  
EXCEPTION  
 WHEN data_exception THEN  
 RAISE WARNING '[AUDIT.LOG_CURRENT_ACTION] - Помилка даних';  
 RETURN NULL;  
 WHEN unique_violation THEN  
 RAISE WARNING '[AUDIT.LOG_CURRENT_ACTION] - Порушення унікальності';  
 RETURN NULL;  
 WHEN others THEN  
 RAISE WARNING '[AUDIT.LOG_CURRENT_ACTION] - Інша помилка';  
 RETURN NULL;  
END;  
$body$  
LANGUAGE plpgsql  
SECURITY DEFINER  
SET search_path = pg_catalog, audit;

Крок 3: Прив'язка тригерів до таблиць

Додайте цю функцію тригера до кожної таблиці, яку ви хочете аудитувати:

CREATE TRIGGER tablename_audit  
AFTER INSERT OR UPDATE OR DELETE ON tablename  
FOR EACH ROW EXECUTE FUNCTION audit.log_current_action();

В Ruby on Rails ви можете автоматизувати цей процес для кожної таблиці в вашій базі даних за допомогою наступного фрагмента:

(ActiveRecord::Base.connection.tables - ["schema_migrations", "ar_internal_metadata"]).each do |table_name|  
 ActiveRecord::Base.connection.execute(<<-SQL)  
 CREATE TRIGGER #{table_name}_audit  
 AFTER INSERT OR UPDATE OR DELETE ON #{table_name}  
 FOR EACH ROW EXECUTE FUNCTION audit.log_current_action();  
 SQL  
end

Автоматизація тригерів при створенні таблиць

Щоб переконатися, що тригери не будуть забуті для новостворених таблиць, розширте методи create_table та drop_table в ActiveRecord:

module TableWithTrigger  
 def create_table(table_name, **options)  
 super(table_name, **options) do |t|  
 yield(t) if block_given?  
 end  
 add_trigger(table_name)  
 end  

 def drop_table(table_name, **options)  
 remove_trigger(table_name)  
 super(table_name, **options)  
 end  

 private  

 def add_trigger(table_name)  
 execute <<-SQL  
 CREATE TRIGGER #{table_name}_trigger  
 AFTER INSERT OR UPDATE OR DELETE ON #{table_name}  
 FOR EACH ROW EXECUTE FUNCTION audit.log_current_action();  
 SQL  
 end  

 def remove_trigger(table_name)  
 execute <<-SQL  
 DROP TRIGGER IF EXISTS #{table_name}_trigger;  
 SQL  
 end  
end  

ActiveSupport.on_load(:active_record) do  
 ActiveRecord::ConnectionAdapters::SchemaStatements.prepend(TableWithTrigger)  
end

## Whodunit: Відстеження відповідального користувача за допомогою `SET LOCAL` і `current_setting`

Одним із викликів аудиту на рівні бази даних є відстеження **того, який користувач** виконав дію.
## На відміну від аудиту на рівні додатка, де ідентичність користувача доступна безпосередньо, аудит на рівні бази даних часто використовує `session_user`, що відображає користувача бази даних, а не фактичного користувача додатка.

`SET LOCAL` і `current_setting` у PostgreSQL надають спосіб відстежити користувача додатка, налаштовуючи змінну, специфічну для сесії, під час підключення до бази даних. Це можна безперешкодно реалізувати в таких додатках, як Rails.

## Використання `SET LOCAL` для відстеження користувачів додатка

PostgreSQL дозволяє встановлювати користувацькі змінні сесії в межах транзакції за допомогою `SET LOCAL`. Ці змінні доступні лише в межах поточної транзакції, що гарантує, що вони не збережуться між непов’язаними операціями бази даних.

**Налаштування змінної**

- **Контекст додатка**: У додатку Rails змінну можна встановити в `before_action` колбеку в контролерах для відстеження автентифікованого користувача.
- **Контекст бази даних**: Користувацька змінна доступна в тригерах аудиту за допомогою `current_setting()`.

Приклад робочого процесу в Rails

1. **Встановіть змінну користувача в Around Action**: Додайте `around_action` в ваш `ApplicationController`, щоб налаштувати [Current](https://api.rubyonrails.org/classes/ActiveSupport/CurrentAttributes.html) користувача для кожного запиту.

class ApplicationController < ActionController::Base
allow_browser versions: :modern

aroundaction :setuser

private

def setuser
Current.set(user: currentuser) do
yield
end
end
end
```

2. Встановіть Current User id в внутрішні механізми ActiveRecord

module CustomTransactionBehavior  
 def begin_db_transaction  
 super  
 internal_execute("SET LOCAL app.current_user_id to '#{Current.user&.id || 'Guest'}';", "TRANSACTION", allow_retry: true, materialize_transactions: false)  
 end  
end  

ActiveSupport.on_load(:active_record) do  
 ActiveRecord::ConnectionAdapters::PostgreSQL::DatabaseStatements.prepend(CustomTransactionBehavior)  
end

Тут команда SET LOCAL встановлює користувацьку змінну app.current_user на період поточної транзакції.

3. Доступ до змінної в PostgreSQL: Змініть функцію тригера аудиту, щоб читати змінну app.current_user:

CREATE OR REPLACE FUNCTION audit.log_current_action() RETURNS trigger AS $$  
DECLARE  
 user_name TEXT;  
 v_old_data TEXT;  
 v_new_data TEXT;  
BEGIN  
 user_name := current_setting('app.current_user_id', false);  

 IF (TG_OP = 'UPDATE') THEN  
 v_old_data := ROW(OLD.*);  
 v_new_data := ROW(NEW.*);  
 INSERT INTO audit.logged_actions (  
 schema_name, table_name, record_id, user_name, action, original_data, new_data, query  
 ) VALUES (  
 TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.id, user_name, 'U', v_old_data, v_new_data, current_query()  
 );  
 RETURN NEW;  
 ELSIF (TG_OP = 'DELETE') THEN  
 v_old_data := ROW(OLD.*);  
 INSERT INTO audit.logged_actions (  
 schema_name, table_name, record_id, user_name, action, original_data, query  
 ) VALUES (  
 TG_TABLE_SCHEMA, TG_TABLE_NAME, OLD.id, user_name, 'D', v_old_data, current_query()  
 );  
 RETURN OLD;  
 ELSIF (TG_OP = 'INSERT') THEN  
 v_new_data := ROW(NEW.*);  
 INSERT INTO audit.logged_actions (  
 schema_name, table_name, record_id, user_name, action, new_data, query  
 ) VALUES (  
 TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.id, user_name, 'I', v_new_data, current_query()  
 );  
 RETURN NEW;  
 ELSE  
 RAISE WARNING 'Unknown operation: %', TG_OP;  
 RETURN NULL;  
 END IF;  
END;  
$$ LANGUAGE plpgsql SECURITY DEFINER;

Використання SET LOCAL у PostgreSQL разом з ActiveRecord, опублікованим через around_action в контролері на рівні додатка, забезпечує детальне відстеження користувачів додатка в аудиті бази даних, гарантуючи, що кожна транзакція буде пов’язана з фактичним користувачем, який ініціює дію. Це безпечно, оскільки змінна обмежена лише транзакцією, і легко інтегрується в логіку додатка.
Однак цей підхід вимагає послідовної реалізації, щоб уникнути втрати контексту користувача, і може не працювати з рішеннями для пулінгу з'єднань, такими як pgbouncer, які не підтримують налаштування на рівні транзакції.

Другий параметр функції current_setting, який називається missing_ok, визначає поведінку, коли налаштування відсутнє. Щоб зробити його необов'язковим, а не обов'язковим, потрібно встановити missing_ok в значення true.

user_name := current_setting('app.current_user_id', true);

Вплив на продуктивність: аудит і швидкість запису

Додавання аудиту безпосередньо до PostgreSQL впливає на продуктивність запису кількома способами:

Збільшене I/O: Кожна операція запису (INSERT, UPDATE, DELETE) тепер викликає додатковий запис до таблиці audit.logged_actions, що збільшує навантаження на дискові I/O.
Вищий навантаження на ЦП: Функції тригерів виконуються при кожній модифікації, що споживає ресурси ЦП і уповільнює реакцію, особливо в таблицях з високим навантаженням.
Зростання обсягу пам'яті: Аудиторська таблиця зростає з кожною зафіксованою зміною, що вимагає ретельного управління диском та регулярного обслуговування (vacuuming та архівування), щоб уникнути накопичення даних.
Складність транзакцій: Аудит є частиною кожної транзакції. Якщо транзакція скасовується, аудиторські записи також скасовуються, що додає складність і потенційно затримує виконання.

Оптимізація продуктивності

Щоб збалансувати потреби аудиту з продуктивністю:

Застосовувати тригери вибірково тільки до критичних таблиць.
Обмежити фіксацію даних лише необхідними стовпцями.
Архівувати старі записи та регулярно виконувати vacuum для запобігання накопиченню в таблицях.
Розподіляти аудиторську таблицю для покращення продуктивності запитів та полегшення обслуговування.

Підсумовуючи, хоча аудит впливає на швидкість запису, ретельна конфігурація та обслуговування допомагають управляти цими ефектами, зберігаючи ефективність аудиторських записів, не перевантажуючи базу даних.

Альтернативи аудиту на рівні бази даних

Якщо ваш додаток є інтенсивним за використанням даних, можна розглянути альтернативи, щоб зменшити вплив аудиту на рівні бази даних:

Аудит на рівні додатка: Відстежуйте зміни у коді додатка, часто за допомогою інструментів ORM (наприклад, paper_trail у Ruby). Це дозволяє уникнути тригерів у базі даних, але може не зафіксувати зміни, зроблені безпосередньо за допомогою SQL.
Аудит на основі журналу: Використовуйте журнал запису перед виконанням (WAL) PostgreSQL для відстеження змін, уникаючи накладних витрат від тригерів. Це ефективно, але складно для розбору та управління.
Джерело подій (Event Sourcing): Зберігайте кожну зміну як "подію", створюючи вбудований аудит-трейл. Це ідеально для систем з високим пропускним здатністю, але вимагає змін у управлінні даними.
Зовнішні сервіси журналювання: Використовуйте інструменти, такі як стек ELK, для перенесення аудиторських записів, обробляючи великі об'єми даних без впливу на продуктивність запису.

Обирайте в залежності від потреб вашого додатка у продуктивності та вимог до аудиту.

Дякую, що прочитали!

Перекладено з: PostgreSQL: Record Every Change in Your Database