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

В этой статье мы рассмотрим, как включить аудит/логирование действий пользователя в PostgreSQL. Для осуществления аудита/логирования я буду использовать встроенную функцию логирования в PostgreSQL (log_statement). Также можно воспользоваться расширением pgAudit, который я рассматривал в этой статье.

Что именно логировать?

Для начала нужно определится что именно записывать в лог файлы, т.е. какие выполняемые команды необходимо включить в аудит.

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

  • none - ничего не логировать (выключено)
  • ddl - операции CREATE, ALTER и DROP
  • mod - всё что в ddl плюс операторы изменения данных, такие как INSERT, UPDATE, DELETE, TRUNCATE, COPY FROM, PREPARE, EXECUTE, EXPLAIN ANALYZE
  • all - все команды

Важно отметить что все записи аудита будут повторять саму команду точь-в-точь, т.е. если log_statement = all и вы выполняете команду установки пароля для пользователя как простой текст, то в логах вы увидите этот пароль. Подробнее я рассматривать такую ситуацию тут.

Подготовка

Перед тем ка приступить к включению аудита подготовим немного наш сервер к этому.

  1. Создадим тестовую бд 
    sudo -u postgres psql postgres

    CREATE DATABASE exampledb;
\c exampledb
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INTEGER,
email VARCHAR(150)
);
  2. Создадим тестового пользователя 
    CREATE  USER test with password '11111111';
  3. Выдадим права пользователю 
    \c exampledb
GRANT CONNECT ON DATABASE exampledb TO test ;
GRANT  SELECT, INSERT, UPDATE, DELETE  ON  ALL TABLES IN SCHEMA public TO test;
GRANT USAGE, SELECT  ON  ALL SEQUENCES IN SCHEMA public TO test;
grant all ON database exampledb to test;
GRANT USAGE ON SCHEMA public TO test ;

Включаем аудит действий

Аудит можно включить, используя несколько методов:

  • Глобально для всех пользователей в файле postgresql.conf
  • Для конкретного пользователя используя команду ALTER ROLE user_name SET log_statement TO 'all';
  • Для конкретной бд, например ALTER DATABASE my_database SET log_statement = 'all';
  • Для конкретной сессии, например SET log_statement = 'all';

Для того, чтобы настроить глобально выполняем следующее:

sudo vim /etc/postgresql/16/main/postgresql.conf

log_directory =  'pg_log'  -- Где хранить логи
log_filename =  'postgresql-%Y-%m-%d_%H%M%S.log' -- Имя файла с логами
log_statement = mod

sudo systemctl restart postgresql

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

sudo -u postgres psql postgres

alter role test set log_statement to 'all'

Проверяем работу аудита действий

  1. Подключаемся к базе под пользователем test 
    psql -U test -d exampledb
  2. Выполняем команду INSERT 
    INSERT INTO users (name, age, email) VALUES ('John Doe', 30, 'john.doe@example.com');
INSERT INTO users (name, age, email) VALUES ('John Doe', 30, 'john.doe@example.com');
  3. В логах проверяем записи о добавлении этих двух строк 
    sudo vim /var/log/postgresql/postgresql-16-main.log

    2024-05-20 15:31:55.826 +05 [52823] test@exampledb LOG:  statement: INSERT INTO users (name, age, email) VALUES ('John Doe', 30, 'john.doe@example.com');
2024-05-20 15:31:57.369 +05 [52823] test@exampledb LOG:  statement: INSERT INTO users (name, age, email) VALUES ('John Doe', 30, 'john.doe@example.com');