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

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

pg_proaudit_set_object(event_type  text,
                   object_type text)
pg_proaudit_set_object(event_type text,
                   object_oid oid)

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

Аргументы:

pg_proaudit_set_role(event_type text,
                 role_oid   oid)

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

Аргументы:

pg_proaudit_reset_object(event_type text,
                    object_type text)
pg_proaudit_reset_object(event_type text,
                     object_oid oid)

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

Аргументы:

pg_proaudit_reset_role(event_type text,
                   role_oid   oid)

Отключает регистрацию событий, связанных с заданным пользователем СУБД. Для сохранения изменённой конфигурации в файле 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 нельзя.

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

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

pg_proaudit.log_destination (string)

Определяет метод фиксирования событий безопасности. Возможные значения:

Данный параметр может содержать одно или несколько этих значений, разделённых запятыми.

По умолчанию: csvlog

pg_proaudit.log_catalog_access (boolean)

Определяет, будут ли регистрироваться обращения к объектам системного каталога в схеме pg_catalog.

По умолчанию отключено.

pg_proaudit.log_command_text (boolean)

Определяет, будет ли записываться в журнал текст команд SQL, вызывающих события безопасности.

По умолчанию включено.

pg_proaudit.log_directory (string)

Задаёт путь к каталогу для сохранения файлов журналов CSV. Это может быть абсолютный путь либо путь, заданный относительно каталога данных (PGDATA). Этот параметр используется, если параметр pg_proaudit.log_destination содержит значение csvlog.

По умолчанию: pg_proaudit

pg_proaudit.log_filename (string)

Определяет шаблон имён файлов для журналов событий. Этот шаблон может содержать спецпоследовательности с %, перечисленные в описании strftime стандарта Open Group (http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html). Данный параметр используется, если параметр pg_proaudit.log_destination содержит csvlog.

По умолчанию: postgresql-%Y-%m-%d_%H%M%S.log

pg_proaudit.log_rotation_size (integer)

Задаёт максимальный размер файла журнала CSV, в килобайтах. По достижении этого размера pg_proaudit создаёт для записи событий безопасности новый файл. Данный параметр используется, если параметр pg_proaudit.log_destination содержит csvlog. Если он равен 0, новый файл в зависимости от размера текущего создаваться не будет.

По умолчанию: 10 МБ

pg_proaudit.log_rotation_age (integer)

Задаёт максимальное время жизни файла журнала, в минутах. По истечении этого времени pg_proaudit создаёт новый файл для записи событий безопасности. Данный параметр используется, если параметр pg_proaudit.log_destination содержит csvlog. Если он равен 0, создание новых файлов журналов по времени отключается.

По умолчанию: 1 день

pg_proaudit.log_truncate_on_rotation (boolean)

Определяет, должны ли усекаться файлы журналов при переключении записи на уже существующий файл журнала. Со значением off pg_proaudit продолжает запись в конец файла. Данный параметр используется, если параметр pg_proaudit.log_destination содержит значение csvlog.

По умолчанию отключено.

pg_proaudit.syslog_facility (enum)

Задаёт категорию событий для syslog. Этот параметр используется, если pg_proaudit.log_destination содержит syslog. Возможные значения: LOCAL0, LOCAL1, LOCAL2, LOCAL3, LOCAL4, LOCAL5, LOCAL6, LOCAL7.

По умолчанию: LOCAL0

pg_proaudit.syslog_ident (string)

Задаёт имя программы, по которому будут идентифицироваться события pg_proaudit в журналах syslog.

По умолчанию: pg_proaudit

Файлы журналов событий безопасности представляют собой текстовые файлы, которые можно просмотреть средствами операционной системы. Чтобы прочитать файлы журналов на уровне 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 | 1.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,
  role_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
);

Установите расширение 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_object ('CONNECT', null);
SELECT pg_proaudit_set_object ('DISCONNECT', null);
SELECT pg_proaudit_set_object ('ALL', 'table');
SELECT pg_proaudit_set_role ('ALL', 'postgres'::regrole);

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

CREATE TABLE app_table (id int, name text);
SELECT pg_proaudit_set_object ('ALL', 'public.app_table'::regclass);

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

postgres=# SELECT * FROM pg_proaudit_settings;
 db_name | event_type | object_type |  object_name    | role_name 
---------+------------+-------------+-----------------+-----------
postgres | DISCONNECT |             | 0               | 0
postgres | CONNECT    |             | 0               | 0
postgres | ALL        | TABLE       | 0               | 0
postgres | ALL        |             | public.app_table| 0
postgres | ALL        |             | 0               | postgres
(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, role_name, 
       session_pid, event_type, query_text 
FROM   pg_proaudit_log 
where  object_name = 'public.app_table';
-[ RECORD 1 ]--------------------------------------------
when        | 24.11.16 16:31:16
role_name   | postgres
session_pid | 26513
event_type  | CREATE TABLE
query_text  | CREATE TABLE app_table (id int, name text);
-[ RECORD 2 ]--------------------------------------------
when        | 24.11.16 16:31:53
role_name   | postgres
session_pid | 26513
event_type  | INSERT
query_text  | INSERT INTO app_table VALUES (1, 'first');
-[ RECORD 3 ]--------------------------------------------
when        | 24.11.16 16:31:54
role_name   | postgres
session_pid | 26513
event_type  | SELECT
query_text  | SELECT * FROM app_table;

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