Уровень 3.0

Авторизация и аутентификация

Создание сертификата центра сертификации

Создайте каталоги для хранения сертификатов и временно назначьте им владельца student.
```
[student@ServerName ~]$ sudo mkdir -p /etc/pangolin/ssl/{CA,PG}
```
Разбор команды
- -p — создавать все родительские каталоги при необходимости;
- {CA,PG} — разворачивается в два аргумента /etc/pangolin/ssl/CA и /etc/pangolin/ssl/PG, создавая оба каталога одной командой.
Назначьте владельца student на весь каталог /etc/pangolin.
```
[student@ServerName ~]$ sudo chown -R student:student /etc/pangolin
```
Проверьте структуру созданных каталогов.
```
[student@ServerName ~]$ ls -Rl /etc/pangolin
```
```
/etc/pangolin:
total 4
drwxr-xr-x 4 student student 4096 сен 25 20:58 ssl
```

/etc/pangolin/ssl: total 8 drwxr-xr-x 2 student student 4096 сен 25 20:58 CA drwxr-xr-x 2 student student 4096 сен 25 20:58 PG

/etc/pangolin/ssl/CA: total 0

/etc/pangolin/ssl/PG: total 0

2. Для удобства экспортируйте переменную окружения с префиксом `DN`, который будет использоваться в командах `openssl`.

```bash:copy-code
[student@ServerName ~]$ export DN_PREFIX='/C=RU/ST=Moscow_reg./L=Moscow/O=PangolinEDU'

Перейдите в каталог /etc/pangolin/ssl/CA и создайте приватный ключ центра сертификации.

[student@ServerName ~]$ cd /etc/pangolin/ssl/CA

[student@ServerName CA]$ openssl genrsa -out CA.key 4096

Generating RSA private key, 4096 bit long modulus (2 primes)
..............++++
...++++
e is 65537 (0x010001)

Проверьте созданный файл ключа.

[student@ServerName CA]$ ls -l

total 4
-rw------- 1 student student 3243 сен 25 21:27 CA.key

Создайте самоподписанный сертификат центра сертификации.
```
[student@ServerName CA]$ openssl req -x509 -new -extensions v3_ca -days 3650 -key CA.key -out CA.crt -subj "${DN_PREFIX}/CN=PangolinEDU_CA"
```
Разбор команды
- openssl req -x509 — создание самоподписанного сертификата X.509;
- -new — сгенерировать новый запрос (встроенный в процесс создания);
- -extensions v3_ca — применить секцию расширений v3_ca (устанавливает CA:TRUE);
- -days 3650 — срок действия сертификата (10 лет);
- -key CA.key — использовать приватный ключ CA.key;
- -out CA.crt — выходной файл сертификата;
- -subj "${DN_PREFIX}/CN=PangolinEDU_CA" — distinguished name (CN = Common Name, имя CA).
Проверьте созданные файлы.
```
[student@ServerName CA]$ ls -ltr
```
```
total 8
-rw------- 1 student student 3243 сен 25 21:27 CA.key
-rw-r--r-- 1 student student 2021 сен 25 21:35 CA.crt
```

Проверьте содержимое сертификата центра сертификации.

[student@ServerName CA]$ openssl x509 -noout -in CA.crt -text -certopt no_sigdump,no_pubkey

Разбор команды

openssl x509 — утилита для отображения/конвертации сертификатов X.509;
-noout — не выводить сертификат в формате PEM;
-in CA.crt — входной файл сертификата;
-text — вывести содержимое сертификата в читаемом текстовом виде;
-certopt no_sigdump,no_pubkey — исключить из вывода блок подписи и открытый ключ.

 Certificate:
   Data:
      Version: 3 (0x2)
      Serial Number:
             79:38:86:f4:d4:46:1c:3a:32:67:0c:b8:ac:1b:a8:1a:65:40:f0:7a
      Signature Algorithm: sha256WithRSAEncryption
      Issuer: C = RU, ST = Moscow_reg., L = Moscow, O = PangolinEDU, CN = PangolinEDU_CA
       Validity
              Not Before: Sep 25 18:35:01 2024 GMT
              Not After : Sep 23 18:35:01 2034 GMT
       Subject: C = RU, ST = Moscow_reg., L = Moscow, 0 = PangolinEDU, CN = PangolinEDU_CA
       X509v3 extensions:
              X509v3 Subject Key Identifier:
                  A3:10:41:AE:DA:43:63:9C:C3:7E:F3:B6:41:4B:5A:BD:E9:56:3E:40
              X509v3 Authority Key Identifier:

  keyid:A3:10:41:AE:DA:43:63:9C:C3:7E:F3:B6:41:4B:5A:BD:E9:56:3E:40

              X509v3 Basic Constraints: critical
                     CA:TRUE

Сертификат CA самоподписанный (Issuer = Subject), срок действия — 10 лет, установлен критический флаг CA:TRUE.

Создание сертификата сервера

Перейдите в каталог для хранения сертификата сервера.
```
[student@ServerName CA]$ cd /etc/pangolin/ssl/PG
```

Создайте конфигурационный файл для openssl.

[student@ServerName PG]$ cat << EOF >> pg.conf

Разбор команды

cat << EOF — чтение текста из стандартного ввода до строки EOF;
>> pg.conf — перенаправление вывода в файл pg.conf в режиме дополнения.

[req]
req_extensions = v3_req
distinguished_name = req_distinguished_name
[req_distinguished_name]
[ v3_req ]
basicConstraints = CA:FALSE
keyUsage = nonRepudiation, digitalSignature, keyEncipherment
subjectAltName = @alt_names
[ ssl_client ]
extendedKeyUsage = clientAuth, serverAuth
basicConstraints = CA:FALSE
subjectKeyIdentifier=hash
authorityKeyIdentifier=keyid,issuer
subjectAltName = @alt_names
[ v3_ca ]
basicConstraints = CA:TRUE
keyUsage = nonRepudiation, digitalSignature, keyEncipherment
subjectAltName = @alt_names
authorityKeyIdentifier=keyid:always,issuer
[alt_names]
EOF

Добавьте в файл конфигурации сертификата сервера DNS-имя хоста.
```
[student@ServerName PG]$ echo DNS.1 = $(hostname -f) >> pg.conf
```
Разбор команды
- $(hostname -f) — возвращает FQDN (полное доменное имя) хоста;
- >> pg.conf — дополнение строки в файл pg.conf.
Добавьте IP-адрес хоста.
```
[student@ServerName PG]$ echo IP.1 = $(hostname --ip-address) >> pg.conf
```

Проверьте итоговое содержимое файла конфигурации.

[student@ServerName PG]$ cat pg.conf

[req]
req_extensions = v3_req
distinguished_name = req_distinguished_name
[req_distinguished_name]
[ v3_req ]
basicConstraints = CA:FALSE
keyUsage = nonRepudiation, digitalSignature, keyEncipherment
subjectAltName = @alt_names
[ ssl_client ]
extendedKeyUsage = clientAuth, serverAuth
basicConstraints = CA:FALSE
subjectKeyIdentifier=hash
authorityKeyIdentifier=keyid,issuer
subjectAltName = @alt_names
[ v3_ca ]
basicConstraints = CA:TRUE
keyUsage = nonRepudiation, digitalSignature, keyEncipherment
subjectAltName = @alt_names
authorityKeyIdentifier=keyid:always,issuer
[alt_names]
DNS.1 = {server_name.class.edu}
IP.1 = 10.0.2.15

Создайте запрос на сертификацию.
```
[student@ServerName PG]$ openssl req -new -nodes -text -out ./pg.csr -keyout ./pg.key -subj "${DN_PREFIX}/CN=$(hostname -f)" -config ./pg.conf
```
Разбор команды
- openssl req -new — создание нового запроса на подпись сертификата (CSR);
- -nodes — не шифровать приватный ключ паролем;
- -text — вывести текстовое представление;
- -out ./pg.csr — файл запроса;
- -keyout ./pg.key — файл приватного ключа;
- -subj "${DN_PREFIX}/CN=$(hostname -f)" — уникальное имя (distinguished name), где CN (Common Name) равен FQDN хоста;
- -config ./pg.conf — использовать конфигурационный файл с расширениями.
```
Generating a RSA private key
......+++++
...................................................+++++
writing new private key to './pg.key'
-----
```
Проверьте созданные файлы.
```
[student@ServerName PG]$ ls -ltrh
```
```
total 12K
-rw-r--r-- 1 student student 623 сен 26 09:42 pg.conf
-rw------- 1 student student 1,7K сен 26 09:52 pg.key
-rw-r--r-- 1 student student 3,8K сен 26 09:52 pg.csr
```

Проверьте содержимое запроса на сертификацию.

[student@ServerName PG]$ openssl req -text -noout -in pg.csr -reqopt no_sigdump,no_pubkey

Разбор команды

openssl req -text — отобразить содержимое запроса на подпись сертификата (CSR) в читаемом виде;
-noout — не выводить запрос в формате DER/PEM;
-in pg.csr — входной файл запроса;
-reqopt no_sigdump,no_pubkey — исключить из вывода блок подписи и открытый ключ.

 Certificate Request:
     Data:
          Version: 1 (0x0)
          Subject: C = RU, ST = Moscow_reg., L = Moscow, O = PangolinEDU, CN = {server_name.class.edu}
          Attributes:
          Requested Extensions:
                 X509v3 Basic Constraints:
                       CA:FALSE
                 X509v3 Key Usage:
                       Digital Signature, Non Repudiation, Key Encipherment
                 X509v3 Subject Alternative Name:
                      DNS:{server_name.class.edu}, IP Address:10.0.2.15

Подпишите запрос на сертификацию и выпустите сертификат.
```
[student@ServerName PG]$ openssl x509 -req -in ./pg.csr -text -days 3650 -CA ../CA/CA.crt -CAkey ../CA/CA.key -CAcreateserial -out ./pg.crt -extensions ssl_client -extfile ./pg.conf
```
Разбор команды
- openssl x509 -req — обработка запроса на подпись сертификата;
- -in ./pg.csr — входной файл запроса;
- -days 3650 — срок действия сертификата (10 лет);
- -CA ../CA/CA.crt — сертификат центра сертификации;
- -CAkey ../CA/CA.key — приватный ключ CA для подписи;
- -CAcreateserial — создать файл серийного номера;
- -out ./pg.crt — выходной файл сертификата;
- -extensions ssl_client — секция расширений из конфигурационного файла;
- -extfile ./pg.conf — файл с конфигурацией расширений.
```
Signature ok
subject=C = RU, ST = Moscow_reg., L = Moscow, O = PangolinEDU, CN = {server_name.class.edu}
Getting CA Private Key
```
Проверьте созданные файлы.
```
[student@ServerName PG]$ ls -ltr
```
```
total 16
-rw-r--r-- 1 student student 623 сен 26 09:42 pg.conf
-rw------- 1 student student 1704 сен 26 09:52 pg.key
-rw-r--r-- 1 student student 3868 сен 26 09:52 pg.csr
-rw-r--r-- 1 student student 3601 сен 26 10:02 pg.crt
```
Проверьте присутствие в сертификате расширенных атрибутов.
```
[student@ServerName PG]$ openssl x509 -noout -in pg.crt -ext subjectAltName,extendedKeyUsage
```
Разбор команды
- openssl x509 -noout — отобразить информацию о сертификате без вывода самого сертификата;
- -in pg.crt — входной файл сертификата;
- -ext subjectAltName,extendedKeyUsage — вывести только указанные расширения: альтернативные имена субъекта (SAN) и расширенное использование ключа.
```
 X509v3 Extended Key Usage:
     TLS Web Client Authentication, TLS Web Server Authentication
 X509v3 Subject Alternative Name:
     DNS:{server_name.class.edu}, IP Address:10.0.2.15
```
Сертификат сервера содержит SAN (``DNS+IP) и Extended Key Usage (client+server`).

Настройка сервера

Вернитесь в домашний каталог.
```
[student@ServerName PG]$ cd
```
Установите владельца postgres и группу postgres на все файлы в каталоге /etc/pangolin/ssl/.
```
[student@ServerName ~]$ sudo chown -R postgres:postgres /etc/pangolin/ssl
```
Ограничьте доступ в каталог PG — только владельцу.
```
[student@ServerName ~]$ sudo chmod go= /etc/pangolin/ssl/PG/
```
Проверьте права на каталог PG.
```
[student@ServerName ~]$ ls -ld /etc/pangolin/ssl/PG/
```
```
drwx------ 2 postgres postgres 4096 сен 26 10:02 /etc/pangolin/ssl/PG/
```
Создайте каталог для хранения подключаемой конфигурации сервера с настройками SSL и подключите его к основному файлу конфигурации.

Создайте каталог подключаемой конфигурации.
```
[student@ServerName ~]$ sudo -u postgres mkdir /etc/pangolin/ssl/conf
```
Добавьте директиву include_dir в postgresql.conf.
```
[student@ServerName ~]$ echo "include_dir '/etc/pangolin/ssl/conf'" | sudo -Eu postgres tee -a $PGDATA/postgresql.conf
```
Разбор команды
- echo "include_dir ..." | — строка с директивой передается через конвейер;
- sudo -Eu postgres — выполнить команду от пользователя postgres с сохранением переменных окружения (-E);
- tee -a $PGDATA/postgresql.conf — записать строку в файл postgresql.conf в режиме дополнения.
```
include_dir '/etc/pangolin/ssl/conf'
```
Проверьте, что строка добавлена.
```
[student@ServerName ~]$ sudo -Eu postgres tail -1 $PGDATA/postgresql.conf
```
```
include_dir '/etc/pangolin/ssl/conf'
```

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

[student@ServerName ~]$ sudo -Eu postgres tee /etc/pangolin/ssl/conf/pgssl.conf << EOF
[student@ServerName ~]$ sudo -Eu postgres tee /etc/pangolin/ssl/conf/pgssl.conf << EOF

ssl = on
ssl_ca_file = '/etc/pangolin/ssl/CA/CA.crt'
ssl_cert_file = '/etc/pangolin/ssl/PG/pg.crt'
ssl_key_file = '/etc/pangolin/ssl/PG/pg.key'
ssl_key_file = '/etc/pangolin/ssl/PG/pg.key'
EOF

Перезапустите сервер, проверьте, есть ли ошибки в конфигурации SSL, а также подключилась ли конфигурация (для пользователя postgres используйте пароль postgres).

Перезапустите сервер.

[student@ServerName ~]$ sudo systemctl restart postgresql

Проверьте параметры SSL в pg_file_settings.

[student@ServerName ~]$ psql -U postgres -h localhost -c "select name, setting, applied, sourcefile from pg_file_settings where name ~ 'ssl';"

 Password for user postgres:
      name      |           setting           | applied |            sourcefile
 ---------------+-----------------------------+---------+-----------------------------------
  ssl           | on                          | t       | /etc/pangolin/ssl/conf/pgssl.conf
  ssl_ca_file   | /etc/pangolin/ssl/CA/CA.crt | t       | /etc/pangolin/ssl/conf/pgssl.conf
  ssl_cert_file | /etc/pangolin/ssl/PG/pg.crt | t       | /etc/pangolin/ssl/conf/pgssl.conf
  ssl_key_file  | /etc/pangolin/ssl/PG/pg.key | t       | /etc/pangolin/ssl/conf/pgssl.conf
 (4 rows)

Все 4 параметра SSL применены (applied = t) из файла pgssl.conf.

Проверьте параметры SSL в pg_settings.

[student@ServerName ~]$ psql -U postgres -h localhost -c "select name, setting from pg_settings where setting ~ 'etc.*ssl';"

 Password for user postgres:
     name      |           setting
 --------------+-----------------------------
 ssl_ca_file   | /etc/pangolin/ssl/CA/CA.crt
 ssl_cert_file | /etc/pangolin/ssl/PG/pg.crt
 ssl_key_file  | /etc/pangolin/ssl/PG/pg.key
 (3 rows)

Настройка разрешений в pg_hba.conf

Сохраните оригинальную версию файла pg_hba.conf.

[student@ServerName ~]$ sudo -Eu postgres cp -v $PGDATA/pg_hba.conf{,.orig}

 '/pgdata/06/data/pg_hba.conf' -> '/pgdata/06/data/pg_hba.conf.orig'

Установите пользователю student пароль student.

[student@ServerName ~]$ psql -U postgres -h localhost -c "alter user student password 'student'"

Password for user postgres:
ALTER ROLE

Замените в pg_hba.conf в записях для локальных подключений через UNIX-сокет тип аутентификации с trust на peer. Все остальные записи с trust должны использовать аутентификацию по паролю.

Замените trust на peer для local и scram-sha-256 для host.
```
[student@ServerName ~]$ sudo -Eu postgres sed -i -e 's/$^local.*$trust/\1peer/' -e '/^host/,$s/trust/scram-sha-256/' $PGDATA/pg_hba.conf
```
Разбор команды
- sed -i — редактирование файла непосредственно в месте его нахождения;
- -e 's/$^local.*$trust/\1peer/' — в строках, начинающихся с local, заменить trust на peer (регулярное выражение $^local.*$ захватывает начало rows);
- -e '/^host/,$s/trust/scram-sha-256/' — в диапазоне от первой строки, начинающейся с host, до конца файла, заменить все trust на scram-sha-256.
Эти действия автоматизированы потоковым редактором sed, но могут быть выполнены в обычном текстовом редакторере.

Выведите записи от первой local до конца файла.

[student@ServerName ~]$ sudo -Eu postgres sed -n '/^local/,$p' $PGDATA/pg_hba.conf

local                   all                    all                                       peer
# IPv4 local connections:
host                    all                    all                    127.0.0.1/32       scram-sha-256
# IPv6 local connections:
host                    all                    all                    ::1/128            scram-sha-256

# Allow replication connections from localhost, by a user with the
# replication privilege.
local                 replication              all                                       peer
host                  replication              all                    127.0.0.1/32       scram-sha-256
host                  replication              all                    ::1/28             scram-sha-256

Проверьте правильность pg_hba.conf средствами SQL.

[student@ServerName ~]$ sudo -u postgres psql -c "SELECT type, database, user_name, auth_method, error from pg_hba_file_rules"

type   |   database    | user_name |  auth_method  | error
-------+---------------+-----------+---------------+-------
local  | {all}         | {all}     | peer          |
host   | {all}         | {all}     | scram-sha-256 |
host   | {all}         | {all}     | scram-sha-256 |
local  | {replication} | {all}     | peer          |
host   | {replication} | {all}     | scram-sha-256 |
host   | {replication} | {all}     | scram-sha-256 |
(6 rows)

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

Замените параметр enabled_extra_auth_methods.
```
[student@ServerName ~]$ sudo -Eu postgres sed -i.bak "s/^enabled_extra_auth_methods.*$/enabled_extra_auth_methods = 'scram-sha-256, peer'/" $PGDATA/postgresql.conf
```
Перезапустите сервер.
```
[student@ServerName ~]$ sudo systemctl restart postgresql
```
Подключитесь локально.
```
[student@ServerName ~]$ psql
```
```
psql (15.5)
Type "help" for help.
```
```
student@student=> \conninfo
```
```
You are connected to database "student" as user "student" via socket in "/tmp" at port "5432".
```

Проверка работы SSL

Получите статистику использования SSL обслуживающих процессов.

student@student=> SELECT * FROM pg_stat_ssl;

 pid   | ssl | version | cipher | bits | client_dn | client_serial | issuer_dn
-------+-----+---------+--------+------+-----------+---------------+-----------
 88281 | f   |         |        |      |           |               |
(1 row)

Проверьте PID текущего процесса.

student@student=> SELECT pg_backend_pid();

 pg_backend_pid
----------------
          88281
(1 row)

Из вывода команд видно, что SSL не используется.

Сбросьте подключение и откройте новое через закольцовывающий сетевой интерфейс (loopback, ему соответствует сетевое имя localhost). При этом потребуется ввести пароль, так как будет использован метод аутентификации scram-sha-256. Проверьте состояние SSL.
```
student@student=> \c - - localhost
```
```
Password for user student:
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "student" as user "student" on host "localhost" (address "::1") at port "5432".
```
Подключение через TCP/IP к localhost автоматически использует SSL.
```
student@student=> SELECT * FROM pg_stat_ssl \gx
```
```
-[ RECORD 1 ] -+-----------------------
pid            | 88359
ssl            | t
version        | TLSv1.3
cipher         | TLS_AES_256_GCM_SHA384
bits           | 256
client_dn      |
client_serial  |
issuer_dn      |
```

Снова сбросьте подключение и подключитесь к localhost, запретив SSL.

student@student=> \c "host=localhost sslmode=disable"

Password for user student:
You are now connected to database "student" as user "student".

student@student=> SELECT * FROM pg_stat_ssl \gx

-[ RECORD 1 ]-+------
pid           | 88423
ssl           | f
version       |
cipher        |
bits          |
client_dn     |
client_serial |
issuer_dn     |

Включите SSL без сброса соединения.

student@student=> \c -reuse-previous=on sslmode=require

SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "student" as user "student".

student@student=> SELECT * FROM pg_stat_ssl \gx

-[ RECORD 1 ]-+-----------------------
pid           | 88441
ssl           | t
version       | TLSv1.3
cipher        | TLS_AES_256_GCM_SHA384
bits          | 256
client_dn     |
client_serial |
issuer_dn     |

Завершите сеанс и попробуйте подключиться с настройками sslmode, требующими подтверждения выпуска сертификата сервера доверенным центром сертификации. Возникнет ошибка.
```
student@student=> \q
```
```
[student@ServerName ~]$ psql "host=localhost password=student sslmode=verify-ca"
```
```
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: root certificate file "/home/student/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.
```
Ошибка возникает, потому что клиент не может найти файл корневого сертификата CA (root.crt).

Повторите попытку подключения в режиме verify-full. Эта команда проверяет не только сертификат CA, но и соответствие имени хоста полям SAN/CN сертификата сервера.
```
[student@ServerName ~]$ psql "host=localhost password=student sslmode=verify-full"
```
```
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: root certificate file "/home/student/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.
```
Все возможные значения параметра sslmode приведены в документации.
Создайте символическую ссылку на файл сертификата центра сертификации.

Создайте каталог .postgresql.
```
[student@ServerName ~]$ mkdir .postgresql
```
Создайте символическую ссылку на сертификат CA.
```
[student@ServerName ~]$ ln -s /etc/pangolin/ssl/CA/CA.crt .postgresql/root.crt
```
```
[student@ServerName ~]$ ls -l .postgresql/
```
```
total 0
lrwxrwxrwx 1 student student 27 сен 27 13:26 root.crt -> /etc/pangolin/ssl/CA/CA.crt
```

Снова проверьте возможность подключения в режиме verify-ca.

[student@ServerName ~]$ psql "host=localhost password=student sslmode=verify-ca"

psql (15.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

student@student=> SELECT * FROM pg_stat_ssl \gx

-[ RECORD 1 ]-+-----------------------
pid           | 90015
ssl           |t
version       | TLSv1.3
cipher        | TLS_AES_256_GCM_SHA384
bits          | 256
client_dn     |
client_serial |
issuer_dn     |

Подключение успешно — клиент проверил сертификат сервера по CA.

Проверьте возможность подключения в режиме verify-full.
```
student@student=> \c -reuse-previous=on sslmode=verify-full
```
```
connection to server at "localhost" (127.0.0.1), port 5432 failed: server certificate for "{server_name.class.edu}" (and 1 other name) does not match host name "localhost"
Previous connection kept
```
Подключение не удалось, так как имя хоста localhost не совпадает с SAN/CN сертификата сервера. Необходимо указать то имя хоста, которое соответствует указанным в атрибутах SAN или CN.

Внимание!
Вам необходимо указать имя вашего хоста вместо {server_name.class.edu} ниже.
```
student@student=> \c -reuse-previous=on "host={server_name.class.edu} sslmode=verify-full"
```
```
Password for user student:
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "student" as user "student" on host "{server_name.class.edu}" at port "5432".
```
```
student@student=> \q
```
Если соединение не удалось, это может быть по причине отсутствия в pg_hba.conf соответствующего разрешения.
Добавьте в pg_hba.conf разрешение подключаться по адресу интерфейса, соответствующего FQDN (Fully Qualified Domain Name — полное доменное имя хоста), внесенному в атрибуты SAN (Subject Alternative Name — альтернативное доменное имя хоста) и CN сертификата (Common Name — обычное имя хоста).

Рекомендуется выполнить эти действия в сеансе пользователя postgres.

Откройте оболочку от имени postgres.
```
[student@ServerName ~]$ sudo -i -u postgres
```
В переменную окружения LNUM запишите номер первой строки с типом подключения, отличным от host (из представления pg_hba_file_rules, поле line_number — номер rows).
```
[postgres@ServerName ~]$ export LNUM=$(psql -At -c "SELECT min(line_number) from pg_hba_file_rules where type != 'host'")
```
Разбор команды
- psql -At — вывод без форматирования (unaligned, no tuples);
- SELECT min(line_number) from pg_hba_file_rules where type != 'host' — запрос к представлению pg_hba_file_rules, возвращающему содержимое pg_hba.conf;
- $(...) — подстановка результата команды в переменную.
```
[postgres@ServerName ~]$ echo $LNUM
```
```
89
```
Добавьте после строки с номером, запомненным в LNUM, правило: host student student <IP адрес хоста> scram-sha-256.
```
[postgres@ServerName ~]$ echo "${LNUM}a\host student student $(hostname -i | tr -dc [0-9.])/32 scram-sha-256" | xargs -i sed -i {} $PGDATA/pg_hba.conf
```
Разбор команды
- $(hostname -i | tr -dc [0-9.]) — IP-адрес хоста (команда tr -dc [0-9.] оставляет только цифры и точки, отсекая IPv6);
- "${LNUM}a\..." — строка для вставки после строки с номером $LNUM (команда a — append);
- | xargs -i sed -i {} — выполнение sed -i с подставленным аргументом из предыдущей команды.
Проверьте, что получилось в файле pg_hba.conf, получив с помощью sed его содержимое от строки, содержащей в начале слово local, до конца файла.
```
[postgres@ServerName ~]$ sed -n '/^local/,$p' $PGDATA/pg_hba.conf
```
```
local                       all                all                                                 peer
host                      student            student                 10.0.2.15/32               scram-sha-256
# IPv4 local connections:
host                       all                 all                   127.0.0.1/32               scram-sha-256
# IPv6 local connections:
host                       all                 all                      ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local                 replication              all                                                 peer
host                  replication              all                   127.0.0.1/32              scram-sha-256
host                  replication              all                     ::1/128                 scram-sha-256
```
Если вывод приведенной выше команды отличается от вашего, то просто скопируйте в обычном текстовом редакторе в файл pg_hba.conf строки, выведенные sed.

Перезагрузите конфигурацию сервера.
```
[postgres@ServerName ~]$ psql -c 'SELECT pg_reload_conf()'
```
```
pg_reload_conf
----------------
t
(1 row)
```
Выйдите из сеанса postgres.
```
[postgres@ServerName ~]$ exit
```
Подключитесь в режиме sslmode=verify-full, указав IPv4-адрес сервера.

[student@ServerName ~]$ psql -x "host=$(hostname -i) password=student sslmode=verify-full" -c 'SELECT * from pg_stat_ssl'

-[ RECORD 1 ]-+-----------------------
pid           | 93891
ssl           | t
version       | TLSv1.3
cipher        | TLS_AES_256_GCM_SHA384
bits          | 256
client_dn     |
client_serial |
issuer_dn     |

При генерации сертификата сервера был указан атрибут IP.1 = 10.0.2.15, что и позволяет подключиться, указав IPv4-адрес.

Попробуйте сделать то же, указав вместо IPv4-адреса FQDN сервера.

[student@ServerName ~]$ psql -x "host=$(hostname) password=student sslmode=verify-full" -c 'SELECT * from pg_stat_ssl'

-[ RECORD 1 ]-+-----------------------
pid           | 93891
ssl           | t
version       | TLSv1.3
cipher        | TLS_AES_256_GCM_SHA384
bits          | 256
client_dn     |
client_serial |
issuer_dn     |

Возможно, вместо выведенного ответа вы получите ошибку вида.

psql: ошибка: подключиться к серверу "{server_name.class.edu}" (fe80::a00:27ff:fe48:5b30), порту 5432 не удалось: FATAL:  no pg_hba.conf entry for host "fe80::a00:27ff:fe48:5b30%enp0s3", user "student", database "student",
SSL encryption

Причина в том, что не работает разрешение имен для IPv4-адреса сервера. В этом случае, выполнив простой пинг, вы увидите в ответе IPv6.

[student@ServerName ~]$ ping -c3 `hostname`

PING {server_name.class.edu}({server_name.class.edu} (fe80::a00:27ff:fe48:5b30%enp0s3)) 56 data bytes
bytes from {server_name.class.edu} (fe80::a00:27ff:fe48:5b30%enp0s3): icmp_seq=1 ttl=64 time=0.029 ms
bytes from {server_name.class.edu} (fe80::a00:27ff:fe48:5b30%enp0s3): icmp_seq=2 ttl=64 time=0.041 ms
bytes from {server_name.class.edu} (fe80::a00:27ff:fe48:5b30%enp0s3): icmp_seq=3 ttl=64 time=0.041 ms

Исправьте ситуацию, добавив в файл /etc/hosts статическое соответствие доменного имени сервера IPv4-адресу его основного интерфейса.

[student@ServerName ~]$ echo "$(hostname -i) $(hostname) $(hostname -s)" | sudo tee -a /etc/hosts

Проверьте содержимое /etc/hosts.

[student@ServerName ~]$ cat /etc/hosts

127.0.0.1       localhost.localdomain localhost
10.0.2.15 {server_name.class.edu} ServerName

Проверьте разрешение имен.

[student@ServerName ~]$ ping -c3 `hostname`

PING {server_name.class.edu} (10.0.2.15) 56(84) bytes of data.
64 bytes from {server_name.class.edu} (10.0.2.15): icmp_seq=1 ttl=64 time=0.026 ms
64 bytes from {server_name.class.edu} (10.0.2.15): icmp_seq=2 ttl=64 time=0.026 ms
64 bytes from {server_name.class.edu} (10.0.2.15): icmp_seq=3 ttl=64 time=0.024 ms

--- {server_name.class.edu} ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2059ms
rtt min/avg/max/mdev = 0.024/0.025/0.026/0.000 ms

Теперь разрешение имен для IPv4-адреса сервера работает. Попробуйте подключиться.

[student@ServerName ~]$ psql -x "host=$(hostname) password=student sslmode=verify-full" -c 'SELECT * from pg_stat_ssl'

-[ RECORD 1 ]-+-----------------------
pid           | 94100
ssl           | t
version       | TLSv1.3
cipher        | TLS_AES_256_GCM_SHA384
bits          | 256
client_dn     |
client_serial |
issuer_dn     |

Подключение успешно — имя хоста разрешается в IPv4, сертификат проверен по CA и SAN.

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

Создайте запрос на подпись клиентского сертификата.

Перейдите в каталог .postgresql.
```
[student@ServerName ~]$ cd .postgresql/
```
```
[student@ServerName .postgresql]$ pwd
```
```
/home/student/.postgresql
```
Сгенерируйте ключ и CSR.
```
[student@ServerName .postgresql]$ openssl req -new -nodes -text -out ./postgresql.csr -keyout ./postgresql.key -subj "/CN=student"
```
Разбор команды
- openssl req -new — создание нового запроса на подпись сертификата (CSR);
- -nodes — не шифровать приватный ключ паролем;
- -text — вывести текстовое представление CSR и ключа;
- -out ./postgresql.csr — файл запроса на подпись сертификата клиента;
- -keyout ./postgresql.key — файл приватного ключа клиента;
- -subj "/CN=student" — уникальное имя клиента, где CN (Common Name) = student.
```
Generating a RSA private key
........+++++ ................................................................................ .....+++++
writing new private key to './postgresql.key'
-----
```

Подпишите запрос на сертификацию и получите сертификат.

[student@ServerName .postgresql]$ sudo openssl x509 -req -in ./postgresql.csr -text -days 3650 -CA ./root.crt -CAkey /etc/pangolin/ssl/CA/CA.key -CAcreateserial -out ./postgresql.crt
[student@ServerName .postgresql]$ sudo openssl x509 -req -in ./postgresql.csr -text -days 3650 -CA ./root.crt -CAkey /etc/pangolin/ssl/CA/CA.key -CAcreateserial -out ./postgresql.crt

Разбор команды

openssl x509 -req — обработка запроса на подпись сертификата (CSR);
-in ./postgresql.csr — входной файл запроса клиента;
-text — вывести текстовое представление подписанного сертификата;
-days 3650 — срок действия сертификата (10 лет);
-CA ./root.crt — сертификат центра сертификации (ссылка на CA);
-CAkey /etc/pangolin/ssl/CA/CA.key — приватный ключ CA для подписи (требуются права sudo);
-CAcreateserial — создать файл серийного номера;
-out ./postgresql.crt — выходной файл подписанного сертификата клиента.

Signature ok
subject=CN = student
Getting CA Private Key

Установите владельца сертификата.

[student@ServerName .postgresql]$ sudo chown student:student postgresql.crt

[student@ServerName .postgresql]$ ls -ltr

total 16
lrwxrwxrwx 1 student student 27 сен 27 13:26 root.crt -> /etc/pangolin/ssl/CA/CA.crt
-rw------- 1 student student 1704 сен 29 19:36 postgresql.key
-rw-r--r-- 1 student student 3316 сен 29 19:36 postgresql.csr
-rw-r--r-- 1 root root 41 сен 29 19:37 root.srl
-rw------- 1 student student 1704 сен 29 19:36 postgresql.key
-rw-r--r-- 1 student student 3316 сен 29 19:36 postgresql.csr
-rw-r--r-- 1 root root 41 сен 29 19:37 root.srl
-rw-r--r-- 1 student student 3226 сен 29 19:37 postgresql.crt

Проверьте возможность подключения.

Вернитесь в домашний каталог.

[student@ServerName .postgresql]$ cd

[student@ServerName ~]$ psql -x "host=$(hostname) password=student sslmode=prefer"

psql (15.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

student@student=> SELECT * FROM pg_stat_ssl \gx

-[ RECORD 1 ]-+--------------------------------------------------------------
pid           | 115125
ssl           | t
version       | TLSv1.3
cipher        | TLS_AES_256_GCM_SHA384
bits          | 256
client_dn     | /CN=student
client_serial | 389951548294704679529084448680543693286257451745
issuer_dn     | /C=RU/ST=Moscow_reg./L=Moscow/O=PangolinEDU/CN=PangolinEDU_CA

Клиентский сертификат распознан сервером — client_dn содержит /CN=student.

student@student=> \q

Расширение sslinfo

Подключите к базе данных student расширение sslinfo.

[student@ServerName ~]$ sudo -Eu postgres psql -d student -c 'create extension sslinfo' 2> /dev/null

CREATE EXTENSION

Подключитесь к БД student в сеансе student и проверьте, подключено ли расширение.

[student@ServerName ~]$ psql

psql (15.5)
Type "help" for help.

student@student=> \dx

              List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------------
plpgsql  |  1.1    | pg_catalog | PL/pgSQL procedural language
sslinfo  |  1.2    | public     | information about SSL certificates
(2 rows)

Получите список функций в расширении.

student@student=> \dx+ sslinfo

Objects in extension "sslinfo"
      Object description
------------------------------------
 function ssl_cipher()
 function ssl_client_cert_present()
 function ssl_client_dn()
 function ssl_client_dn_field(text)
 function ssl_client_serial()
 function ssl_extension_info()
 function ssl_issuer_dn()
 function ssl_issuer_field(text)
 function ssl_is_used()
 function ssl_version()
(10 rows)

Средствами расширения проверьте, включен ли SSL. Затем инициируйте сессию с SSL и проверьте снова.

Подключитесь к серверу по UNIX-сокету.
```
[student@ServerName ~]$ psql -c "SELECT ssl_is_used();"
```
```
 ssl_is_used
-------------
 f
(1 row)
```
Подключение по UNIX-сокету не использует SSL.

Внимание!
Вам необходимо указать имя вашего хоста вместо {server_name.class.edu} ниже.

Переподключитесь через TCP/IP.
```
student@student=> \c "host={server_name.class.edu} password=student"
```
```
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "student" as user "student" (server "{server_name.class.edu}": ip "10.0.2.15", port "5432").
```
```
student@student=> SELECT ssl_is_used();
```
```
 ssl_is_used
-------------
 t
(1 row)
```
Подключение через TCP/IP использует SSL.

Получите сертификаты клиента и издателя.

student@student=> SELECT ssl_client_dn();

 ssl_client_dn
---------------
 /CN=student
(1 row)

student@student=> SELECT ssl_issuer_dn();

                        ssl_issuer_dn
---------------------------------------------------------------
 /C=RU/ST=Moscow_reg./L=Moscow/O=PangolinEDU/CN=PangolinEDU_CA
(1 row)

Сертификат клиента содержит CN=student, издатель — PangolinEDU_CA.

student@student=> \q

Аутентификация по сертификату

Измените метод аутентификации для подключения пользователя student к БД student на cert. Также замените тип подключения на hostssl.

Выполните замену в pg_hba.conf.

[student@ServerName ~]$ sudo -Eu postgres sed -i.bak 's/^host\(.*student.*32\).*$/hostssl\1 cert/' $PGDATA/pg_hba.conf

Разбор команды

sed -i.bak — in-place редактирование с бэкапом;
's/^host$.*student.*32$.*$/hostssl\1 cert/' — замена host на hostssl и метода аутентификации на cert в строке, содержащей student и 32 (маску подсети).

Проверьте результат.

[student@ServerName ~]$ sudo -Eu postgres tail -12 $PGDATA/pg_hba.conf

# "local" is for Unix domain socket connections only
local              all                all                                         peer
hostssl          student            student           10.0.2.15                   cert
# IPv4 local connections:
host               all                all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host               all                all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the # replication privilege.
local           replication           all                                         peer
host            replication           all              127.0.0.1/32           scram-sha-256
host            replication           all              ::1/128                scram-sha-256

Перечитайте и проверьте конфигурацию.

[student@ServerName ~]$ sudo -Eu postgres psql -c 'SELECT pg_reload_conf()'

[student@ServerName ~]$ sudo -Eu postgres psql -c 'SELECT * FROM pg_hba_file_rules'

line_number  | type    | database     | user_name    | address   |   netmask                               |   auth_method   |
-------------+---------+--------------+--------------+-----------+-----------------------------------------+-----------------+--------------------------
    | local   | {all}        | {all}        |           |                                         | peer            |
    | hostssl | {student}    | {student}    | 10.0.2.15 | 255.255.255.255                         | cert            | {clientcert=verify-full}
    | host    | {all}        | {all}        | 127.0.0.1 | 255.255.255.255                         | scram-sha-256   |
    | host    | {all}        | {all}        | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256   |
    | local   | {replication}| {all}        |           |                                         | peer            |
    | host    | {replication}| {all}        | 127.0.0.1 | 255.255.255.255                         | scram-sha-256   |
    | host    | {replication}| {all}        | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256   |
(7 rows)

Строка 90 показывает, что для пользователя student с IP 10.0.2.15 настроена аутентификация cert через hostssl.

Разрешите аутентификацию cert, а затем перезапустите сервер.
```
[student@ServerName ~]$ sudo -Eu postgres sed -i.bak "s/^enabled_extra_auth_methods.*$/enabled_extra_auth_methods = 'scram-sha-256, peer, cert'/" $PGDATA/postgresql.conf
```
Разбор команды
- sudo -Eu postgres — выполнение от пользователя postgres с сохранением переменных окружения (-E);
- sed -i.bak — in-place редактирование с сохранением бэкапа (расширение .bak);
- "s/^enabled_extra_auth_methods.*$/enabled_extra_auth_methods = 'scram-sha-256, peer, cert'/" — замена всей строки, начинающейся с enabled_extra_auth_methods, на новое значение, добавляя cert к разрешенным методам аутентификации;
- $PGDATA/postgresql.conf — целевой файл конфигурации.
Перезапустите сервер:
```
[student@ServerName ~]$ sudo systemctl restart postgresql
```

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

[student@ServerName ~]$ psql -h $(hostname)

psql (15.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

student@student=> SELECT * FROM pg_stat_ssl \gx

-[ RECORD 1 ]-+--------------------------------------------------------------
pid           | 116416
ssl           | t
version       | TLSv1.3
cipher        | TLS_AES_256_GCM_SHA384
bits          | 256
client_dn     | /CN=student
client_serial | 389951548294704679529084448680543693286257451745
issuer_dn     | /C=RU/ST=Moscow_reg./L=Moscow/O=PangolinEDU/CN=PangolinEDU_CA

Аутентификация по сертификату прошла успешно — пароль не запрашивается, client_dn содержит /CN=student.

student@student=> \q

Самопроверка

Вопрос 1

Какие параметры postgresql.conf нужны для включения SSL на сервере без проверки подлинности клиента?

Вопрос 2

Сертификат сервера содержит SAN: DNS=server.class.edu, IP:10.0.2.15. Клиент подключается командой:

psql "host=localhost sslmode=verify-full"

Что произойдет?

Ошибки не будет — verify-full проверяет только CAПодключение не удалось: имя localhost не совпадает с SAN/CN сертификатаПодключение удалось: verify-full игнорирует имя хоста при подключении к localhostОшибки не будет — клиент автоматически подставит FQDN

Вопрос 3

При создании самоподписанного сертификата CA используется опция -extensions v3_ca. Что она делает?

Устанавливает критический флаг CA:TRUE в сертификатеШифрует сертификат с использованием алгоритма v3Создает сертификат с минимальным сроком действияВключает расширенное использование ключа clientAuth

Вопрос 4

В выводе pg_stat_ssl для текущего сеанса:

pid   | ssl | version |         cipher         | bits | client_dn | client_serial | issuer_dn
578151 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 |           |               |

Какой тип соединения используется?

Незасекреченное соединениеЗасекреченное SSL-соединение без проверки подлинности клиентаЗасекреченное SSL-соединение с проверкой подлинности клиентаЗасекреченное SSL-соединение с двусторонней аутентификацией

Вопрос 5

В pg_hba.conf настроено:

hostnossl all             all             all                   reject
hostssl   all             all             all                   scram-sha-256

Разрешены ли незасекреченные соединения?

Разрешены для всех пользователейРазрешены только для пользователей с парольной аутентификациейНе разрешены

Вопрос 6

В pg_hba.conf указано:

hostssl   all   all   all   cert

Параметр enabled_extra_auth_methods содержит scram-sha-256, peer. Что произойдет при попытке подключения по SSL?

Подключение по SSL будет разрешено — метод cert не зависит от enabled_extra_auth_methodsПодключение по SSL будет отклонено — метод cert отсутствует в enabled_extra_auth_methodsПодключение по SSL будет разрешено, но метод cert будет заменен на scram-sha-256Сервер откажется запускаться

Вопрос 7

Клиент подключается по SSL с клиентским сертификатом, содержащим CN=student. Какая роль PostgreSQL будет использована для аутентификации при методе cert?

Суперпользователь сервераРоль publicРоль, имя которой совпадает с CN сертификата (student)Роль, указанная в параметре ssl_ca_file

Вопрос 8

Какие функции предоставляет расширение sslinfo? Выберите все верные варианты.

ssl_is_used() — возвращает true,если SSL включен для текущего сеанса

ssl_client_dn() — возвращает DN клиентского сертификата

ssl_issuer_dn() — возвращает DN издателя сертификата

ssl_enable() — включает SSL для текущего сеанса

ssl_cipher() — возвращает информацию о шифре

Вопрос 9

Клиент подключается к серверу по UNIX-сокету. SSL на сервере включен. Что вернет ssl_is_used()?

t (true) — SSL включен для всех подключенийf (false) — UNIX-сокеты не используют SSLNULL — для UNIX-сокетов SSL не определенЗависит от настроек pg_hba.conf

Вопрос 10

Клиент подключается командой:

psql "host=localhost password=student sslmode=verify-ca"

Но клиентский файл ~/.postgresql/root.crt отсутствует. Что произойдет?

Подключение будет успешным — verify-ca работает без CA-сертификатаПодключение не удалось — клиент не может найти файл корневого сертификата CAПодключение будет успешным — клиент автоматически найдет CA в pg_hba.confПодключение не удалось — требуется клиентский сертификат

Авторизация и аутентификация​

Создание сертификата центра сертификации​

Создание сертификата сервера​

Настройка сервера​

Настройка разрешений в pg_hba.conf​

Проверка работы SSL​

Создание клиентского сертификата​

Расширение sslinfo​

Аутентификация по сертификату​

Самопроверка​

Вопрос 1

Вопрос 2

Вопрос 3

Вопрос 4

Вопрос 5

Вопрос 6

Вопрос 7

Вопрос 8

Вопрос 9

Вопрос 10

Авторизация и аутентификация

Создание сертификата центра сертификации

Создание сертификата сервера

Настройка сервера

Настройка разрешений в pg_hba.conf

Проверка работы SSL

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

Расширение sslinfo

Аутентификация по сертификату

Самопроверка