F.40. pg_proaudit — регистрация различных событий, связанных с безопасностью #

Расширение pg_proaudit позволяет регистрировать различные события, связанные с безопасностью.

pg_proaudit работает параллельно со стандартными средствами протоколирования PostgreSQL (сборщиком сообщений) и не зависит от них. Журнал событий безопасности расширения pg_proaudit сохраняется отдельно от журнала работы сервера. При запуске Postgres Pro Standard расширение pg_proaudit запускает специальный фоновый процесс, который записывает в журнал информацию о событиях безопасности.

Правила регистрации событий сохраняются в файле конфигурации pg_proaudit.conf, располагающемся в каталоге данных кластера (PGDATA). Он представляет собой текстовый файл, который можно редактировать непосредственно, используя инструменты операционной системы. Для изменения его содержимого средствами SQL предусмотрены несколько функций pg_proaudit. В представлении pg_proaudit_settings можно посмотреть текущие правила pg_proaudit, даже если они ещё не были сохранены в файл pg_proaudit.conf.

Обратите внимание, что при обновлении Postgres Pro до новой основной версии с помощью утилиты pg_upgrade файл pg_proaudit.conf необходимо скопировать вручную. Как и другие конфигурационные файлы, pg_proaudit.conf не переносится автоматически в каталог данных нового кластера (PGDATA).

Примечание

Правила регистрации событий в устаревшем формате, содержащие идентификаторы объектов (OID) вместо имён объектов, поддерживаются лишь частично и могут обрабатываться неправильно.

Все фиксируемые события разделяются на следующие классы:

  • команды DDL для создания, изменения и удаления объектов СУБД (баз данных, табличных пространств, схем, таблиц, представлений, последовательностей, языков, функций)

  • команды управления доступом к объектам баз данных (GRANT, REVOKE)

  • команды DML для обращения к объектам баз данных (INSERT, UPDATE, DELETE, SELECT, TRUNCATE для таблиц/представлений, EXECUTE для функций)

  • события аутентификации/отключения от баз данных

  • все команды, выполняемые определённым пользователем

События безопасности могут записываться как в централизованное средство журналирования операционной системы (syslog), так и в обычные файлы. Оба варианта могут использоваться одновременно. Все записи pg_proaudit в syslog имеют метку AUDIT, что позволяет однозначно их определять. Файлы журнала событий могут записываться в форматах CEF (общий формат записи событий, Common Event Format) и CSV (значения, разделённые запятыми, Comma Separated Values). За подробной информацией о полях событий, регистрируемых для каждого формата, обратитесь к Подразделу F.40.6.

Помимо этого, изменения DDL постоянных объектов базы данных могут записываться в отдельный файл. Этот файл не участвует в ротации, поэтому в нём сохраняется полная история всех структурных изменений. Это позволяет администраторам и инженерам поддержки быстро определить, кто и когда изменил объект базы данных, не просматривая весь журнал событий безопасности. Заметьте, что операторы DDL из отменённых транзакций в этот журнал не записываются. За подробностями о доступе к журналу изменений DDL обратитесь к Подразделу F.40.8.

Все строковые поля в выводе журнала преобразуются в UTF-8 независимо от кодировки базы данных.

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

Пользователь Postgres Pro с атрибутом SUPERUSER должен давать доступ к расширению pg_proaudit и журналам событий безопасности только пользователям, исполняющим роль администратора информационной безопасности.

F.40.1. Установка расширения pg_proaudit #

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

  1. Добавьте pg_proaudit в переменную shared_preload_libraries в файле postgresql.conf:

    shared_preload_libraries = 'pg_proaudit'
  2. Перезагрузите сервер базы данных для применения изменений. Чтобы убедиться, что библиотека pg_proaudit установлена правильно, выполните следующую команду:

    SHOW shared_preload_libraries;
  3. В каждой базе данных, для которой должны регистрироваться события безопасности, создайте расширение pg_proaudit, выполнив следующий запрос:

    CREATE EXTENSION pg_proaudit;

    Расширение pg_proaudit добавляет несколько функций для управления файлом pg_proaudit.conf, представление pg_proaudit_settings, показывающее текущие правила pg_proaudit, и событийные триггеры.

F.40.2. Удаление расширения pg_proaudit #

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

  1. Удалите расширение pg_proaudit, выполнив следующий запрос:

    DROP EXTENSION pg_proaudit;
  2. Удалите pg_proaudit из переменной shared_preload_libraries в файле postgresql.conf.

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

F.40.3. Функции для настройки записи событий безопасности в журнал #

Для настройки регистрации событий в 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, exclude_role_name text, comment text)

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

Аргументы:

  • db_name — имя базы данных, для которой задаётся правило регистрации событий. Указание NULL или пустой строки означает, что регистрируются события для всех баз данных, в которых создано расширение pg_proaudit. При указании значения current_database() события регистрируются для текущей базы данных.

  • event_type — тип событий, которые должны регистрироваться, включая имена операторов SQL, а также названия событий AUTHENTICATE и DISCONNECT. Если указано значение ALL, NULL или пустая строка, включается регистрация всех возможных событий для объектов заданного типа. Например, для объектов типа TABLE ключевое слово ALL включает регистрацию команд SELECT, INSERT, UPDATE, DELETE, TRUNCATE, COPY, а также CREATE, ALTER и DROP. Также можно настроить регистрацию классов событий, используя следующие значения: ALL_DDL, ALL_DDL_NONTEMP, ALL_DML, ALL_DML_NONTEMP, ALL_MOD, ALL_PROC, ALL_ROLE и MISC. Полный список допустимых значений параметра event_type приведён в Подразделе F.40.5.

  • object_type — тип объектов, для которых должны регистрироваться события безопасности. Если указано значение ALL, NULL или пустая строка, включается регистрация событий для всех типов объектов. Например, для регистрации всех попыток доступа к сторонним таблицам укажите тип объекта FOREIGN TABLE для события SELECT. Если в event_type передаётся AUTHENTICATE, DISCONNECT, SET или RESET, в object_type должен передаваться NULL. Для всех событий, относящихся к действиям с пользователями, например CREATE USER или DROP USER, в качестве типа объекта нужно передавать ROLE. Поддерживаются следующие типы объектов: COMPOSITE TYPE, DATABASE, EVENT TRIGGER, FUNCTION, INDEX, PREPARED STATEMENT, ROLE, SEQUENCE, SCHEMA, TABLE, FOREIGN TABLE, TOAST TABLE, TABLESPACE, VIEW, MATERIALIZED VIEW, CATALOG RELATION и CATALOG FUNCTION.

    Примечание

    Чтобы регистрировать события для объектов системного каталога, необходимо включить параметр конфигурации pg_proaudit.log_catalog_access. В противном случае эти события не будут зарегистрированы, даже если в правиле указан тип объекта CATALOG RELATION, CATALOG FUNCTION или ALL.

  • object_name — имя объекта, для которого задаётся правило регистрации событий. При указании NULL или пустой строки регистрация событий включается для всех имён объектов.

  • role_name — имя роли, для которой задаётся правило регистрации событий. Если указано, позволяет регистрировать действия, совершённые пользователем с правами этой роли. Это означает, что как минимум один из атрибутов пользовательского сеанса, session_user или current_user, должен либо совпадать с role_name, либо прямо или косвенно являться членом указанной роли. При указании пустой строки или значения NULL включается регистрация событий для всех имён ролей, а при указании значения current_role() события регистрируются для текущей роли.

  • exclude_role_name — имя роли, для которой не применяется правило регистрации событий. Если указано, регистрация событий отключается для любого пользователя, у которого хотя бы один из атрибутов сеанса (session_user или current_user) соответствует этой роли или входит в неё. Этот параметр имеет приоритет над параметром role_name. Если указана пустая строка или NULL, события регистрируются для всех пользователей.

    Примечание

    Если пользователь указан одновременно в параметре exclude_role_name одного правила и в параметре role_name другого правила без exclude_role_name, второе правило всё равно регистрирует действия этого пользователя.

  • comment — комментарий, описывающий созданное правило регистрации событий. Этот аргумент не влияет на выполнение правила и не отражается в журнале.

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

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

Примечание

Если правило, которое вы хотите удалить, настроено для регистрации типа событий DISCONNECT, то события отключения могут продолжать регистрироваться после удаления правила. За дополнительными сведениями обратитесь к Подразделу F.40.3.1.

Аргументы:

  • db_name — имя базы данных, для которой удаляется правило регистрации событий.

  • event_type — тип событий, для которого удаляется правило регистрации. Список возможных значений приведён в Подразделе F.40.5.

  • object_type — тип объекта, для которого удаляется правило регистрации событий.

  • object_name — имя объекта, для которого удаляется правило регистрации событий.

  • role_name — имя роли, для которой удаляется правило регистрации событий.

  • exclude_role_name — имя роли, для которой отменяется исключение из правила регистрации событий.

pg_proaudit_show()

Возвращает список регистрируемых событий в виде таблицы, включая столбец exclude_role_name. Эта функция применяется в представлении 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 нельзя.

F.40.3.1. Обработка событий отключения от базы данных #

Когда настроено правило для регистрации типа событий DISCONNECT, и пользователь аутентифицируется в базе данных, то расширение pg_proaudit проверяет, удовлетворяет ли правилу соответствующее событие отключения и нужно ли его регистрировать.

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

Рассмотрим следующий пример:

  1. Администратор настраивает следующее правило регистрации событий:

    SELECT pg_proaudit_set_rule ('postgres', 'DISCONNECT', null, null, null, null, 'Any disconnect from the postgres DB');
                
  2. Пользователь подключается к базе данных postgres и аутентифицируется в ней.

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

  3. Администратор удаляет правило регистрации событий, при этом пользователь всё ещё подключён к базе данных:

    SELECT pg_proaudit_remove_rule('postgres', 'DISCONNECT', null, null, null, null);
                
  4. Пользователь отключается от базы данных.

    Соответствующее событие отключения регистрируется, несмотря на то, что правило было удалено.

F.40.3.2. Обработка имён объектов #

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

Строки, представляющие имена объектов, необходимо передавать функциям pg_proaudit_set_rule и pg_proaudit_remove_rule как обычные строки: одинарную кавычку необходимо удвоить без экранирования, а другие символы передаются как есть, без экранирования или переноса.

Например, правила для таблиц table1 и "TaBlE1" будут одинаковыми, а их имена можно передавать символами как верхнего, так и нижнего регистра, как и имена ролей. Перед созданием правила для баз данных pg_proaudit проверяет, действительно ли существует указанная база данных, и эта проверка чувствительна к регистру.

В следующих примерах показано создание «странных» имён объектов с указанием корректных правил для них.

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

create table "taBlE""123"();
SELECT pg_proaudit_set_rule(null, null, null, 'public.taBlE"123', null, 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, null);

create role "Some '@#$%' person" with login;
SELECT pg_proaudit_set_rule(null, 'authenticate', null, null, 'Some ''@#$%'' person', null);

create role "Some '@#$%' person" with superuser;
\c - "Some '@#$%' person"
SELECT pg_proaudit_set_rule(null, 'disconnect', null, null, current_user, null);

create database " D B 1";
SELECT pg_proaudit_set_rule(' D B 1', 'authenticate', null, 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, null);

F.40.4. Представление pg_proaudit_settings #

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

  • db_name (text) — имя базы данных, в которой регистрируются события.

  • event_type (text) — тип регистрируемых событий.

  • object_type (text) — тип объекта, для которого должны регистрироваться события безопасности.

  • object_name (text) — имя объекта, для которого должны регистрироваться события.

  • role_name (text) — роль, от имени которой должны выполняться регистрируемые действия.

  • exclude_role_name (text) — роль, для которой не применяется правило регистрации событий.

  • comment (text) — комментарий, описывающий созданное правило регистрации событий.

F.40.5. События безопасности #

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

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

  • ALL_DDL: CREATE, ALTER, DROP для любого объекта базы данных, за исключением хранимых процедур и функций.

  • ALL_DDL_NONTEMP: такой же класс, как ALL_DDL, но его область действия ограничена только теми объектами базы данных, которые не находятся во временных схемах pg_temp_nnn.

  • ALL_DML: SELECT, INSERT, UPDATE, DELETE, TRUNCATE для любых типов таблиц; EXECUTE для функций и хранимых процедур.

  • ALL_DML_NONTEMP: такой же класс, как ALL_DML, но его область действия ограничена только теми объектами базы данных, которые не находятся во временных схемах pg_temp_nnn.

  • ALL_MOD: INSERT, UPDATE, DELETE, TRUNCATE для любых типов таблиц.

  • ALL_PROC: CREATE, ALTER, DROP для любых функций и хранимых процедур.

  • ALL_ROLE: CREATE, ALTER, DROP в отношении USER, ROLE, GROUP, PROFILE, а также выполнение команды GRANT.

  • MISC: все события, не относящиеся к другим вышеперечисленным классам.

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

  • ANALYZE

  • AUTHENTICATE

  • DISCONNECT

  • ALTER AGGREGATE

  • ALTER COLLATION

  • ALTER CONVERSION

  • ALTER DATABASE

  • ALTER DEFAULT PRIVILEGES

  • ALTER DOMAIN

  • ALTER EVENT TRIGGER

  • ALTER EXTENSION

  • ALTER FOREIGN DATA WRAPPER

  • ALTER FOREIGN TABLE

  • ALTER FUNCTION

  • ALTER INDEX

  • ALTER LANGUAGE

  • ALTER LARGE OBJECT

  • ALTER MATERIALIZED VIEW

  • ALTER OPERATOR

  • ALTER OPERATOR CLASS

  • ALTER OPERATOR FAMILY

  • ALTER POLICY

  • ALTER PROFILE

  • ALTER ROLE, ALTER USER, ALTER GROUP

  • ALTER RULE

  • ALTER SCHEMA

  • ALTER SEQUENCE

  • ALTER SERVER

  • ALTER SYSTEM

  • ALTER TABLE

  • ALTER TABLESPACE

  • ALTER TEXT SEARCH CONFIGURATION

  • ALTER TEXT SEARCH DICTIONARY

  • ALTER TEXT SEARCH PARSER

  • ALTER TEXT SEARCH TEMPLATE

  • ALTER TRIGGER

  • ALTER TYPE

  • ALTER USER MAPPING

  • ALTER VIEW

  • CHECKPOINT

  • CLUSTER

  • COMMENT

  • COPY

  • CREATE ACCESS METHOD

  • CREATE AGGREGATE

  • CREATE CAST

  • CREATE COLLATION

  • CREATE CONVERSION

  • CREATE DATABASE

  • CREATE DOMAIN

  • CREATE EVENT TRIGGER

  • CREATE EXTENSION

  • CREATE FOREIGN DATA WRAPPER

  • CREATE FOREIGN TABLE

  • CREATE FUNCTION

  • CREATE INDEX

  • CREATE LANGUAGE

  • CREATE MATERIALIZED VIEW

  • CREATE OPERATOR

  • CREATE OPERATOR CLASS

  • CREATE OPERATOR FAMILY

  • CREATE POLICY

  • CREATE PROFILE

  • CREATE ROLE, CREATE USER, CREATE GROUP

  • CREATE RULE

  • CREATE SCHEMA

  • CREATE SEQUENCE

  • CREATE SERVER

  • CREATE TABLE, CREATE TABLE AS, SELECT INTO

  • CREATE TABLESPACE

  • CREATE TEXT SEARCH CONFIGURATION

  • CREATE TEXT SEARCH DICTIONARY

  • CREATE TEXT SEARCH PARSER

  • CREATE TEXT SEARCH TEMPLATE

  • CREATE TRANSFORM

  • CREATE TRIGGER

  • CREATE TYPE

  • CREATE USER MAPPING

  • CREATE VIEW

  • DEALLOCATE

  • DELETE

  • DISCARD ALL

  • DISCARD PLANS

  • DISCARD SEQUENCES

  • DISCARD TEMP

  • DO

  • DROP ACCESS METHOD

  • DROP AGGREGATE

  • DROP CAST

  • DROP COLLATION

  • DROP CONVERSION

  • DROP DATABASE

  • DROP DOMAIN

  • DROP EVENT TRIGGER

  • DROP EXTENSION

  • DROP FOREIGN DATA WRAPPER

  • DROP FOREIGN TABLE

  • DROP FUNCTION

  • DROP INDEX

  • DROP LANGUAGE

  • DROP MATERIALIZED VIEW

  • DROP OPERATOR

  • DROP OPERATOR CLASS

  • DROP OPERATOR FAMILY

  • DROP OWNED

  • DROP POLICY

  • DROP PROFILE

  • DROP ROLE, DROP USER, DROP GROUP

  • DROP RULE

  • DROP SCHEMA

  • DROP SEQUENCE

  • DROP SERVER

  • DROP TABLE

  • DROP TABLESPACE

  • DROP TEXT SEARCH CONFIGURATION

  • DROP TEXT SEARCH DICTIONARY

  • DROP TEXT SEARCH PARSER

  • DROP TEXT SEARCH TEMPLATE

  • DROP TRANSFORM

  • DROP TRIGGER

  • DROP TYPE

  • DROP USER MAPPING

  • DROP VIEW

  • EXECUTE

  • FETCH

  • GRANT

  • INSERT

  • MOVE

  • PREPARE

  • REASSIGN OWNED

  • REFRESH MATERIALIZED VIEW

  • REINDEX

  • RESET

  • REVOKE

  • SECURITY LABEL

  • SELECT

  • SET

  • UPDATE

  • TRUNCATE TABLE

  • VACUUM

F.40.6. Поля событий безопасности #

Каждое событие безопасности записывается в отдельную строку, набор полей в которой зависит от формата — CEF или CSV. Поля, общие для обоих форматов, перечислены в Таблице F.26. Синтаксис CEF разработан специально для журналов событий, и события в этом формате содержат дополнительные поля. Такие события состоят из стандартного заголовка и дополнительной части, оформленной как пары «ключ-значение». Заголовок содержит обязательные поля, разделённые символами |. Эти поля перечислены в Таблице F.27. Поля дополнительной части события CEF перечислены в Таблице F.28.

Таблица F.26. Поля событий

Тег или пара тегов поля CEFПоле CSVОписание
endlog_timeДата и время события
duserrole_nameИмя текущего пользователя (current_user)
cs1Label=DB cs1=database_nameИмя базы данных
dpidsession_pidИдентификатор серверного процесса (PID)
cs2Label=Severity cs2=error_severityУровень важности: INFO или ERROR
cn1Label=CommandLine cn1=session_line_numПоследовательный номер команды в сеансе
cn2Label=SubcommndLine cn2=session_line_subcommand_numНомер подкоманды в сложных командах (CREATE TABLE ... AS SELECT ...)
cs3Label=EventType cs3=event_typeИмя оператора или имя события
cs4Label=ObjectType cs4=object_typeТип объекта
cs5Label=ObjectName cs5=object_nameИмя объекта
outcomestatusРезультат выполнения оператора: SUCCESS (успех) или FAILURE (отказ)
reasonerror_messageДополнительная информация, например сообщение об ошибке в случае результата FAILURE или параметры подключения и имя приложения для события AUTHENTICATE. Незавершённые попытки аутентификации помечаются как [EOF. No credentials provided] (Конец файла. Учётные данные не предоставлены)
cs6Label=QueryText cs6=query_textТекст SQL-команды
cs7Label=QueryArgs cs7=query_argsПараметры команды (например, для PREPARE)
cs8Label=SessionUser cs8=session_userИмя пользователя сеанса (session_user)
cs9Label=uuid cs9=uuidУникальный идентификатор события (в формате UUIDv7)
cs10Label=xid cs10=xidИдентификатор транзакции (XID)
cs11Label=vxid cs11=vxidВиртуальный идентификатор транзакции (VXID)
fsizerowsКоличество затронутых строк
srcuser_ipIP-адрес источника события

Событие содержит информацию о значениях session_user и current_user, которые используются в рамках пользовательского сеанса. Поэтому можно идентифицировать пользователя, даже если он сменит идентификатор пользователя с помощью команды SET ROLE.

Значения XID и VXID могут быть нулевыми, если событие не относится к транзакции (например, событие DISCONNECT).

Таблица F.27. Поля заголовка CEF

Имя поляОписание
CEF: VersionВерсия формата CEF. В журналах событий pg_proaudit это значение всегда равно1.
Device VendorПоставщик решения. В журналах событий pg_proaudit всегда указан Postgres Pro.
Device ProductРедакция Postgres Pro.
Device VersionВерсия СУБД, зарегистрировавшей событие.
Device Event Class IDИдентификатор класса события безопасности.
NameИмя события безопасности. Это значение может совпадать со значением Device Event Class ID.
SeverityУровень важности события. Возможные значения: 3 (INFO) или 7 (ERROR).

Таблица F.28. Поля дополнительной части CEF

Имя поляОписание
sprocИмя процесса-источника события
dstIP-адрес получателя события
dptНомер порта получателя события
dprocИмя процесса-получателя события

F.40.7. Параметры конфигурации журнала событий безопасности #

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

pg_proaudit.log_destination (string) #

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

  • csvlog — записывать события безопасности в файл CSV.

  • ceflog — записывать события безопасности в файл CEF. CEF — это открытый текстовый формат, предназначенный для записей журнала. Он содержит стандартный заголовок и переменное расширение, оформленное в виде пар ключ-значение.

  • syslog — передавать события безопасности в syslog.

  • cefsyslog — передавать события безопасности в формате CEF в syslog.

  • ddllog — записывать изменения DDL постоянных объектов базы данных в отдельный файл CSV с именем pg_proaudit_ddl.log. В отличие от других методов регистрации, ddllog работает независимо от правил pg_proaudit.

Этот параметр может содержать одно или несколько этих значений, разделённых запятыми. Однако обратите внимание, что значения syslog и cefsyslog являются взаимоисключающими. Если указаны оба, приоритет имеет cefsyslog, а значение syslog игнорируется.

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

pg_proaudit.csv_split_messages (boolean) #

Определяет, записываются ли события безопасности в выводе CSV и syslog в одну строку или в несколько. Если задано значение on, символы перевода строки и возврата каретки сохраняются, и события могут занимать несколько строк. Если задано значение off, эти символы экранируются как \n и \r соответственно, и каждое событие безопасности занимает одну строку. Это упрощает анализ и обработку журналов событий, облегчая фильтрацию, поиск и интеграцию со внешними системами.

Этот параметр применяется как к файлам журналов CSV, так и к выводу syslog, но не влияет на формат CEF, где символы новой строки всегда экранируются в соответствии со стандартом CEF.

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

pg_proaudit.log_catalog_access (boolean) #

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

Со значением off такие события не регистрируются, даже если есть правила для типов объектов CATALOG RELATION, CATALOG FUNCTION или ALL.

Не рекомендуется задавать для этого параметра значение on, если вы не планируете регистрировать события для объектов системного каталога. В противном случае это может влиять на производительность Postgres Pro, даже если правила для упомянутых типов объектов отсутствуют.

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

pg_proaudit.log_command_text (boolean) #

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

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

pg_proaudit.log_directory (string) #

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

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

pg_proaudit.log_file_mode (integer) #

Устанавливает права доступа к новым файлам журнала событий, создаваемым pg_proaudit. Диапазон допустимых значений: от 0600 до 0666.

Наиболее строгое значение — 0600. Оно означает, что только владелец сервера (обычно пользователь postgres) может читать и записывать файлы журнала событий. Другое распространённое значение — 0640, которое разрешает чтение этих файлов участникам группы владельца.

Наименее строгое значение — 0666, которое предоставляет права на чтение и запись всем пользователям. Однако, предоставляя права доступа более широким группам пользователей, имейте в виду, что файлы журнала могут содержать конфиденциальные данные.

Обратите внимание, что владелец сервера всегда имеет доступ к файлам журнала событий, независимо от значения этого параметра.

По умолчанию: 0600 (или 0640 при использовании initdb с опцией -g

pg_proaudit.log_filename (string) #

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

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

pg_proaudit.log_rotation_size (integer) #

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

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

pg_proaudit.log_rotation_age (integer) #

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

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

pg_proaudit.log_rows (boolean) #

Определяет, будет ли регистрироваться количество строк, затронутых SQL-командой.

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

pg_proaudit.log_truncate_on_rotation (boolean) #

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

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

pg_proaudit.max_rules_count (integer) #

Определяет максимально допустимое количество правил. Чтобы изменение параметра вступило в силу, необходимо перезагрузить сервер базы данных.

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

F.40.8. Просмотр журнала событий безопасности #

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

  1. Установите file_fdw и создайте определение стороннего сервера:

    CREATE EXTENSION file_fdw;
    CREATE SERVER pg_proauditlog FOREIGN DATA WRAPPER file_fdw;
  2. Создайте стороннюю таблицу, определив её столбцы и указав абсолютный путь к файлу журнала. Фактический путь определяется параметрами pg_proaudit.log_directory и pg_proaudit.log_filename.

    CREATE FOREIGN 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,
            uuid text,
            xid text,
            vxid text,
            rows text,
            user_ip text )
    
    SERVER pg_proauditlog
    OPTIONS (filename 'absolute_file_path_to_log_file.csv', FORMAT 'csv' );

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

Файл pg_proaudit_ddl.log использует тот же формат CSV, что и обычный журнал аудита, но с одним дополнительным столбцом — is_prepare_transaction. Этот столбец указывает, выполнялся ли оператор DDL в рамках команды PREPARE TRANSACTION. При создании внешней таблицы для журнала изменений DDL добавьте этот столбец:

CREATE FOREIGN TABLE pg_proaudit_ddllog
          ( 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,
            uuid text,
            xid text,
            vxid text,
            rows text,
            user_ip text,
            is_prepare_transaction text )

    SERVER pg_proauditlog_ddllog
    OPTIONS (filename :'ddllog_filename',  FORMAT 'csv');

F.40.9. Примеры #

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

  • аутентификация/отключение от базы данных postgres

  • все действия пользователя, если как минимум один из атрибутов пользовательского сеанса, session_user или current_user, либо явно установлен в значение postgres, либо прямо или косвенно является членом этой роли

  • создание, изменение и удаление любых таблиц

  • все операции с таблицей app_table, принадлежащей схеме public

Все события должны записываться в файлы в формате 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
pg_proaudit.log_rows = 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;
SHOW pg_proaudit.log_rows;

Предположим, что ваша переменная окружения 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,
  uuid text,
  xid text,
  vxid text,
  rows text,
  user_ip 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, null, 'Any authentication in the current DB');
SELECT pg_proaudit_set_rule (current_database(), 'DISCONNECT', null, null, null, null, 'Any disconnect from the current DB');
SELECT pg_proaudit_set_rule (current_database(), 'ALL', 'TABLE', null, null, null, 'Any operations with any table in the current DB');
SELECT pg_proaudit_set_rule (current_database(), 'ALL', null, null, 'postgres', null, '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, null);

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

SELECT * FROM pg_proaudit_settings;
 db_name  |  event_type  | object_type |   object_name    | role_name | exclude_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, rows, user_ip
FROM pg_proaudit_log
WHERE object_name = 'public.app_table';
       when        | current_usr_name | session_pid |  event_type  |                 query_text                  | session_usr_name | rows | user_ip
-------------------+------------------+-------------+--------------+---------------------------------------------+------------------+------+---------
 27.09.23 12:44:27 | postgres         | 2010        | CREATE TABLE | CREATE TABLE app_table (id int, name text); | postgres         | 0    |
 27.09.23 12:45:55 | postgres         | 2010        | INSERT       | INSERT INTO app_table VALUES (1, 'first');  | postgres         | 1    |
 27.09.23 12:46:00 | postgres         | 2010        | SELECT       | SELECT * FROM app_table;                    | postgres         | 1    |
(3 rows)

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

F.40. pg_proaudit — enables detailed logging of various security events #

The pg_proaudit extension enables detailed logging of various security events.

pg_proaudit works in parallel with the standard PostgreSQL logging solutions (logging collector) and does not depend on them. Security event log of the pg_proaudit extension is stored separately from the server log. At the Postgres Pro Standard startup, pg_proaudit launches a special background process to log security events.

Logging rules are stored in the pg_proaudit.conf configuration file located in the cluster data directory (PGDATA). It is a text file that can be edited directly using operating system facilities. To modify the file using SQL, you can use several pg_proaudit functions. The pg_proaudit_settings view displays the current pg_proaudit rules, even if they have not been saved into the pg_proaudit.conf file yet.

Note that pg_proaudit.conf must be copied manually when upgrading Postgres Pro to a major version using the pg_upgrade utility. Like other configuration files, pg_proaudit.conf is not automatically transferred to the new cluster data directory (PGDATA).

Note

Legacy-format logging rules that contain OIDs instead of object names are only partially supported and may be processed incorrectly.

All the logged events belong to the following classes:

  • DDL commands for creating, changing, and deleting DBMS objects (databases, tablespaces, schemas, tables, views, sequences, languages, functions)

  • access control commands for database objects (GRANT, REVOKE)

  • DML commands for access to database objects (INSERT, UPDATE, DELETE, SELECT, TRUNCATE for tables and/or views, EXECUTE for functions)

  • database authentication/disconnection events

  • all commands executed by a particular user

Security events can be logged both in the centralized logging solution of the operating system (syslog) and in the standard file-system files. Event logs can be written both into the syslog and into the files simultaneously. For clear identification, all pg_proaudit records in the syslog are marked with AUDIT. Event log files can be written in the CEF (Common Event Format) and CSV (Comma-Separated Values) formats. For more information about event fields logged for each format, see Section F.40.6.

In addition, DDL changes to permanent database objects can be logged to a separate file. This file is not rotated and thus ensures a persistent record of structural changes. This allows administrators and support engineers to quickly investigate who changed a database object and when, without having to search the entire security event log. Note that DDL statements from rolled-back transactions are not logged. For information on accessing the DDL change log, refer to Section F.40.8.

All string fields in the log output are converted to UTF-8 regardless of the database encoding.

You can define a directory to store security log files and set up log file rotation. pg_proaudit can switch to a new log file either after the specified time interval, or when the specified size of the log file is exceeded. This enables you to define a workflow for cleaning up security event logs.

Postgres Pro user with the SUPERUSER attribute should grant access to the pg_proaudit extension and security event log files only to the user with the information security administrator role.

F.40.1. Installing the pg_proaudit Extension #

The pg_proaudit extension is a built-in extension included into Postgres Pro Standard. To enable pg_proaudit, complete the following steps:

  1. Add pg_proaudit to the shared_preload_libraries variable in the postgresql.conf file:

    shared_preload_libraries = 'pg_proaudit'
    
  2. Restart the database server for the changes to take effect. To verify that the pg_proaudit library was installed correctly, you can run the following command:

    SHOW shared_preload_libraries;
    
  3. In each database for which you are going to log security events, create the pg_proaudit extension using the following query:

    CREATE EXTENSION pg_proaudit;
    

    The pg_proaudit extension adds several functions for managing the pg_proaudit.conf file, the pg_proaudit_settings view that displays the current pg_proaudit rules and event triggers.

F.40.2. Uninstalling the pg_proaudit Extension #

To properly uninstall pg_proaudit, complete the following steps:

  1. Delete the pg_proaudit extension using the following query:

    DROP EXTENSION pg_proaudit;
    

  2. Remove pg_proaudit from the shared_preload_libraries variable in the postgresql.conf file.

    Skip this step if you have several databases in the cluster and you want to remove the extension only for one of them. In this case, it is recommended to remove logging rules related to the corresponding database prior to uninstalling the extension.

F.40.3. Functions to Configure Security Event Logging #

To configure security event logging, pg_proaudit provides an SQL interface that consists of several functions and the pg_proaudit_settings view.

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

Creates the logging rule with the specified parameters. When the pg_proaudit_set_rule() function completes, security event logging starts immediately, but the pg_proaudit.conf file is not updated. To save the changes in the pg_proaudit.conf file, call the pg_proaudit_save() function.

Arguments:

  • db_name — name of the database for which the logging rule is established. An empty string or NULL specified in this argument means that events are logged for all databases where the pg_proaudit extension is created. When set to current_database(), events for the current database are logged.

  • event_type — type of the event that needs to be logged, including SQL operator names, as well as AUTHENTICATE and DISCONNECT events. When set to ALL, as well as when an empty string or NULL is specified, enables logging for all events available for the specified object type. For example, for the TABLE object type, the ALL keyword enables logging for commands SELECT, INSERT, UPDATE, DELETE, TRUNCATE, COPY, as well as CREATE, ALTER, DROP. You can also set up logging of event classes by specifying the following values: ALL_DDL, ALL_DDL_NONTEMP, ALL_DML, ALL_DML_NONTEMP, ALL_MOD, ALL_PROC, ALL_ROLE, and MISC. For the full list of possible event_type values, see Section F.40.5.

  • object_type — type of the object for which security events need to be logged. When set to ALL, as well as when an empty string or the NULL is specified, enables logging of events for all object types. For example, specify FOREIGN TABLE object type for the SELECT event to log all attempts to access foreign tables. Use NULL if event_type is set to AUTHENTICATE, DISCONNECT, SET, or RESET, and the ROLE value for all events that reference user actions, such as CREATE USER or DROP USER. The following object types are supported: COMPOSITE TYPE, DATABASE, EVENT TRIGGER, FUNCTION, INDEX, PREPARED STATEMENT, ROLE, SEQUENCE, SCHEMA, TABLE, FOREIGN TABLE, TOAST TABLE, TABLESPACE, VIEW, MATERIALIZED VIEW, CATALOG RELATION, and CATALOG FUNCTION.

    Note

    To log security events for system catalog objects, it is required to enable the pg_proaudit.log_catalog_access configuration parameter. Otherwise, these events will not be logged, even if the CATALOG RELATION, CATALOG FUNCTION, or ALL object type is specified in the rule.

  • object_name — name of the object for which the logging rule is established. When an empty string or NULL is specified, enables logging of events for all object names.

  • role_name — name of the role for which the logging rule is established. If specified, allows logging the actions caused by the user who has the privileges of the role. This means that at least one of the user session attributes, session_user or current_user, should either be equal to the role_name, or be directly or indirectly a member of this role. When an empty string or NULL is specified, enables logging of actions caused by any user. When set to current_role, the logging rule is established for the user current role.

  • exclude_role_name — name of the role for which the logging rule does not apply. If specified, logging is suppressed for actions performed by any user whose session attributes (session_user or current_user) match this role or are members of it. The exclusion takes precedence over the role_name setting. When an empty string or NULL is specified, no exclusions are applied.

    Note

    If a user matches the exclude_role_name parameter of one rule and the role_name parameter of another rule without an exclude_role_name, the second rule still logs this user's actions.

  • comment — comment to describe the created logging rule. This argument does not affect the rule execution and is not reflected in the log.

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

Removes the specific logging rule with the set parameters. To save the changes in the pg_proaudit.conf file, call the pg_proaudit_save() function.

Note

If the rule that you want to remove is configured to log the DISCONNECT event type, then disconnect events may still be logged after the rule is removed. For additional information, see Section F.40.3.1.

Arguments:

  • db_name — name of the database for which the logging rule needs to be removed.

  • event_type — type of the event for which the logging rule needs to be removed. For the full list of possible event_type values, see Section F.40.5.

  • object_type — type of the object for which the logging rule needs to be removed.

  • object_name — name of the object for which the logging rule needs to be removed.

  • role_name — name of the role for which the logging rule needs to be removed.

  • exclude_role_name — name of the role for which the logging rule exclusion needs to be removed.

pg_proaudit_show()

Returns logged events in a table view, including the exclude_role_name column. This function is used by the pg_proaudit_settings view.

pg_proaudit_reload()

Reads logging configuration from the pg_proaudit.conf file. You must call this function if the pg_proaudit.conf file was modified manually using the operating system facilities.

pg_proaudit_reset()

Removes all logging rules. To save information about the canceled logging in the pg_proaudit.conf file, call the pg_proaudit_save() function.

pg_proaudit_save()

Saves logging rules from memory into the pg_proaudit.conf file. The pg_proaudit.conf file is located in the cluster data directory (PGDATA). You cannot change the pg_proaudit.conf file location.

F.40.3.1. Handling Database Disconnection Events #

When a logging rule is configured for DISCONNECT event type, and a user authenticates in a database, then the pg_proaudit extension checks whether the corresponding disconnection event satisfies the rule and must be logged.

If the event must be logged, then the removal of the logging rule has no effect on the event. This event will be logged no matter if the logging rule exists at the moment of disconnect.

Consider the following example:

  1. An administrator configures the following logging rule:

    SELECT pg_proaudit_set_rule ('postgres', 'DISCONNECT', null, null, null, null, 'Any disconnect from the postgres DB');
                

  2. A user connects to the postgres database and goes through authentication.

    For the rule in effect, a corresponding disconnection event will be logged later.

  3. The administrator removes the logging rule, while the user is still connected to the database:

    SELECT pg_proaudit_remove_rule('postgres', 'DISCONNECT', null, null, null, null);
                

  4. The user disconnects from the database.

    The corresponding disconnection event is logged despite the removal of the logging rule.

F.40.3.2. Handling Object Names #

Comparison of string representations of names for database, user, role and other objects is case-insensitive even if the object name in the database is enclosed in quotes. These string representations are stored as lower-case strings.

Strings representing object names need to be passed to functions pg_proaudit_set_rule and pg_proaudit_remove_rule as regular strings: a single quote needs repeating, and other characters are passed as is, without escaping or wrapping.

For example, rules for tables table1 and "TaBlE1" will be the same, and their names can be passed in upper-case or lower-case characters. The same holds for roles. As for databases, before creating a rule, pg_proaudit checks whether this database actually exists, and this check is case-sensitive.

The following examples illustrate creation of "weird" object names and specifying correct audit rules for them.

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

create table "taBlE""123"();
SELECT pg_proaudit_set_rule(null, null, null, 'public.taBlE"123', null, 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, null);

create role "Some '@#$%' person" with login;
SELECT pg_proaudit_set_rule(null, 'authenticate', null, null, 'Some ''@#$%'' person', null);

create role "Some '@#$%' person" with superuser;
\c - "Some '@#$%' person"
SELECT pg_proaudit_set_rule(null, 'disconnect', null, null, current_user, null);

create database " D B 1";
SELECT pg_proaudit_set_rule(' D B 1', 'authenticate', null, 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, null);

F.40.4. pg_proaudit_settings View #

This view displays the current pg_proaudit rules, even if they have not been saved into the pg_proaudit.conf file yet. The pg_proaudit_settings view consists of the following columns:

  • db_name (text) — name of the database for which to log security events.

  • event_type (text) — event type to log.

  • object_type (text) — type of the object for which security events are to be logged.

  • object_name (text) — name of the object for which security events are to be logged.

  • role_name (text) — the role on behalf of which logged actions are performed.

  • exclude_role_name (text) — the role for which the logging rule is not applied.

  • comment (text) — comment to describe the created logging rule.

F.40.5. Security Events #

You can configure the pg_proaudit extension to log classes of security events and specific events by specifying the respective value in the event_type argument of the pg_proaudit_set_rule() function.

The following classes of security events are supported:

  • ALL_DDL: CREATE, ALTER, DROP for any database object except stored procedures and functions.

  • ALL_DDL_NONTEMP: same as ALL_DDL but the scope is limited to the objects that are not contained in pg_temp_nnn temporary schemas.

  • ALL_DML: SELECT, INSERT, UPDATE, DELETE, TRUNCATE for any table type; EXECUTE for functions and stored procedures.

  • ALL_DML_NONTEMP: same as ALL_DML but the scope is limited to the objects that are not contained in pg_temp_nnn temporary schemas.

  • ALL_MOD: INSERT, UPDATE, DELETE, TRUNCATE for any table type.

  • ALL_PROC: CREATE, ALTER, DROP for any function and stored procedure.

  • ALL_ROLE: CREATE, ALTER, DROP for USER, ROLE, GROUP, PROFILE, as well as execution of the GRANT command.

  • MISC: all events not related to any of the other classes mentioned above.

The following specific security events are supported:

  • ANALYZE

  • AUTHENTICATE

  • DISCONNECT

  • ALTER AGGREGATE

  • ALTER COLLATION

  • ALTER CONVERSION

  • ALTER DATABASE

  • ALTER DEFAULT PRIVILEGES

  • ALTER DOMAIN

  • ALTER EVENT TRIGGER

  • ALTER EXTENSION

  • ALTER FOREIGN DATA WRAPPER

  • ALTER FOREIGN TABLE

  • ALTER FUNCTION

  • ALTER INDEX

  • ALTER LANGUAGE

  • ALTER LARGE OBJECT

  • ALTER MATERIALIZED VIEW

  • ALTER OPERATOR

  • ALTER OPERATOR CLASS

  • ALTER OPERATOR FAMILY

  • ALTER POLICY

  • ALTER PROFILE

  • ALTER ROLE, ALTER USER, ALTER GROUP

  • ALTER RULE

  • ALTER SCHEMA

  • ALTER SEQUENCE

  • ALTER SERVER

  • ALTER SYSTEM

  • ALTER TABLE

  • ALTER TABLESPACE

  • ALTER TEXT SEARCH CONFIGURATION

  • ALTER TEXT SEARCH DICTIONARY

  • ALTER TEXT SEARCH PARSER

  • ALTER TEXT SEARCH TEMPLATE

  • ALTER TRIGGER

  • ALTER TYPE

  • ALTER USER MAPPING

  • ALTER VIEW

  • CHECKPOINT

  • CLUSTER

  • COMMENT

  • COPY

  • CREATE ACCESS METHOD

  • CREATE AGGREGATE

  • CREATE CAST

  • CREATE COLLATION

  • CREATE CONVERSION

  • CREATE DATABASE

  • CREATE DOMAIN

  • CREATE EVENT TRIGGER

  • CREATE EXTENSION

  • CREATE FOREIGN DATA WRAPPER

  • CREATE FOREIGN TABLE

  • CREATE FUNCTION

  • CREATE INDEX

  • CREATE LANGUAGE

  • CREATE MATERIALIZED VIEW

  • CREATE OPERATOR

  • CREATE OPERATOR CLASS

  • CREATE OPERATOR FAMILY

  • CREATE POLICY

  • CREATE PROFILE

  • CREATE ROLE, CREATE USER, CREATE GROUP

  • CREATE RULE

  • CREATE SCHEMA

  • CREATE SEQUENCE

  • CREATE SERVER

  • CREATE TABLE, CREATE TABLE AS, SELECT INTO

  • CREATE TABLESPACE

  • CREATE TEXT SEARCH CONFIGURATION

  • CREATE TEXT SEARCH DICTIONARY

  • CREATE TEXT SEARCH PARSER

  • CREATE TEXT SEARCH TEMPLATE

  • CREATE TRANSFORM

  • CREATE TRIGGER

  • CREATE TYPE

  • CREATE USER MAPPING

  • CREATE VIEW

  • DEALLOCATE

  • DELETE

  • DISCARD ALL

  • DISCARD PLANS

  • DISCARD SEQUENCES

  • DISCARD TEMP

  • DO

  • DROP ACCESS METHOD

  • DROP AGGREGATE

  • DROP CAST

  • DROP COLLATION

  • DROP CONVERSION

  • DROP DATABASE

  • DROP DOMAIN

  • DROP EVENT TRIGGER

  • DROP EXTENSION

  • DROP FOREIGN DATA WRAPPER

  • DROP FOREIGN TABLE

  • DROP FUNCTION

  • DROP INDEX

  • DROP LANGUAGE

  • DROP MATERIALIZED VIEW

  • DROP OPERATOR

  • DROP OPERATOR CLASS

  • DROP OPERATOR FAMILY

  • DROP OWNED

  • DROP POLICY

  • DROP PROFILE

  • DROP ROLE, DROP USER, DROP GROUP

  • DROP RULE

  • DROP SCHEMA

  • DROP SEQUENCE

  • DROP SERVER

  • DROP TABLE

  • DROP TABLESPACE

  • DROP TEXT SEARCH CONFIGURATION

  • DROP TEXT SEARCH DICTIONARY

  • DROP TEXT SEARCH PARSER

  • DROP TEXT SEARCH TEMPLATE

  • DROP TRANSFORM

  • DROP TRIGGER

  • DROP TYPE

  • DROP USER MAPPING

  • DROP VIEW

  • EXECUTE

  • FETCH

  • GRANT

  • INSERT

  • MOVE

  • PREPARE

  • REASSIGN OWNED

  • REFRESH MATERIALIZED VIEW

  • REINDEX

  • RESET

  • REVOKE

  • SECURITY LABEL

  • SELECT

  • SET

  • UPDATE

  • TRUNCATE TABLE

  • VACUUM

F.40.6. Security Event Fields #

Each security event is logged on a separate line, and the fields it contains depend on the format: CEF or CSV. The fields common to both formats are listed in Table F.26. The CEF syntax is designed specifically for log records, and events logged in CEF include additional fields. Such events consist of a standard header and a variable extension formatted as key-value pairs. A header contains the mandatory fields separated by | characters. These fields are listed in Table F.27. Additional fields of the CEF event extension part are listed in Table F.28.

Table F.26. Event Fields

CEF Field Tag or Tag Pair CSV Field Description
endlog_time Date and time of the event
duserrole_name Current user name
cs1Label=DB cs1=database_name Database name
dpidsession_pid Server process ID (PID)
cs2Label=Severity cs2=error_severity Event severity level: INFO or ERROR
cn1Label=CommandLine cn1=session_line_num Serial number of the command in a session
cn2Label=SubcommndLine cn2=session_line_subcommand_num Subcommand number in complex commands (CREATE TABLE ... AS SELECT ...)
cs3Label=EventType cs3=event_type Operator name or event name
cs4Label=ObjectType cs4=object_type Object type
cs5Label=ObjectName cs5=object_name Object name
outcomestatus Operator execution results: SUCCESS or FAILURE
reasonerror_message Additional information like an error message in case of FAILURE or connection parameters and the application name for the AUTHENTICATE event. Unfinished authentication attempts are marked as [EOF. No credentials provided]
cs6Label=QueryText cs6=query_text Text of the SQL command
cs7Label=QueryArgs cs7=query_args Parameters of the command (for example, for PREPARE)
cs8Label=SessionUser cs8=session_user Session user name
cs9Label=uuid cs9=uuid Unique ID of the event (in the UUIDv7 format)
cs10Label=xid cs10=xid Transaction ID (XID)
cs11Label=vxid cs11=vxid Virtual transaction ID (VXID)
fsizerows Number of affected rows
srcuser_ip IP address of the event source

An event contains the information about both session_user and current_user of the session. Therefore, it will be possible to identify the user, even if they execute the SET ROLE command to change the user identifier.

The XID and VXID values can be zero if an event does not relate to a transaction (e. g. the DISCONNECT event).

Table F.27. CEF Header Fields

Field Name Description
CEF: Version Version of the CEF format. In pg_proaudit logs, it is always 1.
Device Vendor Solution vendor. In pg_proaudit logs, it is always Postgres Pro.
Device ProductPostgres Pro edition.
Device Version Version of DBMS logging the event.
Device Event Class ID ID of the security event class.
Name Name of the security event. Its value can coincide with the Device Event Class ID.
Severity Level of the event severity. Possible values are 3 (INFO) and 7 (ERROR).

Table F.28. CEF Extension Fields

Field Name Description
sproc Name of the event source process
dst IP address of the event destination
dpt Port number of the event destination
dproc Name of the event destination process

F.40.7. Security Event Log Configuration Parameters #

The pg_proaudit extension provides several configuration parameters for managing security event log files. These parameters can be set in the postgresql.conf configuration file, or with the help of the ALTER SYSTEM command. For the changes to take effect, call the pg_reload_conf() function or restart the database server. For additional configuration, the syslog_ident and syslog_facility configuration parameters can be used.

pg_proaudit.log_destination (string) #

Defines the method for logging security events. Possible values are:

  • csvlog — log security events in a CSV file.

  • ceflog — log security events in a CEF file. CEF is an open text-based format designed for log records. It contains a standard header and a variable extension formatted as key-value pairs.

  • syslog — log security events in syslog.

  • cefsyslog — log security events in the CEF format in syslog.

  • ddllog — log DDL changes to permanent database objects to a separate CSV file named pg_proaudit_ddl.log. Unlike other log destinations, ddllog operates independently of pg_proaudit rules.

You can specify one or more values separated by commas. Note, however, that syslog and cefsyslog are mutually exclusive. If both are specified, cefsyslog takes precedence and syslog is ignored.

Default: csvlog

pg_proaudit.csv_split_messages (boolean) #

Controls whether security events in CSV and syslog output are written as a single line or multiple lines. When set to on, newline and carriage return characters are preserved, so events may span multiple lines. When set to off, newline and carriage return characters are escaped as \n and \r, respectively, ensuring that each security event is written on a single line. This simplifies log analysis and parsing, making it easier to filter, search, and integrate with external tools.

This parameter applies to both CSV log files and syslog output but does not affect CEF format, where newlines are always escaped according to the CEF standard.

Default: on

pg_proaudit.log_catalog_access (boolean) #

Specifies whether to log access to system catalog objects in the pg_catalog schema. For ddllog, catalog access is not logged, and this parameter has no effect.

When set to off, no events will be logged, even if there are rules for the CATALOG RELATION, CATALOG FUNCTION, or ALL object types.

It is not recommended to set the parameter to on, if you do not plan to log events for system catalog objects. Doing otherwise may impact Postgres Pro performance, even if there are no rules for the aforementioned object types.

Default: off

pg_proaudit.log_command_text (boolean) #

Specifies whether to log the SQL command text for security events. For ddllog, command text is always logged regardless of this parameter.

Default: on

pg_proaudit.log_directory (string) #

Specifies the path to the directory that stores CSV, CEF, and DDL log files. This can be an absolute path or a relative path to the cluster data directory (PGDATA). This parameter is used if the csvlog, ceflog, or ddllog value is specified in the pg_proaudit.log_destination parameter.

Default: pg_proaudit

pg_proaudit.log_file_mode (integer) #

Specifies permissions for new event log files created by pg_proaudit. Possible values range from 0600 to 0666.

The most restrictive value is 0600, which means that only the server owner (typically, the postgres user) can read or write event log files. The other commonly used setting is 0640, allowing members of the owner's group to read the files.

The least restrictive value is 0666, which provides read-write permissions to all users. However, when granting permissions to broader groups of users, keep in mind that log files may contain sensitive data.

Note that regardless of the parameter value, the server owner always retains access to the log files.

Default: 0600 (or 0640 when using initdb with -g)

pg_proaudit.log_filename (string) #

Defines the filenames of the created security event log files. The filename template can contain %-escapes, similar to the ones listed in the strftime specification of the Open Group (http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html). This parameter is used if the csvlog or ceflog value is specified in the pg_proaudit.log_destination parameter. For ddllog, the file name is always pg_proaudit_ddl.log and is not affected by this parameter.

Default: postgresql-%Y-%m-%d_%H%M%S.log

pg_proaudit.log_rotation_size (integer) #

Sets the maximum size of a log file, in kilobytes. When this size is achieved, pg_proaudit creates a new file for logging security events. If set to 0, disables size-based creation of new log files. This parameter is used if the csvlog or ceflog value is specified in the pg_proaudit.log_destination parameter. For ddllog, log rotation is disabled, and this parameter has no effect.

Default: 10MB

pg_proaudit.log_rotation_age (integer) #

Sets the maximum lifetime of a log file, in minutes. After this timeframe is elapsed, pg_proaudit creates a new file for logging security events. If set to 0, disables time-based creation of new log files. This parameter is used if the csvlog or ceflog value is specified in the pg_proaudit.log_destination parameter. For ddllog, log rotation is disabled, and this parameter has no effect.

Default: 1 day

pg_proaudit.log_rows (boolean) #

Specifies whether to log the number of rows affected by an SQL command.

Default: off

pg_proaudit.log_truncate_on_rotation (boolean) #

Specifies whether to truncate log files when logging is switched to an existing log file. If set to off, pg_proaudit appends new log entries to the end of the file. This parameter is used if the csvlog or ceflog value is specified in the pg_proaudit.log_destination parameter. For ddllog, log rotation is disabled, and this parameter has no effect.

Default: off

pg_proaudit.max_rules_count (integer) #

Specifies the maximum number of rules allowed. For the parameter changes to take effect, the database server must be restarted.

Default: 500

F.40.8. Viewing Security Event Log #

Security event log files are text files that can be viewed by the operating system facilities. To access log files using SQL, you can use the file_fdw extension — a foreign data wrapper for accessing files on the database server. To use this method, complete the following steps:

  1. Install the file_fdw and create an external server:

    CREATE EXTENSION file_fdw;
    CREATE SERVER pg_proauditlog FOREIGN DATA WRAPPER file_fdw;
    
  2. Create a foreign table, specifying the columns and the absolute path to the log file. The actual log file location is determined by the pg_proaudit.log_directory and pg_proaudit.log_filename parameters.

    CREATE FOREIGN 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,
            uuid text,
            xid text,
            vxid text,
            rows text,
            user_ip text )
    
    SERVER pg_proauditlog
    OPTIONS (filename 'absolute_file_path_to_log_file.csv', FORMAT 'csv' );
    

Make sure that the pg_proaudit.log_destination parameter contains the csvlog value, which enables writing security event logs to CSV files.

The pg_proaudit_ddl.log file has the same CSV format as the regular audit log, with an additional is_prepare_transaction column. This column indicates whether the DDL statement was executed as part of the PREPARE TRANSACTION command. When creating a foreign table for the DDL change log, include this column:

CREATE FOREIGN TABLE pg_proaudit_ddllog
          ( 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,
            uuid text,
            xid text,
            vxid text,
            rows text,
            user_ip text,
            is_prepare_transaction text )

    SERVER pg_proauditlog_ddllog
    OPTIONS (filename :'ddllog_filename',  FORMAT 'csv');

F.40.9. Examples #

As an example, let's set up logging for the following security events:

  • authentications/disconnections to the postgres database

  • all actions of the user if at least one of the user session attributes, session_user or current_user, is either explicitly set to the postgres role, or is directly or indirectly a member of this role

  • creating, updating, and deleting any tables

  • all operations on the app_table table that belongs to the public schema

All events must be logged in the CSV format and stored for a week. It is required to set up SQL access to the security event log. To complete the scenario, do the following:

In psql, check that the preliminary setup of the pg_proaudit extension is complete in the postgres database:

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

Add the following lines to the postgresql.conf configuration file:

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
pg_proaudit.log_rows = on

For the changes to take effect, run the following query:

SELECT pg_reload_conf();

Check that the following parameters are set as expected:

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;
SHOW pg_proaudit.log_rows;

Suppose your PGDATA environment variable points to the cluster data directory. Since the pg_proaudit.log_directory defines a relative path to the log files, they will be located in the $PGDATA/audit directory. Let's create an empty file for each day of the week and make them available to their owner only:

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 a table for reading log entries:

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,
  uuid text,
  xid text,
  vxid text,
  rows text,
  user_ip text
);

Install the file_fdw extension and create an external server:

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

Now let's create seven child foreign tables for the pg_proaudit_log table, for each day of the week:

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');

To set up logging for the required security events, connect to the postgres database and execute the following commands:

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

Create the app_table table and enable logging for all operations on this table:

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

Check that event logging is configured as expected:

SELECT * FROM pg_proaudit_settings;
 db_name  |  event_type  | object_type |   object_name    | role_name | exclude_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)

Save these logging rules into the pg_proaudit.conf file, so that they are not lost after the server restart:

SELECT pg_proaudit_save();

Let's run several queries on the app_table table:

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

Check the log entries for the app_table 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, rows, user_ip
FROM pg_proaudit_log
WHERE object_name = 'public.app_table';
       when        | current_usr_name | session_pid |  event_type  |                 query_text                  | session_usr_name | rows | user_ip
-------------------+------------------+-------------+--------------+---------------------------------------------+------------------+------+---------
 27.09.23 12:44:27 | postgres         | 2010        | CREATE TABLE | CREATE TABLE app_table (id int, name text); | postgres         | 0    |
 27.09.23 12:45:55 | postgres         | 2010        | INSERT       | INSERT INTO app_table VALUES (1, 'first');  | postgres         | 1    |
 27.09.23 12:46:00 | postgres         | 2010        | SELECT       | SELECT * FROM app_table;                    | postgres         | 1    |
(3 rows)

We have set up a weekly rotation of log files, with the log file switched once a day. It means that queries to the pg_proaudit_log table will return only those security events that have happened in the latest week. Older events will be automatically removed at log file rotation. To define additional access constraints for specific log entries, you can create separate views based on queries to the pg_proaudit_log table and grant read rights to such views using built-in Postgres Pro access control methods.

FAQ