PgBouncer 1.17 + PostgreSQL 14: transaction mode, SCRAM-SHA-256 и ловушки SSL

На кластере, который мы настраивали под PostgreSQL 14 ещё в январе, в мае начали прилетать жалобы: аналитики видят заметные задержки в начале утреннего рабочего дня, когда ETL-процессы ещё не отработали, а дашборды уже пытаются обновить данные. Разобрались - проблема не в vacuum и не в запросах. Проблема в том, что connection pooling там был минимальный, а PostgreSQL 14 при одновременном открытии большого числа соединений начинал тратить ощутимое время на handshake. Пора было сделать это нормально.

Почему PgBouncer и почему 1.17

PgBouncer в нашем стеке уже использовался, но в старых версиях и в session mode - по сути просто перенаправлял соединения без реального мультиплексирования. Для DWH с аналитической инфраструктурой данных и несколькими конкурирующими источниками запросов это почти бесполезно.

Transaction mode - другая история: одно серверное соединение обслуживает множество клиентских транзакций поочерёдно. Для аналитической нагрузки, где транзакции в основном read-only и не держат состояние между запросами, это хорошо работает.

PgBouncer 1.17 вышел в мае 2022 и закрыл давнюю проблему: наконец появилась нормальная поддержка SCRAM-SHA-256. Раньше для работы с PgBouncer приходилось держать пользователей на md5-аутентификации даже если PostgreSQL был настроен на SCRAM - это неприятный компромисс с точки зрения безопасности. Теперь PgBouncer может работать как SCRAM-proxy: клиент общается с баунсером по SCRAM, баунсер общается с PostgreSQL тоже по SCRAM, md5 уходит из схемы.

Дополнительно в 1.17 улучшили работу с TLS - в частности, корректно обрабатывается sslmode=require со стороны клиентов и server_tls_sslmode в сторону PostgreSQL. Это нам тоже нужно - кластер стоит в зоне с требованием шифрования трафика между компонентами.

Что настраивали

Конфигурация pgbouncer.ini для нашего случая:

[databases]
analytics = host=pg14-primary port=5432 dbname=analytics

[pgbouncer]
pool_mode = transaction
max_client_conn = 300
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# TLS к клиентам
client_tls_sslmode = require
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_key_file = /etc/pgbouncer/server.key

# TLS к PostgreSQL
server_tls_sslmode = require
server_tls_ca_file = /etc/pgbouncer/ca.crt

listen_addr = 0.0.0.0
listen_port = 5433

default_pool_size = 20 - это число серверных соединений к PostgreSQL на один пул баз данных. Для одного кластера с одной базой получается ровно 20 соединений на сервер вместо прежних 80-120 в пиках. max_client_conn = 300 - клиентских соединений PgBouncer принимает до 300, и жонглирует ими через 20 серверных.

Ловушки, на которые наступили

SCRAM и userlist.txt. Это первое, что сломалось. В старых версиях userlist.txt содержал md5-хеш пароля. Для SCRAM нужен либо открытый пароль (нежелательно), либо verifier в формате SCRAM-SHA-256. PgBouncer 1.17 умеет вытаскивать verifier из PostgreSQL через auth_query - это правильный путь. Настраивается так:

auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
auth_user = pgbouncer_auth

Специальный пользователь pgbouncer_auth должен иметь право на чтение pg_shadow - это суперпользовательская таблица, поэтому нужен отдельный GRANT:

CREATE USER pgbouncer_auth WITH PASSWORD 'сложный-пароль';
GRANT pg_read_all_stats TO pgbouncer_auth;
-- pg_shadow требует суперпользователя или отдельной роли:
GRANT pg_read_all_data TO pgbouncer_auth; -- недостаточно для pg_shadow
-- Реальное решение:
CREATE OR REPLACE FUNCTION public.pgbouncer_get_auth(p_usename name)
RETURNS TABLE(username name, password text)
SECURITY DEFINER SET search_path = pg_catalog AS $$
  SELECT usename, passwd FROM pg_shadow WHERE usename = p_usename;
$$ LANGUAGE sql;

И в pgbouncer.ini указываем эту функцию через auth_query. Это стандартный паттерн, но нигде в документации 1.17 он не разжёван достаточно подробно для нового читателя.

Transaction mode и SET-переменные. Несколько аналитических запросов использовали SET work_mem = '...'; SELECT ...; в рамках одной сессии. В transaction mode каждая транзакция может уйти к другому серверному соединению, и SET, выставленный в предыдущей транзакции, теряется. PgBouncer честно это документирует, но разработчики запросов про это не знали. Пришлось переписать запросы на SET LOCAL внутри явной транзакции или вынести work_mem в параметры конкретных запросов через /*+ Set(work_mem ...) */ где это поддерживалось.

SSL и sslmode=verify-full со стороны клиентов. Несколько BI-инструментов настраивали соединение с sslmode=verify-full и проверяли hostname в сертификате PgBouncer. Пришлось выдать сертификат с правильным CN и добавить SAN. Не сложно, но этот шаг не очевиден если ставишь PgBouncer «по-быстрому».

server_tls_sslmode = require без CA. PgBouncer 1.17 по умолчанию не проверяет сертификат сервера PostgreSQL даже при require - для проверки нужен verify-ca или verify-full с указанием server_tls_ca_file. Мы поставили verify-ca и указали CA, иначе шифрование есть, а аутентификация сервера - нет, что немного бессмысленно.

Где сейчас

PgBouncer 1.17 в transaction mode работает на кластере около трёх недель. Пиковое число соединений к PostgreSQL сократилось с ~120 до стабильных 20-25 (иногда чуть выше за счёт reserve pool). Утренние задержки при старте аналитических дашбордов исчезли - точнее, время на connection setup перестало быть заметным на фоне времени выполнения запросов, что и является целью.

SCRAM-аутентификация работает прозрачно - клиенты ничего не почувствовали кроме того, что md5 в pg_hba.conf теперь нет. Это правильно.

Transaction mode - не серебряная пуля. Если у кластера появятся приложения с долгими транзакциями или активным использованием временных таблиц - придётся либо выносить их в отдельный пул в session mode, либо думать дальше. Пока нагрузка аналитическая и в основном read-only, всё работает как надо.