Аудит доступу та логування дій користувачів

Уяви, що твоя база даних — це такий клуб із дуже суворими правилами членства. Заходити туди можуть тільки свої, але нам цікаво знати, хто прийшов, коли, скільки часу провів і що вони там робили. Саме для цього й потрібен аудит у PostgreSQL. З його допомогою можна:

1. Відстежувати дії користувачів. Наприклад, хто і коли виконував важливий запит.

2. Виявляти підозрілу активність. Це може бути хтось, хто намагається читати дані, до яких не повинен мати доступ.

3. Дотримуватись вимог законодавства та стандартів. У багатьох індустріях (наприклад, фінансова чи медична) потрібно вести детальний аудит дій користувачів.

4. Розуміти систему зсередини. Логування допомагає розібратися, які запити найчастіше виконуються, де можуть бути вузькі місця і як оптимізувати продуктивність.

Налаштування параметрів логування

PostgreSQL дозволяє налаштувати аудит дій за допомогою параметрів конфігурації, таких як log_statement і log_connections. Давай розглянемо їх детальніше.

1. log_statement: що записуємо?

Параметр log_statement визначає, які SQL-запити будуть записані в логи. Це дуже корисно для розуміння того, що відбувається в системі.

Варіанти значень log_statement:

• none — не записувати нічого (гарний вибір, якщо хочеш жити на межі).
• ddl — записувати тільки команди, що змінюють структуру бази даних (наприклад, CREATE TABLE).
• mod — записувати команди, які змінюють дані (наприклад, INSERT, UPDATE, DELETE).
• all — записувати все підряд.

Приклад налаштування: щоб змінити цей параметр, потрібно оновити файл postgresql.conf:

# Налаштовуємо логування всіх SQL-запитів
log_statement = 'all'

Після цього збережи зміни і перезапусти сервер PostgreSQL:

pg_ctl reload

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

2. log_connections: хто заходить у клуб?

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

Приклад налаштування: Знову ж таки, оновлюємо файл postgresql.conf:

# Логуємо підключення
log_connections = on

# Логуємо відключення
log_disconnections = on

Що це нам дає? Наприклад, ти зможеш побачити в логах, що твій менеджер дві години намагався підключитися до бази з неправильним паролем. Так, о 3-й ночі.

Аналіз логів: що можна знайти?

Після налаштування параметрів log_statement і log_connections PostgreSQL почне писати логи. Ось приклад того, як може виглядати лог-файл при увімкненому log_statement = 'mod':

2023-11-01 12:45:01 UTC [12345] LOG:  connection authorized: user=admin database=university
2023-11-01 12:46:15 UTC [12345] STATEMENT:  INSERT INTO students (name, age) VALUES ('Alice', 22);
2023-11-01 12:47:30 UTC [12345] STATEMENT:  UPDATE students SET age = 23 WHERE name = 'Alice';
2023-11-01 12:48:45 UTC [12345] LOG:  disconnection: session time: 2:45 connection: 1/5

Цікаві моменти:

1. Хто підключився: user=admin database=university — це наш бос.
2. Що робив: вставляв рядок з ім'ям Alice і оновлював її вік.
3. Коли пішов: 12:48:45, після двох з половиною хвилин активності.

Практичні приклади: як використовувати аудит на практиці

Давай розглянемо кілька сценаріїв, де аудит і логування можуть стати в пригоді.

Сценарій 1: відстеження змін даних

Ти підозрюєш, що хтось змінює записи в таблиці students. Ось як можна налаштувати аудит:

1. Встанови log_statement = 'mod' для запису всіх модифікуючих запитів.
2. Аналізуй логи:

cat /var/log/postgresql/postgresql.log | grep "UPDATE students"

Тепер ти зможеш бачити, хто, коли і які зміни зробив.

Сценарій 2: виявлення підозрілих підключень

Якщо в логах з'являються часті підключення з різних IP-адрес, це може бути щось підозріле. Для аналізу використовуй:

1. Логи підключень (log_connections).
2. Фільтрацію за IP-адресами:

cat /var/log/postgresql/postgresql.log | grep "connection authorized"

Сценарій 3: аналіз продуктивності запитів

Хочеш зрозуміти, чому твій сервер гальмує? Простий спосіб — увімкнути log_statement = 'all' на короткий період (наприклад, на годину), зібрати логи і подивитися, де сервер проводить найбільше часу.

Кращі практики

Не логуй усе підряд. Налаштуй log_statement так, щоб записувалися тільки важливі дії — DDL і зміни даних.

Автоматизуй аналіз логів. Використовуй скрипти або інструменти для регулярного читання й аналізу логів, такі як grep, awk або навіть ELK Stack (Elasticsearch, Logstash, Kibana).

Керуй розміром логів. Налаштуй ротацію логів через параметри PostgreSQL або інструменти операційної системи (наприклад, logrotate у Linux).

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