=====================================================================
Настройка PostgreSQL на аутентификацию клиентов только по сертификату
=====================================================================

Оригинальная документация по теме
=================================

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

`17.9. Secure TCP/IP Connections with SSL <https://www.postgresql.org/docs/9.5/ssl-tcp.html>`_

`18.3.2. Security and Authentication <https://www.postgresql.org/docs/9.5/runtime-config-connection.html#GUC-SSL-CIPHERS>`_

И вспомогательные:

`19.3.9. Certificate Authentication <https://www.postgresql.org/docs/9.5/auth-methods.html#AUTH-CERT>`_

`19.2. User Name Maps <https://www.postgresql.org/docs/9.5/auth-username-maps.html>`_

`19.1. The pg_hba.conf File <https://www.postgresql.org/docs/9.5/auth-pg-hba-conf.html#EXAMPLE-PG-HBA.CONF>`_

`20.12. Certificate Authentication <https://www.postgresql.org/docs/11/auth-cert.html>`_

Введение
========

Для включения режима аутентификации клиентов по сертификатам, потребуется произвести следующие изменения в настройках сервера postgresql:

- Разместить на сервере файлы: сертификата сервера, ключа сертификата сервера, доверенный корневой сертификат (для валидации клиентских сертификатов)
- Внести изменения в файлы конфигурации: **postgresql.conf**, **pg_hba.conf**, **pg_ident.conf**

Установка сертификатов на сервер
================================

Необходимо разместить в каталоге **pgdata** трех файла:

- | **postgresql.crt**
  | Сертификат сервера
- | **postgresql.key**
  | Ключ сертификата сервера (без шифрования контента)
- | **robin_CA.crt**
  | Доверенный корневой сертификат (для валидации клиентских сертификатов)

Внести изменения в файлы конфигурации:

- | **postgresql.conf**
  | Укажем размещений файлов сертификатов и разрешим их использование
- | **pg_hba.conf**
  | Включим требование на обязательное использование клиентом сертификата при аутентификации
- | **pg_ident.conf**
  | Смапируем имена клиентов на логины БД

Подготовка файлов сертификатов
------------------------------

Создание файла серверного сертификата
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Для подготовки серверного сертификата воспользуемся процедурой описанной в разделе "Сертификат сервера" страницы :doc:`Создание и установка сертификатов сервера приложений WildFly (JBoss EAP) </ssl_wildfly>`, включая момент скачивания файла сертификата в формате CER, со страницы УЦ. Переименуем скачанный файл в postgresql.cer. Теперь необходимо преобразовать формат файла в CRT. Для этого воспользуемся утилитой openssl:

Конвертация формата файла сертификата CER → CRT
::
 openssl x509 -inform DER -in postgresql.cer -out postgresql.crt

Таким образом мы получили первый из необходимых файлов - **postgresql.crt**

Создание файла приватного ключа серверного сертификата
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Теперь необходимо получить незашифрованный приватный ключ. В данный момент он размещен в хранилище - файле server.keystore. Для его извлечения так же воспользуемся утилитой openssl (в две операции):

 1) Экспортируем приватный ключ из хранилища в файл формата PKCS12
 ::
  keytool -v -importkeystore -srckeystore server.keystore -storepass secret -srcalias selfsigned-cert -destkeystore postgresql.p12 -deststoretype PKCS12
 2) Извлечем приватный ключ из файла PKCS12 в незашифрованном виде
 ::
  openssl pkcs12 -in postgresql.p12 -nocerts -nodes -out postgresql.key

Таким образом мы получили второй из необходимых файлов - **postgresql.key**

Создание файла клиентского корневого доверенного сертификата
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Третий файл скачиваем с частного Центра сертификации по ссылке: http://172.28.4.14/certsrv/certcarc.asp

Далее конвертируем формат файла сертификата (CER → CRT):
::
 openssl x509 -inform DER -in robin_CA.cer -out robin_CA.crt

Таким образом мы получили третий и последний из необходимых файлов - **robin_CA.key**

Теперь, разместив все полученные файлы в директории pgdata, перейдем к правке конфигурационных файлов сервера БД.

Внесение правок в файлы конфигурации
------------------------------------

postgresql.conf (Укажем размещений файлов сертификатов и разрешим их использование)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

В секции SSL, необходимо ключить использование SSL и указать имена файлов сертификатов и приватный ключ

.. image:: images/image-sp-1.png

pg_hba.conf (Включим требование на обязательное использование клиентом сертификата при аутентификации)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Отключим аутентификацию по паролю (закомментировав соответствующую строку)

И включим аутентификацию по сертификату, добавив строку с ключом hostssl. В методе аутентификации указано: "cert" - аутентификация по сертификату, "map=robin" - использовать карту мапирования "robin" (из файла pg_ident.conf), "clientcert=1" - требовать от клиента предоставить клиентский сертификат.

.. image:: images/image-sp-2.png

pg_ident.conf (Мапинг имени клиента на логин БД)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Пример, приведенный на скриншоте, не очень удачный. Используется имя группы, вместо логина юзера. Но так получилось, потому что в сертификате было указано два поля CN, в одном было имя группы, в другом логи пользователя. Сервер подхватил имя группы, поэтому пришлось прописать сюда его. Что может быть удобно в некоторых сценариях организации коннекта к БД.

.. image:: images/image-sp-3.png

Пример использования клиентом сертификата для аутентификации
============================================================

.. image:: images/image-sp-4.png

Клиентский сертификат получаем аналогично сертификату сервера.

Создаем самоподписанный сертификат, подписываем его в УЦ (шаблон сертификата - User), конвертируем в crt.

Приватный ключ получаем из соответствующего самоподписанного сертификата.

Указание доменного имени при коннекте
-------------------------------------

Т.к. в CN серверного сертификата указано конкретное доменное имя, клиент должен обязательно использовать данное доменное имя при установлении соединения

.. image:: images/image-sp-5.png

Предоставление файлов клиентского сертификата и ключа
-----------------------------------------------------

Как видно на скриншоте, при настройке коннекта на клиенте, указываются пути к файлам сертификата в формате CRT и к файлу с незашифрованным приватным ключом.

Предоставление файла доверенного корневого серверного сертификата
-----------------------------------------------------------------

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

Полезные команды OpenSSL
========================

Конвертация формата файла сертификата
-------------------------------------
::

 openssl x509 -inform DER -in postgresql.cer -out postgresql.crt

Экспорт приватного ключа в PKCS12
---------------------------------
::

 keytool -v -importkeystore -srckeystore server.keystore -storepass secret -srcalias selfsigned-cert -destkeystore postgresql.p12 -deststoretype PKCS12

Извлечение приватного ключа в незашифрованном виде
--------------------------------------------------
::

 openssl pkcs12 -in postgresql.p12 -nocerts -nodes -out postgresql.key