Чего у меня не отнять, дак это мастерства заголовка...

В какой-то момент при локальной разработке (да, в общем-то и при тестировании на иных стендах) задумываешься о том, как бы избавиться от довольно монотонных действий. Одним из них является ввод пароля в рамках процесса аутентификации в PostgreSQL. В этой статье я расскажу как слегка автоматизировать данный процесс.

Данная статья является легким переосмыслением того, что я написал на медиуме. Ибо думать я продолжаю на русском (:

TL;DR исходники к вашим услугам.

В рамках любых взаимодействий мы сталкиваемся с такими сущностями как авторизация и аутентификация. Повторять в 100500 раз что есть что я не буду (но мне не лень такую длинную ремарку напечатать, ага). В рамках PostgreSQL первое обеспечивается через Roles, а второе через Privileges.

Если покопаться в документации PostgreSQL, то можно обнаружить, что эта БД поддерживает довольно много типов авторизации. Однако, нас интересует что-то, что могло бы заменить связку логина и пароля. И в этот момент авторизация на основе сертификатов приходит на гугл ум. В общем-то, при корректной настройке, мы не только избавляемся от необходимости ввода пароля для нашего пользователя, но и повышаем уровень защищенности (подделка сертификата немножко сложнее угадывания любимого пароля, который у меня - "qwerty").

Давайте подумаем над тем, чего мы хотим достичь. Но думать просто так скучно, поэтому запишем наши требования в формате пользовательских историй!

  1. Как пользователь, я хочу иметь доступный инстанс PostgreSQL.

  2. Как пользователь, я хочу иметь возможность залогиниться туда и выполнять доступные мне команды.

  3. Как пользователь, я ленив и не хочу вводить пароль (так и быть, согласен поставить флаг "Запомнить пароль" в соответствующем диалоге pgAdmin).

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

Начинаем готовить наш коктейль. Нам понадобятся:

  1. Docker-compose.

  2. OpenSSL.

  3. Любимый текстовый редактор, или IDE.

С учетом ингридиентов для нашего коктейля, давайте подумаем над сетевым взаимодействием элементов. Для начала, конечно же, определим их:

  1. Сервис PostgreSQL

  2. Сервис pgAdmin

  3. Любимый браузер

И попробуем отрисовать их взаимодействие:

1-2 - TLS канал, и сервер, и клиент проверяют сертификаты противоположной стороны. Это соединение mTLS

3-4 - Стандартная request / response последовательность, выполняемая в рамках канала, установленного в шаге 1-2.

5-6 - TLS канал, проверка сертификата осуществляется только на стороне клиента (браузера). Данное соединение является просто TLS, т.к. валидация сертификата выполняется только одной из сторон.

7-8 - Аналогично шагу 3-4, но исполняется в рамках канала, установленного в шаге 5-6.

Теперь мы видим, какие типы сертификатов нам понадобятся:

  • Корневой сертификат (1) для подписания сертификатов связанных с mTLS соединением

  • Сертификат для PostgreSQL сервиса, подписанный корневым сертификатом (1)

  • Сертификаты для каждого из пользователей, кто будет логиниться. Каждый сертификат должен быть подписан сертификатом (1)

  • Корневой сертификат (2) для подписания сертификатов, связанных с HTTPS соединением

  • HTTPS сертификат, подписанный корневым сертификатом (2)

Эмпирическим путем было установлено, что Apple Keychain не поддерживает корневые сертификаты с размером ключа больше чем 8192 бита (что очевидно следует из текста ошибки "Error: -67762"), так что, ограничимся этой размерностью.

Поехали генерировать сертификаты. Мы же программисты, поэтому, пишем скрипт и необходимый конфиг.

Для начала, определяем какие-то общие переменные:

Теперь можно сгенерировать корневой сертификат для целей mTLS:

Генерируем сертификат для PostgreSQL сервиса и подписываем его ранее полученным:

И генерируем по сертификату для каждого из пользователей:

Генерация сертификатов для HTTPS соединения точно такая же, за исключением того, что пользовательские сертификаты генерировать не нужно.

Немного посмотрим на конфигурацию. Конфигурационные файлы в данном случае имеют ini-подобную структуру, т.е. имеются разделы, а в них ключи и значения. При этом, значения могут ссылаться на какой-то из разделов. Например:

В данном случае, значение ключа basicConstraints ссылается на раздел mtls_root_basic_constraints в котором указано, что мы генерируем Certificate Authority (не смог вменяемый перевод вспомнить), который не может выдавать промежуточные сертификаты.

Применяемую секцию из конфигурационного файла мы передаем как значение параметра -extensions: -extensions v3_mtls_root.

Итак, все сертификаты сгенерированы. Пора конфигурировать PostgreSQL. Начнем с файла pg_hba.conf. Этот файл является частью системы аутентификации.

Наши требования довольно очевидны:

  1. Разрешаем любое локальное подключение изнутри контейнера

  2. Любое SSL соединение должно проходить полную валидацию пользовательского сертификата

  3. Иные соединения запрещены

Собственно, и содержимое данного конфигурационного файла это отражает:

Далее нам необходимо сконфигурировать параметры запуска сервиса, переменные окружения, пробросить порт и тома. Собственно, эта часть docker-compose.yml может выглядеть так:

Аналогичную конфигурацию, а именно: переменных окружения, проброса порта и томов мы проводим для нашего pgAdmin:

Собственно, из основного - пробрасываем пользовательские сертификаты и корневой сертификат mTLS. Так же пробрасываем серверный HTTPS сертификат. Ну и раз мы все локации сертификатов знаем, можем сразу же пробросить файл с настройками серверов (servers.json):

Итого, что мы имеем на выходе:

  • Сконфигурированный PostgreSQL проводящий валидацию на основе CN сертификата.

  • Сконфигурированный pgAdmin, который умеет устанавливать mTLS соединение с PostgreSQL и TLS соединение с браузером

Хочется думать, что данный контент оказался вам полезен. Всегда готов ответить вам по DEFAULT_EMAIL - не стесняйтесь.