Расширение pg_proaudit включено в состав Postgres Pro Standard как стандартное расширение. Чтобы задействовать pg_proaudit, выполните следующие действия:

Чтобы корректно удалить pg_proaudit, выполните следующие шаги:

Для настройки регистрации событий в pg_proaudit предусмотрен SQL-интерфейс, состоящий из различных функций, а также представление pg_proaudit_settings.

pg_proaudit_set_rule(db_name text, event_type text, object_type text, object_name text, role_name text, comment text)

Создаёт правило регистрации событий с заданными параметрами. Регистрация начинается сразу после завершения функции pg_proaudit_set_rule(), но файл pg_proaudit.conf не изменяется. Для сохранения изменений в файле pg_proaudit.conf вызовите функцию pg_proaudit_save().

Аргументы:

pg_proaudit_remove_rule(db_name text, event_type text, object_type text, object_name text, role_name text)

Удаляет конкретное правило регистрации событий с заданными параметрами. Для сохранения изменений в файле конфигурации pg_proaudit.conf вызовите функцию pg_proaudit_save().

Аргументы:

pg_proaudit_show()

Возвращает список регистрируемых событий в виде таблицы. Эта функция применяется в представлении pg_proaudit_settings.

pg_proaudit_reload()

Считывает конфигурацию регистрации из файла pg_proaudit.conf. Вы должны вызывать эту функцию, если файл pg_proaudit.conf изменялся непосредственно средствами операционной системы.

pg_proaudit_reset()

Удаляет все правила регистрации событий. Для сохранения изменённой конфигурации в файле pg_proaudit.conf вызовите функцию pg_proaudit_save().

pg_proaudit_save()

Сохраняет правила регистрации событий из памяти в файле pg_proaudit.conf. Файл pg_proaudit.conf размещается в каталоге данных кластера (PGDATA). Изменить расположение файла pg_proaudit.conf нельзя.

CREATE TABLE "TAbLe''123"();
SELECT pg_proaudit_set_rule(current_database(), null, null, 'public.TabLe''''123', null, 'tst cmnt');

CREATE TABLE "taBlE""123"();
SELECT pg_proaudit_set_rule(null, null, null, 'public.taBlE"123', null);

CREATE SCHEMA "(BIG!) Schema";
CREATE TABLE "(BIG!) Schema"."My Tab.lE"();
SELECT pg_proaudit_set_rule(null, null, null, '(BIG!) Schema.My Tab.lE', null);

CREATE ROLE "Some '@#$%' person" WITH LOGIN;
SELECT pg_proaudit_set_rule(null, 'authenticate', null, null, 'Some ''@#$%'' person');

CREATE ROLE "Some '@#$%' person" WITH SUPERUSER;
\c - "Some '@#$%' person"
SELECT pg_proaudit_set_rule(null, 'disconnect', null, null, current_user);

CREATE DATABASE " D B 1";
SELECT pg_proaudit_set_rule(' D B 1', 'authenticate', null, null, null);

CREATE DATABASE " D b 2";
\c " D b 2"
CREATE EXTENSION pg_proaudit;
SELECT pg_proaudit_set_rule(current_database(), 'disconnect', null, null, null);

В этом представлении показываются текущие правила pg_proaudit, даже если они ещё не были сохранены в файл pg_proaudit.conf. Представление pg_proaudit_settings образуют следующие столбцы:

Можно настроить расширение pg_proaudit для регистрации событий безопасности определённых классов или конкретных событий, указав соответствующее значение в аргументе event_type функции pg_proaudit_set_rule().

Поддерживаются следующие классы событий безопасности:

Поддерживаются следующие события безопасности:

Расширение pg_proaudit предоставляет несколько параметров конфигурации для управления файлами журналов событий безопасности. Эти параметры можно задать непосредственно в файле конфигурации postgresql.conf или посредством команды ALTER SYSTEM. Чтобы изменения вступили в силу, вызовите функцию pg_reload_conf() или перезапустите сервер баз данных. Для дополнительной настройки можно использовать параметры конфигурации syslog_ident и syslog_facility.

Файлы журналов событий безопасности представляют собой текстовые файлы, которые можно просмотреть средствами операционной системы. Чтобы прочитать файлы журналов на уровне SQL, вы можете применить расширение file_fdw — обёртку сторонних данных для обращения к файлам на сервере. Для этого выполните следующие действия:

Убедитесь в том, что параметр pg_proaudit.log_destination содержит значение csvlog, включающее запись событий безопасности в файлы CSV.

В качестве примера давайте настроим регистрацию следующих событий:

Все события должны записываться в файлы в формате CSV и храниться неделю. Для обращения к журналу событий необходимо организовать доступ на уровне SQL. Для решения этой задачи выполните следующее:

Воспользовавшись psql, убедитесь, что предварительная подготовка расширения pg_proaudit в базе данных postgres выполнена:

SHOW shared_preload_libraries;
 shared_preload_libraries
--------------------------
 pg_proaudit

\dx pg_proaudit
                   List of installed extensions
    Name     | Version | Schema |           Description
-------------+---------+--------+---------------------------------
 pg_proaudit | 2.0     | public | provides auditing functionality

Добавьте следующие строки в файл конфигурации postgresql.conf:

pg_proaudit.log_destination = 'csvlog'
pg_proaudit.log_directory = 'audit'
pg_proaudit.log_filename = 'audit-%u.csv'
pg_proaudit.log_rotation_age = 1440
pg_proaudit.log_rotation_size = 0
pg_proaudit.log_truncate_on_rotation = on
pg_proaudit.log_command_text = on

Чтобы изменения вступили в силу, выполните:

SELECT pg_reload_conf();

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

SHOW pg_proaudit.log_destination;
SHOW pg_proaudit.log_directory;
SHOW pg_proaudit.log_filename;
SHOW pg_proaudit.log_rotation_age;
SHOW pg_proaudit.log_rotation_size;
SHOW pg_proaudit.log_truncate_on_rotation;
SHOW pg_proaudit.log_command_text;

Предположим, что ваша переменная окружения PGDATA указывает на каталог данных кластера. Так как в pg_proaudit.log_directory задан относительный путь, файлы журналов будут находиться в каталоге $PGDATA/audit. Создадим пустые файлы для семи дней недели и сделаем их доступными только для владельца:

touch $PGDATA/audit/audit-1.csv
touch $PGDATA/audit/audit-2.csv
touch $PGDATA/audit/audit-3.csv
touch $PGDATA/audit/audit-4.csv
touch $PGDATA/audit/audit-5.csv
touch $PGDATA/audit/audit-6.csv
touch $PGDATA/audit/audit-7.csv
chmod 600 $PGDATA/audit/audit-*.csv

Создайте таблицу для чтения записей журнала:

CREATE TABLE pg_proaudit_log (
  log_time timestamp(3) with time zone,
  current_usr_name text,
  database_name text,
  session_pid text,
  error_severity text,
  session_line_num bigint,
  session_line_subcommand_num bigint,
  event_type text,
  object_type text,
  object_name text,
  status text,
  error_message text,
  query_text text,
  query_args text,
  session_usr_name text
);

Установите расширение file_fdw и создайте сторонний сервер:

CREATE EXTENSION file_fdw;
CREATE SERVER pg_proauditlog FOREIGN DATA WRAPPER file_fdw;

Теперь создадим для таблицы pg_proaudit_log семь дочерних сторонних таблиц, для каждого дня недели:

CREATE FOREIGN TABLE pg_proaudit_log_1 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-1.csv',  FORMAT 'csv');
CREATE FOREIGN TABLE pg_proaudit_log_2 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-2.csv',  FORMAT 'csv');
CREATE FOREIGN TABLE pg_proaudit_log_3 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-3.csv',  FORMAT 'csv');
CREATE FOREIGN TABLE pg_proaudit_log_4 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-4.csv',  FORMAT 'csv');
CREATE FOREIGN TABLE pg_proaudit_log_5 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-5.csv',  FORMAT 'csv');
CREATE FOREIGN TABLE pg_proaudit_log_6 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-6.csv',  FORMAT 'csv');
CREATE FOREIGN TABLE pg_proaudit_log_7 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-7.csv',  FORMAT 'csv');

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

SELECT pg_proaudit_set_rule (current_database(), 'AUTHENTICATE', null, null, null, 'Any authentication in the current DB');
SELECT pg_proaudit_set_rule (current_database(), 'DISCONNECT', null, null, null, 'Any disconnect from the current DB');
SELECT pg_proaudit_set_rule (current_database(), 'ALL', 'TABLE', null, null, 'Any operations with any table in the current DB');
SELECT pg_proaudit_set_rule (current_database(), 'ALL', null, null, 'postgres', 'Any operation by "postgres" user in the current DB');

Создайте таблицу app_table и включите регистрацию всех операций с этой таблицей:

CREATE TABLE app_table (id int, name text);
SELECT pg_proaudit_set_rule (current_database(), 'ALL', null, 'public.app_table', null);

Проверьте, работает ли регистрация событий должным образом:

SELECT * FROM pg_proaudit_settings;
 db_name  |  event_type  | object_type |   object_name    | role_name |                      comment
----------+--------------+-------------+------------------+-----------+----------------------------------------------------
 postgres | authenticate | ALL         |                  |           | Any authentication in the current DB
 postgres | disconnect   | ALL         |                  |           | Any disconnect from the current DB
 postgres | ALL          | table       |                  |           | Any operations with any table in the current DB
 postgres | ALL          | ALL         |                  | postgres  | Any operation by "postgres" user in the current DB
 postgres | ALL          | ALL         | public.app_table |           |
(5 rows)

Сохраните эти правила регистрации событий в файле pg_proaudit.conf, чтобы они не были потеряны при перезапуске сервера:

SELECT pg_proaudit_save();

Выполним несколько запросов, обращающихся к таблице app_table:

INSERT INTO app_table VALUES (1, 'first');
SELECT * FROM app_table;

Проверьте записи в журнале, относящиеся к таблице app_table:

SELECT to_char(log_time, 'DD.MM.YY HH24:MI:SS') AS when, current_usr_name,
      session_pid, event_type, query_text, session_usr_name
FROM pg_proaudit_log
WHERE object_name = 'public.app_table';
       when        | current_usr_name | session_pid |  event_type  |                 query_text                  | session_usr_name
-------------------+------------------+-------------+--------------+---------------------------------------------+------------------
 27.09.23 12:44:27 | postgres         | 2010        | CREATE TABLE | CREATE TABLE app_table (id int, name text); | postgres
 27.09.23 12:45:55 | postgres         | 2010        | INSERT       | INSERT INTO app_table VALUES (1, 'first');  | postgres
 27.09.23 12:46:00 | postgres         | 2010        | SELECT       | SELECT * FROM app_table;                    | postgres
(3 rows)

Мы настроили еженедельную ротацию файлов журналов с переключением файлов журналов каждый день. Это означает, что запросы к таблице pg_proaudit_log будут возвращать только события, произошедшие за последнюю неделю. Более старые события будут автоматически удаляться при ротации файлов. Для дополнительного ограничения доступа к определённым записям журнала вы можете создать отдельные представления на базе таблицы pg_proaudit_log и разрешить чтение этих представлений, используя встроенные механизмы управления доступом Postgres Pro.