До этого урока мы сидели внутри базы и говорили SQL. Теперь смотрим снаружи: как приложение вообще попадает на сервер postgresql, кого пускать, по какому каналу и сколько коннектов держать открытыми. Тут три отдельных слоя, которые путают: на каком адресе сервер слушает (listen_addresses), кого и каким методом он пускает (pg_hba.conf), и как это шифруется (TLS/SSL). А поверх всего этого - пул соединений, без которого боевой бэкенд под нагрузкой просто положит базу. Разберём прод-подключение целиком, от сокета до PgBouncer.
Как это работает
Когда клиент стучится в postgres, проверка идёт в два этапа, и их легко перепутать. Сначала надо физически дойти до порта - за это отвечает параметр listen_addresses в postgresql.conf. По умолчанию там localhost, то есть сервер слушает только себя, и удалённое приложение получит connection refused, даже не дойдя до пароля. Ставят listen_addresses = '*' (слушать все интерфейсы) и перезапускают сервер - этот параметр на лету не подхватывается.
Дальше начинается pg_hba.conf - host-based authentication, файл правил доступа. Каждая строка это запись вида: тип соединения, база, пользователь, адрес клиента, метод аутентификации. Сервер читает файл сверху вниз и применяет ПЕРВУЮ подходящую строку. Это ключевой момент: не самую точную, а первую совпавшую. Если она отказала - всё, второго шанса нет, до следующих строк дело не дойдёт.
Тип соединения определяет канал. local - это Unix-сокет (только с той же машины), host - TCP по обычному или шифрованному каналу, hostssl - только зашифрованный TLS, hostnossl - наоборот, только нешифрованный. На проде для внешних клиентов используют hostssl, чтобы пароль и данные не шли открытым текстом.
Метод аутентификации - самое важное поле. scram-sha-256 это современный стандарт: пароль не передаётся по сети даже в хешированном виде, идёт обмен вызов-ответ (challenge-response), и подслушанный трафик не даёт злоумышленнику войти. md5 - старый метод, его постепенно выкидывают, держите только для совместимости. peer работает лишь для local: postgres спрашивает у операционной системы, под каким юзером запущен процесс, и сверяет с именем роли - удобно для локального обслуживания без пароля. cert требует, чтобы клиент предъявил свой TLS-сертификат, которому доверяет сервер - аутентификация вообще без паролей. И помните про trust (пускать без проверки) и reject (всегда отказывать) - trust на сетевом интерфейсе это дыра, через которую входят без пароля.
SQL и примеры
Посмотреть, как сервер РАЗОБРАЛ свой pg_hba.conf, можно не открывая файл - есть системное представление:
Код: Выделить всё
SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
ORDER BY line_number;
Типичный боевой pg_hba.conf для приложения выглядит так (это содержимое файла, не SQL):
Код: Выделить всё
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
hostssl bookings app_user 10.0.0.0/24 scram-sha-256
hostssl bookings readonly 10.0.0.0/24 scram-sha-256
host all all 0.0.0.0/0 reject
После правки файла перезапуск НЕ нужен - достаточно перечитать конфигурацию:
Код: Выделить всё
SELECT pg_reload_conf();
Проверить, что вы сидите внутри по шифрованному каналу:
Код: Выделить всё
SELECT ssl, version, cipher
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();
Со стороны клиента шифрование требуют параметром sslmode. Строка подключения для приложения:
Код: Выделить всё
host=db.internal port=5432 dbname=bookings user=app_user sslmode=verify-full sslrootcert=/etc/ssl/ca.crt
Частые грабли
- Поправили pg_hba.conf, но не перечитали конфиг - правила старые. reload, а не просто сохранить файл.
- Забыли про порядок строк: широкое правило стоит выше узкого и перехватывает соединение раньше. Первая совпавшая строка - финальная.
- listen_addresses оставили localhost и удивляются connection refused снаружи. Это не про пароль, это про сетевой доступ.
- Открыли host all all 0.0.0.0/0 trust для удобства тестов и забыли убрать - вход без пароля из любой точки.
- sslmode=require вместо verify-full: канал шифрован, но сервер никто не проверяет, MITM проходит.
- Подняли max_connections до 2000 вместо пула. Каждый коннект postgres это отдельный процесс с памятью - сервер захлебнётся на переключениях контекста.
- PgBouncer в transaction pooling, а приложение использует SET, подготовленные выражения на сессию или advisory-блокировки - они утекают между клиентами и ломаются.
- Выполните SELECT * FROM pg_hba_file_rules; и найдите строку, по которой пускают вас сейчас.
- Командой SHOW listen_addresses; и SHOW ssl; посмотрите, на каких адресах сервер слушает и включён ли TLS.
- Создайте роль: CREATE ROLE app_user LOGIN PASSWORD 'secret'; и добавьте в pg_hba.conf строку host bookings app_user 127.0.0.1/32 scram-sha-256.
- Перечитайте конфиг: SELECT pg_reload_conf(); - и снова загляните в pg_hba_file_rules, убедитесь, что ошибок нет.
- Подключитесь новым пользователем: psql 'host=127.0.0.1 dbname=bookings user=app_user' и введите пароль.
- Внутри проверьте канал: SELECT ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();
- Замените метод на reject, сделайте reload и убедитесь, что вход теперь отклоняется - а потом верните scram-sha-256.
- Чем отличаются listen_addresses и pg_hba.conf - за что отвечает каждый и в каком порядке срабатывают?
- Почему scram-sha-256 безопаснее md5, если в обоих случаях пользователь вводит пароль?
- По какому правилу сервер выбирает строку в pg_hba.conf, если подходят несколько?
- Чем sslmode=require отличается от verify-full и почему второй важен на проде?
- Зачем нужен пул соединений и в чём разница между transaction и session pooling в PgBouncer?
- Какие изменения конфигурации применяются через reload, а какие требуют рестарта сервера?
