Linux: PostgreSQL 9.x и сертификаты

Захотелось тут, чтобы определенный пользователь при авторизации в СУБД был избавлен от необходимости ввода пароля. Можно было бы указать, что соединения от имени этого пользователя - доверенные, но я почему-то предпочёл другой путь: авторизацию с использованием сертификата. Наверно, в последнее время слишком часто приходилось иметь с этими штуками дело, вот и решил пойти проторенной дорожкой.

Как я понял, PostgreSQL использует сертификаты в двух случаях: для установления защищенного соединения и для проверки подлинности пользователя. В любом случае используется информация:

1) адрес или имя сервера баз данных, по которому будет стучаться клиент, например, myserver

2) имя системного пользователя, который пытается авторизоваться, например, sysUser

3) имя пользователя базы данных, под которым пытается авторизоваться системный пользователь, например, pgUser

При этом затрагиваются следующие каталоги и файлы конфигурации:

1) На стороне сервера: файлы postgresql.conf, pg_hba.conf и pg_ident.conf. Эти файлы в зависимости от сборки могут располагаться в разных местах. Например, на дебиане они обнаружились в каталоге /etc/postgresql, а на слакваре при установке из исходных кодов обосновались в рабочем каталоге /usr/local/pgsql/data

2) На стороне клиента: каталог ~/.postgresql/, в который должна быть положена пара ключ-сертификат, использующаяся для проверки подлинности пользователя.

Итак, настраиваем все эти сертификаты. Так как я - по натуре нищеброд, то сертификаты у меня будут самоподписанные, а создавать их буду бесплатной утилитой openssl.

Сначала генерируем корневой сертификат, которым будем подписывать сертификаты сервера и клиента:

# генерируем приватный ключ
openssl genrsa -out root.key 1024

# создаем самоподписанный корневой сертификат
openssl req -new -x509 -days 1826 -key root.key -out root.crt

При этом будут заданы всякие вопросы, от ответов, я так понял, мало что зависит.

Затем при помощи этого корневого сертификата создаем сертификат сервера:

# генерируем приватный ключ
openssl genrsa -out server.key 1024

# создаем запрос сертификата
# тут надо внимательно отнестись к заполнению полей:
# в поле Common Name (CN) надо указать адрес или имя сервера баз данных
# (например, myserver)
openssl req -new -utf8 -key server.key -out server.csr

# из запроса создаём сертификат сервера, подписанный корневым сертификатом
openssl x509 -req -days 730 -in server.csr -CA root.crt -CAkey root.key -out server.crt

У нас получилось три ценных файла: root.crt, server.key, server.crt. Их нужно запихнуть в рабочий каталог postgresql (тот самый что-то-там/data, в котором при установке СУБД делалась команда initdb). При этом на файл server.key налагаются суровые ограничения по безопасности: он должен иметь владельцем пользователя, под которым работает СУБД, и уровень доступа -rw-------.

Теперь сгенерируем сертификат для клиента:

# генерируем приватный ключ
openssl genrsa -out postgresql.key 1024

# создаем запрос сертификата
# тут тоже надо внимательно отнестись к заполнению полей:
# в поле Common Name (CN) следует указать имя системного пользователя,
# для которого создаётся сертификат
# (например, sysUser)
openssl req -new -utf8 -key postgresql.key -out postgresql.csr

# из запроса создаём сертификат клиента, подписанный корневым сертификатом
openssl x509 -req -days 730 -in postgresql.csr -CA root.crt -CAkey root.key -out postgresql.crt -CAcreateserial

После этой процедуры два файла, postgresql.key и postgresql.crt, закидываем клиенту в папку ~/.postgresql/ и у файла postgresql.key опять-таки выставляем правильного владельца и права -rw-------.

Стоит отметить, что содержимое получившихся сертификатов, в частности, заполнение поля CN можно всегда посмотреть командой:

openssl x509 -in postgresql.crt -text -noout -nameopt oneline,-esc_msb,utf8

Так или иначе, осталось написать правильные настройки сервера баз данных:

1) в файле postgresql.conf проверяем, что включена опция ssl = on

2) прописываем в pg_hba.conf строку:

#TYPE    DATABASE  USER    ADDRESS   METHOD
hostssl  all       pgUser  myserver  cert clientcert=1 map=myMap

Тут мы указали, что соединение должно быть защищено ssl, для авторизации следует использовать сертификат клиента и при сопоставлении системного имени и имени пользователя баз данных будет использоваться запись с меткой myMap из файла pg_ident.conf

3) в файле pg_ident.conf прописываем строку:

#MAPNAME  SYSTEM-USERNAME  PG-USERNAME
myMap     sysUser          pgUser

На самом деле, как я понял, этот файл содержит довольно простую информацию: какому системному пользователю под каким именем СУБД разрешается подключаться к серверу. То есть, если пользователю позволено подключаться под разными именами, pg_ident.conf будет выглядеть так:

#MAPNAME  SYSTEM-USERNAME  PG-USERNAME
myMap     sysUser          pgUser1
myMap     sysUser          pgUser2

4) перезапускаем сервер PostgreSQL.

Процесс авторизации по этой схеме выглядит, насколько я понял, так:

1) Устанавливается защищенное соединение при помощи сертификатов server.key + server.crt

2) От клиента сервер получает сертификат postgresql.crt, из которого выясняет системное имя пользователя. Имя пользователя базы данных, под которым хочет зайти системный пользователь, указывается явно в строке подключения.

3) По имени пользователя базы данных, имени сервера и режиму соединения (hostssl) определяется строка в файле pg_hba.conf, из которой выясняется имя метки в файле pg_ident.conf.

4) По метке в файле pg_ident.conf устанавливается, может ли данный системный пользователь авторизоваться в СУБД под данным пользователем баз данных.

Вообще говоря, требование размещения клиентских сертификатов в каталоге ~/.postgresql/ не является таким уж обязательным. Просто их там по умолчанию ищет библиотека libpq. В произвольных же скриптах можно указывать явно, какие файлы использовать в качестве пары ключ-сертификат. Вот, например, скрипт на Python-е:

import psycopg2

connString = "host=myserver"             + \
             " user=pgUser"              + \
             " dbname=postgres"          + \
             " sslcert=./postgresql.crt" + \
             " sslkey=./postgresql.key"

conn = psycopg2.connect(connString)