Перейти к содержимому

04 Занятие 04: Управление доступом / Подходы к настройке

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

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

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

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

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

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

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

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

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

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

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

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;
  • bob

ADMIN

  • alice

CREATEROLE

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

Если роль имеет атрибут CREATEROLE, она может создавать и удалять другие роли, а также изменять их атрибуты. Созданная роль автоматически включает в себя создавшую ее роль с параметром членства ADMIN. Таким образом, роль с атрибутом CREATEROLE может управлять членством в созданных ею ролях.

Чтобы создать роль с одним из атрибутов SUPERUSER, CREATEDB, REPLICATION или BYPASSRLS, создающая роль должна сама обладать соответствующим атрибутом.

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

Page 9: Примеры настройки

Личные схемы

  • Групповой доступ
  • Группа-владелец
  • Внешние пользователи

Page 10: Личные схемы

Source slide 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 slide 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 slide 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 slide 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 slide page 20: Внешние пользователи{#source-figure-020 caption=“Source slide page 20: Внешние пользователи”}

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

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

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

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

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

Page 21: Внешние пользователи

Source slide 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 slide 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