# Занятие 06: Методы аутентификации / Использование SSL

> Исходная страница в нативном Markdown: Методы аутентификации / Использование SSL

---

Индекс LLMS: [llms.txt](/ru/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета.
> **HTML source:** [dbs_06_auth_ssl.html](https://edu.postgrespro.ru/16/dbs-16/dbs_06_auth_ssl.html)
> **Archive:** [DBS-handouts-16.zip](https://edu.postgrespro.ru/16/DBS-handouts-16.zip)
> **Страниц PDF:** 29

## Page 1: Методы аутентификации / Использование SSL {#source-page-001}

### Авторские права

© Postgres Professional, 2017–2026 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Алексей Береснев Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
### Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.
### Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru
### Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

## Page 2: Темы {#source-page-002}

- Введение в прикладную криптографию
- Подключение SSL: защита трафика и аутентификация

## Page 3: Введение в криптографию {#source-page-003}

**Симметричное и асимметричное шифрование**
- Цифровая подпись
- Сертификат
- Цепочка сертификатов

## Page 4: Симметричный шифр {#source-page-004}

![Source slide page 4: Симметричный шифр](images/page-004.png){#source-figure-004 caption="Source slide page 4: Симметричный шифр"}

**Задача: защита трафика от прослушивания**

### Комментарии преподавателя

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

Начнем со схемы с симметричным шифрованием. В ней один и тот же ключ используется и для шифрования, и для расшифровывания. Алиса может зашифровать сообщение и переслать его Бобу по незащищенному каналу связи. Если Чарли перехватит такое сообщение, он не сможет расшифровать его, не имея ключа.

Симметричное шифрование работает быстро, но Алиса и Боб должны каким-то образом получить идентичные ключи. Ключ может сгенерировать один из них (например, Алиса), но она не может передать ключ по незащищенному каналу, поскольку Чарли сможет перехватить его. Для обмена ключами подходит только надежный канал — например, передать при личной встрече на флешке. Во многих случаях это неудобно.

## Page 5: Асимметричный шифр {#source-page-005}

![Source slide page 5: Асимметричный шифр](images/page-005.png){#source-figure-005 caption="Source slide page 5: Асимметричный шифр"}

**Задача: коммуникация при отсутствии общего секрета**
  - A
  - A
  - B

### Комментарии преподавателя

Поэтому еще в 1976 году была придумана схема с асимметричным шифрованием.

Алиса генерирует пару ключей: закрытый (приватный, темный на рисунке) и открытый (публичный, светлый). Закрытый ключ она хранит только у себя, а публичный раздает всем, включая Чарли.

То же делает Боб.

В самом упрощенном виде асимметричная криптография работает так: если сообщение зашифровано открытым ключом, расшифровать его может только закрытый. Наоборот — открытый ключ расшифровывает зашифрованное только соответствующим закрытым. Под «шифрованием» здесь понимаются математические преобразования, в которые мы не будем вдаваться.

Итак, Алиса зашифровывает документ открытым ключом Боба. Расшифровать его может только Боб, у которого есть его закрытый ключ.

Задача передавать сообщения так, чтобы их не мог расшифровать Чарли, решена.

## Page 6: Цифровая подпись {#source-page-006}

![Source slide page 6: Цифровая подпись](images/page-006.png){#source-figure-006 caption="Source slide page 6: Цифровая подпись"}

**Задача: защита от подмены**
  - A3F602
  - A3F602
  - A3F602
  - A
  - A
  - A
  - B

### Комментарии преподавателя

Но есть другая проблема: поскольку открытый ключ Боба есть у всех (в том числе и у Чарли), Чарли может отправлять сообщение Бобу, прикидываясь Алисой (а Алисе — прикидываясь Бобом; такая атака называется MITM — Man-in-the-Middle). Как Бобу убедиться в том, что сообщение отправила именно Алиса?

Алиса вычисляет хеш-функцию от документа, который она переслала Бобу, и зашифровывает это хеш-значение своим закрытым ключом. Такое сообщение называется цифровой подписью Алисы.

Боб расшифровывает сообщение открытым ключом Алисы — таким образом он уверен, что только Алиса могла его послать, ведь закрытого ключа Алисы больше ни у кого нет.

Затем Боб вычисляет хеш-функцию от полученного ранее документа и сравнивает с тем значением, которое получил от Алисы. Если значения совпадают, Боб уверен, что исходный документ был отправлен Алисой и дошел до него в неизменном виде.

Алиса может отправить подпись вместе с документом, зашифровав и то, и другое открытым ключом Боба, но даже если отправить подпись отдельно, ничего страшного не случится. Чарли имеет открытый ключ Алисы и сможет прочитать хеш-значение, но он не сможет восстановить по нему исходный документ.

## Page 7: Сертификат {#source-page-007}

![Source slide page 7: Сертификат](images/page-007.png){#source-figure-007 caption="Source slide page 7: Сертификат"}

**Проблема доверия**
  - A
- A A
  - B
  - C

### Комментарии преподавателя

Но есть и еще одна проблема. Откуда Боб знает, что открытый ключ Алисы, который он получил (например, скачал с сайта Алисы), действительно принадлежит Алисе, а не сфабрикован Чарли (который мог взломать сайт)? Доверять можно только информации, которую получил лично от знакомого человека. А ведь существуют тысячи Алис и Бобов, каждый из которых не может знать в лицо всех остальных.

Проблему доверия решают центры сертификации (ЦС или CA, Certification Authority), называемые иначе удостоверяющие центры. У Алисы и Боба должен быть посредник, которому они оба доверяют и открытый ключ которого имеют. Такой ключ, конечно, должен передаваться по надежному каналу связи. Это неудобно, но требуется всего один раз.

Алиса обращается в центр сертификации. Центр проверяет личность Алисы и подписывает ее открытый ключ и метаинформацию о владельце (имя, срок действия ключа и т. п.) своим закрытым ключом. Такой документ называется сертификатом.

Теперь Алиса может послать Бобу свой сертификат. Он расшифрует его открытым ключом центра сертификации, получит открытый ключ Алисы и удостоверится, что он действительно принадлежит Алисе.

## Page 8: Сертификат {#source-page-008}

![Source slide page 8: Сертификат](images/page-008.png){#source-figure-008 caption="Source slide page 8: Сертификат"}

**Задача: перевыпуск сертификатов**
  - A
- A A A B B
  - B
  - C
  - R
  - C
  - R
  - Алиса

### Комментарии преподавателя

На практике вместо открытого ключа ЦС используют сертификат ЦС, который содержит открытый ключ ЦС и метаинформацию, подписанные закрытым ключом вышестоящего ЦС. В принципе, достаточно и просто открытого ключа, но сертификат более удобен: в частности, он содержит срок действия ключа. Сертификат корневого ЦС подписывается ключом самого корневого ЦС, такой сертификат называется самоподписанным.

Несколько центров сертификации могут образовывать иерархию. Например, свои ЦС могут иметь разные подразделения компании.

Промежуточный ЦС, удостоверяя личность Алисы, подписывает ее открытый ключ и метаинформацию своим закрытым ключом. Вместе со своим сертификатом Алиса получает и всю цепочку сертификатов промежуточных ЦС.

У Боба могут отсутствовать сертификаты промежуточных ЦС, поэтому Алиса, подтверждая идентичность, вместе со своим сертификатом посылает Бобу и полученную цепочку сертификатов промежуточных ЦС. Таким образом Боб может проверить все сертификаты в цепочке вплоть до корневого, сертификат которого у него имеется.

Цепочка доверия позволяет менять ключи промежуточных ЦС (периодически для профилактики или в случае компрометации), не перегенерируя вообще все ключи в компании.

Далее мы рассмотрим, как эти технологии применяются на практике.

## Page 9: Подключение SSL {#source-page-009}

**Схема SSL-подключения**
- Настройки сервера
- Настройки клиента
- Проверка сервера

## Page 10: Схема SSL-подключения {#source-page-010}

![Source slide page 10: Схема SSL-подключения](images/page-010.png){#source-figure-010 caption="Source slide page 10: Схема SSL-подключения"}

**TLS-рукопожатие**
  - A
  - A
  - C

### Комментарии преподавателя

Трафик между клиентом и сервером может быть перехвачен программами-снифферами, такими, как tcpdump или Wireshark. Если трафик не защищен, злоумышленник увидит все команды и ответы сервера (и даже пароли в случае метода аутентификации password).

PostgreSQL поддерживает криптографическую защиту SSL (Secure Sockets Layer). Это историческое название протокола; сейчас он называется TLS (Transport Layer Security), но старое название привычно и по-прежнему широко используется. PostgreSQL позволяет использовать разные библиотеки, но наиболее популярной является OpenSSL: <https://docs.openssl.org/master/man7/ossl-guide-introduction/>

Когда клиент подключается к серверу, он с помощью клиент-серверного протокола может попросить (или потребовать) защищенное SSL-соединение. Если сервер отвечает, что готов к SSL-соединению, выполняется так называемое TLS-рукопожатие:

1. Клиент пересылает серверу свой открытый ключ и набор параметров, которые он поддерживает (версию протокола TLS, доступные алгоритмы шифрования и т. п.).

2. В ответ сервер отправляет клиенту свой сертификат и набор параметров, которые он выбрал для продолжения общения из предложенных клиентом. Из сертификата клиент извлекает открытый ключ сервера, по метаинформации он также может проверить, что подключился именно к тому серверу, к которому ожидал.

TLS-рукопожатие требует нескольких пересылок между клиентом и сервером и может занимать десятки миллисекунд.

## Page 11: Схема SSL-подключения {#source-page-011}

![Source slide page 11: Схема SSL-подключения](images/page-011.png){#source-figure-011 caption="Source slide page 11: Схема SSL-подключения"}

**Защита трафика**

### Комментарии преподавателя

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

Далее порожденный обслуживающий процесс выполняет аутентификацию и в случае успеха начинает сеанс.

## Page 12: Настройки клиента {#source-page-012}

**Параметр sslmode библиотеки libpq**
  - disable
  - не использовать SSL
  - allow
  - попытка соединиться без SSL,
  - при неудаче — попытка использовать SSL
  - prefer
  - попытка использовать SSL,
  - при неудаче — попытка соединиться без него
  - (по умолчанию)
  - require
  - потребовать SSL

### Комментарии преподавателя

При подключении клиент высказывает свои пожелания по использованию SSL. Они определяются значением параметра sslmode библиотеки libpq, которое может, например, указываться в строке подключения.

Клиент может запретить SSL-соединение (disable) или потребовать его (require). Если сервер не разрешает желаемое соединение, оно не будет установлено.

Клиент также может объявить, что готов на любой вариант, и установить предпочтения: сначала просить либо SSL-соединение (prefer, по умолчанию), либо незащищенное соединение (allow). Какое соединение будет установлено, зависит в таком случае от возможностей сервера.

(Есть еще два значения параметра sslmode, verify-ca и verify-full, которые будут рассмотрены ниже на слайде «Проверка сервера»).

<https://postgrespro.ru/docs/postgresql/16/libpq-ssl>

<https://postgrespro.ru/docs/postgresql/16/libpq-connect#LIBPQ-CONNECT-SSLMODE>

## Page 13: Настройки сервера {#source-page-013}

**Параметры**
  - ssl = off
  - разрешены ли подключения SSL
  - ssl\_ca\_file
  - файл с сертификатами ЦС
  - ssl\_cert\_file = server.crt
  - файл с сертификатом сервера
  - ssl\_key\_file = server.key
  - файл с закрытым ключом сервера
- Типы сетевых подключений в pg\_hba.conf
  - hostnossl
  - подключение без SSL
  - hostssl
  - подключение с SSL
  - host
  - любое сетевое подключение

### Комментарии преподавателя

Минимально необходимые параметры настройки сервера для SSL:

● ssl — разрешает подключаться к серверу, используя SSL;

● ssl\_ca\_file — имя файла с сертификатами ЦС;

● ssl\_cert\_file — файл, содержащий сертификат сервера;

● ssl\_key\_file — файл с закрытым ключом сервера.

Могут быть указаны и другие параметры, указывающие расположение списка отзыва сертификатов, настройки преобразования и прочее.

<https://postgrespro.ru/docs/postgresql/16/ssl-tcp#SSL-SETUP>

<https://postgrespro.ru/docs/postgresql/16/runtime-config-connection#GUC-S> SL

Можно ограничиться простым самоподписанным сертификатом сервера, как это показано в документации:

<https://postgrespro.ru/docs/postgresql/16/ssl-tcp#SSL-CERTIFICATE-CREA> TION

Если параметры сервера допускают SSL-подключения, возможность установки такого соединения зависит и от настроек в pg\_hba.conf, как рассматривалось в теме «Подключение и аутентификация». Записи с типом hostssl соответствуют SSL-подключению, а host — любому сетевому подключению, в том числе и SSL. Записи с типом hostnossl относятся к подключению без SSL; такие подключения разумно разрешать только в защищенных сетях, для которых SSL излишен.

<https://postgrespro.ru/docs/postgresql/16/auth-pg-hba-conf>

## Page 14: Настройки сервера {#source-page-014}

В Ubuntu конфигурация по умолчанию использует сертификат и ключ, созданные при установке ОС:
```text
student=# \dconfig ssl|ssl_ca_file|ssl_cert_file|ssl_key_file
```
```text
            List of configuration parameters
   Parameter   |                 Value
---------------+----------------------------------------
 ssl           | on
 ssl_ca_file   |
 ssl_cert_file | /etc/ssl/certs/ssl-cert-snakeoil.pem
 ssl_key_file  | /etc/ssl/private/ssl-cert-snakeoil.key
(4 rows)
```
Сертификат является самоподписанным: он выдан без участия центра сертификации. Такой сертификат можно использовать для шифрования трафика, но не для проверки аутентичности сервера. Поэтому мы будем использовать другой набор файлов, подготовленный для курса в виртуальной машине:
/etc/pgssl/root/root.crt — корневой сертификат центра сертификации; /etc/pgssl/serv/serv.crt — сертификат сервера PostgreSQL; /etc/pgssl/serv/serv.key — закрытый ключ сервера PostgreSQL.
Заглянем в сертификат сервера serv.crt (мы не выводим открытый ключ, который в нем содержится):
```sh
student$ openssl x509 -noout -text -in /etc/pgssl/serv/serv.crt -certopt no_sigdump,no_pubkey
```
```text
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            1f:14:a1:f4:35:05:1d:13:da:13:ba:f1:c3:2d:fe:f0:ee:33:3c:6c
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C = RU, ST = Moscow, L = Moscow, O = PPG, CN = PPG_EDU_CA
        Validity
            Not Before: Jul 15 20:09:50 2026 GMT
            Not After : Jul 12 20:09:50 2036 GMT
        Subject: C = RU, ST = Moscow, L = Moscow, O = PPG, CN = course.local
        X509v3 extensions:
            X509v3 Extended Key Usage:
                TLS Web Client Authentication, TLS Web Server Authentication
            X509v3 Basic Constraints:
                CA:FALSE
            X509v3 Subject Key Identifier:
                8A:9B:FF:F4:06:B7:CD:8B:AE:D3:0A:F9:F9:48:64:DC:27:EF:7E:7E
            X509v3 Authority Key Identifier:
                2E:FB:48:C4:6C:46:63:A7:B3:F6:56:1C:96:12:F1:31:3C:B2:B3:19
            X509v3 Subject Alternative Name:
                DNS:course.local, IP Address:127.0.1.1
```
сертификат выпустил ЦС PPG\_EDU\_CA (Issuer CN; CN — Common Name, основное имя); сертификат выдан серверу с доменным именем course.local (Subject CN); срок действия сертификата — 10 лет (Validity).
Имя course.local соответствует доменному имени нашего сервера:
```sh
student$ hostname
```
```text
course.local
```
Расширенные атрибуты сертификата SAN (Subject Alternative Name), определенные стандартом RFC 5280, позволяют указывать несколько доменных имен или IP адресов сервера.
Добавим параметры конфигурации сервера в дополнительный файл в подкаталоге conf.d. Сервер должен прослушивать все интерфейсы.
```text
/etc/postgresql/16/main/conf.d/ssl.conf
```
```text
listen_addresses = '*'
ssl_ca_file   = '/etc/pgssl/root/root.crt'
ssl_cert_file = '/etc/pgssl/serv/serv.crt'
ssl_key_file  = '/etc/pgssl/serv/serv.key'
```
Перезапустим сервер, поскольку был изменен параметр listen\_addresses.

## Page 15: Page 15 {#source-page-015}

```sh
student$ sudo pg_ctlcluster 16 main restart
```
Откроем новый сеанс без SSL. Для этого в строке соединения укажем параметр sslmode=disable (или allow):
```sh
student$ psql 'postgresql://student@localhost:5432/student?password=student&sslmode=disable'
```
```sql
student=# \conninfo
```
```text
You are connected to database "student" as user "student" on host "localhost" (address
"127.0.0.1") at port "5432".
```
Представление pg\_stat\_ssl показывает статистику использования SSL сеансами:
```sql
student=# SELECT *
FROM pg_stat_ssl
WHERE pid = pg_backend_pid() \gx
```
```text
-[ RECORD 1 ]-+------
pid           | 56736
ssl           | f
version       |
cipher        |
bits          |
client_dn     |
client_serial |
issuer_dn     |
```
В поле ssl видим значение false — SSL не используется.
Напомним, что администратор сервера может запретить нешифрованные соединения, поместив в начало конфигурационного файла аутентификации соответствующее правило:
```text
/etc/postgresql/16/main/pg_hba.conf.d/ssl.conf
```
```text
# TYPE     DATABASE  USER   ADDRESS   METHOD
hostnossl  all       all    all       reject
```
```sql
student=# SELECT pg_reload_conf();
```
```text
 pg_reload_conf
----------------
 t
(1 row)
```
```sql
student=# \c
```
```sql
\connect: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:
pg_hba.conf rejects connection for host "127.0.0.1", user "student", database "student",
no encryption
```
Теперь попробуем подключиться с SSL. Для этого достаточно установить параметр соединения sslmode=prefer (или require):
```sh
student$ psql 'postgresql://student@localhost:5432/student?password=student&sslmode=prefer'
```
```sql
student=# \conninfo
```
```text
You are connected to database "student" as user "student" on host "localhost" (address
"127.0.0.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
```
Трафик этого сеанса защищен. Аналогичную информацию дает и представление:
```sql
student=# SELECT *
FROM pg_stat_ssl
WHERE pid = pg_backend_pid() \gx
```
```text
-[ RECORD 1 ]-+-----------------------
pid           | 56988
ssl           | t
version       | TLSv1.3
cipher        | TLS_AES_256_GCM_SHA384
bits          | 256
client_dn     |
client_serial |
issuer_dn     |
```

## Page 16: Page 16 {#source-page-016}

Поле cipher сообщает, что используется протокол TLS, применяется симметричное шифрование AES с длиной ключа 256 битов и другие подробности.

## Page 17: Проверка сервера {#source-page-017}

**Параметр sslmode библиотеки libpq**
  - verify-ca
  - проверить, что сертификат сервера подписан
  - доверенным ЦС
  - verify-full
  - также проверить, что доменное имя сервера
  - соответствует имени в сертификате
- Расположение файла сертификата ЦС
  - ~/.postgresql/root.crt
  - параметр sslrootcert библиотеки libpq
  - переменная окружения PGSSLROOTCERT

### Комментарии преподавателя

Обращение к серверу не гарантирует, что соединение будет установлено именно с этим сервером: существуют атаки как на DNS (службу разрешения доменных имен), так и на коммутационное оборудование и маршрутизацию.

<https://ru.wikipedia.org/wiki/DNS\_hijacking>

Но, как мы говорили выше, клиент может проверить аутентичность сервера, получив его сертификат.

Возможны два вида проверки, которые выбираются параметром sslmode библиотеки libpq.

В первом случае (значение verify-ca) клиент проверяет подпись ЦС — такая проверка гарантирует, что сервер знаком центру сертификации. Для этого у клиента должен быть сертификат центра сертификации. Имя файла по умолчанию ~/.postgresql/root.crt, другое имя можно указать в параметре соединения sslrootcert или в переменной окружения PGSSLROOTCERT.

Во втором случае (verify-full) в дополнение к проверке подписи ЦС клиент проверяет, что доменное имя сервера, к которому он подключается, совпадает с доменным именем, указанным в сертификате.

## Page 18: Проверка сервера {#source-page-018}

Каталог для клиентских сертификатов ~student/.postgresql уже существует в виртуальной машине и содержит сертификат центра сертификации, а также сертификаты для пользователей student, alice и bob — они понадобятся нам чуть позже:
```sh
student$ ls -l ~/.postgresql/
```
```text
total 40
-rw-r--r-- 1 student student 3790 июл 15 23:09 alice.crt
-rw-rw-r-- 1 student student 3351 июл 15 23:09 alice.csr
-rw------- 1 student student 1704 июл 15 23:09 alice.key
-rw-r--r-- 1 student student 3784 июл 15 23:09 bob.crt
-rw-rw-r-- 1 student student 3345 июл 15 23:09 bob.csr
-rw------- 1 student student 1704 июл 15 23:09 bob.key
-rw-rw-r-- 1 student student 1281 июл 15 23:09 root.crt
-rw-r--r-- 1 student student 3796 июл 15 23:09 student.crt
-rw-rw-r-- 1 student student 3353 июл 15 23:09 student.csr
-rw------- 1 student student 1704 июл 15 23:09 student.key
```
Пробуем подключиться в режиме проверки сертификата ЦС, которым подписан сертификат сервера:
```sh
student$ psql -c '\conninfo' 'host=localhost password=student sslmode=verify-ca'
```
```text
You are connected to database "student" as user "student" on host "localhost" (address
"127.0.0.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
```
Обратите внимание, что мы подключились через loopback-интерфейс, которому соответствует имя localhost, хотя в сертификате сервера указано другое имя — course.local.
Повторим эксперимент в режиме полной проверки:
```sh
student$ psql -c '\conninfo' 'host=localhost password=student sslmode=verify-full'
```
```text
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: server
certificate for "course.local" (and 1 other name) does not match host name "localhost"
```
На этот раз соединение запрещено, поскольку доменное имя в сертификате не совпадает с именем сервера.
Теперь подключимся в режиме полной проверки, но используем то имя сервера, которое указано в сертификате:
```sh
student$ psql -c '\conninfo' 'host=course.local password=student sslmode=verify-full'
```
```text
You are connected to database "student" as user "student" on host "course.local" (address
"127.0.1.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
```
Так все работает.

## Page 19: Проверка клиента {#source-page-019}

**Параметр clientcert любого метода аутентификации**
  - verify-ca
  - проверить, что клиентский сертификат подписан
  - доверенным ЦС
  - verify-full
  - также проверить, что имя пользователя БД
  - соответствует имени в сертификате
- Метод аутентификации cert
  - работает как trust и clientcert = verify-full
- Файл сопоставления имен pg\_ident.conf

### Комментарии преподавателя

Точно так же, как клиент проверяет сертификат сервера, сервер может запросить клиентский сертификат и проверить его.

<https://postgrespro.ru/docs/postgresql/16/ssl-tcp#SSL-CLIENT-CERTIFICAT> ES

Для этого в pg\_hba.conf к обычному (например, парольному) методу аутентификации добавляется параметр clientcert:

● verify-ca — проверить только подпись ЦС;

● verify-full — проверить подпись ЦС и потребовать, чтобы имя пользователя БД совпадало с именем, указанным в сертификате.

Но в этом случае запрашивать пароль избыточно, ведь подлинность клиента удостоверяет само наличие сертификата. Поэтому обычно используется метод аутентификации cert, который подразумевает проверку уровня verify-full и не запрашивает пароль.

В обоих случаях требуется, чтобы соответствующая запись pg\_hba.conf имела тип hostssl (просто host недостаточно).

<https://postgrespro.ru/docs/postgresql/16/auth-cert>

Если же имя клиента в сертификате отличается от имени пользователя базы данных, в файле pg\_ident.conf можно указать сопоставление имен.

<https://postgrespro.ru/docs/postgresql/16/auth-username-maps>

## Page 20: Проверка клиента {#source-page-020}

В предыдущих примерах клиент проверял сертификат сервера. Теперь посмотрим, как сервер может проверить сертификат клиента. Есть два способа:
указать в строке pg\_hba.conf параметр clientcert; использовать метод аутентификации cert.
Опробуем второй вариант:
```text
/etc/postgresql/16/main/pg_hba.conf.d/ssl.conf
```
```text
# TYPE   DATABASE  USER   ADDRESS   METHOD
hostssl  all       all    all       cert
```
```sql
student=# SELECT pg_reload_conf();
```
```text
 pg_reload_conf
----------------
 t
(1 row)
```
Заглянем в клиентский сертификат student:
```sh
student$ openssl x509 -noout -text -in /home/student/.postgresql/student.crt -certopt no_sigdump,no_pubkey
```
```text
Certificate:
    Data:
        Version: 1 (0x0)
        Serial Number:
            1f:14:a1:f4:35:05:1d:13:da:13:ba:f1:c3:2d:fe:f0:ee:33:3c:6d
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C = RU, ST = Moscow, L = Moscow, O = PPG, CN = PPG_EDU_CA
        Validity
            Not Before: Jul 15 20:09:51 2026 GMT
            Not After : Jul 12 20:09:51 2036 GMT
        Subject: CN = student
```
Сертификат выдан на имя student (Subject CN).
Попробуем подключиться от имени student.
```sh
student$ psql
```
```sql
student=# \c 'user=student host=course.local sslcert=/home/student/.postgresql/student.crt sslkey=/home/student/.postgresql/student.key'
```
```text
You are now connected to database "student" as user "student" on host "course.local"
(address "127.0.1.1") at port "5432".
```
```sql
student=# SELECT * FROM pg_stat_ssl
WHERE pid = pg_backend_pid() \gx
```
```text
-[ RECORD 1 ]-+-------------------------------------------------
pid           | 57555
ssl           | t
version       | TLSv1.3
cipher        | TLS_AES_256_GCM_SHA384
bits          | 256
client_dn     | /CN=student
client_serial | 177438836976917814846351759711444836652177112173
issuer_dn     | /C=RU/ST=Moscow/L=Moscow/O=PPG/CN=PPG_EDU_CA
```
Обратите внимание:
клиентский сертификат предоставлен; показаны атрибуты, которые мы видели в сертификате.
Метод cert позволяет подключиться к базе данных с именем пользователя, отличающимся от имени в сертификате. В этом случае понадобится дополнительный файл сопоставления имен:
```text
/etc/postgresql/16/main/pg_ident.conf.d/cert.conf
```
```text
m1 alice student
```
И нужно указать имя отображения в параметре map строки конфигурации pg\_hba:
```text
/etc/postgresql/16/main/pg_hba.conf.d/ssl.conf
```
```text
# TYPE   DATABASE  USER   ADDRESS   METHOD
hostssl  all       all    all       cert map=m1
```
```sql
student=# SELECT pg_reload_conf();
```
```text
 pg_reload_conf
----------------
 t
(1 row)
```
Пробуем подключиться как student с сертификатом Алисы:
```sql
student=# \c 'user=student host=course.local sslcert=/home/student/.postgresql/alice.crt sslkey=/home/student/.postgresql/alice.key'
```

## Page 21: Page 21 {#source-page-021}

```text
You are now connected to database "student" as user "student".
```
```sql
student=# SELECT * FROM pg_stat_ssl
WHERE pid = pg_backend_pid() \gx
```
```text
-[ RECORD 1 ]-+-------------------------------------------------
pid           | 57779
ssl           | t
version       | TLSv1.3
cipher        | TLS_AES_256_GCM_SHA384
bits          | 256
client_dn     | /CN=alice
client_serial | 177438836976917814846351759711444836652177112174
issuer_dn     | /C=RU/ST=Moscow/L=Moscow/O=PPG/CN=PPG_EDU_CA
```
Все получилось.

## Page 22: Итоги {#source-page-022}

**Основные криптографические понятия: открытый и закрытый ключи, подпись, сертификат**
- SSL применяется для защиты трафика и аутентификации
- Серверный сертификат удостоверяет аутентичность сервера
- Метод cert позволяет аутентифицировать пользователя по клиентскому сертификату

## Page 23: Практика {#source-page-023}

- 1. По аналогии с демонстрацией настройте сервер для возможности работы с SSL. 2. Проверьте, сколько времени занимает установка сетевого соединения с PostgreSQL: – без SSL, метод аутентификации trust; – с шифрованием SSL, метод аутентификации trust; – с аутентификацией по сертификату и проверкой сервера.

### Комментарии преподавателя

1. Используйте утилиту эталонного тестирования pgbench. С параметром --connect каждый запрос выполняется в отдельном соединении. В числе прочих показателей, утилита выводит усредненное время установки соединения.

<https://postgrespro.ru/docs/postgresql/16/pgbench>

## Page 24: 1. Настройка SSL {#source-page-024}

Запишем в каталог /etc/postgresql/16/main/conf.d параметры конфигурации сервера для SSL. Настроим сервер для прослушивания всех интерфейсов.
```text
/etc/postgresql/16/main/conf.d/ssl.conf
```
```text
listen_addresses = '*'
ssl_ca_file   = '/etc/pgssl/root/root.crt'
ssl_cert_file = '/etc/pgssl/serv/serv.crt'
ssl_key_file  = '/etc/pgssl/serv/serv.key'
```
Перезагрузим сервер.
```sh
student$ sudo pg_ctlcluster 16 main restart
```
### 2a. Соединение без SSL (trust)
Создадим конфигурационный файл аутентификации с методом trust для сетевых соединений:
```text
/etc/postgresql/16/main/pg_hba.conf.d/ssl.conf
```
```text
# TYPE  DATABASE  USER   ADDRESS   METHOD
host    all       all    all       trust
```
```sh
student$ psql
```
```sql
student=# SELECT pg_reload_conf();
```
```text
 pg_reload_conf
----------------
 t
(1 row)
```
Выполним инициализацию таблиц, которые используются эталонным тестом pgbench:
```sh
student$ pgbench -i
```
```sql
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.13 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 1.38 s (drop tables 0.03 s, create tables 0.34 s, client-side generate 0.28 s,
vacuum 0.37 s, primary keys 0.36 s).
```
Запускаем эталонный тест на 30 секунд в режиме, при котором для каждой транзакции устанавливается новое соединение:
```sh
student$ pgbench --connect -T 30 "host=localhost sslmode=disable"
```
```sql
pgbench (16.14 (Ubuntu 16.14-1.pgdg24.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 1590
number of failed transactions: 0 (0.000%)
latency average = 18.875 ms
average connection time = 7.619 ms
tps = 52.979758 (including reconnection times)
```
> [!NOTE]
> **Note**
> Внимание надо обратить на значение показателя average connection time.

## Page 25: 2b. Соединение с шифрованием SSL (trust) {#source-page-025}

```sh
student$ pgbench --connect -T 30 "host=localhost sslmode=require"
```
```sql
pgbench (16.14 (Ubuntu 16.14-1.pgdg24.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 1158
number of failed transactions: 0 (0.000%)
latency average = 25.920 ms
average connection time = 14.893 ms
tps = 38.580672 (including reconnection times)
```
### 2c. Соединение с аутентификацией по сертификату и проверкой сервера.
Потребуется изменить настройку аутентификации:
```text
/etc/postgresql/16/main/pg_hba.conf.d/ssl.conf
```
```text
# TYPE   DATABASE  USER   ADDRESS   METHOD
hostssl  all       all    all       cert
```
```sql
student=# SELECT pg_reload_conf();
```
```text
 pg_reload_conf
----------------
 t
(1 row)
```
```sh
student$ pgbench --connect -T 30 "host=localhost sslcert=/home/student/.postgresql/student.crt
sslkey=/home/student/.postgresql/student.key sslmode=verify-ca"
```
```sql
pgbench (16.14 (Ubuntu 16.14-1.pgdg24.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 950
number of failed transactions: 0 (0.000%)
latency average = 31.614 ms
average connection time = 20.569 ms
tps = 31.631896 (including reconnection times)
```
В первом случае установка соединения происходит быстрее всего. Во втором случае добавляется время на обмен сообщениями между клиентом и сервером, в ходе которых они договариваются о режиме шифрования. В третьем случае установка соединения самая медленная, поскольку сервер дополнительно аутентифицирует клиента по сертификату.
В нашем случае сетевые задержки сведены к минимуму, поскольку клиент и сервер находятся на одном компьютере. В реальности они вносят существенный вклад во время установки соединения.
### Уборка
```sh
student$ sudo rm /etc/postgresql/16/main/pg_hba.conf.d/*
```
```sh
student$ sudo rm /etc/postgresql/16/main/conf.d/*
```
```sh
student$ sudo pg_ctlcluster 16 main reload
```
```sql
student=# DROP TABLE pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers;
```
```sql
DROP TABLE
```

## Page 26: Практика+ {#source-page-026}

- 1. По аналогии с демонстрацией настройте сервер для возможности работы с SSL. 2. Зарегистрируйте роль alice с правом подключения и паролем SECRET. 3. Проверьте, можно ли подсмотреть в трафике имя пользователя, пароль или команды SQL для следующих вариантов подключения пользователя alice: – без SSL, метод аутентификации password; – с SSL, метод аутентификации password; – без SSL, метод аутентификации scram-sha-256.

### Комментарии преподавателя

3. Для перехвата трафика используйте команду: sudo tcpdump -w ~/tmp/tcpdump.bin -i any "dst port 5432" >& ~/tmp/tcpdump.log &

Здесь ключ -w указывает файл для сохранения перехваченных пакетов, ключ -i указывает прослушиваемые интерфейсы. Далее в командной строке указано правило фильтрации перехватываемого трафика: порт назначения 5432. С помощью >& поток вывода и поток ошибок перенаправлены в файл отчета. Заключительный амперсанд указывает, что команда запускается в фоновом режиме.

Для завершения перехвата трафика необходимо остановить команду следующим образом: sudo killall tcpdump

Для просмотра сохраненного трафика можно выполнить команду: tcpdump -Aqr ~/tmp/tcpdump.bin 2> /dev/null

<https://www.tcpdump.org/>

Перед очередным запуском не забывайте удалять файлы перехвата и отчета. Например, так: sudo rm -f ~/tmp/tcpdump.\{bin,log\}

## Page 27: 1. Настройка SSL {#source-page-027}

Запишем в каталог /etc/postgresql/16/main/conf.d параметры конфигурации сервера для SSL. Настроим сервер для прослушивания всех интерфейсов.
```text
/etc/postgresql/16/main/conf.d/ssl.conf
```
```text
listen_addresses = '*'
ssl_ca_file   = '/etc/pgssl/root/root.crt'
ssl_cert_file = '/etc/pgssl/serv/serv.crt'
ssl_key_file  = '/etc/pgssl/serv/serv.key'
```
Перезагрузим сервер.
```sh
student$ sudo pg_ctlcluster 16 main restart
```
### 2. Регистрация роли
```sh
student$ psql
```
```sql
student=# CREATE USER alice PASSWORD 'SECRET';
```
```sql
CREATE ROLE
```
### 3a. Без SSL, метод password
Создадим файл HBA с разрешением alice подключаться к базе данных student при успешной аутентификации методом password. SSL выключен.
```text
/etc/postgresql/16/main/pg_hba.conf.d/ssl.conf
```
```text
# TYPE     DATABASE  USER   ADDRESS   METHOD
hostnossl  all       all    all       password
```
Перечитаем конфигурацию.
```sql
student=# SELECT pg_reload_conf();
```
```text
 pg_reload_conf
----------------
 t
(1 row)
```
Запустим перехват трафика, направленного на порт TCP 5432, на всех сетевых интерфейсах.
```sh
student$ sudo tcpdump -w ~/tmp/tcpdump.bin -i any "dst port 5432" >& ~/tmp/tcpdump.log &
```
Выполним код SQL в сеансе alice, используя аутентификацию password при отсутствии шифрования.
```sh
student$ psql
```
```sql
=> \c 'host=localhost dbname=student user=alice password=SECRET sslmode=disable'
```
```text
You are now connected to database "student" as user "alice" on host "localhost" (address
"127.0.0.1") at port "5432".
```
```sql
=> SELECT 42;
```
```text
 ?column?
----------
       42
(1 row)
```
```sql
=> \q
```
Завершим перехват трафика после небольшой паузы.
```sh
student$ sudo killall tcpdump
```
Проверим, видны ли в трафике имена пользователей, пароли и команды:
```sh
student$ sudo chown student:student ~/tmp/*
```

## Page 28: 3b. С SSL, метод password {#source-page-028}

```sh
student$ tcpdump -Aqr ~/tmp/tcpdump.bin 2> /dev/null | egrep -i '(alice|select|secret)'
```
```sql
%...%......;....user.alice.database.student.application_name.psql..
%...%...p....SECRET.
%...%...Q....SELECT pg_backend_pid();.
%..Q%..$Q....SELECT 42;.
```
В перехваченном трафике вся информация открыта.
Теперь включим шифрование SSL.
```text
/etc/postgresql/16/main/pg_hba.conf.d/ssl.conf
```
```text
# TYPE   DATABASE  USER   ADDRESS   METHOD
hostssl  all       all    all       password
```
```sql
student=# SELECT pg_reload_conf();
```
```text
 pg_reload_conf
----------------
 t
(1 row)
```
Снова запустим перехват.
```sh
student$ rm -rf ~/tmp/*
```
```sh
student$ sudo tcpdump -w ~/tmp/tcpdump.bin -i any "dst port 5432" >& ~/tmp/tcpdump.log &
```
```sh
student$ psql
```
```sql
=> \c 'host=localhost dbname=student user=alice password=SECRET sslmode=require'
```
```text
You are now connected to database "student" as user "alice" on host "localhost" (address
"127.0.0.1") at port "5432".
```
```sql
=> \conninfo
```
```text
You are connected to database "student" as user "alice" on host "localhost" (address
"127.0.0.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
```
```sql
=> SELECT 42;
```
```text
 ?column?
----------
       42
(1 row)
```
```sql
=> \q
```
```sh
student$ sudo killall tcpdump
```
Проверим перехваченный трафик:
```sh
student$ sudo chown student:student ~/tmp/*
```
```sh
student$ tcpdump -Aqr ~/tmp/tcpdump.bin 2> /dev/null | egrep -i '(alice|select|secret)'
```
На этот раз ничего не выведено.
Трафик зашифрован, подсмотреть конфиденциальную информацию не получится.
### 3c. Без SSL, метод scram-sha-256
Теперь повторим первый эксперимент, но с методом аутентификации scram-sha-256.
```text
/etc/postgresql/16/main/pg_hba.conf.d/ssl.conf
```
```text
# TYPE     DATABASE  USER   ADDRESS   METHOD
hostnossl  all       all    all       scram-sha-256
```
```sql
student=# SELECT pg_reload_conf();
```

## Page 29: Page 29 {#source-page-029}

```text
 pg_reload_conf
----------------
 t
(1 row)
```
Запустим перехват.
```sh
student$ rm -rf ~/tmp/*
```
```sh
student$ sudo tcpdump -w ~/tmp/tcpdump.bin -i any "dst port 5432" >& ~/tmp/tcpdump.log &
```
```sh
student$ psql
```
```sql
=> \c 'host=localhost dbname=student user=alice password=SECRET sslmode=disable'
```
```text
You are now connected to database "student" as user "alice" on host "localhost" (address
"127.0.0.1") at port "5432".
```
```sql
=> SELECT 42;
```
```text
 ?column?
----------
       42
(1 row)
```
```sql
=> \q
```
```sh
student$ sudo killall tcpdump
```
Проверим перехваченный трафик:
```sh
student$ sudo chown student:student ~/tmp/*
```
```sh
student$ tcpdump -Aqr ~/tmp/tcpdump.bin 2> /dev/null | egrep -i '(alice|select|secret)'
```
```sql
%...%......;....user.alice.database.student.application_name.psql..
%...%...Q....SELECT pg_backend_pid();.
%..F%...Q....SELECT 42;.
```
Трафик не шифруется, но пароль в открытом виде не передается.
### Уборка
```sh
student$ sudo rm /etc/postgresql/16/main/pg_hba.conf.d/*
```
```sh
student$ sudo rm /etc/postgresql/16/main/conf.d/*
```
```sh
student$ sudo pg_ctlcluster 16 main reload
```
```sql
student=# DROP ROLE alice;
```
```sql
DROP ROLE
```