Основы безопасности PostgreSQL

Роли, членство, защита на уровне строк, аутентификация, SSL и интеграция с внешними системами идентификации.

Основы безопасности PostgreSQL

Роли, членство, защита на уровне строк, аутентификация, SSL и интеграция с внешними системами идентификации.

Курс сохраняет официальный порядок тем. Русские материалы извлечены из PDF в читаемый нативный Markdown для дальнейшей редакторской работы.

Состояние материалов: 官方 PDF 已提取为原生 Markdown

Базовый язык: Базовый язык: Русский. В проверенных открытых каталогах не найден текущий английский архив, поэтому базовой является русская версия.

О курсе

Роли, членство, защита на уровне строк, аутентификация, SSL и интеграция с внешними системами идентификации.

Состояние материалов

Язык	Текущий архив	Версия	Темы	Страница курса
English	Открытый архив не найден	-	-	HTTP 404
Русский	DBS-handouts-16.zip	16	8	HTTP 200

Содержание

Дополнительные материалы

dbs_new_features.pdf
dbs_student_guide.pdf
openssl_commands.pdf

Редакторский статус

Русский комплект доступен в виде нативного Markdown. Если английский комплект не найден, английский маршрут оставлен для будущего источника.

00 Занятие 00: PostgreSQL 16 / Основы безопасности

Исходная страница в нативном Markdown: PostgreSQL 16 / Основы безопасности

Примечание

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: dbs_00_introduction.html Archive: DBS-handouts-16.zip Страниц PDF: 9

Page 1: PostgreSQL 16 / Основы безопасности

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

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

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

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

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

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

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

Page 2: Администраторам

{#source-figure-002 caption=“Source slide page 2: Администраторам”}

DBA2, 4 дня

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

Для прохождения всех курсов необходимы предварительные знания основ языка SQL. Специального курса по языку SQL в нашей линейке курсов нет, но существует множество книг и других образовательных ресурсов, с помощью которых можно освоить SQL. Мы рекомендуем книгу Евгения Моргунова «PostgreSQL. Основы языка SQL»:

https://postgrespro.ru/education/books/sqlprimer

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

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

В курсе DBA2 обсуждается настройка конфигурационных параметров исходя из понимания внутренней организации сервера; говорится о мониторинге сервера с использованием обратной связи для итеративной настройки параметров.

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

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

В курсе PGPRO рассматриваются дополнительные возможности, которые предоставляет СУБД Postgres Pro Enterprise.

Page 3: Разработчикам

{#source-figure-003 caption=“Source slide page 3: Разработчикам”}

DEV2, 4 дня

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

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

Базовым курсом для разработчиков является курс DEV1, в котором даются общие сведения об архитектуре PostgreSQL и использовании основных объектов базы данных; изучается программирование на стороне сервера на языках SQL и PL/pgSQL с использованием основных типов данных (включая составной тип и массивы), а также рассматривается взаимодействие с клиентской частью.

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

Курсы DBS, QPT и PGPRO являются общими для разработчиков и администраторов.

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

https://postgrespro.ru/education/courses

Page 4: О курсе DBS

Продолжительность: 2 дня

Предварительные знания
- знание SQL
- знакомство с Unix
- PostgreSQL в объеме курса DBA1 или DEV1
Какие навыки будут получены
- управление доступом к объектам на основе ролевой модели
- разграничение доступа на уровне строк
- настройка подключения и аутентификации

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

Курс «Основы безопасности PostgreSQL» рассказывает про конфигурирование подключений клиентов к СУБД и про управление доступом внутри самой СУБД. Этот курс предназначен в первую очередь для администраторов, поскольку рассматривает вопросы настройки экземпляра. Но может быть интересен и разработчикам, позволяя взглянуть с другой стороны — в контексте безопасности — на использование ролей и распределение данных по схемам и таблицам.

Page 5: Используемые материалы

Подготовленная виртуальная машина

ОС Xubuntu 24.04
PostgreSQL 16
документация на русском языке
Учебные материалы
- презентации, демонстрации, практические задания и их решение
- (в форматах html и pdf)
- справочный материал — команды создания сертификатов OpenSSL,
- используемых в курсе

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

Если вы проходите курс самостоятельно, обязательно начните со знакомства с Руководством слушателя. В числе прочего в нем написано, где скачать и как использовать виртуальную машину курса и прочие материалы. Все материалы курса, включая Руководство слушателя, доступны по адресу: https://postgrespro.ru/education/courses/DBS

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

Учебные материалы (презентации, демонстрации, практические задания и их решения) доступны в двух форматах. Формат html удобен для онлайн-работы, он позволяет копировать фрагменты текста и кода. Формат pdf разбит на страницы и удобен для печати на бумаге.

Справочный материал может быть полезен для самостоятельной настройки дополнительных SSL-соединений.

Page 6: Виртуальная машина

Имя хоста виртуальной машины:

student$ host $(hostname)

course.local has address 127.0.1.1

Адрес 127.0.1.1 привязан к доменному имени хоста и используется в демонстрациях и практических заданиях курса. Адреса 127.0.0.1 (localhost) и 127.0.1.1 (course.local) связаны с закольцовывающим интерфейсом, но соответствуют разным именам хостов. В виртуальной машине курса инициализирован стандартный кластер main:

student$ pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main
/var/log/postgresql/postgresql-16-main.log

В конфигурационные файлы pg_hba.conf и pg_ident.conf добавлены директивы include_dir, чтобы можно было менять настройки, добавляя и удаляя дополнительные конфигурационные файлы в подкаталогах pg_hba.conf.d и pg_ident.conf.d соответственно.

postgres$ grep ^include_dir /etc/postgresql/16/main/pg_hba.conf

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

postgres$ grep ^include_dir /etc/postgresql/16/main/pg_ident.conf

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

Для темы «Внешняя аутентификация» в виртуальной машине настроены службы OpenLDAP и Kerberos:

student$ systemctl list-units --no-legend krb* slapd*

  krb5-admin-server.service loaded active running Kerberos 5 Admin Server
  krb5-kdc.service          loaded active running Kerberos 5 Key Distribution Center
  slapd.service             loaded active running LSB: OpenLDAP standalone server
(Lightweight Directory Access Protocol)

Page 7: Организационные вопросы

День: ~8 академических часов + обед (1час)

Каждая тема, как правило, состоит из
- презентации и демонстрации: ~40–60 мин
- практических заданий: ~30 мин, включая перерыв

Page 8: День 1

Управление доступом

1. Роли и привилегии
1. Членство в ролях
1. Доступ на уровне строк
1. Подходы к настройке

Page 9: День 2

Методы аутентификации

1. Подключение и аутентификация
1. Использование SSL
1. Внешняя аутентификация

01 Занятие 01: Управление доступом / Роли и привилегии

Исходная страница в нативном Markdown: Управление доступом / Роли и привилегии

Примечание

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: dbs_01_access_roles.html Archive: DBS-handouts-16.zip Страниц PDF: 30

Page 1: Управление доступом / Роли и привилегии

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

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

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

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

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

Page 2: Темы

Роли
Атрибуты
Владелец
Привилегии
Управление привилегиями
Передача права
Начальные привилегии

Page 3: Роли

Роль — учетная запись СУБД и набор прав доступа

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

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

PostgreSQL использует вариант ролевой модели доступа (Role-Based Access Control, RBAC). В классической модели выделяются пользователи (учетные записи) и назначаемые им роли, которые определяют права пользователей. В PostgreSQL оба этих понятия объединены и называются ролью. Таким образом, роль — это и учетная запись СУБД, и набор прав доступа.

Роли задаются на уровне кластера: для всех баз данных набор ролей одинаков. Клиент всегда подключается к базе данных от имени некоторой роли. Формально роли (как учетные записи СУБД) никак не связаны с учетными записями операционной системы, хотя многие программы это предполагают, выбирая значения по умолчанию. Например, psql, запущенный от имени пользователя ОС student, автоматически использует одноименную роль student (если в ключах утилиты не указана какая-либо другая роль).

https://postgrespro.ru/docs/postgresql/16/database-roles

Page 4: Атрибуты

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

LOGIN
возможность подключения
SUPERUSER
суперпользователь
CREATEDB
возможность создавать базы данных
CREATEROLE
возможность создавать роли
REPLICATION
использование протокола репликации
и другие

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

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

Обычно атрибуты имеют два варианта, например, CREATEDB (дает право на создание БД) и NOCREATEDB (не дает такого права).

Если у роли есть атрибут LOGIN, она имеет право подключаться к серверу, то есть является пользователем. Атрибут NOLOGIN запрещает подключение, то есть роль может использоваться только как набор прав (который можно передать другим пользователям, о чем будет говориться в следующей теме «Членство в ролях»).

На слайде перечислены некоторые атрибуты.

https://postgrespro.ru/docs/postgresql/16/role-attributes

https://postgrespro.ru/docs/postgresql/16/sql-createrole

Page 5: Роли и атрибуты

Сейчас в кластере две пользовательские роли:

student=# \du

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 student   | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres — начальный суперпользователь, создается при инициализации кластера, удалить его нельзя, student — тоже суперпользователь, создан для курса, большинство действий в демонстрациях выполняется от его имени; Создадим базу данных:

student=# CREATE DATABASE access_roles;

CREATE DATABASE

student=# \c access_roles

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

Создадим роль для пользователя Алисы с несколькими атрибутами:

student=# CREATE ROLE alice
  LOGIN
  CONNECTION LIMIT 1;

CREATE ROLE

student=# \du alice

      List of roles
 Role name |  Attributes
-----------+--------------
 alice     | 1 connection

Атрибуты роли alice: LOGIN — имеет право подключаться, то есть является пользовательской; CONNECTION LIMIT 1 — не более одного соединения. Обратите внимание, что наличие атрибута LOGIN не показывается. Алиса может подключиться к базе данных:

student$ psql -U alice -d access_roles

alice=> \conninfo

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

Но это подключение — единственное возможное для нее:

student$ psql -U alice -d access_roles -c 'SELECT now();'

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed:
FATAL:  too many connections for role "alice"

Алиса не имеет атрибута CREATEROLE, поэтому она не может создать роль для Боба:

alice=> CREATE ROLE bob;

ERROR:  permission denied to create role
DETAIL:  Only roles with the CREATEROLE attribute may create roles.

Придется использовать сеанс роли с атрибутом SUPERUSER.

student=# \du student

Page 6: Page 6

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 student   | Superuser, Create role, Create DB, Replication, Bypass RLS

student=# CREATE USER bob; -- то же, что CREATE ROLE ... LOGIN

CREATE ROLE

Роль для Боба создана, теперь он может подключаться:

student$ psql -U bob -d access_roles -c '\conninfo'

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

Существующие роли можно изменять. Например, student может отобрать у Боба право входа:

student=# ALTER ROLE bob NOLOGIN;

ALTER ROLE

Метакоманда \du не показывает атрибут LOGIN, но показывает его отсутствие:

student=# \du bob

      List of roles
 Role name |  Attributes
-----------+--------------
 bob       | Cannot login

Вернем Бобу право подключаться к базам данных кластера:

student=# ALTER ROLE bob LOGIN;

ALTER ROLE

Теперь он снова может начать сеанс:

student$ psql -U bob -d access_roles

А Алису освободим от ограничения на количество сеансов:

student=# ALTER ROLE alice CONNECTION LIMIT -1;

ALTER ROLE

student=# \du alice|bob

     List of roles
 Role name | Attributes
-----------+------------
 alice     |
 bob       |

Page 7: Привилегия и владелец

Привилегия

дает роли право на определенные действия с объектом Владелец объекта
- изначально — создавшая его роль, может быть переназначен
- получает все привилегии для этого объекта
- имеет право на действия, не регулируемые привилегиями

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

Привилегия на объект, полученная ролью, дает ей право на определенные действия с этим объектом. Например, привилегия SELECT на таблицу позволяет читать данные из нее.

Когда роль создает какой-либо объект, она по умолчанию становится его владельцем. Для некоторых объектов команда создания позволяет указать другого владельца. При необходимости владельца объекта можно сменить командой ALTER с предложением OWNER TO.

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

В дополнение к таким неотъемлемым правам, при создании объекта владелец получает все возможные привилегии на него. Формально эти привилегии могут быть отозваны суперпользователем, однако владелец всегда может их вернуть, поскольку имеет право на выдачу привилегий, в том числе и самому себе.

Page 8: Владелец

Создадим схему alice, сразу назначив владельца:

student=# CREATE SCHEMA alice AUTHORIZATION alice;

CREATE SCHEMA

student=# \dn alice

List of schemas
 Name  | Owner
-------+-------
 alice | alice
(1 row)

Алиса может создавать объекты в своей схеме:

alice=> CREATE TABLE t AS SELECT 'Таблица Алисы' msg;

SELECT 1

student=# \dt *.t

       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 alice  | t    | table | alice
(1 row)

Как владелец, Алиса имеет право на все действия с таблицей, например, построить индекс:

alice=> CREATE INDEX ON t(msg);

CREATE INDEX

Поменяем владельца таблицы:

student=# ALTER TABLE alice.t OWNER TO student;

ALTER TABLE

student=# \dt *.t

        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+---------
 alice  | t    | table | student
(1 row)

Теперь Алиса не может перестроить индекс:

alice=> REINDEX TABLE t;

ERROR:  must be owner of table t

Это право привилегиями не регулируется.

Page 9: Привилегии

{#source-figure-009 caption=“Source slide page 9: Привилегии”}

Табличные пространства, базы данных, схемы

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

Список возможных привилегий отличается для объектов различных типов. Привилегии для основных объектов приведены на этом и следующих слайдах.

Для табличных пространств есть привилегия CREATE, разрешающая создание объектов в этом пространстве.

Для баз данных привилегия CREATE разрешает создавать схемы в этой БД, а привилегия CONNECT разрешает подключение к этой базе данных.

Привилегия USAGE схемы разрешает обращаться к объектам в этой схеме, а для создания объектов в ней требуется привилегия CREATE.

Привилегия на создание временных таблиц вынесена на уровень БД (TEMPORARY), поскольку точное имя схемы для временных объектов заранее неизвестно.

https://postgrespro.ru/docs/postgresql/16/ddl-priv#PRIVILEGES-SUMMARY-TABLE

Page 10: Привилегии

Таблицы и представления

SELECT
чтение данных
INSERT
вставка строк
можно на уровне столбцов
UPDATE
изменение строк
REFERENCES
внешний ключ (для таблиц)
DELETE
удаление строк
TRUNCATE
опустошение (для таблиц)
TRIGGER
создание триггеров
Последовательности
- SELECT
- currval
- UPDATE
- nextval
- setval
- USAGE
- currval
- nextval

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

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

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

https://postgrespro.ru/docs/postgresql/16/ddl-priv

Page 11: Привилегии

Подпрограммы

EXECUTE
выполнение
Типы данных, языки, …
- USAGE
- использование
Параметры
- ALTER SYSTEM
- изменение в кластере
- SET
- изменение в сеансе

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

Набор привилегий для других объектов довольно прост.

Для подпрограмм единственная привилегия EXECUTE разрешает выполнение функции или процедуры.

Для таких объектов, как тип данных, домен, язык, обертка сторонних данных и внешний сервер, есть единственная привилегия USAGE, разрешающая использовать объект. Эти виды объектов изучаются в курсе DEV2 «Разработка серверной части PostgreSQL. Расширенный курс».

Для параметров конфигурации привилегия SET дает право задать значение на уровне сеанса, а привилегия ALTER SYSTEM — в postgresql.auto.conf командой ALTER SYSTEM.

Page 12: Управление привилегиями

{#source-figure-012 caption=“Source slide page 12: Управление привилегиями”}

Выдача привилегии

alice
bob
Отзыв привилегии
- alice
- bob

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

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

Синтаксис команд GRANT и REVOKE достаточно сложен и позволяет указывать как отдельные, так и все возможные привилегии; как отдельные объекты, так и группы объектов, входящие в определенные схемы и т. п. Можно указать одну или несколько ролей, а также использовать ключевое слово PUBLIC; в последнем случае команда действует на все роли кластера, в том числе и будущие.

https://postgrespro.ru/docs/postgresql/16/sql-grant

https://postgrespro.ru/docs/postgresql/16/sql-revoke

Page 13: Привилегии

{#source-figure-013 caption=“Source slide page 13: Привилегии”}

Алиса создает пару таблиц:

alice=> CREATE TABLE t1 AS SELECT 1 AS n;

SELECT 1

alice=> CREATE TABLE t2(n integer,m integer);

CREATE TABLE

А Боб пытается обратиться к таблицам Алисы:

bob=> SELECT * FROM alice.t1;

ERROR:  permission denied for schema alice
LINE 1: SELECT * FROM alice.t1;
                      ^

Не получилось: у Боба нет привилегий на схему Алисы, по умолчанию их имеет только владелец:

alice=> \dn+ alice

                 List of schemas
 Name  | Owner | Access privileges | Description
-------+-------+-------------------+-------------
 alice | alice |                   |
(1 row)

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

student=# SELECT acldefault(
  'n',             -- тип объекта (n — схема)
  'alice'::regrole -- OID владельца
);

    acldefault
------------------
 {alice=UC/alice}
(1 row)

Привилегии выводятся как массив значений в формате: роль=привилегии/кем_предоставлены. При создании объекта владелец получает привилегии от самого себя. Привилегии обозначаются одной буквой. Для схем: U = usage — право обращаться к объектам схемы; C = create — право создавать объекты в схеме. Алиса выдает Бобу привилегию на доступ к объектам схемы:

alice=> GRANT USAGE ON SCHEMA alice TO bob;

GRANT

Теперь метакоманда покажет выданные привилегии:

alice=> \dn+ alice

                 List of schemas
 Name  | Owner | Access privileges | Description
-------+-------+-------------------+-------------
 alice | alice | alice=UC/alice   +|
       |       | bob=U/alice       |
(1 row)

Боб повторяет попытку обратиться к таблице alice.t1:

bob=> SELECT * FROM alice.t1;

ERROR:  permission denied for table t1

Теперь Бобу не хватает прав на чтение таблицы. Алиса выдает привилегию:

alice=> GRANT SELECT ON t1 TO bob;

Page 14: Page 14

{#source-figure-014 caption=“Source slide page 14: Page 14”}

GRANT

Посмотрим, какие привилегии установлены:

alice=> \dp t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=r/alice         |                   |
(1 row)

Видим, что привилегии Бобу предоставила Алиса. Обозначения привилегий для таблиц (не вполне очевидные): a = insert; r = select; w = update; d = delete; D = truncate; x = reference; t = trigger. Теперь Боб может прочитать содержимое таблицы:

bob=> SELECT * FROM alice.t1;

 n
---
 1
(1 row)

Но, например, добавить строку в таблицу он не сможет:

bob=> INSERT INTO alice.t1 VALUES (42);

ERROR:  permission denied for table t1

Некоторые привилегии можно выдать на определенные столбцы:

alice=> GRANT INSERT(n,m) ON t2 TO bob;

GRANT

alice=> GRANT SELECT(m) ON t2 TO bob;

GRANT

alice=> \dp t2

                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
 alice  | t2   | table |                   | n:               +|
        |      |       |                   |   bob=a/alice    +|
        |      |       |                   | m:               +|
        |      |       |                   |   bob=ar/alice    |
(1 row)

Теперь Боб может добавлять строки в t2:

bob=> INSERT INTO alice.t2(n,m) VALUES (1,2);

INSERT 0 1

А читать сможет только один столбец:

bob=> SELECT * FROM alice.t2;

ERROR:  permission denied for table t2

bob=> SELECT m FROM alice.t2;

 m
---
 2
(1 row)

Если необходимо, Алиса может выдать Бобу все привилегии, не перечисляя их явно:

alice=> GRANT ALL ON t1 TO bob;

Page 15: Page 15

{#source-figure-015 caption=“Source slide page 15: Page 15”}

GRANT

alice=> \dp t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=arwdDxt/alice   |                   |
(1 row)

Теперь Бобу доступны все действия, например, удаление строк:

bob=> DELETE FROM alice.t1;

DELETE 1

Однако удалить таблицу он не может, поскольку привилегии для этого не существует:

bob=> DROP TABLE alice.t1;

ERROR:  must be owner of table t1

Page 16: Право передачи

{#source-figure-016 caption=“Source slide page 16: Право передачи”}

Выдача привилегии с правом передачи

alice

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

При выдаче привилегии можно передать роли право дальнейшей передачи (и отзыва) этой привилегии с помощью команды GRANT с предложением WITH GRANT OPTION.

Таким образом могут образовываться цепочки передачи привилегий; переданные привилегии считаются зависимыми от передавшей их роли. Причем одна привилегия может быть получена ролью несколько раз от разных ролей (как ролью charlie на слайде).

Page 17: Право передачи

{#source-figure-017 caption=“Source slide page 17: Право передачи”}

Отзыв привилегии вместе с правом передачи

alice
Отзыв только права передачи
- alice

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

Команда REVOKE отзывает привилегию вместе с правом передачи. Роль может отозвать привилегию только у той роли, которой она непосредственно выдала эту привилегию. Например, в ситуации, показанной на слайде, alice не может отозвать привилегию у dave.

Однако при отзыве привилегии у роли bob также должны быть отозваны и все зависимые привилегии: отзыв коснется всех ролей, которым эта привилегия досталась через Боба. Для этого надо указать ключевое слово CASCADE (без него команда завершится ошибкой, если Боб успел передать привилегию кому-либо).

Право передачи можно отозвать, не отзывая у роли саму привилегию. Это выполняется с помощью команды REVOKE GRANT OPTION FOR. Слово CASCADE имеет здесь такое же значение, как и при отзыве привилегии: поскольку Боб теперь не может передавать привилегию другим ролям, все зависимые привилегии будут отозваны.

Page 18: Право передачи

{#source-figure-018 caption=“Source slide page 18: Право передачи”}

Создадим третью роль, для Чарли.

student=# CREATE ROLE charlie LOGIN;

CREATE ROLE

У Боба есть полный доступ к t1, принадлежащей Алисе:

bob=> \dp alice.t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=arwdDxt/alice   |                   |
(1 row)

Но Боб не может передать свою привилегию Чарли:

bob=> GRANT SELECT ON alice.t1 TO charlie;

WARNING:  no privileges were granted for "t1"
GRANT

Заметьте, что команда завершилась успешно, однако при этом ничего не сделала. В подобных случаях PostgreSQL может выдавать предупреждение, но рассчитывать на него не стоит. Чтобы Боб мог передать привилегию, Алиса, владелец таблицы, должна выдать Бобу разрешение.

alice=> GRANT SELECT,UPDATE ON alice.t1 TO bob WITH GRANT OPTION;

GRANT

alice=> \dp alice.t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=ar*w*dDxt/alice |                   |
(1 row)

Звездочки справа от символов привилегий показывают право передачи. Теперь Боб может поделиться с Чарли привилегиями, в том числе и правом передачи:

bob=> GRANT SELECT ON alice.t1 TO charlie WITH GRANT OPTION;

GRANT

bob=> GRANT UPDATE ON alice.t1 TO charlie;

GRANT

bob=> \dp alice.t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=ar*w*dDxt/alice+|                   |
        |      |       | charlie=r*w/bob     |                   |
(1 row)

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

student=# GRANT UPDATE ON alice.t1 TO charlie;

GRANT

student=# \dp alice.t1

Page 19: Page 19

{#source-figure-019 caption=“Source slide page 19: Page 19”}

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=ar*w*dDxt/alice+|                   |
        |      |       | charlie=r*w/bob    +|                   |
        |      |       | charlie=w/alice     |                   |
(1 row)

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

alice=> REVOKE GRANT OPTION FOR SELECT ON alice.t1 FROM charlie;

REVOKE

Никакой ошибки не фиксируется, но и права не изменяются:

alice=> \dp alice.t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=ar*w*dDxt/alice+|                   |
        |      |       | charlie=r*w/bob    +|                   |
        |      |       | charlie=w/alice     |                   |
(1 row)

В то же время Алиса не может просто так отозвать привилегии у Боба, если он успел передать их кому-либо еще:

alice=> REVOKE GRANT OPTION FOR SELECT ON alice.t1 FROM bob;

ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.

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

alice=> REVOKE GRANT OPTION FOR SELECT ON alice.t1 FROM bob CASCADE;

REVOKE

alice=> \dp alice.t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=arw*dDxt/alice +|                   |
        |      |       | charlie=w/bob      +|                   |
        |      |       | charlie=w/alice     |                   |
(1 row)

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

alice=> REVOKE SELECT ON alice.t1 FROM bob CASCADE;

REVOKE

alice=> \dp alice.t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=aw*dDxt/alice  +|                   |
        |      |       | charlie=w/bob      +|                   |
        |      |       | charlie=w/alice     |                   |
(1 row)

Page 20: Начальные привилегии

Клонируемые объекты — привилегии в шаблонной БД

Новые объекты — привилегии по умолчанию
- база данных
- CONNECT, TEMPORARY
- подпрограмма
- EXECUTE
- язык, домен, тип
- USAGE

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

При создании объекта ролям назначаются определенные привилегии на него.

Поскольку базы данных клонируются из шаблонов, после создания базы объекты в ней уже имеют определенный набор привилегий. В частности, псевдороль PUBLIC, а вместе с ней и все роли, имеют привилегию USAGE на схемы public и information_schema.

В PostgreSQL 14 и более ранних версиях псевдороль PUBLIC получала на схему public еще и привилегию CREATE; такая конфигурация небезопасна, поскольку позволяет подменить объект из схемы pg_catalog собственным объектом. Подробности описаны в статье:

https://wiki.postgresql.org/wiki/A\_Guide\_to\_CVE-2018-1058%3A\_Protect\_Y our_Search_Path

Для новых объектов начальные привилегии определяются их типом. Для объектов некоторых типов псевдороль PUBLIC получает определенный набор привилегий — эти ситуации приведены на слайде.

Привилегии по умолчанию на новые объекты можно настроить для определенного владельца и схемы с помощью команды ALTER DEFAULT PRIVILEGES. Механизм срабатывает при создании объекта определенного класса в указанной (или любой) схеме определенным пользователем.

https://postgrespro.ru/docs/postgresql/16/ddl-priv.html#PRIVILEGES-SUMM ARY-TABLE

https://postgrespro.ru/docs/postgresql/16/sql-alterdefaultprivileges

Page 21: Начальные привилегии

Посмотрим на привилегии, выданные на схемы:

student=# \dnS+

                                                  List of schemas
        Name        |       Owner       |           Access privileges            |
   Description
--------------------+-------------------+----------------------------------------+--------
--------------------------
 alice              | alice             | alice=UC/alice                        +|
                    |                   | bob=U/alice                            |
 information_schema | postgres          | postgres=UC/postgres                  +|
                    |                   | =U/postgres                            |
 pg_catalog         | postgres          | postgres=UC/postgres                  +| system
catalog schema
                    |                   | =U/postgres                            |
 pg_toast           | postgres          |                                        |
reserved schema for TOAST tables
 public             | pg_database_owner | pg_database_owner=UC/pg_database_owner+|
standard public schema
                    |                   | =U/pg_database_owner                   |
(5 rows)

Слева от некоторых знаков равенства нет имени роли — это означает, что привилегии на схему public и две схемы системного каталога выданы псевдороли PUBLIC. Привилегии были выданы в шаблонной базе template1 и скопированы в базу access_roles при ее создании. Алиса создает простую функцию (она попадет в схему alice):

alice=> CREATE FUNCTION foo() RETURNS integer LANGUAGE sql RETURN 42;

CREATE FUNCTION

alice=> SELECT foo();

 foo
-----
  42
(1 row)

Псевдороль PUBLIC автоматически получает привилегию EXECUTE для любой создаваемой функции. Поэтому, например, Боб может выполнить функцию, которую только что создала Алиса.

bob=> SELECT alice.foo();

 foo
-----
  42
(1 row)

Если это нежелательно, можно отозвать привилегию EXECUTE у псевдороли PUBLIC и выдавать ее явно только нужным ролям:

alice=> REVOKE EXECUTE ON ALL ROUTINES
  IN SCHEMA alice
  FROM PUBLIC;

REVOKE

bob=> SELECT alice.foo();

ERROR:  permission denied for function foo

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

alice=> CREATE FUNCTION bar() RETURNS integer LANGUAGE sql RETURN 6174;

CREATE FUNCTION

И Боб сможет выполнить новую функцию Алисы:

bob=> SELECT alice.bar();

Page 22: Page 22

{#source-figure-022 caption=“Source slide page 22: Page 22”}

 bar
------
 6174
(1 row)

При создании объекта на него выдается определенный набор начальных привилегий. Набор по умолчанию показывает функция acldefault:

alice=> SELECT acldefault(
  'f',             -- тип объекта (f — функция)
  'alice'::regrole -- OID владельца
);

        acldefault
--------------------------
 {=X/alice,alice=X/alice}
(1 row)

Обратите внимание, что набор содержит привилегию EXECUTE для псевдороли PUBLIC. Если Алиса не хочет автоматически разрешать всем ролям выполнять свои функции, она может исключить привилегию из начального набора:

alice=> ALTER DEFAULT PRIVILEGES
  REVOKE EXECUTE ON ROUTINES
  FROM PUBLIC;

ALTER DEFAULT PRIVILEGES

Можно и наоборот, выдавать дополнительные привилегии:

alice=> ALTER DEFAULT PRIVILEGES
  GRANT SELECT ON TABLES
  TO bob;

ALTER DEFAULT PRIVILEGES

Настроенные привилегии по умолчанию показывает метакоманда:

alice=> \ddp

            Default access privileges
 Owner | Schema |   Type   |  Access privileges
-------+--------+----------+---------------------
 alice |        | function | alice=X/alice
 alice |        | table    | alice=arwdDxt/alice+
       |        |          | bob=r/alice
(2 rows)

Теперь выполнение новых функций будет доступно только Алисе, а Боб будет получать доступ на чтение ко всем новым таблицам Алисы. Проверим.

alice=> CREATE TABLE t3 AS SELECT * from t1;

SELECT 0

alice=> \dp t3

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t3   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=r/alice         |                   |
(1 row)

bob=> SELECT * FROM alice.t3;

 n
---
(0 rows)

alice=> CREATE FUNCTION baz() RETURNS integer LANGUAGE sql RETURN 1729;

CREATE FUNCTION

alice=> SELECT proname, proacl FROM pg_proc where proname='baz';

Page 23: Page 23

 proname |     proacl
---------+-----------------
 baz     | {alice=X/alice}
(1 row)

bob=> SELECT * FROM alice.baz();

ERROR:  permission denied for function baz

Page 24: Итоги

Роль — учетная запись СУБД и набор прав

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

Page 25: Практика

Создайте пользователей для Алисы и Боба.

1. Алиса создает две схемы: приватную alice и публичную app, и дает Бобу право обращаться к объектам публичной схемы.
1. Алиса создает таблицу с целочисленным столбцом в схеме app и дает Бобу привилегию на изменение ее строк. Сможет ли Боб увеличить значения во всех строках таблицы на единицу? А изменить значение всех строк на 42? Почему?
1. Алиса настраивает привилегии по умолчанию так, чтобы Боб получал право чтения строк любой таблицы, созданной Алисой в публичной схеме. Проверьте настройку.

Page 26: 1. Роли

Зарегистрируем роли Алисы и Боба с правом подключения.

student=# CREATE USER alice;

CREATE ROLE

student=# CREATE USER bob;

CREATE ROLE

student=# CREATE DATABASE access_roles;

CREATE DATABASE

student=# GRANT CREATE ON DATABASE access_roles TO alice;

GRANT

2. Схемы

student$ psql -U alice -d access_roles

Приватная схема:

alice=> CREATE SCHEMA alice;

CREATE SCHEMA

Публичная схема:

alice=> CREATE SCHEMA app;

CREATE SCHEMA

alice=> GRANT USAGE ON SCHEMA app TO bob;

GRANT

3. Таблица

alice=> CREATE TABLE app.t(n) AS VALUES (1),(2),(3);

SELECT 3

alice=> GRANT UPDATE ON app.t TO bob;

GRANT

student$ psql -U bob -d access_roles

Так не работает:

bob=> UPDATE app.t SET n = n + 1;

ERROR:  permission denied for table t

bob=> UPDATE app.t SET n = 42 WHERE n = 1;

ERROR:  permission denied for table t

А так — работает:

bob=> UPDATE app.t SET n = 42;

UPDATE 3

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

alice=> GRANT SELECT ON app.t TO bob;

GRANT

bob=> UPDATE app.t SET n = n + 1;

UPDATE 3

Page 27: 4. Привилегии по умолчанию

{#source-figure-027 caption=“Source slide page 27: 4. Привилегии по умолчанию”}

alice=> ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT SELECT ON TABLES TO bob;

ALTER DEFAULT PRIVILEGES

Проверяем:

alice=> CREATE TABLE app.test(s text);

CREATE TABLE

bob=> SELECT * FROM app.test;

 s
---
(0 rows)

Уборка

bob=> \q

alice=> \q

student=# DROP DATABASE access_roles WITH (FORCE);

DROP DATABASE

student=# DROP ROLE alice, bob;

DROP ROLE

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

Создайте пользователя для Алисы и установите для него нулевое значение параметра log_min_duration_statement.

1. Убедитесь, что все запросы Алисы записываются в журнал сообщений и что Алиса не может отменить эту запись.
1. Выдайте Алисе разрешение изменять параметр в своем сеансе. Убедитесь, что после этого она может отменить запись своих запросов в журнал сообщений.

Page 29: 1. Пользователь и значение параметра

student=# CREATE USER alice;

CREATE ROLE

student=# ALTER ROLE alice SET log_min_duration_statement = 0;

ALTER ROLE

2. Сеанс Алисы

student$ psql -U alice -d student

alice=> SELECT now();

             now
------------------------------
 2026-07-30 16:36:18.64657+03
(1 row)

Действия Алисы записываются в журнал сообщений:

student$ tail -n 1 /var/log/postgresql/postgresql-16-main.log

2026-07-30 16:36:18.647 MSK [60308] alice@student LOG:  duration: 0.311 ms  statement:
SELECT now();

И Алиса не может отменить эту запись:

alice=> SET log_min_duration_statement = -1;

ERROR:  permission denied to set parameter "log_min_duration_statement"

3. Привилегия на параметр

Дадим Алисе право менять значение параметра в текущем сеансе:

student=# GRANT SET ON PARAMETER log_min_duration_statement TO alice;

GRANT

Теперь она может избавиться от записи своих действий.

alice=> SET log_min_duration_statement = -1;

SET

alice=> SELECT 'I leave no traces now';

       ?column?
-----------------------
 I leave no traces now
(1 row)

student$ tail -n 1 /var/log/postgresql/postgresql-16-main.log

2026-07-30 16:36:18.864 MSK [60308] alice@student STATEMENT:  SET
log_min_duration_statement = -1;

Уборка

alice=> \q

Удалить роль Алисы сейчас нельзя, от нее зависит привилегия на параметр:

student=# DROP ROLE alice;

ERROR:  role "alice" cannot be dropped because some objects depend on it
DETAIL:  privileges for parameter log_min_duration_statement

Придется сначала удалить привилегию.

student=# REVOKE ALL ON PARAMETER log_min_duration_statement FROM alice;

Page 30: Page 30

REVOKE

student=# DROP ROLE alice;

DROP ROLE

02 Занятие 02: Управление доступом / Членство в ролях

Исходная страница в нативном Markdown: Управление доступом / Членство в ролях

Примечание

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: dbs_02_access_membership.html Archive: DBS-handouts-16.zip Страниц PDF: 24

Page 1: Управление доступом / Членство в ролях

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

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

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

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

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

Page 2: Темы

Включение роли в роль
Способы получения прав на объект
Наследование
Переключение на роль
Управление включением ролей

Page 3: Включение роли в роль

{#source-figure-003 caption=“Source slide page 3: Включение роли в роль”}

Включение в роль Исключение из роли

student=# GRANT dba TO alice;
student=# REVOKE dba FROM alice;

dba
alice
Псевдороль PUBLIC неявно включает остальные роли

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

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

Роль может быть одновременно «пользовательской» (иметь атрибут LOGIN) и «групповой» (включать другие роли).

В кластере существует также псевдороль PUBLIC, которая неявно включает в себя все остальные роли.

https://postgrespro.ru/docs/postgresql/16/role-membership

Page 4: Получение прав на объект

Суперпользовательский доступ

суперпользователи не ограничены в действиях, за исключением удаления начального суперпользователя Владение
Обладание привилегией
Членство в группе
- INHERIT
- наследование прав групповой роли
- SET
- переключение на групповую роль
- ADMIN
- управление членством в групповой роли

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

В целом у роли есть несколько вариантов получить право на действия с определенным объектом.

Суперпользователи (роли с атрибутом SUPERUSER) — особый случай, для них проверки прав доступа не выполняются. При этом нельзя удалить начальную суперпользовательскую роль или лишить ее атрибута SUPERUSER. Начальный суперпользователь (bootstrap superuser) создается при создании кластера баз данных.

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

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

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

● INHERIT включает наследование прав групповой роли на действия с объектами;

● SET позволяет переключаться на групповую роль;

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

Параметры INHERIT и SET появились в PostgreSQL 16.

Page 5: Наследование

{#source-figure-005 caption=“Source slide page 5: Наследование”}

Атрибуты не наследуются

Права на объекты наследуются
- если роль входит в группу с параметром членства INHERIT
- grp1
- grp2
- grp3

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

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

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

Наследование срабатывает, когда роль включена в группу с параметром членства INHERIT. По умолчанию именно так и происходит, поскольку команда GRANT берет значение параметра INHERIT из одноименного атрибута включаемой роли, а при создании роли этот атрибут по умолчанию устанавливается. Однако параметр можно установить и явно, переопределив атрибут. Для этого надо указать в команде GRANT предложение WITH INHERIT.

Page 6: Наследование

Зарегистрируем роли Алисы и Боба.

student=# CREATE ROLE alice LOGIN;

CREATE ROLE

student=# CREATE ROLE bob LOGIN;

CREATE ROLE

Алиса будет владельцем базы данных:

student=# CREATE DATABASE access_membership OWNER alice;

CREATE DATABASE

Алиса подключается к БД и создает таблицу в схеме public.

student$ psql -d access_membership -U alice

alice=> CREATE TABLE t (id int);

CREATE TABLE

Включим Боба в роль Алисы:

student=# GRANT alice TO bob;

GRANT ROLE

Параметры членства ролей в группах можно увидеть в системном каталоге:

student=# SELECT roleid::regrole AS group,
  member::regrole,
  grantor::regrole,
  inherit_option
FROM pg_auth_members
WHERE member = 'bob'::regrole;

 group | member | grantor  | inherit_option
-------+--------+----------+----------------
 alice | bob    | postgres | t
(1 row)

Заметьте, что в сеансе student включение в роль выполнено от имени начального суперпользователя postgres. В этом случае не возникает зависимость от роли student, которая затруднит ее удаление. Последний столбец показывает параметр членства INHERIT. Он унаследован от атрибута INHERIT роли bob, который у нее включен по умолчанию. Поэтому Боб, подключившись, сможет прочитать таблицу.

student$ psql -d access_membership -U bob

bob=> SELECT * FROM t;

 id
----
(0 rows)

Убедиться, что привилегии Алисы унаследованы Бобом, можно и с помощью системной функции. Ее вызов с параметром USAGE показывает, наследует ли текущий пользователь привилегии роли alice:

bob=> SELECT pg_has_role(CURRENT_ROLE, 'alice', 'USAGE');

 pg_has_role
-------------
 t
(1 row)

Можно задать параметр членства явно:

student=# GRANT alice TO bob WITH INHERIT OPTION; -- или INHERIT TRUE

NOTICE:  role "bob" has already been granted membership in role "alice" by role "postgres"
GRANT ROLE

Что будет, если лишить роль Боба атрибута INHERIT?

Page 7: Page 7

{#source-figure-007 caption=“Source slide page 7: Page 7”}

student=# ALTER ROLE bob NOINHERIT;

ALTER ROLE

bob=> SELECT pg_has_role(CURRENT_ROLE, 'alice', 'USAGE');

 pg_has_role
-------------
 t
(1 row)

Наследование обеспечивается параметром членства INHERIT, поэтому Боб по-прежнему может читать содержимое таблицы:

bob=> SELECT * FROM t;

 id
----
(0 rows)

Параметр появился в PostgreSQL 16, в более ранних версиях отмена атрибута привела бы к потере наследования. Заметьте, что наследуются и права, которые дает владение, поэтому Боб может, например, изменить таблицу:

bob=> ALTER TABLE t SET (fillfactor=10);

ALTER TABLE

Page 8: Переключение на роль

{#source-figure-008 caption=“Source slide page 8: Переключение на роль”}

Параметр членства SET

Переключение на роль Возврат к исходной роли

alice=> SET ROLE dba;
alice=> RESET ROLE;

session_user

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

Параметр членства SET позволяет выполнить команду SET ROLE и (временно) переключиться на групповую роль со всеми ее атрибутами и привилегиями на объекты. Все действия будут выполняться от имени групповой роли и создаваемые объекты будут принадлежать групповой роли. Права исходной роли при этом недоступны. Параметр появился в PostgreSQL 16, до этого переключение на групповую роль разрешалось всегда.

Имя пользователя, подключившегося к серверу, возвращает функция session_user. А имя текущей роли можно узнать с помощью функции current_user (или current_role, что одно и то же).

https://postgrespro.ru/docs/postgresql/16/functions-info#FUNCTIONS-INFO-SESSION

Page 9: Переключение на роль

{#source-figure-009 caption=“Source slide page 9: Переключение на роль”}

Удалим параметр INHERIT членства роли bob в группе alice:

student=# REVOKE INHERIT OPTION FOR alice FROM bob;

REVOKE ROLE

Членство ролей в группах и параметры членства удобно проверять с помощью метакоманды \drg:

student=# \drg bob

            List of role grants
 Role name | Member of | Options | Grantor
-----------+-----------+---------+----------
 bob       | alice     | SET     | postgres
(1 row)

Привилегии отозваны немедленно:

bob=> SELECT * FROM t;

ERROR:  permission denied for table t

Оставшийся параметр членства SET позволяет члену группы переключиться на групповую роль:

bob=> SET ROLE alice;

SET

bob=> SELECT * FROM t;

 id
----
(0 rows)

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

bob=> SELECT current_user, session_user;

 current_user | session_user
--------------+--------------
 alice        | bob
(1 row)

Боб может, например, от имени Алисы выдать себе все привилегии на таблицу:

bob=> GRANT ALL ON t TO bob;

GRANT

bob=> \dp t

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 public | t    | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=arwdDxt/alice   |                   |
(1 row)

При переключении роль также получает атрибуты групповой роли. Добавим роли alice атрибут, разрешающий создавать базы данных:

student=# ALTER ROLE alice CREATEDB;

ALTER ROLE

Обладает ли теперь Боб, переключивший свой сеанс на роль Алисы, правом создавать базы данных?

bob=> SELECT has_database_privilege(current_catalog, 'CREATE');

 has_database_privilege
------------------------
 t
(1 row)

Page 10: Page 10

Да, право есть. Вернемся к исходной роли сеанса:

bob=> RESET ROLE;

RESET

bob=> SELECT current_user, session_user;

 current_user | session_user
--------------+--------------
 bob          | bob
(1 row)

Суперпользователь может использовать другую команду для переключения на любую роль:

student=# SET SESSION AUTHORIZATION bob;

SET

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

student=# SELECT current_user, session_user;

 current_user | session_user
--------------+--------------
 bob          | bob
(1 row)

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

student=# RESET SESSION AUTHORIZATION;

RESET

student=# SELECT current_user, session_user;

 current_user | session_user
--------------+--------------
 student      | student
(1 row)

Page 11: Управление членством

{#source-figure-011 caption=“Source slide page 11: Управление членством”}

Параметр членства ADMIN

включение в группу
исключение из группы
изменение параметров членства

alice=> GRANT dba TO bob;
alice=> REVOKE dba FROM bob;

alice

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

Управлять включением ролей в группу (а также исключением ролей из группы и изменением параметров членства ролей в группе) могут те члены группы, которые имеют в ней параметр членства ADMIN.

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

Суперпользователи могут управлять включением любых ролей в любые.

Page 12: Управление включением

{#source-figure-012 caption=“Source slide page 12: Управление включением”}

Зарегистрируем еще роль для Чарли:

student=# CREATE ROLE charlie;

CREATE ROLE

Если роль входит в группу с параметром ADMIN, она может добавлять в группу другие роли:

student=# GRANT alice TO bob WITH ADMIN OPTION; -- или ADMIN TRUE

GRANT ROLE

student=# \drg bob

              List of role grants
 Role name | Member of |  Options   | Grantor
-----------+-----------+------------+----------
 bob       | alice     | ADMIN, SET | postgres
(1 row)

Обладая параметром членства ADMIN в группе Алисы, Боб теперь может включить Чарли в эту группу:

bob=> GRANT alice TO charlie;

GRANT ROLE

bob=> \drg charlie

              List of role grants
 Role name | Member of |   Options    | Grantor
-----------+-----------+--------------+---------
 charlie   | alice     | INHERIT, SET | bob
(1 row)

По умолчанию параметр SET устанавливается, а параметр INHERIT, как мы уже видели, наследуется от атрибута INHERIT включаемой роли. Параметр членства ADMIN в группе Алисы дает Бобу право изменять параметры членства других ролей в этой группе:

bob=> REVOKE INHERIT OPTION FOR alice FROM charlie;

REVOKE ROLE

bob=> \drg charlie

            List of role grants
 Role name | Member of | Options | Grantor
-----------+-----------+---------+---------
 charlie   | alice     | SET     | bob
(1 row)

Включать в свою группу другие роли Боб не может:

bob=> GRANT bob TO charlie;

ERROR:  permission denied to grant role "bob"
DETAIL:  Only roles with the ADMIN option on role "bob" may grant this role.

Page 13: Каскадное исключение

{#source-figure-013 caption=“Source slide page 13: Каскадное исключение”}

Лишение членства в роли с параметром ADMIN

student=# REVOKE alice FROM bob CASCADE;

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

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

Такое лишение членства действует рекурсивно, точно так же, как при отзыве права передачи привилегии GRANT OPTION, которое рассматривалось в предыдущей теме.

https://postgrespro.ru/docs/postgresql/16/sql-revoke

Page 14: Каскадное исключение

Боб был включен в группу Алисы с правом управления и воспользовался им:

student=# \drg bob|charlie

              List of role grants
 Role name | Member of |  Options   | Grantor
-----------+-----------+------------+----------
 bob       | alice     | ADMIN, SET | postgres
 charlie   | alice     | SET        | bob
(2 rows)

Поэтому нельзя просто так исключить его из группы:

student=# REVOKE alice FROM bob;

ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.

Но можно, используя ключевое слово CASCADE, удалить Боба из группы вместе со всеми, кого тот успел включить в группу Алисы:

student=# REVOKE alice FROM bob CASCADE;

REVOKE ROLE

student=# \drg bob|charlie

            List of role grants
 Role name | Member of | Options | Grantor
-----------+-----------+---------+---------
(0 rows)

Можно было бы отозвать у Боба право управления, не исключая его из группы Алисы, но при этом все равно пришлось бы указать CASCADE. Роль можно удалить, если она не владеет объектами. Просто так удалить Алису и Боба не получится:

student=# DROP ROLE alice;

ERROR:  role "alice" cannot be dropped because some objects depend on it
DETAIL:  owner of database access_membership
1 object in database access_membership

student=# DROP ROLE bob;

ERROR:  role "bob" cannot be dropped because some objects depend on it
DETAIL:  1 object in database access_membership

Можно удалить объекты, принадлежащие роли, если они не нужны:

student=# \c access_membership

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

student=# DROP OWNED BY bob;

DROP OWNED

Если же удалить объекты нельзя, можно передать их другому владельцу:

student=# REASSIGN OWNED BY alice TO student;

REASSIGN OWNED

В общем случае (без прав суперпользователя) для смены владельца командой REASSIGN OWNED BY или ALTER … OWNER TO нужен параметр членства SET в роли нового владельца.

student=# DROP ROLE alice, bob, charlie;

DROP ROLE

Page 15: Итоги

Роли можно включать друг в друга

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

Page 16: Практика

Создайте роль для Алисы, включив ее в суперпользователь-скую роль с параметром SET. Все дальнейшие действия выполняются в сеансе Алисы.

1. Создайте пользователя Боб — члена группы readers — и пользователя Чарли — члена группы writers. Оба включения выполнены с параметром INHERIT (без SET).
1. Создайте таблицу в отдельной схеме, выдайте роли readers привилегию на ее чтение, а роли writers — на изменение.
1. Проверьте, могут ли Алиса, Боб и Чарли читать и менять строки таблицы.

Page 17: 1. Роль Алисы

student=# CREATE USER alice NOINHERIT IN ROLE student;

CREATE ROLE

Эта команда эквивалентна двум следующим:

CREATE ROLE alice LOGIN NOINHERIT;
GRANT student TO alice;

student=# \c - alice

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

alice=> SET ROLE student;

SET

2. Пользователи и группы

Создадим группы readers и writers.

alice=> CREATE ROLE readers;

CREATE ROLE

alice=> CREATE ROLE writers;

CREATE ROLE

Боб — читатель.

alice=> CREATE USER bob;

CREATE ROLE

alice=> GRANT readers TO bob WITH SET FALSE;

GRANT ROLE

Чарли — писатель.

alice=> CREATE USER charlie;

CREATE ROLE

alice=> GRANT writers TO charlie WITH SET FALSE;

GRANT ROLE

Вот что получилось:

alice=> \drg

            List of role grants
 Role name | Member of | Options | Grantor
-----------+-----------+---------+----------
 alice     | student   | SET     | postgres
 bob       | readers   | INHERIT | postgres
 charlie   | writers   | INHERIT | postgres
(3 rows)

3. База данных, схема и таблица

alice=> CREATE DATABASE access_membership OWNER alice;

CREATE DATABASE

alice=> \c access_membership

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

alice=> CREATE SCHEMA app;

CREATE SCHEMA

alice=> CREATE TABLE app.tab(who text DEFAULT current_user);

Page 18: Page 18

CREATE TABLE

Привилегии:

alice=> GRANT USAGE ON SCHEMA app TO readers, writers;

GRANT

alice=> GRANT SELECT ON TABLE app.tab TO readers;

GRANT

alice=> GRANT INSERT, UPDATE, DELETE ON TABLE app.tab TO writers;

GRANT

4. Права на таблицу

alice=> \dp app.tab

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 app    | tab  | table | alice=arwdDxt/alice+|                   |
        |      |       | readers=r/alice    +|                   |
        |      |       | writers=awd/alice   |                   |
(1 row)

Алиса может читать и менять строки, поскольку является владельцем:

alice=> INSERT INTO app.tab DEFAULT VALUES;

INSERT 0 1

alice=> SELECT * FROM app.tab;

  who
-------
 alice
(1 row)

alice=> \c - bob

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

Боб может читать:

bob=> SELECT * FROM app.tab;

  who
-------
 alice
(1 row)

Но не может менять:

bob=> INSERT INTO app.tab DEFAULT VALUES;

ERROR:  permission denied for table tab

bob=> \c - charlie

You are now connected to database "access_membership" as user "charlie".

Чарли может менять строки:

charlie=> INSERT INTO app.tab DEFAULT VALUES;

INSERT 0 1

Но не читать их:

charlie=> SELECT * FROM app.tab;

ERROR:  permission denied for table tab

Уборка

charlie=> \c student student

Page 19: Page 19

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

student=# DROP DATABASE access_membership WITH (FORCE);

DROP DATABASE

student=# DROP ROLE alice, bob, charlie, readers, writers;

DROP ROLE

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

Изучите способы получения метаинформации.

1. Создайте роли, схему и таблицу, как это сделано в Практике.
1. Получите: список объектов Алисы; права пользователей на эти объекты; список ролей, имеющих право чтения таблицы; информацию о включении ролей в группы.
1. Может ли пользователь Дейв, не имеющий доступа к объектам, увидеть эту метаинформацию?

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

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

● системный каталог https://postgrespro.ru/docs/postgresql/16/catalogs;

● системные информационные функции https://postgrespro.ru/docs/postgresql/16/functions-info;

● метакоманды psql https://postgrespro.ru/docs/postgresql/16/app-psql#:~:text=удаляет%20п еред%20отправкой.-,Метакоманды,-Всё%2C%20что%20вводится

● информационную схему https://postgrespro.ru/docs/postgresql/16/information-schema.

Page 21: 1. Роли, схема, таблица

Роль Алисы.

student=# CREATE USER alice NOINHERIT IN ROLE student;

CREATE ROLE

student=# \c - alice

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

alice=> SET ROLE student;

SET

Группы readers и writers.

alice=> CREATE ROLE readers;

CREATE ROLE

alice=> CREATE ROLE writers;

CREATE ROLE

Боб — читатель.

alice=> CREATE USER bob;

CREATE ROLE

alice=> GRANT readers TO bob WITH SET FALSE;

GRANT ROLE

Чарли — писатель.

alice=> CREATE USER charlie;

CREATE ROLE

alice=> GRANT writers TO charlie WITH SET FALSE;

GRANT ROLE

Вот что получилось:

alice=> \drg

            List of role grants
 Role name | Member of | Options | Grantor
-----------+-----------+---------+----------
 alice     | student   | SET     | postgres
 bob       | readers   | INHERIT | postgres
 charlie   | writers   | INHERIT | postgres
(3 rows)

База данных и таблица.

alice=> CREATE DATABASE access_membership OWNER alice;

CREATE DATABASE

alice=> \c access_membership

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

alice=> CREATE SCHEMA app;

CREATE SCHEMA

alice=> CREATE TABLE app.tab(who text DEFAULT current_user);

CREATE TABLE

Привилегии.

alice=> GRANT USAGE ON SCHEMA app TO readers, writers;

GRANT

Page 22: 2. Метаинформация

alice=> GRANT SELECT ON TABLE app.tab TO readers;

GRANT

alice=> GRANT INSERT, UPDATE, DELETE ON TABLE app.tab TO writers;

GRANT

Получим информацию от имени суперпользователя.

alice=> \c - student

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

В системном каталоге можно увидеть список схем:

student=# SELECT * FROM pg_namespace;

  oid  |      nspname       | nspowner |                            nspacl
-------+--------------------+----------+--------------------------------------------------
-------------
    99 | pg_toast           |       10 |
    11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
  2200 | public             |     6171 |
{pg_database_owner=UC/pg_database_owner,=U/pg_database_owner}
 13296 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
 16395 | app                |    16386 | {alice=UC/alice,readers=U/alice,writers=U/alice}
(5 rows)

А также список объектов Алисы с привилегиями на них:

student=# SELECT oid, relname, relacl
FROM pg_class
WHERE relowner = 'alice'::regrole;

  oid  |       relname        |                         relacl
-------+----------------------+---------------------------------------------------------
 16400 | pg_toast_16396       |
 16401 | pg_toast_16396_index |
 16396 | tab                  | {alice=arwdDxt/alice,readers=r/alice,writers=awd/alice}
(3 rows)

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

student=# SELECT rolname
FROM pg_roles
WHERE has_table_privilege(rolname, 'app.tab', 'SELECT');

     rolname
------------------
 pg_read_all_data
 student
 postgres
 alice
 readers
 bob
(6 rows)

А сведения о включении ролей — используя другую функцию:

student=# SELECT rolname
FROM pg_roles
WHERE pg_has_role('bob', rolname, 'MEMBER,USAGE');

 rolname
---------
 readers
 bob
(2 rows)

Более подробную информацию о включении дает метакоманда psql:

student=# \drg

Page 23: Page 23

            List of role grants
 Role name | Member of | Options | Grantor
-----------+-----------+---------+----------
 alice     | student   | SET     | postgres
 bob       | readers   | INHERIT | postgres
 charlie   | writers   | INHERIT | postgres
(3 rows)

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

student=# SELECT table_schema, table_name, string_agg(privilege_type,',') AS privs
FROM information_schema.tables
  NATURAL JOIN information_schema.table_privileges
WHERE table_schema = 'app'
GROUP BY 1,2
ORDER BY 1,2 \gx

-[ RECORD 1
]+------------------------------------------------------------------------------------
table_schema | app
table_name   | tab
privs        |
INSERT,SELECT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,SELECT,INSERT,UPDATE,DELETE

3. Обычный пользователь

student=# CREATE USER dave;

CREATE ROLE

student=# \c - dave

You are now connected to database "access_membership" as user "dave".

Дейв не имеет доступа к схеме и таблице:

dave=> SELECT * FROM app.tab;

ERROR:  permission denied for schema app
LINE 1: SELECT * FROM app.tab;
                      ^

Однако он видит список всех таблиц:

dave=> SELECT oid, relname, relacl
FROM pg_class
WHERE relowner = 'alice'::regrole;

  oid  |       relname        |                         relacl
-------+----------------------+---------------------------------------------------------
 16400 | pg_toast_16396       |
 16401 | pg_toast_16396_index |
 16396 | tab                  | {alice=arwdDxt/alice,readers=r/alice,writers=awd/alice}
(3 rows)

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

dave=> SELECT rolname
FROM pg_roles
WHERE has_table_privilege(rolname, 16396::regclass, 'SELECT');

     rolname
------------------
 pg_read_all_data
 student
 postgres
 alice
 readers
 bob
(6 rows)

Пользователь может получить и информацию о включении ролей:

dave=> SELECT rolname
FROM pg_roles
WHERE pg_has_role('bob', rolname, 'MEMBER,USAGE');

Page 24: Page 24

 rolname
---------
 readers
 bob
(2 rows)

dave=> \drg

            List of role grants
 Role name | Member of | Options | Grantor
-----------+-----------+---------+----------
 alice     | student   | SET     | postgres
 bob       | readers   | INHERIT | postgres
 charlie   | writers   | INHERIT | postgres
(3 rows)

Уборка

dave=> \c student student

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

student=# DROP DATABASE access_membership WITH (FORCE);

DROP DATABASE

student=# DROP ROLE alice, bob, charlie, dave, readers, writers;

DROP ROLE

03 Занятие 03: Управление доступом / Доступ на уровне строк

Исходная страница в нативном Markdown: Управление доступом / Доступ на уровне строк

Примечание

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: dbs_03_access_rows.html Archive: DBS-handouts-16.zip Страниц PDF: 32

Page 1: Управление доступом / Доступ на уровне строк

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

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

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

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

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

Page 2: Темы

Задача доступа на уровне строк
Реализация с помощью функций
Реализация с помощью представлений
Политики защиты строк

Page 3: Доступ на уровне строк

Управление доступом на основе ролей

разграничение на уровне отношений (столбцов)
роли и привилегии, базовый механизм
Управление доступом на уровне строк
- разграничение на уровне отдельных строк отношений
- требуется доверенный контекст
- различные способы реализации

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

Роли и привилегии реализуют ролевую модель доступа (Role-Based Access Control, RBAC), базовый механизм управления доступом PostgreSQL. Эта модель регламентирует доступ к отношениям (таблицам, представлениям), возможно к отдельным их столбцам.

Более детальное разграничение информации на уровне отдельных строк как правило реализуется в приложениях, но может возникнуть потребность ограничить доступ и на уровне базы данных. Такое разграничение требует учета детальных условий (Fine-Grained Access Control, FGAC).

Критерии доступа должны быть основаны на информации, которой можно доверять. Очевидно, что нельзя полагаться ни на какие данные, передаваемые самим пользователем (параметры функций, условия в запросах и т. п.), поскольку злоумышленник может их подменить. По сути, единственное, чему можно доверять — это имя пользователя, поскольку СУБД проводит его проверку (аутентификацию). Если же аутентификация клиентов выполняется приложением, задача осложняется (см. задание Практики+).

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

Page 4: Функции

Подход к решению

Характеристика безопасности

Page 5: Подход к решению

{#source-figure-005 caption=“Source slide page 5: Подход к решению”}

Табличные функции для чтения данных

Обычные функции или процедуры для изменения данных

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

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

https://postgrespro.ru/docs/postgresql/16/xfunc-sql#XFUNC-SQL-FUNCTIO NS-RETURNING-SET

https://postgrespro.ru/docs/postgresql/16/xfunc-sql#XFUNC-SQL-FUNCTIO NS-RETURNING-TABLE

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

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

Page 6: Функции

Создадим базу данных:

student=# CREATE DATABASE access_rows;

CREATE DATABASE

student=# \c access_rows

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

Для демонстрации нам потребуются традиционные роли:

student=# CREATE USER alice; -- примерный пользователь

CREATE ROLE

student=# CREATE USER bob;   -- хакер в душе

CREATE ROLE

Алиса будет работать в одном сеансе, Боб — в другом.

student$ psql -d access_rows -U alice

student$ psql -d access_rows -U bob

Таблица содержит информацию о доходах и расходах.

student=# CREATE TABLE revenue(
  who text DEFAULT current_user,
  amount numeric(10,2)
);

CREATE TABLE

Заполним таблицу произвольными данными (Алиса зарабатывает, Боб тратит).

student=# INSERT INTO revenue(who, amount) VALUES
  ('alice', 1000.00), ('alice', 2000.00),
  ('bob',  -1000.00), ('bob',  -2000.00);

INSERT 0 4

Задача состоит в том, чтобы Алиса видела только свои операции, а Боб — только свои. Создадим функцию, возвращающую множество строк. Запрос фильтрует данные, оставляя только строки, соответствующие пользователю сеанса:

student=# CREATE FUNCTION get_revenue() RETURNS TABLE(who text, amount numeric)
LANGUAGE sql
BEGIN ATOMIC
  SELECT * FROM revenue WHERE who = session_user;
END;

CREATE FUNCTION

Алиса пробует воспользоваться функцией:

alice=> SELECT * FROM get_revenue();

ERROR:  permission denied for table revenue
CONTEXT:  SQL function "get_revenue" statement 1

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

Page 7: Права для подпрограмм

SECURITY INVOKER (по умолчанию)

выполняется с правами вызывающего
тело функции может подставляться в запрос
SECURITY DEFINER
- выполняется с правами владельца
- тело функции никогда не подставляется в запрос

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

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

При этом важно, от имени какого пользователя будет выполняться подпрограмма. Если подпрограмма объявлена как SECURITY INVOKER (по умолчанию), она выполняется с правами вызывающего пользователя. В этом случае операторы внутри подпрограммы смогут обращаться только к тем объектам, к которым у вызывающего пользователя есть доступ.

Если же указать фразу SECURITY DEFINER, подпрограмма работает с правами ее владельца. Так можно позволить другим пользователям выполнять определенные действия над объектами, к которым у них нет непосредственного доступа.

Тонкий момент состоит в том, что при определенных условиях тело SQL-функции с характеристикой безопасности SECURITY INVOKER может подставляться в основной запрос (рассматривается подробно в курсах QPT и DEV2) — обычно это хорошо, поскольку планировщик получает больше свободы и может найти более оптимальный план. Но тело функции с характеристикой SECURITY DEFINER никогда не подставляется. Таким образом, сначала функция полностью вычисляется, и только затем к полученному результату применяются дополнительные условия. Это может быть менее эффективно.

https://postgrespro.ru/docs/postgresql/16/sql-createfunction#SQL-CREATEF UNCTION-SECURITY

Page 8: Характеристики безопасности

{#source-figure-008 caption=“Source slide page 8: Характеристики безопасности”}

Поменяем характеристику безопасности функции:

student=# ALTER FUNCTION get_revenue SECURITY DEFINER;

ALTER FUNCTION

Обратите внимание, что в условии мы использовали функцию session_user, а не current_user. Поскольку функция с характеристикой SECURITY DEFINER выполняется от имени владельца, то и current_user в ней принимает значение имени роли-владельца. Теперь Алиса видит свои строки, а Боб — свои, как и задумано:

alice=> SELECT * FROM get_revenue();

  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
(2 rows)

bob=> SELECT * FROM get_revenue();

 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)

Изменение данных

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

student=# CREATE FUNCTION ins_revenue(who text, amount numeric) RETURNS void
LANGUAGE sql
SECURITY DEFINER
BEGIN ATOMIC
  INSERT INTO revenue(who, amount) VALUES (ins_revenue.who, ins_revenue.amount);
END;

CREATE FUNCTION

У Алисы все работает:

alice=> SELECT ins_revenue(current_user,100.00);

 ins_revenue
-------------
(1 row)

alice=> SELECT * FROM get_revenue();

  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
(3 rows)

Но Боб решил посмотреть, получится ли у него вставить строку от имени Алисы:

bob=> SELECT ins_revenue('alice',-9999.99);

 ins_revenue
-------------
(1 row)

Строка добавилась, но Боб ее не видит:

bob=> SELECT * FROM get_revenue();

Page 9: Page 9

 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)

Зато Алиса удивилась:

alice=> SELECT * FROM get_revenue();

  who  |  amount
-------+----------
 alice |  1000.00
 alice |  2000.00
 alice |   100.00
 alice | -9999.99
(4 rows)

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

student=# DELETE FROM revenue WHERE who = 'alice' AND amount < 0;

DELETE 1

student=# DROP FUNCTION ins_revenue;

DROP FUNCTION

student=# CREATE FUNCTION ins_revenue(amount numeric) RETURNS void
LANGUAGE sql
SECURITY DEFINER
BEGIN ATOMIC
  INSERT INTO revenue(who, amount) VALUES (session_user, ins_revenue.amount);
END;

CREATE FUNCTION

Page 10: Представления

Подход к решению

Барьер безопасности и герметичные функции

Page 11: Подход к решению

{#source-figure-011 caption=“Source slide page 11: Подход к решению”}

Представления для чтения

Обновляемые представления или триггеры для изменения

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

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

В простом случае, когда представление строится на одной таблице, не использует группировку и т. д., представление будет обновляемым и для него будут работать команды INSERT, UPDATE, DELETE, MERGE. В более сложном случае для обновления придется создать триггеры INSTEAD OF (триггеры подробно обсуждаются в одноименной теме курса DEV1).

https://postgrespro.ru/docs/postgresql/16/sql-createview

Page 12: Представления

{#source-figure-012 caption=“Source slide page 12: Представления”}

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

student=# CREATE VIEW user_revenue AS
SELECT * FROM revenue
WHERE who = session_user;

CREATE VIEW

Предоставим Алисе и Бобу право читать данные из представления:

student=# GRANT SELECT ON user_revenue TO alice, bob;

GRANT

Все работает, как задумано:

alice=> SELECT * FROM user_revenue;

  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
(3 rows)

bob=> SELECT * FROM user_revenue;

 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)

Обратите внимание, что представление работает как функция SECURITY DEFINER: права на базовые таблицы не требуются. Если требуется аналог функции SECURITY INVOKER, нужно установить соответствующий параметр представления:

student=# ALTER VIEW user_revenue SET (security_invoker);

ALTER VIEW

alice=> SELECT * FROM user_revenue;

ERROR:  permission denied for table revenue

Теперь представление требует привилегии на базовые таблицы. Вернем поведение по умолчанию:

student=# ALTER VIEW user_revenue SET (security_invoker = false);

ALTER VIEW

Боб подумал, что может использовать в условии WHERE функцию, выводящую на терминал все строки, которые попадают ей на вход. Если при выполнении запроса эта функция будет вызываться для каждой строки таблицы, он сможет увидеть секретные сведения. У Боба нет привилегий ни на одну схему, но через псевдороль PUBLIC он получил на базу данных привилегию TEMPORARY, позволяющую создавать объекты во временной схеме.

bob=> CREATE FUNCTION pg_temp.hack(who text, amount numeric) RETURNS boolean AS
$$
BEGIN
  RAISE NOTICE '%, %', who, amount;
  RETURN true;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

bob=> SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);

Page 13: Page 13

{#source-figure-013 caption=“Source slide page 13: Page 13”}

NOTICE:  bob, -1000.00
NOTICE:  bob, -2000.00
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)

Не получилось, но Боб не отчаивается и смотрит план выполнения запроса:

bob=> EXPLAIN SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);

                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on revenue  (cost=0.00..293.55 rows=2 width=48)
   Filter: ((who = SESSION_USER) AND pg_temp.hack(who, amount))
(2 rows)

Очевидно, условие на пользователя сработало раньше его функции. Если бы он мог убедить планировщик в том, что функцию надо выполнить первой… и такой способ действительно есть: можно задать функции очень низкую стоимость (это подробно рассматривается в теме “Функции” курса QPT):

bob=> ALTER FUNCTION pg_temp.hack COST 0.0000000000000000000001;

ALTER FUNCTION

bob=> SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);

NOTICE:  alice, 1000.00
NOTICE:  alice, 2000.00
NOTICE:  bob, -1000.00
NOTICE:  bob, -2000.00
NOTICE:  alice, 100.00
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)

Вуаля! Боб увидел строки, которые от него скрывают.

Page 14: Барьер безопасности

Защита от потенциально опасных (негерметичных) функций

не содержит
негерметичных
SELECT …
FROM view
WHERE условие1 AND условие2
функций
барьер
безопасности
SELECT …
FROM (
SELECT … FROM … WHERE фильтр AND условие1 AND условие2
- SELECT …
- FROM …
- WHERE фильтр
- AND условие2
- )
- WHERE условие1

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

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

С функциями такой проблемы не возникает, они непрозрачны: сначала вычисляется результат, потом к получившемуся набору строк применяются дополнительные условия. Исключение составляет подстановка тела SQL-функций в запрос (inlining), но для функций с характеристикой SECURITY DEFINER подстановка никогда не выполняется.

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

https://postgrespro.ru/docs/postgresql/16/sql-createview

https://postgrespro.ru/docs/postgresql/16/sql-createfunction

Page 15: Барьер безопасности

{#source-figure-015 caption=“Source slide page 15: Барьер безопасности”}

Включим барьер безопасности:

student=# ALTER VIEW user_revenue SET (security_barrier);

ALTER VIEW

Теперь уловка Боба не работает:

bob=> SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);

NOTICE:  bob, -1000.00
NOTICE:  bob, -2000.00
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)

В плане запроса видно, что сначала вычисляется условие из представления, а затем уже функция Боба:

bob=> EXPLAIN SELECT * FROM user_revenue WHERE pg_temp.hack(who, amount);

                            QUERY PLAN
-------------------------------------------------------------------
 Subquery Scan on user_revenue  (cost=0.00..26.10 rows=2 width=48)
   Filter: pg_temp.hack(user_revenue.who, user_revenue.amount)
   ->  Seq Scan on revenue  (cost=0.00..26.05 rows=5 width=48)
         Filter: (who = SESSION_USER)
(4 rows)

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

bob=> ALTER FUNCTION pg_temp.hack LEAKPROOF;

ERROR:  only superuser can define a leakproof function

Пометить функцию как герметичную может только суперпользователь, это действие недоступно даже ее владельцу.

Изменение данных

Наше представление обновляемое. Проверим, например, как работает вставка:

student=# GRANT INSERT ON user_revenue TO alice, bob;

GRANT

alice=> INSERT INTO user_revenue(who, amount) VALUES (current_user, 200.00);

INSERT 0 1

alice=> SELECT * FROM user_revenue;

  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
 alice |  200.00
(4 rows)

У Алисы все работает. Но Боб снова пытается вставить чужую строку, и у него снова получается:

bob=> INSERT INTO user_revenue(who, amount) VALUES ('alice', -9999.99);

INSERT 0 1

bob=> SELECT * FROM user_revenue;

 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
(2 rows)

Page 16: Page 16

{#source-figure-016 caption=“Source slide page 16: Page 16”}

Можно пойти тем же путем, что и в подходе с функциями: разрешить вставку только для столбца amount.

student=# REVOKE INSERT ON user_revenue FROM alice, bob;

REVOKE

student=# GRANT INSERT(amount) ON user_revenue TO alice, bob;

GRANT

student=# DELETE FROM revenue WHERE who = 'alice' AND amount < 0;

DELETE 1

Теперь Боб не может указать пользователя:

bob=> INSERT INTO user_revenue(who, amount) VALUES ('alice', -9999.99);

ERROR:  permission denied for view user_revenue

Но с указанием только суммы вставка работает:

bob=> INSERT INTO user_revenue(amount) VALUES (-100.00);

INSERT 0 1

bob=> SELECT * FROM user_revenue;

 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
 bob |  -100.00
(3 rows)

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

student=# GRANT INSERT ON user_revenue TO alice, bob;

GRANT

student=# ALTER VIEW user_revenue SET (check_option = local);

ALTER VIEW

Боб проверяет и терпит неудачу:

bob=> INSERT INTO user_revenue(who, amount) VALUES ('alice', -9999.99);

ERROR:  new row violates check option for view "user_revenue"
DETAIL:  Failing row contains (who, amount) = (alice, -9999.99).

Page 17: Политики защиты строк

Подход к решению

Условия применения политик
Комбинирование политик

Page 18: Подход к решению

{#source-figure-018 caption=“Source slide page 18: Подход к решению”}

Доступ к базовой таблице со включенной защитой строк

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

Третий подход заключается в использовании специально созданного механизма — политик защиты строк (row-level security, RLS). В этом случае пользователю предоставляется доступ к самой таблице, а проверка условий, заданных в политике, происходит автоматически.

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

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

https://postgrespro.ru/docs/postgresql/16/ddl-rowsecurity

Page 19: Условия применения

Политика применяется

к таблице, для которой включена защита
для указанных ролей и для указанных операторов
(SELECT, INSERT, UPDATE, DELETE)
Политика не применяется
- при проверке ограничений целостности
- для суперпользователей и ролей с атрибутом BYPASSRLS
- для владельца (если не включить принудительно)
Применение приводит к ошибке
- при создании логической копии утилитой pg_dump
- row_security = off

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

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

При создании политики можно также задать, для каких ролей она будет работать (по умолчанию — для всех) и для каких операторов (по умолчанию — также для всех).

Политики не применяются при проверке ограничений целостности — независимо от настроенных политик СУБД гарантирует целостность данных.

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

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

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

Page 20: Политики защиты строк

Определим политику с тем же условием, что раньше, и включим защиту строк на таблице:

student=# CREATE POLICY users ON revenue
USING ( who = current_user );

CREATE POLICY

student=# ALTER TABLE revenue ENABLE ROW LEVEL SECURITY;

ALTER TABLE

Политики, созданные для таблицы, показывают команды psql \d (описание объекта) и \dp (описание привилегий), например:

student=# \d revenue

                    Table "public.revenue"
 Column |     Type      | Collation | Nullable |   Default
--------+---------------+-----------+----------+--------------
 who    | text          |           |          | CURRENT_USER
 amount | numeric(10,2) |           |          |
Policies:
    POLICY "users"
      USING ((who = CURRENT_USER))

Эту информацию можно получить и из представления pg_policies системного каталога. Теперь можно выдать Алисе и Бобу привилегии на таблицу:

student=# GRANT SELECT ON revenue TO alice, bob;

GRANT

Проверяем:

alice=> SELECT * FROM revenue;

  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
 alice |  200.00
(4 rows)

bob=> SELECT * FROM revenue;

 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
 bob |  -100.00
(3 rows)

Изменение данных

Наша политика работает так же, как представление с параметром check_option — не допускает добавления строк, не удовлетворяющих условию.

student=# GRANT INSERT ON revenue TO alice, bob;

GRANT

У Алисы все работает:

alice=> INSERT INTO revenue(who, amount) VALUES (current_user, 300.00);

INSERT 0 1

alice=> SELECT * FROM revenue;

Page 21: Page 21

{#source-figure-021 caption=“Source slide page 21: Page 21”}

  who  | amount
-------+---------
 alice | 1000.00
 alice | 2000.00
 alice |  100.00
 alice |  200.00
 alice |  300.00
(5 rows)

Боб пробует свои наработки, но безуспешно:

bob=> SELECT * FROM revenue WHERE pg_temp.hack(who, amount);

NOTICE:  bob, -1000.00
NOTICE:  bob, -2000.00
NOTICE:  bob, -100.00
 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
 bob |  -100.00
(3 rows)

bob=> INSERT INTO revenue(who, amount) VALUES ('alice', -9999.99);

ERROR:  new row violates row-level security policy for table "revenue"

Для новых строк можно задать другие условия в предложении WITH CHECK. Например, создадим аналог представления с выключенным параметром check_option, допускающий вставку любых строк.

student=# DROP POLICY users ON revenue;

DROP POLICY

student=# CREATE POLICY users ON revenue
USING ( who = current_user )
WITH CHECK ( true );

CREATE POLICY

Теперь Боб может развернуться:

bob=> INSERT INTO revenue(who, amount) VALUES ('alice', -9999.99);

INSERT 0 1

bob=> SELECT * FROM revenue;

 who |  amount
-----+----------
 bob | -1000.00
 bob | -2000.00
 bob |  -100.00
(3 rows)

Обратите внимание, что команда COPY FROM не работает при включенной защите строк:

bob=> COPY revenue FROM stdin;

ERROR:  COPY FROM not supported with row-level security
HINT:  Use INSERT statements instead.

Page 22: Комбинирование политик

Разрешительные политики

видимость должна предоставить хотя бы одна разрешительная политика
если не определена ни одна политика, строка не видима
Ограничительные политики
- видимость должны предоставить все ограничительные политики,
- если они определены

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

На одной таблице можно определить несколько политик. В этом случае будут учитываться условия всех политик.

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

Но если на таблице включена защита строк, и при этом не определено ни одной разрешительной политики, не будет доступна ни одна строка.

Дополнительно можно создать и ограничительные (restrictive) политики. Если они заданы, то все их предикаты должны быть истинны.

Иными словами, если определены только разрешительные политики с условиями P1,…, PN, то для каждой строки вычисляется выражение

P1 OR … OR PN.

А если к тому же определены ограничительные политики с предикатами R1,…, RM, то для каждой строки будет вычисляться выражение (P1 OR … OR PN) AND R1 AND … AND RM.

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

Page 23: Комбинирование политик

{#source-figure-023 caption=“Source slide page 23: Комбинирование политик”}

Сначала убедимся, что при включенной защите строк отсутствие политик приводит к тому, что не видна ни одна строка:

student=# DROP POLICY users ON revenue;

DROP POLICY

alice=> SELECT * FROM revenue;

 who | amount
-----+--------
(0 rows)

Теперь создадим две политики — разрешительные по умолчанию. Первая, как и раньше, разрешает пользователю видеть свои строки:

student=# CREATE POLICY users ON revenue
USING ( who = current_user );

CREATE POLICY

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

student=# CREATE POLICY small_amount ON revenue
USING ( abs(amount) <= 500.00 );

CREATE POLICY

Проверим.

bob=> SELECT * FROM revenue;

  who  |  amount
-------+----------
 bob   | -1000.00
 bob   | -2000.00
 alice |   100.00
 alice |   200.00
 bob   |  -100.00
 alice |   300.00
(6 rows)

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

student=# CREATE POLICY big_amount ON revenue AS RESTRICTIVE
USING ( abs(amount) < 1000.00 );

CREATE POLICY

bob=> SELECT * FROM revenue;

  who  | amount
-------+---------
 alice |  100.00
 alice |  200.00
 bob   | -100.00
 alice |  300.00
(4 rows)

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

Page 24: Итоги

Ограничения доступа на уровне строк реализуют с помощью

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

Page 25: Практика

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

1. Решите ту же задачу с помощью представления.
1. Решите ту же задачу с помощью политик защиты строк.

Page 26: 1. Разграничение с помощью функции

Создадим необходимые роли и базу данных.

student=# CREATE USER alice;

CREATE ROLE

student=# CREATE USER bob;

CREATE ROLE

student=# CREATE USER charlie;

CREATE ROLE

student=# CREATE USER dave;

CREATE ROLE

student=# CREATE ROLE staff ROLE alice, bob, charlie, dave;

CREATE ROLE

student=# CREATE DATABASE access_rows;

CREATE DATABASE

student=# \c access_rows

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

Создадим таблицу распределения сотрудников по отделам.

student=# CREATE TABLE staff(
  who text PRIMARY KEY,
  department text
);

CREATE TABLE

student=# INSERT INTO staff(who, department) VALUES
  ('alice', 'Sales'),
  ('bob', 'PR'),
  ('charlie', 'Sales'),
  ('dave', 'PR');

INSERT 0 4

Таблица доходов и расходов из демонстрации теперь ссылается на staff.

student=# CREATE TABLE revenue(
  who text DEFAULT current_user REFERENCES staff,
  amount numeric(10,2)
);

CREATE TABLE

student=# INSERT INTO revenue(who, amount)
SELECT
  s.who,
  random()*1000 * CASE WHEN s.department = 'Sales' THEN 1 ELSE -1 END
FROM staff s, generate_series(1,1000);

INSERT 0 4000

Функция для разграничения доступа по строкам:

student=# CREATE FUNCTION get_revenue() RETURNS TABLE (LIKE revenue)
LANGUAGE sql
SECURITY DEFINER
BEGIN ATOMIC
  SELECT r.*
  FROM revenue r
    JOIN staff s ON s.who = r.who
    JOIN staff s0 ON s0.department = s.department AND s0.who = session_user;
END;

CREATE FUNCTION

Проверяем в сеансе Алисы:

Page 27: Page 27

{#source-figure-027 caption=“Source slide page 27: Page 27”}

student$ psql -d access_rows -U alice

alice=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;

ERROR:  permission denied for table revenue

Прямого доступа к таблице нет.

alice=> SELECT who, sum(amount)
FROM get_revenue()
GROUP BY who;

   who   |    sum
---------+-----------
 charlie | 489497.33
 alice   | 510889.47
(2 rows)

Функция работает. Проверяем в сеансе Боба:

student$ psql -d access_rows -U bob

bob=> SELECT who, sum(amount)
FROM get_revenue()
GROUP BY who;

 who  |    sum
------+------------
 dave | -498182.39
 bob  | -490463.06
(2 rows)

2. Разграничение с помощью представления

Представление для разграничения доступа:

student=# CREATE VIEW user_revenue AS
  SELECT r.*
  FROM revenue r
    JOIN staff s ON s.who = r.who
    JOIN staff s0 ON s0.department = s.department AND s0.who = session_user;

CREATE VIEW

student=# GRANT SELECT ON user_revenue TO staff;

GRANT

Проверяем.

alice=> SELECT who, sum(amount)
FROM user_revenue
GROUP BY who;

   who   |    sum
---------+-----------
 alice   | 510889.47
 charlie | 489497.33
(2 rows)

bob=> SELECT who, sum(amount)
FROM user_revenue
GROUP BY who;

 who  |    sum
------+------------
 bob  | -490463.06
 dave | -498182.39
(2 rows)

3. Разграничение с помощью политик

Политика защиты строк:

Page 28: Page 28

{#source-figure-028 caption=“Source slide page 28: Page 28”}

student=# CREATE POLICY dept ON revenue
USING (
  who IN (
    SELECT s.who
    FROM staff s0
      JOIN staff s ON s.department = s0.department AND s0.who = session_user
  )
);

CREATE POLICY

student=# ALTER TABLE revenue ENABLE ROW LEVEL SECURITY;

ALTER TABLE

student=# GRANT SELECT ON staff, revenue TO staff;

GRANT

Проверяем.

alice=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;

   who   |    sum
---------+-----------
 charlie | 489497.33
 alice   | 510889.47
(2 rows)

bob=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;

 who  |    sum
------+------------
 dave | -498182.39
 bob  | -490463.06
(2 rows)

Уборка

student=# \c student

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

student=# DROP DATABASE access_rows WITH (FORCE);

DROP DATABASE

student=# DROP ROLE alice, bob, charlie, dave, staff;

DROP ROLE

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

Выполните настройку политик защиты строк, как описано в задании 3 Практики.

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

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

Установите у представления параметр security_invoker.

Page 30: 1. Настройка политик

Создаем роли, база данных и таблицы как в Практике.

student=# CREATE USER alice;

CREATE ROLE

student=# CREATE USER bob;

CREATE ROLE

student=# CREATE USER charlie;

CREATE ROLE

student=# CREATE USER dave;

CREATE ROLE

student=# CREATE ROLE staff ROLE alice, bob, charlie, dave;

CREATE ROLE

student=# CREATE DATABASE access_rows;

CREATE DATABASE

student=# \c access_rows

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

student=# CREATE TABLE staff(
  who text PRIMARY KEY,
  department text
);

CREATE TABLE

student=# INSERT INTO staff(who, department) VALUES
  ('alice', 'Sales'),
  ('bob', 'PR'),
  ('charlie', 'Sales'),
  ('dave', 'PR');

INSERT 0 4

student=# CREATE TABLE revenue(
  who text DEFAULT current_user REFERENCES staff,
  amount numeric(10,2)
);

CREATE TABLE

student=# INSERT INTO revenue(who, amount)
SELECT
  s.who,
  random()*1000 * CASE WHEN s.department = 'Sales' THEN 1 ELSE -1 END
FROM staff s, generate_series(1,1000);

INSERT 0 4000

Политика защиты строк:

student=# CREATE POLICY dept ON revenue
USING (
  who IN (
    SELECT s.who
    FROM staff s0
      JOIN staff s ON s.department = s0.department AND s0.who = session_user
  )
);

CREATE POLICY

student=# ALTER TABLE revenue ENABLE ROW LEVEL SECURITY;

ALTER TABLE

student=# GRANT SELECT ON staff, revenue TO staff;

GRANT

Page 31: Page 31

Проверяем работу политик, сначала в сеансе Алисы:

student$ psql -d access_rows -U alice

alice=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;

   who   |    sum
---------+-----------
 charlie | 498936.20
 alice   | 505916.69
(2 rows)

Затем в сеансе Боба:

student$ psql -d access_rows -U bob

bob=> SELECT who, sum(amount)
FROM revenue
GROUP BY who;

 who  |    sum
------+------------
 dave | -502933.63
 bob  | -506120.34
(2 rows)

2. Представление

student=# CREATE VIEW v_dept_totals AS
  SELECT department, sum(amount)
  FROM revenue r
    JOIN staff s ON s.who = r.who
  GROUP BY s.department;

CREATE VIEW

Владелец по умолчанию видит все:

student=# SELECT * FROM v_dept_totals;

 department |     sum
------------+-------------
 PR         | -1009053.97
 Sales      |  1004852.89
(2 rows)

student=# GRANT SELECT ON v_dept_totals TO staff;

GRANT

3. Проверка представления

Обратимся к представлению в сеансе Алисы:

alice=> SELECT * FROM v_dept_totals;

 department |     sum
------------+-------------
 PR         | -1009053.97
 Sales      |  1004852.89
(2 rows)

Несмотря на наличие политики, Алиса видит все строки исходной таблицы, так как запрос, определяющий представление, по умолчанию выполняется от имени его владельца student. То же и для Боба:

bob=> SELECT * FROM v_dept_totals;

 department |     sum
------------+-------------
 PR         | -1009053.97
 Sales      |  1004852.89
(2 rows)

Page 32: 4. Свойство представления security_invoker

{#source-figure-032 caption=“Source slide page 32: 4. Свойство представления security_invoker”}

Чтобы запрос представления выполнялся от имени текущего пользователя, нужно задать параметр security_invoker:

student=# ALTER VIEW v_dept_totals SET (security_invoker);

ALTER VIEW

Теперь и Алиса, и Боб видят строки только своих отделов:

alice=> SELECT * FROM v_dept_totals;

 department |    sum
------------+------------
 Sales      | 1004852.89
(1 row)

bob=> SELECT * FROM v_dept_totals;

 department |     sum
------------+-------------
 PR         | -1009053.97
(1 row)

Уборка

student=# \c student

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

student=# DROP DATABASE access_rows WITH (FORCE);

DROP DATABASE

student=# DROP ROLE alice, bob, charlie, dave, staff;

DROP ROLE

04 Занятие 04: Управление доступом / Подходы к настройке

Исходная страница в нативном Markdown: Управление доступом / Подходы к настройке

Примечание

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: dbs_04_access_use_cases.html Archive: DBS-handouts-16.zip Страниц PDF: 27

Page 1: Управление доступом / Подходы к настройке

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

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

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

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

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

Page 2: Темы

Администрирование без прав суперпользователя
Примеры организации доступа

Page 3: Без суперпользователя

Административные роли

Предопределенные роли
Управление ролями

Page 4: Административные роли

Не имеют атрибута суперпользователя

Используются для отдельных действий по сопровождению
- административные атрибуты
- параметр членства ADMIN
- предопределенные роли
- привилегии на параметры сервера
- привилегии на системные функции

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

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

Дать административные права таким ролям можно с помощью:

● атрибутов CREATEDB, CREATEROLE, BYPASSRLS, REPLICATION;

● параметра членства ADMIN;

● предопределенных ролей;

● привилегий на изменение параметров сервера;

● привилегий на отдельные системные функции;

● привилегии MAINTAIN на отдельные таблицы для выполнения административных команд, таких как VACUUM (начиная с PostgreSQL 17).

Большинство из этих возможностей были рассмотрены в предыдущих темах курса.

Page 5: Предопределенные роли

Мониторинг

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

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

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

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

https://postgrespro.ru/docs/postgresql/16/predefined-roles

Page 6: Предопределенные роли

Вот список всех ролей кластера:

student=# \duS

                                      List of roles
          Role name          |                         Attributes
-----------------------------+------------------------------------------------------------
 pg_checkpoint               | Cannot login
 pg_create_subscription      | Cannot login
 pg_database_owner           | Cannot login
 pg_execute_server_program   | Cannot login
 pg_monitor                  | Cannot login
 pg_read_all_data            | Cannot login
 pg_read_all_settings        | Cannot login
 pg_read_all_stats           | Cannot login
 pg_read_server_files        | Cannot login
 pg_signal_backend           | Cannot login
 pg_stat_scan_tables         | Cannot login
 pg_use_reserved_connections | Cannot login
 pg_write_all_data           | Cannot login
 pg_write_server_files       | Cannot login
 postgres                    | Superuser, Create role, Create DB, Replication, Bypass RLS
 student                     | Superuser, Create role, Create DB, Replication, Bypass RLS

Роль postgres — начальный суперпользователь — была создана при инициализации кластера. Вторая суперпользовательская роль, student, совпадает по имени с пользователем ОС. Она добавлена, чтобы можно было вызывать psql без параметров. Эта роль владеет одноименной базой, к которой подключен наш сеанс:

student=# \x \l student

Expanded display is on.
List of databases
-[ RECORD 1 ]-----+------------
Name              | student
Owner             | student
Encoding          | UTF8
Locale Provider   | libc
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
ICU Locale        |
ICU Rules         |
Access privileges |

Предопределенная роль pg_database_owner неявно включает в себя владельца базы данных и не может включать другие роли. Она удобна для настройки шаблонной базы, поскольку может владеть объектами. В частности, ей принадлежит схема public:

student=# \dn+ public

List of schemas
-[ RECORD 1 ]-----+---------------------------------------
Name              | public
Owner             | pg_database_owner
Access privileges | pg_database_owner=UC/pg_database_owner+
                  | =U/pg_database_owner
Description       | standard public schema

student=# \x

Expanded display is off.

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

student=# \drgS

Page 7: Page 7

                     List of role grants
 Role name  |      Member of       |   Options    | Grantor
------------+----------------------+--------------+----------
 pg_monitor | pg_read_all_settings | INHERIT, SET | postgres
 pg_monitor | pg_read_all_stats    | INHERIT, SET | postgres
 pg_monitor | pg_stat_scan_tables  | INHERIT, SET | postgres
(3 rows)

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

student=# \dconfig *reserved*

    List of configuration parameters
           Parameter            | Value
--------------------------------+-------
 reserved_connections           | 0
 superuser_reserved_connections | 3
(2 rows)

Всего возможно не более max_connections клиентских соединений. Последние reserved_connections + superuser_reserved_connections доступных соединений могут использовать только суперпользователи и члены роли pg_use_reserved_connections. Однако если осталось superuser_reserved_connections соединений или меньше, подключиться смогут только суперпользователи.

Page 8: Управление ролями

Атрибут CREATEROLE

создание, изменение и удаление ролей
членство с параметром ADMIN в созданных ролях

alice=> CREATE ROLE bob;

ADMIN

alice

CREATEROLE

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

Если роль имеет атрибут CREATEROLE, она может создавать и удалять другие роли, а также изменять их атрибуты. Созданная роль автоматически включает в себя создавшую ее роль с параметром членства ADMIN. Таким образом, роль с атрибутом CREATEROLE может управлять членством в созданных ею ролях.

Чтобы создать роль с одним из атрибутов SUPERUSER, CREATEDB, REPLICATION или BYPASSRLS, создающая роль должна сама обладать соответствующим атрибутом.

Суперпользователи могут управлять включением любых ролей в любые.

Page 9: Примеры настройки

Личные схемы

Групповой доступ
Группа-владелец
Внешние пользователи

Page 10: Личные схемы

{#source-figure-010 caption=“Source slide page 10: Личные схемы”}

alice
bob
walter

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

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

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

Начнем с простого примера. Алиса и Боб занимаются научной работой под руководством Уолтера и хранят результаты измерений на сервере в базе данных своей лаборатории.

Уолтер не является суперпользователем, администратор баз данных предоставил ему атрибуты CREATEDB и CREATEROLE. Благодаря этому Уолтер может создавать роли для сотрудников и управлять ими.

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

Page 11: Личные схемы

Администратор зарегистрировал роль руководителя:

student=# CREATE ROLE walter LOGIN CREATEROLE CREATEDB;

CREATE ROLE

Уолтер подключается:

walter=> \c - walter

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

Он создает базу данных для лаборатории.

walter=> CREATE DATABASE access_use_cases;

CREATE DATABASE

walter=> \c access_use_cases

You are now connected to database "access_use_cases" as user "walter".

Руководитель регистрирует сотрудников:

walter=> CREATE ROLE alice LOGIN;

CREATE ROLE

Создающая роль всегда включается в созданную с параметром членства ADMIN, что дает первой возможность управлять членством во второй; такое включение происходит от имени начального суперпользователя:

walter=> \drg walter

            List of role grants
 Role name | Member of | Options | Grantor
-----------+-----------+---------+----------
 walter    | alice     | ADMIN   | postgres
(1 row)

При желании можно при создании роли дополнительно выдавать параметры членства SET и INHERIT (оба или один из них), задав параметр сервера:

walter=> SET createrole_self_grant = 'SET, INHERIT';

SET

walter=> CREATE ROLE bob LOGIN;

CREATE ROLE

walter=> \drg walter

               List of role grants
 Role name | Member of |   Options    | Grantor
-----------+-----------+--------------+----------
 walter    | alice     | ADMIN        | postgres
 walter    | bob       | ADMIN        | postgres
 walter    | bob       | INHERIT, SET | walter
(3 rows)

Заметьте, что сначала суперпользователь включил создающую роль в создаваемую, а затем, благодаря параметру членства ADMIN, создавшая роль включила себя в созданную. Таким образом, роль может быть включена в одну и ту же группу несколькими ролями с разными параметрами членства. При этом управлять членством, то есть изменять параметры и исключать из группы, могут только суперпользователи и та роль, которая выполнила включение. Например, Уолтер не может исключить себя из группы Алисы:

walter=> REVOKE alice FROM walter;

WARNING:  role "walter" has not been granted membership in role "alice" by role "walter"
REVOKE ROLE

Уолтер создает для своих объектов личную схему:

walter=> CREATE SCHEMA walter;

CREATE SCHEMA

Page 12: Page 12

Параметр search_path установлен в значение по умолчанию:

walter=> SHOW search_path;

   search_path
-----------------
 "$user", public
(1 row)

Поэтому, если не указать полное имя, таблица будет создана в личной схеме.

walter=> CREATE TABLE staff (name) AS VALUES ('Walter');

SELECT 1

walter=> \d

        List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+--------
 walter | staff | table | walter
(1 row)

Руководитель создает схемы для сотрудников, становясь их владельцем.

walter=> CREATE SCHEMA alice;

CREATE SCHEMA

walter=> CREATE SCHEMA bob;

CREATE SCHEMA

Сотрудникам нужны привилегии для работы со схемами.

walter=> GRANT ALL ON SCHEMA alice TO alice;

GRANT

walter=> GRANT ALL ON SCHEMA bob TO bob;

GRANT

walter=> \x \dn+ alice|bob \x

Expanded display is on.
List of schemas
-[ RECORD 1 ]-----+-----------------
Name              | alice
Owner             | walter
Access privileges | walter=UC/walter+
                  | alice=UC/walter
Description       |
-[ RECORD 2 ]-----+-----------------
Name              | bob
Owner             | walter
Access privileges | walter=UC/walter+
                  | bob=UC/walter
Description       |

Expanded display is off.

Алиса и Боб подключаются к базе данных и создают таблицы в своих схемах.

student$ psql -d access_use_cases -U alice

alice=> CREATE TABLE experiments (name) AS VALUES ('Mice');

SELECT 1

student$ psql -d access_use_cases -U bob

bob=> CREATE TABLE experiments (name) AS VALUES ('Rats');

SELECT 1

Сотрудники могут видеть имена таблиц в других схемах:

alice=> \dt alice|bob.*

Page 13: Page 13

          List of relations
 Schema |    Name     | Type  | Owner
--------+-------------+-------+-------
 alice  | experiments | table | alice
 bob    | experiments | table | bob
(2 rows)

Но доступа к чужим схемам и объектам в них они не имеют:

alice=> SELECT has_schema_privilege('bob', 'USAGE');

 has_schema_privilege
----------------------
 f
(1 row)

Уолтер пока не имеет доступа к таблицам сотрудников:

walter=> SELECT has_table_privilege('alice.experiments', 'SELECT');

 has_table_privilege
---------------------
 f
(1 row)

Но сотрудники могут выдавать ему привилегии:

alice=> GRANT SELECT ON TABLE experiments TO walter;

GRANT

Теперь Уолтер имеет доступ к таблице Алисы:

walter=> SELECT * FROM alice.experiments;

 name
------
 Mice
(1 row)

Чтобы завершить пример, удалим схемы. Это может сделать Уолтер, как владелец:

walter=> DROP SCHEMA alice, bob, walter CASCADE;

NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table staff
drop cascades to table bob.experiments
drop cascades to table alice.experiments
DROP SCHEMA

Удалим также включение в группу Боба с параметром SET, Уолтер имеет такое право как включивший:

walter=> REVOKE bob FROM walter;

REVOKE ROLE

Page 14: Групповой доступ

{#source-figure-014 caption=“Source slide page 14: Групповой доступ”}

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

Более сложный пример.

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

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

Чтобы гарантировать порядок, Уолтер создает необходимые объекты сам; он является владельцем и схем, и объектов в них.

Page 15: Групповой доступ

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

walter=> CREATE ROLE bio_lab ROLE alice, bob;

CREATE ROLE

walter=> CREATE USER charlie;

CREATE ROLE

walter=> CREATE ROLE chem_lab ROLE charlie;

CREATE ROLE

При включении пользователей в групповые роли лабораторий им по умолчанию устанавливаются параметры членства INHERIT и SET:

walter=> \drg alice|bob|charlie

              List of role grants
 Role name | Member of |   Options    | Grantor
-----------+-----------+--------------+---------
 alice     | bio_lab   | INHERIT, SET | walter
 bob       | bio_lab   | INHERIT, SET | walter
 charlie   | chem_lab  | INHERIT, SET | walter
(3 rows)

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

walter=> CREATE SCHEMA bio_lab;

CREATE SCHEMA

walter=> CREATE SCHEMA chem_lab;

CREATE SCHEMA

walter=> GRANT USAGE ON SCHEMA bio_lab TO bio_lab;

GRANT

walter=> GRANT USAGE ON SCHEMA chem_lab TO chem_lab;

GRANT

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

walter=> ALTER DEFAULT PRIVILEGES FOR ROLE walter IN SCHEMA bio_lab
GRANT ALL ON TABLES TO bio_lab;

ALTER DEFAULT PRIVILEGES

walter=> ALTER DEFAULT PRIVILEGES FOR ROLE walter IN SCHEMA chem_lab
GRANT ALL ON TABLES TO chem_lab;

ALTER DEFAULT PRIVILEGES

Теперь Уолтер создает таблицы.

walter=> CREATE TABLE bio_lab.experiments(name) AS VALUES ('Mice'),('Rats');

SELECT 2

walter=> CREATE TABLE chem_lab.experiments(name) AS VALUES ('Acids'),('Alkalis');

SELECT 2

Поскольку Алиса входит в группу bio_lab с наследованием привилегий, она имеет доступ к таблице в схеме своей лаборатории:

alice=> SELECT * FROM bio_lab.experiments;

 name
------
 Mice
 Rats
(2 rows)

Page 16: Page 16

alice=> INSERT INTO bio_lab.experiments(name) VALUES ('Guinea-pigs');

INSERT 0 1

Но не может создавать или удалять таблицы:

alice=> CREATE TABLE bio_lab.genes(name) AS VALUES ('CFTR'),('DMD');

ERROR:  permission denied for schema bio_lab

alice=> DROP TABLE bio_lab.experiments;

ERROR:  must be owner of table experiments

У Алисы также нет доступа к таблицам других лабораторий:

alice=> SELECT * FROM chem_lab.experiments;

ERROR:  permission denied for schema chem_lab
LINE 1: SELECT * FROM chem_lab.experiments;
                      ^

Права для Боба и Чарли работают аналогично.

Page 17: Группа-владелец

{#source-figure-017 caption=“Source slide page 17: Группа-владелец”}

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

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

Разработчиков может быть несколько, но удобно, чтобы всеми объектами владела одна роль — и с точки зрения администрирования, и чтобы можно было выполнять подпрограммы от имени владельца.

Для установки нужного владельца есть два способа:

● использовать команду смены владельца после создания объекта;

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

В обоих случаях требуются членство ролей разработчиков в групповой роли владельца объектов с параметром SET.

Page 18: Группа-владелец

Биолаборатория теперь использует приложение, и Уолтер уже не может самостоятельно заниматься его разработкой и сопровождением. Он передает владение существующей схемой и таблицами группой роли:

walter=> CREATE ROLE application; -- NOLOGIN

CREATE ROLE

walter=> ALTER SCHEMA bio_lab OWNER TO application;

ALTER SCHEMA

walter=> ALTER TABLE bio_lab.experiments OWNER TO application;

ALTER TABLE

Привилегии по умолчанию необходимо изменить: теперь объектами владеет роль application, а не Уолтер:

walter=> ALTER DEFAULT PRIVILEGES FOR ROLE walter IN SCHEMA bio_lab
REVOKE ALL ON TABLES FROM bio_lab;

ALTER DEFAULT PRIVILEGES

walter=> ALTER DEFAULT PRIVILEGES FOR ROLE application IN SCHEMA bio_lab
GRANT ALL ON TABLES TO bio_lab;

ALTER DEFAULT PRIVILEGES

walter=> \ddp

                  Default access privileges
    Owner    |  Schema  | Type  |      Access privileges
-------------+----------+-------+-----------------------------
 application | bio_lab  | table | bio_lab=arwdDxt/application
 walter      | chem_lab | table | chem_lab=arwdDxt/walter
(2 rows)

Для разработки приложения Уолтер принимает на работу Дейва:

walter=> CREATE USER dave;

CREATE ROLE

Чтобы Дейв не мог по ошибке создать или изменить таблицу приложения, разрешим ему такие действия только после переключения роли. Для этого включим Дейва в роль application с параметром SET, а параметр INHERIT отключим, чтобы Дейв не унаследовал права, которые дает владение:

walter=> GRANT application TO dave WITH INHERIT FALSE, SET TRUE;

GRANT ROLE

Теперь Дейв может, начав сеанс и переключившись на роль application, добавлять объекты для приложения:

student$ psql -d access_use_cases -U dave

dave=> SET ROLE application;

SET

dave=> CREATE TABLE bio_lab.dna (
  name text,
  image bytea
);

CREATE TABLE

dave=> SELECT tableowner
FROM pg_tables
WHERE schemaname = 'bio_lab' AND tablename = 'dna';

 tableowner
-------------
 application
(1 row)

Пользователи сразу получают права на таблицу:

alice=> SELECT has_table_privilege('bio_lab.dna','SELECT');

Page 19: Page 19

{#source-figure-019 caption=“Source slide page 19: Page 19”}

 has_table_privilege
---------------------
 t
(1 row)

Проверим, что Дейв, переключившись обратно на свою роль, не может, например, изменить таблицу:

dave=> RESET ROLE;

RESET

dave=> ALTER TABLE bio_lab.dna
ADD id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY;

ERROR:  permission denied for schema bio_lab

Page 20: Внешние пользователи

{#source-figure-020 caption=“Source slide page 20: Внешние пользователи”}

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

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

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

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

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

Page 21: Внешние пользователи

{#source-figure-021 caption=“Source slide page 21: Внешние пользователи”}

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

walter=> DROP ROLE alice, bob;

DROP ROLE

walter=> ALTER ROLE bio_lab LOGIN;

ALTER ROLE

Дейв-разработчик создает таблицы для пользователей и сеансов в схеме, к которой bio_lab не имеет доступа:

walter=> CREATE SCHEMA app AUTHORIZATION application;

CREATE SCHEMA

dave=> SET ROLE application;

SET

dave=> CREATE TABLE app.users(
  name text NOT NULL,
  password text NOT NULL
);

CREATE TABLE

dave=> INSERT INTO app.users VALUES ('alice','alicepass'), ('bob','bobpass');

INSERT 0 2

dave=> CREATE TABLE app.sessions(
  name text NOT NULL,
  token uuid
);

CREATE TABLE

Представим, что Алиса открыла приложение. Оно подключается к серверу под ролью bio_lab и должно аутентифицировать Алису. Доступа к таблице пользователей у bio_lab нет и не должно быть, поэтому необходима функция с правами владельца:

dave=> CREATE FUNCTION bio_lab.auth(name text, password text)
RETURNS uuid
AS $$
  INSERT INTO app.sessions(name, token)
  SELECT auth.name, gen_random_uuid()
  FROM app.users u
  WHERE auth.name = u.name AND auth.password = u.password
  RETURNING token;
$$ LANGUAGE sql SECURITY DEFINER;

CREATE FUNCTION

Функция сохраняет и возвращает токен — случайное значение, которое невозможно подобрать:

alice=> \c - bio_lab

You are now connected to database "access_use_cases" as user "bio_lab".

bio_lab=> SELECT bio_lab.auth('alice', 'wrong'); -- нет пароля — нет токена

 auth
------
(1 row)

bio_lab=> SELECT bio_lab.auth('alice', 'alicepass');

                 auth
--------------------------------------
 5e6742f2-a42b-48dd-8cb7-3b89b6e12659
(1 row)

Приложение получает токен и передает его тому функционалу приложения, для которого важно идентифицировать пользователя. Он может воспользоваться функцией проверки:

Page 22: Page 22

{#source-figure-022 caption=“Source slide page 22: Page 22”}

dave=> CREATE FUNCTION bio_lab.check_auth(token uuid)
RETURNS text
AS $$
  SELECT name
  FROM app.sessions s
  WHERE s.token = check_auth.token;
$$ LANGUAGE sql SECURITY DEFINER;

CREATE FUNCTION

Злоумышленник, получив доступ к роли bio_lab, не сможет притвориться Алисой или другим пользователем, потому что не сможет угадать токен.

bio_lab=> SELECT bio_lab.check_auth('00000000-0000-0000-0000-000000000000'); -- не угадали

 check_auth
------------
(1 row)

bio_lab=> SELECT bio_lab.check_auth('5e6742f2-a42b-48dd-8cb7-3b89b6e12659');

 check_auth
------------
 alice
(1 row)

Скорее всего, прямой доступ bio_lab к таблицам следует запретить, а обращения к данным организовать с помощью API. С таким подходом можно ознакомиться в расширенном курсе по разработке приложений DEV2.

Page 23: Итоги

Отдельные задачи по сопровождению можно делегировать привилегированным ролям

Схемы, роли и привилегии позволяют варьировать строгость разграничения доступа в зависимости от уровня доверия

Page 24: Практика

Рассмотрите сценарий, в котором на одном сервере право создания ролей имеют два пользователя.

1. Создайте базу данных и роли bio_lab и chem_lab, обе с атрибутом CREATEROLE, владеющие одноименными схемами.
1. От имени bio_lab создайте пользователей alice и bob, выдайте им доступ к схеме bio_lab. От имени chem_lab создайте пользователя charlie, выдайте ему доступ к схеме chem_lab.
1. Убедитесь, что chem_lab не управляет пользователем alice.
1. Проверьте, что пользователи имеют доступ к «своим» схемам.

Page 25: 1. База данных и роли

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

student=# CREATE DATABASE access_use_cases;

CREATE DATABASE

student=# \c access_use_cases

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

student=# CREATE SCHEMA bio_lab;

CREATE SCHEMA

student=# CREATE SCHEMA chem_lab;

CREATE SCHEMA

student=# CREATE USER bio_lab CREATEROLE;

CREATE ROLE

student=# CREATE USER chem_lab CREATEROLE;

CREATE ROLE

Назначаем созданные роли владельцами схем.

student=# ALTER SCHEMA bio_lab OWNER TO bio_lab;

ALTER SCHEMA

student=# ALTER SCHEMA chem_lab OWNER TO chem_lab;

ALTER SCHEMA

2. Пользователи и права доступа

От имени bio_lab создаем таблицу и пользователей alice и bob.

student=# \c - bio_lab

You are now connected to database "access_use_cases" as user "bio_lab".

bio_lab=> CREATE TABLE bio_lab.experiments (name) AS VALUES ('Mice');

SELECT 1

bio_lab=> CREATE USER alice;

CREATE ROLE

bio_lab=> CREATE USER bob;

CREATE ROLE

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

bio_lab=> GRANT USAGE ON SCHEMA bio_lab TO alice, bob;

GRANT

bio_lab=> GRANT ALL ON TABLE bio_lab.experiments TO alice, bob;

GRANT

От имени chem_lab создаем таблицу и пользователя charlie.

bio_lab=> \c - chem_lab

You are now connected to database "access_use_cases" as user "chem_lab".

chem_lab=> CREATE TABLE chem_lab.experiments (name) AS VALUES ('Rats');

SELECT 1

chem_lab=> CREATE USER charlie;

CREATE ROLE

Page 26: 3. Проверка управления ролями

Выдаем необходимые права.

chem_lab=> GRANT USAGE ON SCHEMA chem_lab TO charlie;

GRANT

chem_lab=> GRANT ALL ON TABLE chem_lab.experiments TO charlie;

GRANT

Так сейчас выглядят включения ролей:

chem_lab=> \drg

            List of role grants
 Role name | Member of | Options | Grantor
-----------+-----------+---------+----------
 bio_lab   | alice     | ADMIN   | postgres
 bio_lab   | bob       | ADMIN   | postgres
 chem_lab  | charlie   | ADMIN   | postgres
(3 rows)

Обратите внимание: создающая роль автоматически включается в созданную с опцией ADMIN. Именно поэтому в выводе \drg роль bio_lab показана как член ролей alice и bob, а не наоборот. В реальных сценариях часто требуется обратная иерархия, при которой, например, роли сотрудников включаются в роль подразделения. Роль chem_lab не может управлять пользователем alice, поскольку не имеет в нем параметра членства ADMIN:

chem_lab=> DROP USER alice;

ERROR:  permission denied to drop role
DETAIL:  Only roles with the CREATEROLE attribute and the ADMIN option on role "alice"
may drop this role.

chem_lab=> ALTER USER alice NOLOGIN;

ERROR:  permission denied to alter role
DETAIL:  Only roles with the CREATEROLE attribute and the ADMIN option on role "alice"
may alter this role.

4. Проверка доступности

student$ psql -U alice -d access_use_cases

alice=> SELECT * FROM bio_lab.experiments;

 name
------
 Mice
(1 row)

Алисе доступны объекты схемы ее лаборатории.

alice=> \c - charlie

You are now connected to database "access_use_cases" as user "charlie".

Чарли видит таблицы своей лаборатории:

charlie=> SELECT * FROM chem_lab.experiments;

 name
------
 Rats
(1 row)

Но не видит объекты “чужой” схемы:

charlie=> SELECT * FROM bio_lab.experiments;

ERROR:  permission denied for schema bio_lab
LINE 1: SELECT * FROM bio_lab.experiments;
                      ^

Уборка

Page 27: Page 27

chem_lab=> \c student student

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

student=# DROP DATABASE access_use_cases WITH (FORCE);

DROP DATABASE

student=# DROP ROLE alice, bob, charlie, bio_lab, chem_lab;

DROP ROLE

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

Исходная страница в нативном Markdown: Методы аутентификации / Подключение и аутентификация

Примечание

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: dbs_05_auth_authentication.html Archive: DBS-handouts-16.zip Страниц PDF: 37

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

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

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

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

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

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

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

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

Запрещаются нешифрованные соединения.
Разрешается доступ пользователям из своей подсети к одноименным базам данных.
Разрешается доступ пользователям, входящим в роль 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$

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

Для локальных соединений c базой данных, совпадающей по имени с именем роли, PostgreSQL запрашивает имя пользователя у операционной системы. Сопоставление m1 определяет, что пользователь ОС student может подключаться под ролями alice и bob.
Правило m2, на которое ссылается вторая строка pg_hba.conf, сопоставляет пользователей OC с именем, которое начинается на букву «a», с ролями, которые заканчиваются на букву «b». Например, пользователь OC alice сможет подключиться под ролью bob. Эта строка будет использована, только если имя базы не совпадает с именем роли: в противном случае первая строка сработает раньше.
Сетевые соединения с локальным сервером аутентифицируются с помощью пароля по протоколу 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: Практика

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

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

Перед тем как вносить изменения в конфигурационный файл 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. Восстановите первоначальные настройки.

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

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

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

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

Примечание

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: dbs_06_auth_ssl.html Archive: DBS-handouts-16.zip Страниц PDF: 29

Page 1: Методы аутентификации / Использование SSL

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

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

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

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

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

Page 2: Темы

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

Page 3: Введение в криптографию

Симметричное и асимметричное шифрование

Цифровая подпись
Сертификат
Цепочка сертификатов

Page 4: Симметричный шифр

{#source-figure-004 caption=“Source slide page 4: Симметричный шифр”}

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

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

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

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

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

Page 5: Асимметричный шифр

{#source-figure-005 caption=“Source slide page 5: Асимметричный шифр”}

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

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

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

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

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

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

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

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

Page 6: Цифровая подпись

{#source-figure-006 caption=“Source slide page 6: Цифровая подпись”}

Задача: защита от подмены

A3F602
A3F602
A3F602
A
A
A
B

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

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

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

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

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

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

Page 7: Сертификат

{#source-figure-007 caption=“Source slide page 7: Сертификат”}

Проблема доверия

A
A A
- B
- C

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

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

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

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

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

Page 8: Сертификат

{#source-figure-008 caption=“Source slide page 8: Сертификат”}

Задача: перевыпуск сертификатов

A
A A A B B
- B
- C
- R
- C
- R
- Алиса

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

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

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

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

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

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

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

Page 9: Подключение SSL

Схема SSL-подключения

Настройки сервера
Настройки клиента
Проверка сервера

Page 10: Схема SSL-подключения

{#source-figure-010 caption=“Source slide page 10: Схема SSL-подключения”}

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

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

Трафик между клиентом и сервером может быть перехвачен программами-снифферами, такими, как 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-рукопожатие:

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

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

Page 11: Схема SSL-подключения

{#source-figure-011 caption=“Source slide page 11: Схема SSL-подключения”}

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

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

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

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

Page 12: Настройки клиента

Параметр 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: Настройки сервера

Параметры

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: Настройки сервера

В Ubuntu конфигурация по умолчанию использует сертификат и ключ, созданные при установке ОС:

student=# \dconfig ssl|ssl_ca_file|ssl_cert_file|ssl_key_file

            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 (мы не выводим открытый ключ, который в нем содержится):

student$ openssl x509 -noout -text -in /etc/pgssl/serv/serv.crt -certopt no_sigdump,no_pubkey

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 соответствует доменному имени нашего сервера:

student$ hostname

course.local

Расширенные атрибуты сертификата SAN (Subject Alternative Name), определенные стандартом RFC 5280, позволяют указывать несколько доменных имен или IP адресов сервера. Добавим параметры конфигурации сервера в дополнительный файл в подкаталоге conf.d. Сервер должен прослушивать все интерфейсы.

/etc/postgresql/16/main/conf.d/ssl.conf

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

student$ sudo pg_ctlcluster 16 main restart

Откроем новый сеанс без SSL. Для этого в строке соединения укажем параметр sslmode=disable (или allow):

student$ psql 'postgresql://student@localhost:5432/student?password=student&sslmode=disable'

student=# \conninfo

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

Представление pg_stat_ssl показывает статистику использования SSL сеансами:

student=# SELECT *
FROM pg_stat_ssl
WHERE pid = pg_backend_pid() \gx

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

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

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

# TYPE     DATABASE  USER   ADDRESS   METHOD
hostnossl  all       all    all       reject

student=# SELECT pg_reload_conf();

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

student=# \c

\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):

student$ psql 'postgresql://student@localhost:5432/student?password=student&sslmode=prefer'

student=# \conninfo

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)

Трафик этого сеанса защищен. Аналогичную информацию дает и представление:

student=# SELECT *
FROM pg_stat_ssl
WHERE pid = pg_backend_pid() \gx

-[ 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

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

Page 17: Проверка сервера

Параметр 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: Проверка сервера

Каталог для клиентских сертификатов ~student/.postgresql уже существует в виртуальной машине и содержит сертификат центра сертификации, а также сертификаты для пользователей student, alice и bob — они понадобятся нам чуть позже:

student$ ls -l ~/.postgresql/

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

Пробуем подключиться в режиме проверки сертификата ЦС, которым подписан сертификат сервера:

student$ psql -c '\conninfo' 'host=localhost password=student sslmode=verify-ca'

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. Повторим эксперимент в режиме полной проверки:

student$ psql -c '\conninfo' 'host=localhost password=student sslmode=verify-full'

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"

На этот раз соединение запрещено, поскольку доменное имя в сертификате не совпадает с именем сервера. Теперь подключимся в режиме полной проверки, но используем то имя сервера, которое указано в сертификате:

student$ psql -c '\conninfo' 'host=course.local password=student sslmode=verify-full'

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: Проверка клиента

Параметр 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: Проверка клиента

В предыдущих примерах клиент проверял сертификат сервера. Теперь посмотрим, как сервер может проверить сертификат клиента. Есть два способа: указать в строке pg_hba.conf параметр clientcert; использовать метод аутентификации cert. Опробуем второй вариант:

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

# TYPE   DATABASE  USER   ADDRESS   METHOD
hostssl  all       all    all       cert

student=# SELECT pg_reload_conf();

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

Заглянем в клиентский сертификат student:

student$ openssl x509 -noout -text -in /home/student/.postgresql/student.crt -certopt no_sigdump,no_pubkey

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.

student$ psql

student=# \c 'user=student host=course.local sslcert=/home/student/.postgresql/student.crt sslkey=/home/student/.postgresql/student.key'

You are now connected to database "student" as user "student" on host "course.local"
(address "127.0.1.1") at port "5432".

student=# SELECT * FROM pg_stat_ssl
WHERE pid = pg_backend_pid() \gx

-[ 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 позволяет подключиться к базе данных с именем пользователя, отличающимся от имени в сертификате. В этом случае понадобится дополнительный файл сопоставления имен:

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

m1 alice student

И нужно указать имя отображения в параметре map строки конфигурации pg_hba:

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

# TYPE   DATABASE  USER   ADDRESS   METHOD
hostssl  all       all    all       cert map=m1

student=# SELECT pg_reload_conf();

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

Пробуем подключиться как student с сертификатом Алисы:

student=# \c 'user=student host=course.local sslcert=/home/student/.postgresql/alice.crt sslkey=/home/student/.postgresql/alice.key'

Page 21: Page 21

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

student=# SELECT * FROM pg_stat_ssl
WHERE pid = pg_backend_pid() \gx

-[ 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: Итоги

Основные криптографические понятия: открытый и закрытый ключи, подпись, сертификат

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

Page 23: Практика

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

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

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

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

Page 24: 1. Настройка SSL

Запишем в каталог /etc/postgresql/16/main/conf.d параметры конфигурации сервера для SSL. Настроим сервер для прослушивания всех интерфейсов.

/etc/postgresql/16/main/conf.d/ssl.conf

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'

Перезагрузим сервер.

student$ sudo pg_ctlcluster 16 main restart

2a. Соединение без SSL (trust)

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

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

# TYPE  DATABASE  USER   ADDRESS   METHOD
host    all       all    all       trust

student$ psql

student=# SELECT pg_reload_conf();

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

Выполним инициализацию таблиц, которые используются эталонным тестом pgbench:

student$ pgbench -i

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 секунд в режиме, при котором для каждой транзакции устанавливается новое соединение:

student$ pgbench --connect -T 30 "host=localhost sslmode=disable"

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 Внимание надо обратить на значение показателя average connection time.

Page 25: 2b. Соединение с шифрованием SSL (trust)

student$ pgbench --connect -T 30 "host=localhost sslmode=require"

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. Соединение с аутентификацией по сертификату и проверкой сервера.

Потребуется изменить настройку аутентификации:

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

# TYPE   DATABASE  USER   ADDRESS   METHOD
hostssl  all       all    all       cert

student=# SELECT pg_reload_conf();

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

student$ pgbench --connect -T 30 "host=localhost sslcert=/home/student/.postgresql/student.crt
sslkey=/home/student/.postgresql/student.key sslmode=verify-ca"

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)

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

Уборка

student$ sudo rm /etc/postgresql/16/main/pg_hba.conf.d/*

student$ sudo rm /etc/postgresql/16/main/conf.d/*

student$ sudo pg_ctlcluster 16 main reload

student=# DROP TABLE pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers;

DROP TABLE

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

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

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

Для перехвата трафика используйте команду: 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

/etc/postgresql/16/main/conf.d/ssl.conf

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'

Перезагрузим сервер.

student$ sudo pg_ctlcluster 16 main restart

2. Регистрация роли

student$ psql

student=# CREATE USER alice PASSWORD 'SECRET';

CREATE ROLE

3a. Без SSL, метод password

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

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

# TYPE     DATABASE  USER   ADDRESS   METHOD
hostnossl  all       all    all       password

Перечитаем конфигурацию.

student=# SELECT pg_reload_conf();

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

Запустим перехват трафика, направленного на порт TCP 5432, на всех сетевых интерфейсах.

student$ sudo tcpdump -w ~/tmp/tcpdump.bin -i any "dst port 5432" >& ~/tmp/tcpdump.log &

Выполним код SQL в сеансе alice, используя аутентификацию password при отсутствии шифрования.

student$ psql

=> \c 'host=localhost dbname=student user=alice password=SECRET sslmode=disable'

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

=> SELECT 42;

 ?column?
----------
       42
(1 row)

=> \q

Завершим перехват трафика после небольшой паузы.

student$ sudo killall tcpdump

Проверим, видны ли в трафике имена пользователей, пароли и команды:

student$ sudo chown student:student ~/tmp/*

Page 28: 3b. С SSL, метод password

student$ tcpdump -Aqr ~/tmp/tcpdump.bin 2> /dev/null | egrep -i '(alice|select|secret)'

%...%......;....user.alice.database.student.application_name.psql..
%...%...p....SECRET.
%...%...Q....SELECT pg_backend_pid();.
%..Q%..$Q....SELECT 42;.

В перехваченном трафике вся информация открыта. Теперь включим шифрование SSL.

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

# TYPE   DATABASE  USER   ADDRESS   METHOD
hostssl  all       all    all       password

student=# SELECT pg_reload_conf();

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

Снова запустим перехват.

student$ rm -rf ~/tmp/*

student$ sudo tcpdump -w ~/tmp/tcpdump.bin -i any "dst port 5432" >& ~/tmp/tcpdump.log &

student$ psql

=> \c 'host=localhost dbname=student user=alice password=SECRET sslmode=require'

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

=> \conninfo

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)

=> SELECT 42;

 ?column?
----------
       42
(1 row)

=> \q

student$ sudo killall tcpdump

Проверим перехваченный трафик:

student$ sudo chown student:student ~/tmp/*

student$ tcpdump -Aqr ~/tmp/tcpdump.bin 2> /dev/null | egrep -i '(alice|select|secret)'

На этот раз ничего не выведено. Трафик зашифрован, подсмотреть конфиденциальную информацию не получится.

3c. Без SSL, метод scram-sha-256

Теперь повторим первый эксперимент, но с методом аутентификации scram-sha-256.

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

# TYPE     DATABASE  USER   ADDRESS   METHOD
hostnossl  all       all    all       scram-sha-256

student=# SELECT pg_reload_conf();

Page 29: Page 29

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

Запустим перехват.

student$ rm -rf ~/tmp/*

student$ sudo tcpdump -w ~/tmp/tcpdump.bin -i any "dst port 5432" >& ~/tmp/tcpdump.log &

student$ psql

=> \c 'host=localhost dbname=student user=alice password=SECRET sslmode=disable'

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

=> SELECT 42;

 ?column?
----------
       42
(1 row)

=> \q

student$ sudo killall tcpdump

Проверим перехваченный трафик:

student$ sudo chown student:student ~/tmp/*

student$ tcpdump -Aqr ~/tmp/tcpdump.bin 2> /dev/null | egrep -i '(alice|select|secret)'

%...%......;....user.alice.database.student.application_name.psql..
%...%...Q....SELECT pg_backend_pid();.
%..F%...Q....SELECT 42;.

Трафик не шифруется, но пароль в открытом виде не передается.

Уборка

student$ sudo rm /etc/postgresql/16/main/pg_hba.conf.d/*

student$ sudo rm /etc/postgresql/16/main/conf.d/*

student$ sudo pg_ctlcluster 16 main reload

student=# DROP ROLE alice;

DROP ROLE

07 Занятие 07: Методы аутентификации / Внешняя аутентификация

Исходная страница в нативном Markdown: Методы аутентификации / Внешняя аутентификация

Примечание

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: dbs_07_auth_external.html Archive: DBS-handouts-16.zip Страниц PDF: 29

Page 1: Методы аутентификации / Внешняя аутентификация

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

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

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

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

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

Page 2: Темы

Внешняя аутентификация
Аутентификация LDAP
Аутентификация GSSAPI
Аутентификация PAM

Page 3: Внешняя аутентификация

peer [параметры]

проверка в операционной системе ldap [параметры]
- проверка в службе каталогов LDAP
gssapi [параметры]
- использование GSSAPI, обычно Kerberos
sspi [параметры]
проверка средствами аутентификации Windows pam [параметры]
- проверка посредством подключаемых модулей PAM

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

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

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

При использовании метода ldap PostgreSQL обращается к службе каталогов LDAP.

GSSAPI (Generic Security Service Application Program Interface) — универсальный интерфейс для служб аутентификации, соответствующий спецификации RFC 2743. На практике используется для аутентификации в UNIX/Linux средах Kerberos, где в PostgreSQL используется метод аутентификации gssapi.

В аналогичных средах Windows, реализующих подход одноразовой аутентификации (single sign-on), используется метод sspi.

Метод pam использует подключаемые модули аутентификации (Pluggable Authentication Modules) — стандартный механизм Linux, предоставляющий приложениям высокоуровневый API для аутентификации с помощью внешних модулей.

Page 4: Аутентификация LDAP

Служба каталогов

Простое связывание
Поиск + связывание

Page 5: Служба каталогов

{#source-figure-005 caption=“Source slide page 5: Служба каталогов”}

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

LDAP (Lightweight Directory Access Protocol) — протокол для доступа к службе каталогов. Каталог представляет собой иерархию записей, которые состоят из различных атрибутов и описывают какой-либо объект: учетную запись пользователя, группу пользователей, организацию, доменное имя, сетевую службу и т. п. В учетной записи может храниться пароль, обычно в виде хеш-кода в атрибуте userPassword.

Запись идентифицируется уникальным именем (DN, distinguished name), составленным из DN родительской записи и, как правило, некоторого атрибута текущей. Например, DN учетной записи Алисы на слайде — «uid=alice,ou=BioLab,dc=course,dc=local»:

uid — идентификатор пользователя (user ID);
ou — подразделение (organization unit);
dc — домен (domain component).

https://ru.wikipedia.org/wiki/LDAP

Page 6: Простое связывание

{#source-figure-006 caption=“Source slide page 6: Простое связывание”}

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

В простом варианте СУБД связывается с сервером LDAP по уникальному имени, составленному из префикса, имени подключающейся роли и суффикса. Например, при подключении клиента под ролью alice с параметрами метода ldap

● префикс ldapprefix = «uid=»,

● суффикс ldapsuffix = «,ou=BioLab,dc=course,dc=local»

PostgreSQL пошлет запрос аутентификации (bind) к серверу LDAP, используя DN «uid=alice,ou=BioLab,dc=course,dc=local» и переданный клиентом пароль. Адрес и порт сервера LDAP задаются параметрами ldapserver и ldapport.

Если запрос выполняется успешно, аутентификация считается пройденной.

https://postgrespro.ru/docs/postgresql/16/auth-ldap

Page 7: LDAP: простое связывание

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

student=# CREATE ROLE alice LOGIN;

CREATE ROLE

student=# CREATE ROLE bob LOGIN;

CREATE ROLE

student=# CREATE ROLE charlie LOGIN;

CREATE ROLE

Учетные записи пользователей alice и bob уже заведены в дереве LDAP, их можно посмотреть с помощью утилиты:

student$ ldapsearch \
  -x                              `# аутентификация по DN и паролю` \
  -LLL                            `# краткая информация` \
  -H ldaps://$(hostname)          `# сервер LDAP` \
  -b ou=BioLab,dc=course,dc=local `# ветвь дерева` \
  "(uid=*)"                       `# фильтр` \
  cn                              `# выводить только common name`

dn: uid=bob,ou=BioLab,dc=course,dc=local
cn: Bob

dn: uid=alice,ou=BioLab,dc=course,dc=local
cn: Alice

Для PostgreSQL мы хотим использовать аутентификацию LDAP без шифрования (PostgreSQL и сервер LDAP обмениваются сообщениями в открытом виде). Добавим правило с методом аутентификации ldap для Алисы. В параметрах метода указываем префикс и суффикс:

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

# TYPE  DATABASE  USER   ADDRESS  METHOD
host    all       alice  samehost ldap ldapserver=course.local ldapprefix="uid="
ldapsuffix=",ou=BioLab,dc=course,dc=local"

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

student=# ALTER SYSTEM SET listen_addresses = '*';

ALTER SYSTEM

student=# ALTER SYSTEM SET log_connections = on;

ALTER SYSTEM

Изменение первого параметра требует перезапуска сервера.

student$ sudo pg_ctlcluster 16 main restart

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

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

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

Проверим журнал сообщений:

student$ tail -3 /var/log/postgresql/postgresql-16-main.log

2026-07-30 16:35:46.062 MSK [58393] [unknown]@[unknown] LOG:  connection received:
host=127.0.0.1 port=45556
2026-07-30 16:35:46.069 MSK [58393] alice@student LOG:  connection authenticated:
identity="uid=alice,ou=BioLab,dc=course,dc=local" method=ldap
(/etc/postgresql/16/main/pg_hba.conf.d/ext.conf:2)
2026-07-30 16:35:46.069 MSK [58393] alice@student LOG:  connection authorized: user=alice
database=student application_name=psql

Page 8: Поиск + связывание

{#source-figure-008 caption=“Source slide page 8: Поиск + связывание”}

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

Если пользователи хранятся в разных поддеревьях, суффиксы уникальных имен записей будут разными и возможностей простого связывания не хватит. Например, записи alice и charlie заведены в разных лабораториях: суффикс Алисы будет ‘ou=BioLab’, а Чарли — ‘ou=ChemLab’.

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

Сначала выполняется поиск нужной записи (search). Для этого PostgreSQL подключается к серверу LDAP под фиксированным именем и паролем (ldapbinddn, ldapbindpasswd) и ищет в поддереве ldapbasedn запись, у которой атрибут ldapsearchattribute равен имени подключающейся роли.

Например, для роли charlie с настройками

● ldapbasedn = ‘dc=course,dc=local’,

● ldapsearchattribute = ‘uid’

PostgreSQL обнаружит запись «uid=charlie,ou=ChemLab,dc=course,dc=local».

Page 9: Поиск + связывание

{#source-figure-009 caption=“Source slide page 9: Поиск + связывание”}

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

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

Page 10: LDAP: поиск + связывание

Испытаем аутентификацию LDAP с поиском и связыванием на Чарли. Его учетная запись зарегистрирована в другом поддереве LDAP, поэтому простой режим не подходит:

student$ ldapsearch \
  -x                              `# аутентификация по DN и паролю` \
  -LLL                            `# краткая информация` \
  -H ldaps://$(hostname)          `# сервер LDAP` \
  -b dc=course,dc=local           `# ветвь дерева` \
  -D cn=admin,dc=course,dc=local  `# от кого запрос` \
  -w admin                        `# пароль` \
  "(uid=*)"                       `# фильтр` \
  cn                              `# выводить только common name`

dn: uid=alice,ou=BioLab,dc=course,dc=local
cn: Alice

dn: uid=bob,ou=BioLab,dc=course,dc=local
cn: Bob

dn: uid=charlie,ou=ChemLab,dc=course,dc=local
cn: Charlie

Изменим правило аутентификации. Значения отдельных параметров можно указать в более компактном виде (ldapurl): ldaps — аутентификация LDAP, в данном случае — с шифрованием SSL; course.local — адрес сервера (ldapserver); dc=course,dc=local — поддерево для поиска (ldapbasedn); uid — атрибут для поиска (ldapsearchattribute); sub — указание искать в указанном поддереве. Параметры ldapbinddn и ldapbindpasswd не указываем: в этом случае происходит анонимный запрос, который разрешен конфигурацией нашего LDAP-сервера.

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

# TYPE  DATABASE  USER     ADDRESS   METHOD
host    all       charlie  samehost  ldap
ldapurl="ldaps://course.local/dc=course,dc=local?uid?sub"

student$ sudo pg_ctlcluster 16 main reload

Пробуем.

student$ psql "host=$(hostname) user=charlie password=charlie dbname=student" -c "\conninfo"

You are connected to database "student" as user "charlie" on host "course.local" (address
"127.0.1.1") at port "5432".

student$ tail -3 /var/log/postgresql/postgresql-16-main.log

2026-07-30 16:35:47.150 MSK [58624] [unknown]@[unknown] LOG:  connection received:
host=127.0.0.1 port=41714
2026-07-30 16:35:47.171 MSK [58624] charlie@student LOG:  connection authenticated:
identity="uid=charlie,ou=ChemLab,dc=course,dc=local" method=ldap
(/etc/postgresql/16/main/pg_hba.conf.d/ext.conf:2)
2026-07-30 16:35:47.172 MSK [58624] charlie@student LOG:  connection authorized:
user=charlie database=student application_name=psql

Page 11: Аутентификация GSSAPI

Kerberos и GSSAPI

Настройка клиента
Настройка сервера
Аутентификация
Запрос сеансового ключа
Передача ключа сервису
Коммуникация с сервисом

Page 12: Kerberos и GSSAPI

Kerberos — протокол аутентификации

надежный обмен ключами и взаимная аутентификация
между клиентом и сервисом
симметричные ключи, доверенная третья сторона
разграничение доступа по областям (realm)
GSSAPI — универсальный интерфейс безопасности
- поддерживает разные протоколы, в том числе Kerberos

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

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

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

Kerberos рассчитан на большое количество пользователей и сервисов, и не каждому пользователю нужно разрешать подключаться к любому сервису. Для разграничения доступа существуют области (realm): пользователь может получать билет для сервиса, только если они входят в одну область. В нашем примере это область COURSE.LOCAL.

PostgreSQL работает с протоколом Kerberos не напрямую, а через универсальный прикладной интерфейс GSSAPI (Generic Security Services API), который, помимо Kerberos, поддерживает NTLM и другие протоколы.

Page 13: Настройки клиента

Параметр gssencmode

disable
не использовать шифрование GSSAPI
prefer
попытка использовать шифрование GSSAPI,
при неудаче — попытка соединиться без него
(по умолчанию)
require
потребовать шифрование GSSAPI
Параметр krbsrvname
- имя службы Kerberos для аутентификации

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

Аналогично тому, как при подключении клиент может попросить или потребовать SSL-соединение с помощью параметра sslmode библиотеки libpq, пожелания по использованию шифрования GSSAPI определяются значением параметра gssencmode.

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

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

https://postgrespro.ru/docs/postgresql/16/gssapi-enc

https://postgrespro.ru/docs/postgresql/16/libpq-connect#LIBPQ-CONNECT-GSSENCMODE

Также клиент может указать имя службы Kerberos, которая должна использоваться для аутентификации (по умолчанию — postgres).

https://postgrespro.ru/docs/postgresql/16/libpq-connect#LIBPQ-CONNECT-KRBSRVNAME

Page 14: Настройки сервера

Параметры

krb_server_keyfile
имя keytab-файла с ключом PostgreSQL
Типы сетевых подключений в pg_hba.conf
- host
- любое сетевое подключение
- hostgssenc
- подключение с шифрованием GSSAPI
- hostnogssenc
- подключение без шифрования GSSAPI
Метод аутентификации
- gss
- аутентификация с помощью GSSAPI
- sspi
- аутентификация SSPI для Windows

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

Для аутентификации с помощью Kerberos необходимо указать метод аутентификации gss. (В ОС Windows для работы с Kerberos используется собственный интерфейс SSPI и соответствующий ему метод аутентификации sspi, которые мы здесь не рассматриваем.)

В параметре krb_server_keyfile указывается расположение файла с ключом PostgreSQL. Такие файлы, содержащие ключ Kerberos определенного сервиса или клиента, называются keytab-файлами.

В качестве типа подключения при этом можно указать как простой тип host, так и явно разделить соединения на использующие шифрование GSSAPI (hostgssenc) и не использующие (hostnogssenc).

https://postgrespro.ru/docs/postgresql/16/gssapi-auth

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

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

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

Служба Kerberos хранит ключи всех пользователей и сервисов, которые заранее созданы администратором. На иллюстрации это ключ пользователя Алисы и ключ сервиса PostgreSQL. У Kerberos есть также свой ключ, не известный никому.

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

Клиент Алисы обращается к службе аутентификации Kerberos AS (Authentication Service). Если Алиса ввела правильный пароль, ключи совпадут. Тогда Kerberos генерирует временный ключ, предназначенный для сеанса общения клиента и самого Kerberos, и отправляет клиенту два сообщения:

● Временный ключ, зашифрованный ключом Алисы. Клиент сможет его расшифровать.

● Временный ключ (плюс дополнительная служебная информация), зашифрованный ключом Kerberos. Это сообщение называется TGT, Ticket Granting Ticket — билет на получение билетов. Клиент не может его расшифровать, но будет предъявлять его Kerberos в доказательство того, что пользователь уже прошел аутентификацию.

Page 16: Аутентификация в Kerberos

Kerberos установлен и настроен в виртуальной машине курса, учетные записи (principals) зарегистрированы.

student$ sudo kadmin.local list_principals | grep '^[abc]'

alice@COURSE.LOCAL
bob@COURSE.LOCAL
charlie@COURSE.LOCAL

alice@COURSE.LOCAL — Алиса хранит пароль в специальном файле alice.keytab. bob@COURSE.LOCAL — Боб — в файле bob.keytab. charlie@COURSE.LOCAL — Чарли не хранит пароль в файле и должен вводить его вручную. Вы можете использовать его для собственных экспериментов. Сначала Алисе нужно аутентифицироваться и получить билет, дающий возможность получать билеты сервисов (TGT, Ticket Granting Ticket). Аутентификацию в Kerberos можно пройти с помощью команды kinit. Она запрашивает у пользователя пароль, из которого генерируется ключ, подтверждающий право на получение TGT. Мы воспользуемся заранее подготовленным ключом Алисы (в виртуальной машине он находится в файле alice.keytab), чтобы не вводить пароль интерактивно:

student$ kinit -kt alice.keytab alice

В результате Kerberos сформировал TGT и сохранил его в кеше:

student$ klist

Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: alice@COURSE.LOCAL

Valid starting       Expires              Service principal
30.07.2026 16:35:47  31.07.2026 02:35:47  krbtgt/COURSE.LOCAL@COURSE.LOCAL
renew until 31.07.2026 16:35:47

Билет ограничен по времени действия (valid starting, expires/renew until); Термин principal означает учетную запись Kerberos: это может быть как клиент, так и сервис; COURSE.LOCAL — название области Kerberos, которая используется в этом примере.

Page 17: Запрос сеансового ключа

{#source-figure-017 caption=“Source slide page 17: Запрос сеансового ключа”}

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

Теперь Алиса хочет подключиться к PostgreSQL. Для этого ее клиент отправляет запрос на получение доступа к сервису, подтверждая аутентификацию Алисы с помощью полученного ранее TGT. Это позволяет Алисе не вводить пароль каждый раз при подключении к другим сервисам — то, что называется single sign-on.

Служба Kerberos TGS (Ticket Granting Service) генерирует сеансовый ключ, которым должны обладать как клиент, так и сервис. Клиенту снова передаются два сообщения:

● Сеансовый ключ, зашифрованный временным ключом (который уже есть у клиента).

● Сеансовый ключ, зашифрованный ключом сервиса (которого у клиента, конечно, нет). Это сообщение называется ST, Service Ticket — билет сервиса, и предназначено для сервиса.

Page 18: Передача ключа сервису

{#source-figure-018 caption=“Source slide page 18: Передача ключа сервису”}

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

Клиент Алисы подключается к PostgreSQL (который использует метод аутентификации gssapi) и предъявляет полученный от Kerberos билет. PostgreSQL расшифровывает его своим ключом, который хранится в keytab-файле. Факт того, что билет зашифрован ключом PostgreSQL, говорит о том, что он получен от Kerberos (больше никто не знает этот ключ), следовательно, пользователь прошел аутентификацию и ему можно доверять.

Из расшифрованного билета PostgreSQL извлекает сеансовый ключ для общения с клиентом.

Page 19: Коммуникация с сервисом

{#source-figure-019 caption=“Source slide page 19: Коммуникация с сервисом”}

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

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

Билет TGT остается у клиента и будет использован для запросов на подключения к другим сервисам, если они понадобятся пользователю.

Page 20: Подключение к PostgreSQL

Включим в дополнительный файл аутентификации правило, разрешающее Алисе аутентифицироваться в Kerberos:

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

# TYPE      DATABASE  USER   ADDRESS   METHOD
hostgssenc  student   alice  samehost  gss include_realm=0

Параметр include_realm=0 убирает из имени учетной записи пользователя название области Kerberos. Например, alice@COURSE.LOCAL преобразуется в alice, то есть в обычное имя учетной записи PostgreSQL. Перечитаем конфигурацию.

student$ sudo pg_ctlcluster 16 main reload

При подключении к PostgreSQL psql запрашивает у Kerberos билет сервиса и передает его PostgreSQL. Имя сервиса задается в строке подключения libpq в параметре krbsrvname; в нашем случае годится значение по умолчанию — postgres. Проверим, сможет ли Алиса начать защищенный сеанс:

student$ psql "host=$(hostname) user=alice dbname=student" -c "\conninfo"

You are connected to database "student" as user "alice" on host "course.local" (address
"127.0.1.1") at port "5432".
GSSAPI-encrypted connection

student$ tail -3 /var/log/postgresql/postgresql-16-main.log

2026-07-30 16:35:48.479 MSK [58922] [unknown]@[unknown] LOG:  connection received:
host=127.0.0.1 port=41730
2026-07-30 16:35:48.485 MSK [58922] alice@student LOG:  connection authenticated:
identity="alice@COURSE.LOCAL" method=gss
(/etc/postgresql/16/main/pg_hba.conf.d/ext.conf:2)
2026-07-30 16:35:48.485 MSK [58922] alice@student LOG:  connection authorized: user=alice
database=student application_name=psql GSS (authenticated=yes, encrypted=yes,
delegated_credentials=no, principal=alice@COURSE.LOCAL)

Теперь в кеше будет и билет сервиса PostgreSQL:

student$ klist

Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: alice@COURSE.LOCAL

Valid starting       Expires              Service principal
30.07.2026 16:35:47  31.07.2026 02:35:47  krbtgt/COURSE.LOCAL@COURSE.LOCAL
renew until 31.07.2026 16:35:47
30.07.2026 16:35:48  31.07.2026 02:35:47  postgres/course.local@
renew until 31.07.2026 16:35:47
Ticket server: postgres/course.local@COURSE.LOCAL

Кроме учетной записи Алисы, в Kerberos зарегистрирована и учетная запись Боба: bob@COURSE.LOCAL. В случае Алисы мы просто отбросили из имени учетной записи название области, пользуясь тем, что оставшаяся часть совпадает с именем роли в PostgreSQL. Можно использовать более универсальный подход: превращать имена учетных записей Kerberos в имена пользователей PostgreSQL с помощью сопоставлений имен, с которыми мы уже встречались в теме «Подключение и аутентификация». Заменим правило аутентификации для Алисы универсальным правилом с именем сопоставления krb:

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

# TYPE      DATABASE  USER   ADDRESS   METHOD
hostgssenc  student   all    samehost  gss map=krb

Теперь зададим сопоставление, которое отбрасывает имя области с помощью регулярного выражения:

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

krb /^(.+)@ \1

Перечитаем конфигурацию:

student$ sudo pg_ctlcluster 16 main reload

Page 21: Page 21

Аутентифицируем Боба в Kerberos:

student$ kinit -kt bob.keytab bob

Боб начинает сеанс:

student$ psql "host=$(hostname) user=bob dbname=student" -c "\conninfo"

You are connected to database "student" as user "bob" on host "course.local" (address
"127.0.1.1") at port "5432".
GSSAPI-encrypted connection

student$ tail -3 /var/log/postgresql/postgresql-16-main.log

2026-07-30 16:35:49.624 MSK [59170] [unknown]@[unknown] LOG:  connection received:
host=127.0.0.1 port=41746
2026-07-30 16:35:49.630 MSK [59170] bob@student LOG:  connection authenticated:
identity="bob@COURSE.LOCAL" method=gss (/etc/postgresql/16/main/pg_hba.conf.d/ext.conf:2)
2026-07-30 16:35:49.631 MSK [59170] bob@student LOG:  connection authorized: user=bob
database=student application_name=psql GSS (authenticated=yes, encrypted=yes,
delegated_credentials=no, principal=bob@COURSE.LOCAL)

Билеты, полученные Бобом:

student$ klist

Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: bob@COURSE.LOCAL

Valid starting       Expires              Service principal
30.07.2026 16:35:49  31.07.2026 02:35:49  krbtgt/COURSE.LOCAL@COURSE.LOCAL
renew until 31.07.2026 16:35:49
30.07.2026 16:35:49  31.07.2026 02:35:49  postgres/course.local@
renew until 31.07.2026 16:35:49
Ticket server: postgres/course.local@COURSE.LOCAL

Page 22: Аутентификация PAM

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

pam [параметры]

проверяет пары «имя пользователя — пароль»

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

Система PAM (Pluggable Authentication Modules — подключаемые модули аутентификации) является стандартной инфраструктурой в Linux и многих UNIX системах. Модули этой системы представляют собой разделяемые библиотеки, реализующие четыре сервиса PAM:

● библиотеки аутентификации (Authentication Service Modules), посредством которых выполняется проверка подлинности;

● библиотеки учетных записей (Account Service Modules), выполняющих такие задачи, как проверка устаревания учетных записей;

● библиотеки сеансов (Session Service Modules), отвечающие за действия, выполняемые при входе в сеанс;

● библиотеки управления паролями (Password Service Modules).

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

В PostgreSQL аутентификация PAM работает подобно методу password, используя пары «имя пользователя — пароль». Сам же механизм аутентификации может быть реализован, например, с помощью LDAP, к которому будет обращаться модуль PAM.

Page 23: Итоги

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

Метод ldap запрашивает у клиента пароль и проверяет его с помощью службы каталогов
Методы gss и sspi аутентифицируют соединения через протокол Kerberos
Метод pam позволяет использовать для аутентификации внешнюю библиотеку

Page 24: Практика

Настройте аутентификацию ldap по методу «поиск + связывание», как в демонстрации. Поддеревом для поиска укажите ou=BioLab. Проверьте, что Алиса и Боб могут подключиться к серверу, а Чарли — нет.

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

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

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

Page 25: 1. Аутентификация LDAP

Создаем роли:

student=# CREATE ROLE alice LOGIN;

CREATE ROLE

student=# CREATE ROLE bob LOGIN;

CREATE ROLE

student=# CREATE ROLE charlie LOGIN;

CREATE ROLE

Попросим PostgreSQL принимать подключения со всех сетевых интерфейсов:

student=# ALTER SYSTEM SET listen_addresses = '*';

ALTER SYSTEM

student=# ALTER SYSTEM SET log_connections = on;

ALTER SYSTEM

Настройка аутентификации:

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

# TYPE  DATABASE  USER  ADDRESS   METHOD
host    all       all   all       ldap ldapserver=course.local
ldapbasedn="ou=BioLab,dc=course,dc=local"

student$ sudo pg_ctlcluster 16 main restart

student$ psql student

Пробуем.

student$ psql "host=$(hostname) user=alice password=alice dbname=student" -c "\conninfo"

You are connected to database "student" as user "alice" on host "course.local" (address
"127.0.1.1") at port "5432".

student$ psql "host=$(hostname) user=bob password=bob dbname=student" -c "\conninfo"

You are connected to database "student" as user "bob" on host "course.local" (address
"127.0.1.1") at port "5432".

Алиса и Боб подключаются. А Чарли — нет:

student$ psql "host=$(hostname) user=charlie password=charlie dbname=student" -c "\conninfo"

psql: error: connection to server at "course.local" (127.0.1.1), port 5432 failed: SSL
error: certificate verify failed
connection to server at "course.local" (127.0.1.1), port 5432 failed: FATAL:  LDAP
authentication failed for user "charlie"

2. Шифрование GSSAPI

Первым правилом запрещаем все соединения без шифрования GSSAPI. Второе правило аналогично правилу из предыдущего задания:

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

# TYPE        DATABASE  USER   ADDRESS   METHOD
hostnogssenc  all       all    all       reject
host          all       all    all       ldap ldapserver=course.local
ldapbasedn="ou=BioLab,dc=course,dc=local"

student=# SELECT pg_reload_conf();

Page 26: Page 26

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

Пробуем подключиться:

student$ psql "host=$(hostname) user=alice password=alice dbname=student" -c "\conninfo"

psql: error: connection to server at "course.local" (127.0.1.1), port 5432 failed: SSL
error: certificate verify failed
connection to server at "course.local" (127.0.1.1), port 5432 failed: FATAL:  pg_hba.conf
rejects connection for host "127.0.0.1", user "alice", database "student", no encryption

Что произошло: Утилита psql не пытается установить соединение с шифрованием GSSAPI: Алиса не аутентифицирована в Керберос, у нее нет билета на подключение к сервисам. Сначала psql попытался установить SSL-соединение (параметр sslmode по умолчанию равен prefer). Затем psql попытался установить открытое соединение. Ни одна попытка не увенчалась успехом, поскольку сервер принимает только соединение с шифрованием GSSAPI. Теперь Алиса получает билет на соединение с сервисами:

student$ kinit -kt alice.keytab alice

На этот раз подключение срабатывает: параметр gssencmode по умолчанию равен prefer и у Алисы есть билет.

student$ psql "host=$(hostname) user=alice password=alice dbname=student" -c "\conninfo"

You are connected to database "student" as user "alice" on host "course.local" (address
"127.0.1.1") at port "5432".
GSSAPI-encrypted connection

В журнале сообщений видно, что для аутентификации использовался LDAP.

student$ tail -2 /var/log/postgresql/postgresql-16-main.log

2026-07-30 16:40:26.302 MSK [74724] alice@student LOG:  connection authenticated:
identity="uid=alice,ou=BioLab,dc=course,dc=local" method=ldap
(/etc/postgresql/16/main/pg_hba.conf.d/ext.conf:3)
2026-07-30 16:40:26.302 MSK [74724] alice@student LOG:  connection authorized: user=alice
database=student application_name=psql GSS (authenticated=no, encrypted=yes,
delegated_credentials=no)

Уборка

Очистим кеш Керберос:

student$ kdestroy

student$ sudo rm /etc/postgresql/16/main/pg_hba.conf.d/*

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, charlie;

DROP ROLE

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

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

1. Настройте журналирование подключений PostgreSQL. 2. Зарегистрируйте средствами операционной системы пользователя pamuser и назначьте ему пароль. Зарегистрируйте одноименную роль pamuser в СУБД. 3. В pg_hba.conf настройте для pamuser аутентификацию PAM с помощью сервиса login. 4. Обеспечьте доступ на чтение файла /etc/shadow пользователю postgres, добавив его в группу shadow. 5. Перезапустите СУБД и проверьте возможность подключения пользователем pamuser.

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

Для регистрации пользователя в операционной системе используйте команду: sudo useradd pamuser

Назначить пароль можно следующей командой (предложит ввести пароль и подтвердить его): sudo passwd pamuser

Также пароль для пользователя можно установить так: echo ‘pamuser:пароль’ | sudo chpasswd

Вариант настройки аутентификации PAM для сервиса login:

# TYPE DATABASE USER    ADDRESS  METHOD
local  all      pamuser          pam pamservice=login

Для предоставления доступа на чтение файла /etc/shadow для пользователя postgres, добавьте его в группу shadow: sudo gpasswd -a postgres shadow

После выполнения задания удалите postgres из группы: sudo gpasswd -d postgres shadow

Доступ пользователей к /etc/shadow не рекомендуется в реальной эксплуатации по соображениям безопасности.

Page 28: 1. Журналирование подключений

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

student=# ALTER SYSTEM SET log_connections TO on;

ALTER SYSTEM

2. Регистрация пользователя

Создадим в PostgreSQL роль pamuser и зарегистрируем одноименного пользователя операционной системы.

student=# CREATE ROLE pamuser LOGIN;

CREATE ROLE

student$ sudo useradd pamuser

Установим пароль пользователю pamuser, совпадающий с его именем:

student$ echo 'pamuser:pamuser' | sudo chpasswd

Проверим, что пароль установлен:

student$ sudo passwd -S pamuser

pamuser P 2026-07-30 0 99999 7 -1

Символ P в выводе означает, что пароль установлен и не заблокирован.

3. Настройка аутентификации

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

# TYPE  DATABASE  USER    ADDRESS  METHOD
local   all       pamuser          pam pamservice=login

4. Права доступа для пользователя ОС postgres

Проверим права доступа на /etc/shadow

student$ ls -l /etc/shadow

-rw-r----- 1 root shadow 1286 июл 30 16:40 /etc/shadow

Пользователи операционной системы, входящие в группу shadow, могут читать файл. В этом файле находятся зашифрованные пароли, необходимые PostgreSQL для локальной аутентификации PAM. Добавим пользователя ОС postgres в группу shadow:

student$ sudo gpasswd -a postgres shadow

Adding user postgres to group shadow

student$ id postgres

uid=116(postgres) gid=124(postgres) groups=124(postgres),42(shadow),105(ssl-cert)

postgres$ grep pamuser /etc/shadow

pamuser:$y$j9T$miZzcSUuy/Ra5ogTFGGvO/$nidLLgPMXeG3NMStu1HHny.JYh.d5Ngsn0EIdhjZzSA:20664:0:
99999:7:::

5. Перезапуск СУБД и подключение

Перезапустим экземпляр.

student$ sudo pg_ctlcluster 16 main restart

student$ psql student

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

student$ psql "user=pamuser password=pamuser dbname=student" -c "\conninfo"

Page 29: Page 29

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

student$ tail -3 /var/log/postgresql/postgresql-16-main.log

2026-07-30 16:40:39.541 MSK [75516] [unknown]@[unknown] LOG:  connection received:
host=[local]
2026-07-30 16:40:39.614 MSK [75516] pamuser@student LOG:  connection authenticated:
identity="pamuser" method=pam (/etc/postgresql/16/main/pg_hba.conf.d/ext.conf:2)
2026-07-30 16:40:39.614 MSK [75516] pamuser@student LOG:  connection authorized:
user=pamuser database=student application_name=psql

Уборка

Выведем пользователя ОС postgres из группы shadow и удалим пользователя pamuser:

student$ sudo gpasswd -d postgres shadow

Removing user postgres from group shadow

student$ sudo userdel pamuser

student$ sudo rm /etc/postgresql/16/main/pg_hba.conf.d/*

student$ sudo pg_ctlcluster 16 main restart

student=# \c student

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

student=# DROP ROLE pamuser;

DROP ROLE