В статье рассматривается логирование соединений с базами данных кластера PostgreSQL. Системы мониторинга создают сессии для сбора метрик и проверки доступности экземпляра. Это создаёт большое число записей в диагностическом журнале кластера, затрудняя его анализ. Приходится искать возможность отключения логирования для сессий мониторинга. Такая возможность есть только у параметра log_disconnections. В статье приводится пример, как для этого параметра отключить логирование при создании сессии. Также рассматриваются особенности использования расширений pgaudit и pgaudittofile, которые позволяют выводить логирование соединений в отдельный файл аудита, что удобно, так как не замусоривает файлы диагностического лога PostgreSQL.

Введение

Логирование соединений с экземпляром полезно для выявления, нет ли слишком частых соединений и коротких сессий. Могут встречаться приложения, работающие в режиме подсоединение-запрос-отсоединение. В СУБД PostgreSQL при создании сессии порождается процесс в операционной системе, выполняются подготовительные операции (аутентификация, проверка прав доступа, регистрация сигналов, выделение структур памяти), что относительно трудоёмко. Порождение сессии для выполнения одного запроса не оптимально и приводит к бесполезному использованию вычислительных ресурсов и памяти. Приложения мониторинга экземпляра PostgreSQL могут подсоединяется к базе данных с частотой раз в несколько секунд или десятков секунд, выполнять несколько запросов и отсоединяться. Логирование соединений позволяет выявить такие приложения. Достаточно логировать каждое соединение и его длительность.

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

Параметры конфигурации

Для логирования соединений есть параметры конфигурации:

log_connections=on 
log_disconnections=on
pgaudit.log_connections=on
pgaudit.log_disconnections=on

Параметр log_connections

Параметр log_connections=on записывает в диагностический журнал кластера попытки подключения к экземпляру, попытки аутентификации и успешную аутентификацию. Параметр может породить несколько диагностических записей в журнале, связанных с одним соединением. По умолчанию, параметр отключён. Изменить значение можно только на уровне кластера, хотя в документации декларируется возможность поменять значение до установления соединения, но это не так. также, ни на уровне роли, ни на уровне базы данных параметр не может устанавливаться:

postgres=# alter user alice set log_connections = on;
ERROR:  parameter "log_connections" cannot be set after connection start

Для применения значения достаточно перечитать конфигурацию:

alter system set log_connections = on;
select pg_reload_conf();

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

FATAL:  role "fff" does not exist

При включении параметра, к вышеприведённой строке с ошибкой, в лог кластера дополнительно добавятся строки:

LOG:  connection received: host="10.0.2.15"
LOG:  connection authorized: user=fff database=fff application_name=psql

К диагностическим сообщениям можно добавлять атрибуты параметром log_line_prefix, который может быть установлен только на уровне кластера. Для изменения параметра достаточно перечитать конфигурационные файлы. По умолчанию, значение параметра '%m [%p] ' и к сообщению добавляется дата, время и номер процесса в квадратных скобках:

2025-09-03 11:01:01.924 MSK [1773081]

Добавив значение %r или %h в параметр log_line_prefix = '%h ' можно добавить логирование IP-адреса или имени клиентского узла. IP адрес будет присутствовать в каждом сообщении:

10.0.2.15 FATAL:  role "fff" does not exist

Параметр log_disconnections

Параметр log_disconnections=on записывает в диагностический журнал одно сообщение при остановке серверного процесса, обслуживавшего сессию. В сообщении присутствует длительность сессии, что полезно для идентификации короткоживущих сессий. По умолчанию параметр отключён. Изменить значение можно на уровне кластера, а также, в отличие от параметра log_connections, параметр log_disconnections можно изменить перед созданием сессии на уровне сеанса:

export PGOPTIONS="-c log_disconnections=on -c work_mem=5MB"
psql -h 127.0.0.1 -c "show work_mem;"
 work_mem 
----------
 5MB

Пример сообщения в диагностическом журнале:

tail -n 1 $PGDATA/log/postgresql-* 
LOG:  disconnection: session time: 0:00:00.007 user=postgres database=postgres host=127.0.0.1 port=34298

Для приложений, использующих не libpq, а драйвер pgx (язык Go), параметр PGOPTIONS нельзя использовать, так как драйвер pgx имеет ограниченный функционал и распознаёт разве что переменные окружения: PGHOST PGPORT PGDATABASE PGUSER PGPASSWORD PGSSLMODE PGSSLCERT PGSSLKEY PGSSLROOTCERT PGAPPNAME PGCONNECT_TIMEOUT PGTARGETSESSIONATTRS.

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

Преимущество параметра в том, что если какая-то утилита или клиент, часто соединяется с базой данных, перед её запуском можно установить переменную окружения на клиентском узле и отключить логирование их сессий. Это уменьшает ненужные сообщения в диагностическом журнале кластера. Параметр log_connections таким образом не меняется, хотя в документации написано: "Only superusers and users with the appropriate SETprivilege can change this parameter at session start, and it cannot be changed at all within a session." Каким образом log_connections потерял возможность меняться при установке сессии, а log_disconnections не потерял, было ли это оформлено патчем неизвестно.

Пример использования строки соединения в формате URI, передача параметра log_disconnections работает корректно:

psql postgres://localhost:5432/postgres?options=-c%20synchronous_commit%3Dremote_write%20-c%20log_disconnections%3Don
postgres=# \q

Если к списку параметров добавить параметр application_name, то для успешного соединения нужно заключить строку с параметрами соединения в апострофы или двойные кавычки:

psql 'postgresql://localhost:5432/postgres?application_name=pmagent&options=-c%20synchronous_commit%3Dremote_write%20-c%20log_disconnections%3Don'
postgres=# \q

без которых соединение не установится. Это неочевидно и в "man psql" не описано. В документации PostgreSQL приводятся примеры без апострофов и кавычек.

Параметров, которые могут устанавливаться до создания сессии через PGOPTIONS (или options=-c параметр=значение) не много, всего 6:

select context, name from pg_settings where context like '%backend%' order by 1;
      context      |         name          
-------------------+-----------------------
 backend           | ignore_system_indexes
 backend           | post_auth_delay
 superuser-backend | jit_debugging_support
 superuser-backend | jit_profiling_support
 superuser-backend | log_connections
 superuser-backend | log_disconnections
(6 rows)

Диагностика частоты соединений с базой данных

log_disconnections=on записывает в лог событие завершения сессии. Записывается та же информация, что log_connections плюс длительность сессии. Преимущество в том, что выводится одна строка, что не замусоривает лог. Позволяет идентифицировать короткие по времени сессии. Короткие сессии приводят к частому порождению серверных процессов, что увеличивает нагрузку и снижает производительность:

LOG: disconnection: session time: 0:00:04.056 user=oleg database=db1 host=[vm1]

В примере длительность сессии 4 секунды.

log_connections=on записывает в лог попытки установить сессию. Недостаток в том, что для многих типов клиентов в журнал выводится две строки: первая строка об определении способа аутентификации (без пароля, с паролем), вторая строка - аутентификация. Если не используется балансировщик соединений (pgbouncer), то до аутентификации порождается серверный процесс, это трудоемкая операция. Параметр полезен для идентификации проблем, когда клиент безостановочно пытается подсоединиться с неверным паролем или к несуществующей базе или с несуществующей ролью. Недостаток в том, что неудачные попытки отличаются только дополнительной строкой FATAL:

LOG: connection received: host=[local]
LOG: connection authorized: user=postgres database=db2 application_name=psql 
FATAL: database "db2" does not exist
LOG: connection received: host=[local]
LOG: connection authorized: user=alice database=alice application_name=psql 
FATAL: role "alice" does not exist

Расширения pgaudit и pgaudittofile

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

В СУБД Tantor Postgres есть расширения pgaudit и pgauditlogtofile, которыми можно направить сообщения о создании сессий и их длительности в отдельный файл или файлы аудита. В ванильный PostgreSQL расширения не входят, но расширения можно самостоятельно собрать и подключить. Расширение pgauditlogtofile перенаправляет записи, создаваемые раширением pgaudit, в отдельный файл или файлы. Без расширения pgaudittofile записи pgaudit попадают в журнал кластера, что его замусоривает. Расширение pgauditlogtofile зависит от расширения pgaudit и не работает без pgaudit. Для использования расширений достаточно загрузить две библиотеки:

alter system set shared_preload_libraries = pgaudit, pgauditlogtofile;

и рестартовать экземпляр. Библиотеки расширений регистрируют в экземпляре параметры конфигурации, которыми можно настроить что и куда будет логироваться. Расширения работают независимо и параллельно с журналом кластера и управляются собственными параметрами, которые имеют префикс «pgaudit.»

У расширений 18 параметров конфигурации. 7 параметров относятся к библиотеке pgauditlogtofile, в том числе параметры pgaudit.log_connections и pgaudit.log_disconnections. Эти параметры аналогичны одноимённым параметрам PostgreSQL и могут создавать аналогичные записи, но только в отдельном файле аудита, а не в журнале кластера, в чём большое преимущество этих параметров.

Преимущество вывода сообщений в отдельный файл аудита перевешивает недостатки в виде необходимости загрузки двух библиотек и неудобств их использования. Параметры библиотеки устанавливаются только на уровне кластера, указание этих параметров в переменной окружения приводит к ошибке и невозможности соединиться (в отличие от стандартных параметров log_connections и log_disconnections):

export PGOPTIONS="-c pgaudit.log_connections=off"
psql 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  parameter "pgaudit.log_connections" cannot be changed now

Параметр pgaudit.log_disconnections , в отличие от параметра log_disconnections не может быть установлен при создании сессии.

Конфигурирование расширений pgaudit и pgaudittofile

Для использования расширений достаточно загрузить две библиотеки:

alter system set shared_preload_libraries = pgaudit, pgauditlogtofile;

и рестартовать экземпляр.

Устанавливать расширение pgauditlogtoile командой CREATE EXTENSION не нужно, так как в расширении нет обьектов:

create extension pgauditlogtofile;
\dx+ pgauditlogtofile 
Objects in extension "pgauditlogtofile"
--------------------
(0 rows)

Переменная подмены '%F' (или её эквивалент %Y-%m-%d) в названии журнала аудита и журнала кластера удобнее значения по умолчанию (%Y%m%d_%H%M) тем, что не создаёт отдельный файл при рестарте экземпляра. Новый файл создаётся раз в сутки. Пример установки значений параметрам:

alter system set pgaudit.log_filename = 'audit-%F.log'; 
alter system set log_filename = 'postgresql-%F.log';

Для того, чтобы файл аудита создавался, нужно, чтобы параметр pgaudit.log отличался от значения none, которое является значением по умолчанию. То есть для логирования создания сессий, нужно ещё что-то залогировать. Неудобство использования параметров расширений в том, что нужно установить параметр pgaudit.log как минимум в значение 'misc', чтобы создавался журнал аудита. Но значение 'misc' заставляет логировать команды DISCARD, FETCH, CHECKPOINT, VACUUM, SET и раздувает журнал аудита. При значении по умолчанию 'none' файл журнала не создаётся. При установке в значения 'role' и 'ddl' параметры pgaudit.log_connections и pgaudit.log_disconnections не действуют. Пример включения аудита соединений в отдельный файл:

alter system reset log_disconnections;
alter system reset log_connections;
alter system set pgaudit.log_connections = on;
alter system set pgaudit.log_filename = 'audit-%F.log';
alter system set pgaudit.log = 'misc';
select pg_reload_conf();

расширения не создают файл аудита, если pgaudit.log = none. Для логирования соединений и отсоединений нужно установить значение 'misc', без этого значения эти параметры не действуют.

Создадим сессию и посмотрим, появился ли файл аудита:

postgres@tantor:~$ psql -h 127.0.0.1 -c "show work_mem;"
 work_mem 
----------
 4MB
(1 row)

postgres@tantor:~$ tail -n 1  $PGDATA/log/audit-*
2025-09-03 12:13:44.562 MSK,"postgres","postgres",1072044,127.0.0.1:51678,67b843c8.105bac,1,SHOW
2025-09-03 12:13:44 MSK,6/302,0,00000,SESSION,1,1,MISC,SHOW,,,show work_mem,<not logged>,,,,,,,,,"psql"

Параметры расширения pgaudit.log_connections и pgaudit.log_disconnections ведут себя не так как их аналоги log_connections и log_disconnections. Они устанавливаются только на уровне кластера, указание этих параметров в переменной окружения приводит к ошибке и невозможности соединиться:

postgres@tantor:~$ export PGOPTIONS="-c pgaudit.log_connections=off"
postgres@tantor:~$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  parameter "pgaudit.log_connections" cannot be changed now
postgres@tantor:~$ export PGOPTIONS="-c pgaudit.log_disconnections=off"
postgres@tantor:~$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  parameter "pgaudit.log_disconnections" cannot be changed now
postgres=# alter role postgres set pgaudit.log_connections=off;
ERROR:  parameter "pgaudit.log_connections" cannot be changed now
postgres=# alter role postgres set pgaudit.log_disconnections=off;
ERROR:  parameter "pgaudit.log_disconnections" cannot be changed now

Заключение

Параметры конфигурации PostgreSQL log_connections и log_disconnections создают записи в общем журнале кластера, что неудобно и увеличивает размер журнала. Параметр log_disconnections можно изменить, передав значение параметра как опцию в процессе соединения. Удобно отключать логирование для сессий систем мониторинга, которые могут создавать соединения очень часто. Расширения pgaudit и pgaudittofile позволяют сохранять данные в отдельном файле. Это удобно при администрировании кластера баз данных, так как не замусоривает основной журнал сообщениями о создании и завершении сессий, что затрудняет анализ журнала.