05 Занятие 05: Методы аутентификации / Подключение и аутентификация

Page 1: Методы аутентификации / Подключение и аутентификация

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

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

Использование материалов курса

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

Обратная связь

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

Отказ от ответственности

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

Page 2: Темы

• Процедура подключения
• Параметры подключения
• Простая аутентификация
• Аутентификация по паролю
• Аутентификация средствами ОС

Page 3: Процедура подключения

Процедура подключения

• Задачи при подключении
• Основные настройки
• Схема обработки конфигурационного файла

Page 4: Процедура подключения

{#source-figure-004 caption=“Source slide page 4: Процедура подключения”}

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

При подключении к серверу клиент отправляет сообщение процессу postmaster.

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

Page 5: Задачи при подключении

{#source-figure-005 caption=“Source slide page 5: Задачи при подключении”}

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

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

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

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

В-третьих, он авторизует пользователя, то есть определяет, имеет ли тот право подключения (эта задача пересекается с функционалом привилегий).

Иногда все три задачи называют общим словом «аутентификация».

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

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

https://postgrespro.ru/docs/postgresql/16/client-authentication

Page 6: Основные настройки

pg\_hba.conf

• конфигурационный файл, при изменении нужно перечитать
• строка — набор полей, разделитель — пробел или табуляция
• пустые строки и текст после # игнорируются
• Поля
  • тип подключения
  • имя базы данных
  • имя пользователя
  • адрес узла
  • метод аутентификации
  • необязательные дополнительные параметры в виде имя=значение
• параметры подключения

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

Настройки аутентификации хранятся в конфигурационном файле, который используется подобно postgresql.conf, но отличается от него по формату. Файл называется pg_hba.conf (от «host-based authentication»), его расположение определяется параметром hba_file. Он генерируется при создании кластера баз данных командой initdb.

Файл pg_hba.conf содержит строки настройки аутентификации, каждая из которых считается отдельной записью.

Пустые строки и комментарии (все после символа #) игнорируются.

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

В случае, если запись в pg_hba.conf получается длинной и неудобной для восприятия, ее можно разбить на несколько строк, вставляя непосредственно перед переводом строки символ обратной косой черты \. Такая запись, состоящая из нескольких строк, читается как единое целое.

Также можно подключать дополнительные файлы настроек аутентификации с помощью директив include, include_if_exists или include_dir.

При изменении pg_hba.conf необходимо перечитать настройки с помощью pg_ctl reload или вызовом функции pg_reload_conf.

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

Page 7: Схема обработки

Записи просматриваются сверху вниз

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

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

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

Когда клиент пытается установить соединение, серверу известны параметры подключения: тип (локально или по сети), запрашиваемое имя базы данных, имя пользователя, в случае подключения по сети — IP-адрес клиента. Обслуживающий процесс последовательно проверяет записи файла pg_hba.conf на соответствие этим параметрам.

Если подходящая запись нашлась, выполняется аутентификация указанным в ней методом. При успешной аутентификации подключение разрешается, иначе — запрещается (следующие записи при этом уже не рассматриваются).

Если ни одна из записей не подошла, то доступ запрещается.

Записи в файле pg_hba.conf должны идти сверху вниз от частного к общему. Иначе запись более общего вида сработает раньше и до частного случая дело не дойдет.

В примере на слайде видно три строки:

● первая относится к локальным подключениям (local) через Unix-сокет для любых баз (all) и пользователей (all);

● вторая и третья относятся к подключениям (host) через закольцовывающий (loopback) сетевой интерфейс (которому соответствует имя хоста localhost) по адресам IPv4 127.0.0.1/32 и IPv6 ::1/128.

В такой конфигурации PostgreSQL допускает соединения только с локального компьютера через Unix-сокет или loopback-интерфейс.

Далее возможные значения полей рассматриваются более подробно.

Page 8: Содержимое файла pg_hba.conf

Расположение конфигурационного файла:

student=# SHOW hba_file;

              hba_file
-------------------------------------
 /etc/postgresql/16/main/pg_hba.conf
(1 row)

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

student$ sudo tail -17 /etc/postgresql/16/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Include additional configuration files:
include_dir /etc/postgresql/16/main/pg_hba.conf.d

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# 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                                     trust
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

В виртуальной машине курса в конфигурационный файл аутентификации добавлена директива include_dir, позволяющая помещать в подкаталог дополнительные файлы конфигурации, а настройки в основном файле разрешают любые локальные подключения. Строки в дополнительных файлах интерпретируются по тем же правилам, что и в основном файле. Поскольку директива include_dir указана в начале основного файла, правила из дополнительных файлов будут иметь приоритет. Файлы в каталоге обрабатываются по порядку имен, поэтому последовательность применения правил можно задавать, например, начиная имена файлов с цифр. Строки с именем replication задают отдельные разрешения для протокола репликации, который подробно рассматривается в курсе DBA3 «Резервное копирование и репликация».

Page 9: Параметры подключения

Тип подключения

• Базы данных
• Пользователи
• Адреса узлов

Page 10: Тип подключения

local

• локальное подключение через Unix-сокет
• host
  • сетевое подключение по TCP/IP
  • listen_addresses = localhost
• hostssl, hostnossl
  • сетевое подключение по TCP/IP с SSL-шифрованием и без него

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

В поле типа подключения можно задать одно из значений:

● local — подключение через Unix-сокет;

● host — сетевое подключение через TCP/IP;

Параметр listen_addresses определяет сетевые адреса, которые PostgreSQL прослушивает на предмет входящих соединений. Значение по умолчанию — localhost — разрешает подключения только с локального компьютера через loopback-интерфейс. Чтобы подключаться к серверу удаленно, потребуется задать другие адреса. В частности, при значении * запросы на соединение принимаются со всех сетевых интерфейсов сервера.

● hostssl — сетевое соединение с использованием SSL-шифрования (сервер должен быть собран с поддержкой SSL и требуется установить параметр конфигурации ssl = on);

● hostnossl — сетевое соединение без использования SSL-шифрования.

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

Page 11: Базы данных

БД

• БД с указанным именем (возможно, в кавычках) sameuser, samerole
• БД, совпадающая по имени с пользователем или с любой ролью, в которую он входит all
• подключение к любой БД значение[, значение…]
• несколько вариантов из вышеперечисленного @имя-файла
  • ссылка на файл с именами

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

В поле баз данных можно задать одно из значений, перечисленных ниже, или несколько таких значений через запятую:

● имя конкретной базы данных;

Если значение начинается с косой черты /, то оно интерпретируется как регулярное выражение.

● sameuser — база данных, совпадающая по имени с пользователем;

● samerole — база данных, совпадающая по имени с какой-либо ролью, в которую входит пользователь (в том числе с самим именем пользователя, поскольку пользователь — тоже роль);

● all — любая база данных;

● @ — ссылка на файл с именами баз данных. В файле имена могут быть разделены запятыми, пробелами, табуляциями или переводами строк. Допускаются вложенные подключения файлов (@) и комментарии (#).

Page 12: Пользователи

пользователь

• пользователь с указанным именем (возможно, в кавычках)
• +пользователь
  • пользователи, входящие в указанную роль
• all
  • любой пользователь
• значение[, значение…]
  • несколько вариантов из вышеперечисленного
• @имя-файла
  • ссылка на файл с именами

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

В поле пользователей можно указать одно из значений, перечисленных ниже, или несколько таких значений через запятую:

● имя — пользователь с указанным именем, если значение начинается с косой черты /, оно интерпретируется как регулярное выражение;

● +имя — пользователь с указанным именем и все пользователи, входящие в его роль;

● all — любой пользователь;

● @ — ссылка на файл с именами пользователей. В файле имена могут быть разделены запятыми, пробелами, табуляциями или переводами строк. Допускаются вложенные подключения файлов (@) и комментарии (#).

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

Page 13: Адреса клиентов

адрес

• диапазон IP-адресов с длиной префикса (например, 172.20.143.0/24)
• или IP-адрес подсети с маской (172.20.143.0 255.255.255.0)
• samehost, samenet
  • любые IP-адреса интерфейсов сервера
  • или принадлежащие любым подсетям интерфейсов сервера
• доменное_имя
  • IP-адреса, соответствующие указанному имени (например, domain.com)
  • или части имени (.com)
• all
  • любой IP-адрес

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

В поле адресов может быть указано одно из следующих значений:

● all — любой IP-адрес клиента;

● IP-адрес с длиной префикса (CIDR-нотация) — диапазон допустимых адресов IPv4 или IPv6 клиентов, например, ::1/128 — адрес localhost для IPv6, а 127.0.0.1/32 — то же самое для IPv4;

● IP-адрес и маска подсети — диапазон допустимых IPv4-адресов, например, 192.168.1.0 255.255.255.128 — адрес подсети, которой соответствует CIDR-нотация 192.168.1.0/25;

● samehost — IP-адреса интерфейсов сервера;

● samenet — IP-адреса из любой подсети, которой принадлежат адреса интерфейсов сервера;

● доменное имя (или часть доменного имени, начиная с точки) — IP-адреса, соответствующие данному имени.

Принадлежность IP-адреса клиента указанному домену проверяется в два шага. Сначала по IP-адресу определяется доменное имя (reverse lookup), а затем проверяется, что такому домену действительно соответствует исходный IP-адрес (forward lookup). Таким образом проверяется соответствие владельца сети и владельца доменного имени для отсечения скомпрометированных адресов:

https://en.wikipedia.org/wiki/Forward-confirmed\_reverse\_DNS

Page 14: Простая аутентификация

Ничего не проверяет

Page 15: Простая аутентификация

trust

• допустить без аутентификации
• reject
  • отказать без аутентификации

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

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

Метод trust безусловно доверяет пользователю и не выполняет проверку. В реальной жизни имеет смысл применять разве что для локальных соединений.

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

Page 16: Вопрос

Что обозначает приведенная ниже настройка?

# TYPE    DATABASE        USER            ADDRESS               METHOD
hostnossl all             all             all                   reject
host      sameuser        all             samenet               trust
host      pub             +reader         all                   trust

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

1. Запрещаются нешифрованные соединения.

2. Разрешается доступ пользователям из своей подсети к одноименным базам данных.

3. Разрешается доступ пользователям, входящим в роль reader, к базе данных pub.

Обратите внимание, что первую строку нельзя сместить вниз — смысл настройки изменится.

Page 17: Редактирование файла pg_hba.conf

Получить содержимое pg_hba.conf в SQL запросе позволяет представление pg_hba_file_rules:

student=# SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules;

 line_number | type  |   database    | user_name |  address  |  auth_method
-------------+-------+---------------+-----------+-----------+---------------
         120 | local | {all}         | {all}     |           | trust
         122 | host  | {all}         | {all}     | 127.0.0.1 | scram-sha-256
         124 | host  | {all}         | {all}     | ::1       | scram-sha-256
         127 | local | {replication} | {all}     |           | trust
         128 | host  | {replication} | {all}     | 127.0.0.1 | scram-sha-256
         129 | host  | {replication} | {all}     | ::1       | scram-sha-256
(6 rows)

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

/etc/postgresql/16/main/pg_hba.conf.d/10_hba.conf

# TYPE  DATABASE  USER   ADDRESS  METHOD
local   all       all             trast

student=# SELECT line_number, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL;

 line_number |                 error
-------------+---------------------------------------
           2 | invalid authentication method "trast"
(1 row)

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

Page 18: Аутентификация по паролю

Сервер запрашивает у клиента пароль

Page 19: Парольная аутентификация

password

• передается в незашифрованном виде
• md5
  • передается MD5-хеш
• scram-sha-256
  • используется протокол SCRAM

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

При парольной аутентификации сервер PostgreSQL запрашивает у пользователя пароль и проверяет его на соответствие паролю, который хранится либо в самой СУБД, либо во внешней службе.

https://postgrespro.ru/docs/postgresql/16/protocol-flow#PROTOCOL-FLOW-START-UP

Для паролей, хранящихся в СУБД, поддерживаются три метода.

Метод md5 сравнивает MD5-дайджест пароля с MD5-дайджестом, хранящимся в базе.

В настоящее время использовать этот метод не стоит: он недостаточно криптостойкий и обладает несколькими серьезными недостатками, а в PostgreSQL 19 его уже не будет.

Наиболее безопасный метод scram-sha-256 использует при аутентификации протокол SCRAM и задействует криптостойкий алгоритм SHA-256. Метод реализует фреймворк SASL, отделяющий механизм аутентификации от прикладного протокола.

https://postgrespro.ru/docs/postgresql/16/sasl-authentication

Метод password предполагает передачу пароля в незашифрованном виде. Его не следует применять, если клиент-серверное соединение не зашифровано.

Более подробная информация об этих методах аутентификации приводится в статье Питера Эйзентраута: https://www.enterprisedb.com/blog/tale-password-authentication-methods-p ostgresql

Page 20: Пароль в СУБД

Установить пароль пользователя

[ CREATE | ALTER ] ROLE ...
  PASSWORD 'пароль'
[ VALID UNTIL дата_время ];

• пользователю с пустым паролем будет отказано в доступе
• при аутентификации по паролю
• Пароли хранятся в системном каталоге
  • pg_authid
  • метод шифрования определяется параметром password_encryption
  • метод аутентификации должен совпадать с методом шифрования
  • (md5 автоматически переключается на scram-sha-256)

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

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

Пароль хранится в базе данных в таблице pg_authid.

Чтобы установить пароль, надо указать его — либо сразу при создании роли в команде CREATE ROLE, либо впоследствии в ALTER ROLE. Пароль хранится в зашифрованном виде, алгоритм шифрования (MD5 или SCRAM-SHA-256) определяется параметром password_encryption.

Также можно указать время действия пароля.

Если сохраненный пароль был зашифрован алгоритмом SCRAM-SHA-256 и установлен метод аутентификации MD5, при обмене сообщениями будет использован более надежный метод SCRAM-SHA-256.

Page 21: Парольная аутентификация

Настроим парольную аутентификацию для локальных подключений пользователя student. Сначала проверим, как шифруются пароли при сохранении.

student=# SHOW password_encryption;

 password_encryption
---------------------
 scram-sha-256
(1 row)

Теперь зададим пароль для пользователя student. Пароль может состоять из любых символов Unicode.

student=# ALTER ROLE student PASSWORD 'пароль';

ALTER ROLE

Осталось настроить правила аутентификации:

/etc/postgresql/16/main/pg_hba.conf.d/10_hba.conf

# TYPE  DATABASE  USER   ADDRESS  METHOD
local   all       postgres        trust
local   all       student         scram-sha-256

Если бы мы указали метод MD5, при аутентификации пользователя student все равно использовался бы SCRAM-SHA-256. Но при этом другие пользователи могли бы сохранять пароли, зашифрованные менее криптостойким алгоритмом MD5.

student=# SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

Пытаемся угадать пароль:

student$ psql 'user=student password=1234'

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed:
FATAL:  password authentication failed for user "student"

Теперь с правильным паролем:

student$ psql 'user=student password=пароль' -c '\conninfo'

You are connected to database "student" as user "student" via socket in
"/var/run/postgresql" at port "5432".

Page 22: Ввод пароля

Вручную

• Установить переменную PGPASSWORD
  • неудобно при подключении к разным базам
  • не рекомендуется из соображений безопасности
• Файл с паролями
  • ~/.pgpass на узле клиента
  • строки в формате узел:порт:база_данных:имя_пользователя:пароль
  • в качестве значения можно указать * (любое значение)
  • строки просматриваются сверху вниз, выбирается первая подходящая
  • файл должен иметь права доступа 600 (rw- — —)

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

Пользователь может вводить пароль вручную (или указывать в явном виде в строке подключения, как мы видели в демонстрации), а может автоматизировать ввод. Для этого есть две возможности.

Во-первых, можно задать пароль в переменной окружения PGPASSWORD (на клиенте). Однако это неудобно, если приходится подключаться к нескольким базам, и не рекомендуется из соображений безопасности.

Во-вторых, можно задать пароли в файле ~/.pgpass, доступ к которому должен иметь только владелец (иначе файл будет проигнорирован). Расположение файла можно изменить, задав переменную окружения PGPASSFILE.

Page 23: Ввод пароля

Попробуем подключиться, не указав пароль (ключ -w нужен, чтобы psql не выдавал запрос на ввод пароля):

student$ psql -w 'dbname=student user=student' -c '\conninfo'

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed:
fe_sendauth: no password supplied

Автоматизируем ввод пароля с помощью файла .pgpass, поместив в него строку с паролем пользователя student:

/home/student/.pgpass

localhost:5432:student:student:пароль

Чтобы клиент воспользовался файлом, оставим доступ только владельцу; иначе этот файл будет проигнорирован:

student$ chmod go= ~/.pgpass

student$ ls -l ~/.pgpass

-rw------- 1 student student 44 июл 30 16:35 /home/student/.pgpass

Теперь, если не указать пароль, клиент получит его из этого файла и передаст на сервер:

student$ psql 'dbname=student user=student' -c '\conninfo'

You are connected to database "student" as user "student" via socket in
"/var/run/postgresql" at port "5432".

Пользователь не всегда знает, какой метод аутентификации будет использовать сервер. Допустим, настройки сервера используют метод password:

/etc/postgresql/16/main/pg_hba.conf.d/10_hba.conf

# TYPE  DATABASE  USER   ADDRESS  METHOD
local   all       postgres        trust
local   all       student         password

student=# SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

В этом случае сервер запросит пароль, который будет передан в незашифрованном виде. Если пользователь опасается, что пароль будет перехвачен злоумышленником, он может запретить открытую передачу в параметре соединения require_auth:

student$ psql 'dbname=student user=student require_auth=!password' -c '\conninfo'

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed:
authentication method requirement "!password" failed: server requested a cleartext
password

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

Page 24: Аутентификация ОС

Для локальных подключений

Page 25: Аутентификация peer

{#source-figure-025 caption=“Source slide page 25: Аутентификация peer”}

peer [map=…]

• запрос имени пользователя у ядра ОС (для локальных подключений)

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

Метод аутентификации peer запрашивает имя текущего пользователя у ядра ОС. Поскольку ОС уже аутентифицировала этого пользователя (скорее всего, запросив пароль), мы просто верим ей.

В простейшем случае подключение разрешается, если имена пользователей в ОС и в PostgreSQL совпадают, но, как мы увидим позже, есть возможность задать и более сложное соответствие с помощью сопоставления имен.

Page 26: Аутентификация средствами ОС

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

/etc/postgresql/16/main/pg_hba.conf.d/10_hba.conf

# TYPE  DATABASE  USER   ADDRESS  METHOD
local   all       all             peer

student$ sudo pg_ctlcluster 16 main reload

Подключение для student по-прежнему разрешается, но теперь имя роли сверяется с именем пользователя в ОС.

student$ psql

=> \conninfo

You are connected to database "student" as user "student" via socket in
"/var/run/postgresql" at port "5432".

А подключиться под другим именем не получится.

=> \c - alice

\connect: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed:
FATAL:  Peer authentication failed for user "alice"

Page 27: Сопоставление имен

pg\_ident.conf

• еще один конфигурационный файл
• строка — набор полей, разделитель — пробел или табуляция
• пустые строки и текст после # игнорируются
• Поля
  • название сопоставления
  • (указывается в параметре map в pg_hba.conf)
  • внешнее имя
  • внутреннее имя пользователя БД

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

Сопоставления имен определяются в отдельном файле pg_ident.conf; его расположение задается параметром ident_file. Файл имеет такую же структуру, как и pg_hba.conf, но записи состоят из трех полей: название сопоставления, внешнее имя, внутреннее имя.

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

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

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

Запись, сопоставляющая внутреннее и внешнее имена, означает, что внешнему пользователю разрешено подключаться к СУБД от имени соответствующего внутреннего пользователя после успешной аутентификации.

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

К pg_ident.conf можно подключать дополнительные файлы конфигурации директивами include, include_if_exists или include_dir.

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

Page 28: Вопрос

Что обозначает приведенная ниже настройка?

• pg_hba.conf

# TYPE    DATABASE       USER       ADDRESS       METHOD
local     sameuser       all                      peer map=m1
local     all            all                      peer map=m2
host      all            all        samehost      scram-sha-256

• pg_ident.conf

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
m1              student                 alice
m1              student                 bob
m2              /^a                     /b$

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

1. Для локальных соединений c базой данных, совпадающей по имени с именем роли, PostgreSQL запрашивает имя пользователя у операционной системы. Сопоставление m1 определяет, что пользователь ОС student может подключаться под ролями alice и bob.

2. Правило m2, на которое ссылается вторая строка pg_hba.conf, сопоставляет пользователей OC с именем, которое начинается на букву «a», с ролями, которые заканчиваются на букву «b». Например, пользователь OC alice сможет подключиться под ролью bob. Эта строка будет использована, только если имя базы не совпадает с именем роли: в противном случае первая строка сработает раньше.

3. Сетевые соединения с локальным сервером аутентифицируются с помощью пароля по протоколу SCRAM.

Другие сетевые соединения не допускаются.

Page 29: Сопоставление имен

Допустим, мы хотим разрешить Алисе и Бобу подключаться локально из сеанса пользователя ОС student. Для этого нужно настроить сопоставление имен. Расположение основного файла соответствий имен указывает параметр:

student=# SHOW ident_file;

              ident_file
---------------------------------------
 /etc/postgresql/16/main/pg_ident.conf
(1 row)

Последние шесть строк его содержимого:

student$ sudo tail -6 /etc/postgresql/16/main/pg_ident.conf

# ----------------------------------

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
# Include additional configuration files:
include_dir /etc/postgresql/16/main/pg_ident.conf.d

Добавим в правило аутентификации имя соответствия:

/etc/postgresql/16/main/pg_hba.conf.d/10_hba.conf

# TYPE  DATABASE  USER  ADDRESS  METHOD
local   all       all            peer map=local_users

А само соответствие зададим в дополнительном файле:

/etc/postgresql/16/main/pg_ident.conf.d/local_users.conf

local_users student student
local_users student /^(alice|bob)$

Первая строка соответствия разрешает пользователю ОС student подключаться как student, а во второй для пользователя ОС student указано регулярное выражение, соответствующее Алисе и Бобу. Увидеть содержимое pg_ident.conf и дополнительных файлов можно и через представление:

student=# SELECT map_number, map_name, sys_name, pg_username
FROM pg_ident_file_mappings;

 map_number |  map_name   | sys_name |  pg_username
------------+-------------+----------+----------------
          1 | local_users | student  | student
          2 | local_users | student  | /^(alice|bob)$
(2 rows)

student$ sudo pg_ctlcluster 16 main reload

student=# CREATE USER alice;

CREATE ROLE

student=# CREATE USER bob;

CREATE ROLE

student=# CREATE USER charlie;

CREATE ROLE

Теперь под Алисой и Бобом можно подключиться.

student$ psql

=> \conninfo

You are connected to database "student" as user "student" via socket in
"/var/run/postgresql" at port "5432".

=> \c student alice

Page 30: Page 30

{#source-figure-030 caption=“Source slide page 30: Page 30”}

You are now connected to database "student" as user "alice".

Внешнее имя, сообщенное пользователем системе при идентификации, выводит функция system_user:

=> SELECT system_user;

 system_user
--------------
 peer:student
(1 row)

=> \c student bob

You are now connected to database "student" as user "bob".

=> SELECT system_user;

 system_user
--------------
 peer:student
(1 row)

Внешнее имя, конечно, не изменилось. А под Чарли подключиться не получится:

=> \c student charlie

\connect: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed:
FATAL:  Peer authentication failed for user "charlie"

Page 31: Итоги

При подключении выполняется аутентификация

• Настройки определяются конфигурационным файлом pg_hba.conf
• При парольной аутентификации шифрованные пароли хранятся в системном каталоге и проверяются СУБД
• Локальные подключения можно аутентифицировать с помощью ОС

Page 32: Практика

1. Разрешите локальные подключения суперпользователям и сетевые подключения всем пользователям к любым базам данных с аутентификацией MD5 по паролю. Любые другие подключения должны быть запрещены. 2. Создайте роль alice с паролем, зашифрованным MD5, и роль bob с паролем, зашифрованным SCRAM-SHA-256. 3. Проверьте возможность подключения под созданными ролями. 4. Под суперпользовательской ролью посмотрите пароли пользователей alice и bob в системном каталоге. 5. Восстановите исходную конфигурацию.

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

1. Перед тем как вносить изменения в конфигурационный файл pg_hba.conf, сохраните его исходную версию, чтобы иметь возможность вернуть начальное состояние в п. 5.

Page 33: 1. Настройка аутентификации

Сохраним исходный файл настроек:

student$ sudo cp --update=none /etc/postgresql/16/main/pg_hba.conf ~/tmp/pg_hba.conf.orig

Теперь перезапишем pg_hba.conf с нуля:

/etc/postgresql/16/main/pg_hba.conf

# TYPE  DATABASE  USER      ADDRESS  METHOD
local   all       postgres           trust
local   all       student            trust
host    all       all       all      md5

student$ sudo pg_ctlcluster 16 main reload

2. Создание ролей

student=# SHOW password_encryption;

 password_encryption
---------------------
 scram-sha-256
(1 row)

Алиса будет использовать старый способ шифрования пароля…

student=# SET password_encryption='md5';

SET

student=# CREATE ROLE alice LOGIN PASSWORD 'alice';

CREATE ROLE

…а Боб — современный:

student=# RESET password_encryption;

RESET

student=# CREATE ROLE bob LOGIN PASSWORD 'bob';

CREATE ROLE

3. Проверка подключения

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

student$ psql 'dbname=student user=alice host=localhost password=alice' -c '\conninfo'

You are connected to database "student" as user "alice" on host "localhost" (address
"127.0.0.1") at port "5432".

student$ psql 'dbname=student user=bob host=localhost password=bob' -c '\conninfo'

You are connected to database "student" as user "bob" on host "localhost" (address
"127.0.0.1") at port "5432".

При выполнении этого задания желательно вводить пароль вручную, чтобы убедиться в том, что он запрашивается.

4. Просмотр паролей

student=# SELECT rolname, rolpassword FROM pg_authid WHERE rolname IN ('alice','bob')
\gx (xheader_width=90)

Page 34: Page 34

-[ RECORD 1 ]-----------------------------------------------------------------------------
rolname     | alice
rolpassword | md5579e43b423b454623383471aeb85cd87
-[ RECORD 2 ]-----------------------------------------------------------------------------
rolname     | bob
rolpassword |
SCRAM-SHA-256$4096:5DreNlEAnCLTp4jH1e+JRg==$KriZbAhQDF79+FoDCplAnWFEN3HzWF89P+/ejKwDBqg=:J
NOfTFjriP18Xbs4jWpn1YZIHNWlKR+gh5nSZysIwZo=

Пароли хранятся как значения хеш-функции, не допускающие расшифровки. Сервер всегда сравнивает между собой зашифрованные значения — хеш-код введенного пароля и значение из pg_authid.

Уборка

student$ sudo cp ~/tmp/pg_hba.conf.orig /etc/postgresql/16/main/pg_hba.conf

student$ sudo pg_ctlcluster 16 main reload

student=# \c student student

You are now connected to database "student" as user "student".

student=# DROP ROLE alice, bob;

DROP ROLE

Page 35: Практика+

Определенным пользователям, список которых время от времени меняется, требуется разрешить локальный доступ без авторизации. Сложность в том, что любое изменение списка требует изменения файла pg_hba.conf.

• 1. Настройте аутентификацию, лишенную этого недостатка. 2. Убедитесь в правильности изменений. 3. Восстановите первоначальные настройки.

1. Используйте групповую роль.

Page 36: 1. Настройка аутентификации

Сохраним исходный файл настроек:

student$ sudo cp --update=none /etc/postgresql/16/main/pg_hba.conf ~/tmp/pg_hba.conf.orig

Будем управлять аутентификацией пользователей, добавляя их в группу locals. Перезапишем файл pg_hba.conf:

/etc/postgresql/16/main/pg_hba.conf

# TYPE  DATABASE  USER    ADDRESS  METHOD
local   all       student          trust
local   all       +locals          trust

student$ sudo pg_ctlcluster 16 main reload

Создадим групповую роль:

student=# CREATE ROLE locals;

CREATE ROLE

2. Проверка

Алиса в группе locals:

student=# CREATE ROLE alice LOGIN;

CREATE ROLE

student=# GRANT locals TO alice;

GRANT ROLE

Боб пока не в группе:

student=# CREATE ROLE bob LOGIN;

CREATE ROLE

student$ psql "dbname=student user=alice" -c "\conninfo"

You are connected to database "student" as user "alice" via socket in
"/var/run/postgresql" at port "5432".

student$ psql "dbname=student user=bob" -c "\conninfo"

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed:
FATAL:  no pg_hba.conf entry for host "[local]", user "bob", database "student", no
encryption

Включаем Боба в группу:

student=# GRANT locals TO bob;

GRANT ROLE

student$ psql "dbname=student user=bob" -c "\conninfo"

You are connected to database "student" as user "bob" via socket in "/var/run/postgresql"
at port "5432".

Уборка

student$ sudo cp ~/tmp/pg_hba.conf.orig /etc/postgresql/16/main/pg_hba.conf

student$ sudo pg_ctlcluster 16 main reload

student=# \c student

You are now connected to database "student" as user "student".

student=# DROP ROLE alice, bob, locals;

DROP ROLE

Page 37: Page 37