# Занятие 19: Управление доступом / Обзор > Исходная страница в нативном Markdown: Управление доступом / Обзор --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [dev1_19_access_overview.html](https://edu.postgrespro.ru/16/dev1-16/dev1_19_access_overview.html) > **Archive:** [DEV1-handouts-16.zip](https://edu.postgrespro.ru/16/DEV1-handouts-16.zip) > **Страниц PDF:** 30 ## Page 1: Управление доступом / Обзор {#source-page-001} ### Авторские права © Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Роли и атрибуты - Подключение к серверу - Парольная аутентификация - Привилегии и управление ими - Категории ролей - Групповые и предопределенные роли - Привилегии по умолчанию - Привилегии и подпрограммы ## Page 3: Роли и атрибуты {#source-page-003} **Роль может быть пользователем СУБД** - не связана с пользователем ОС - Роли можно включать друг в друга - удобно при настройке доступа - Свойства роли определяются атрибутами - LOGIN - возможность подключения - SUPERUSER - суперпользователь - CREATEDB - возможность создавать базы данных - CREATEROLE - возможность создавать роли - и другие ### Комментарии преподавателя Роли в PostgreSQL используют для двух целей. Во-первых, роль может выступать как пользователь, подключающийся к СУБД. Во-вторых, роли можно включать друг в друга — это удобно при настройке доступа. Формально роли никак не связаны с пользователями операционной системы, хотя многие программы это предполагают, выбирая значения по умолчанию. Например, psql, запущенный от имени пользователя ОС student, автоматически использует одноименную роль student (если в ключах утилиты не указана какая-либо другая роль). При создании кластера определяется одна начальная роль, имеющая суперпользовательский доступ (обычно она называется postgres). В дальнейшем роли можно создавать, изменять и удалять. Роль обладает некоторыми атрибутами, определяющими ее общие особенности и права (не связанные с определенными объектами). Обычно атрибуты имеют два варианта, например, CREATEDB (дает право на создание БД) и NOCREATEDB (не дает такого права). Если у роли есть атрибут LOGIN, эта роль — пользователь. Если NOLOGIN — роль не является пользовательской и не сможет подключиться к серверу; такие роли обычно используют для включения других ролей. В таблице перечислены лишь некоторые из атрибутов. ## Page 4: Роли и атрибуты {#source-page-004} Создадим роль для пользователя Алисы. В команде указаны два атрибута. В этой теме нам важно, от имени какой роли выполняются команды, поэтому имя текущей роли вынесено в приглашение. ```sql student=# CREATE ROLE alice LOGIN PASSWORD 'alice'; ``` ```sql CREATE ROLE ``` Список ролей можно узнать командой: ```text student=# \du ``` ```sql List of roles Role name | Attributes -----------+------------------------------------------------------------ alice | postgres | Superuser, Create role, Create DB, Replication, Bypass RLS student | Superuser ``` Обратите внимание, что роль student является суперпользователем. Поэтому до сих пор мы не задумывались о разграничении доступа. Создадим и базу данных: ```sql student=# CREATE DATABASE access_overview; ``` ```sql CREATE DATABASE ``` ```sql student=# \c access_overview ``` ```text You are now connected to database "access_overview" as user "student". ``` ## Page 5: Подключение к серверу {#source-page-005} **1. Строки pg\\\_hba.conf просматриваются сверху вниз** - 2. Выбирается первая запись, соответствующая параметрам подключения (тип, база, пользователь и адрес) ```text # TYPE DATABASE USER ADDRESS METHOD local all postgres peer local all all peer host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256 ``` - local — сокет all — любая роль host — TCP/IP имя роли - listen\_addresses - all — любая БД - all — любой IP - имя БД - IP/маска - доменное имя ### Комментарии преподавателя Для каждого нового клиента сервер определяет, надо ли разрешить подключение к базе данных. Настройки подключения определяются в конфигурационном файле pg\_hba.conf («host-based authentication»). Как и с основным конфигурационным файлом postgresql.conf, изменения вступают в силу только после перечитывания файла сервером (SQL-функцией pg\_reload\_conf() или командой reload утилиты управления). При появлении нового клиента сервер просматривает конфигурацион-ный файл сверху вниз в поисках строки, подходящей к запрашиваемо-му клиентом подключению. Соответствие определяется по четырем полям: типу подключения, имени БД, имени пользователя и IP-адресу. Ниже перечислены только самые основные возможности. Подключение — local (unix-сокеты) или host (подключение по протоколу TCP/IP). База данных – ключевое слово all (соответствует любой БД) или имя конкретной базы данных. Пользователь — all или имя конкретной роли. Адрес — all, диапазон IP-адресов или доменное имя. Не указывается для типа local. По умолчанию PostgreSQL слушает входящие соединения только с localhost; обычно параметр listen\_addresses ставят в значение «\*» (слушать все интерфейсы) и дальше регулируют доступ средствами pg\_hba.conf. ## Page 6: Подключение к серверу {#source-page-006} **3. Выполняется аутентификация указанным методом** - 4. Удачно — доступ разрешается, иначе — запрещается (если не подошла ни одна запись — доступ запрещается) ```text # TYPE DATABASE USER ADDRESS METHOD local all postgres peer local all all peer host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256 ``` - trust — разрешить - reject — отказать - scram-sha-256 и md5 — запросить пароль - peer — спросить ОС ### Комментарии преподавателя Когда в файле найдена подходящая строка, выполняется аутентификация указанным в этой строке методом, а также проверяется наличие атрибута LOGIN и привилегии CONNECT. Если результат проверки успешен, то подключение разрешается, иначе — запрещается (другие строки файла при этом уже не рассматриваются). Если ни одна из строк не подошла, то доступ также запрещается. Таким образом, записи в файле должны идти сверху вниз от частных правил к общим. Существует множество методов аутентификации: Ниже перечислены только самые основные. Метод trust безусловно разрешает подключение. Если вопросы безопасности не важны, можно указать «все all» и метод trust — тогда будут разрешены все подключения. Метод reject, наоборот, безусловно запрещает подключение. Метод scram-sha-256 запрашивает у пользователя пароль и проверяет его соответствие хешу, который хранится в системном каталоге кластера. Парольный метод md5 также используется, но объявлен устаревшим. Метод peer запрашивает имя пользователя у операционной системы и разрешает подключение, если имя пользователя ОС и пользователя БД совпадают (можно установить и другие соответствия имен). ## Page 7: Парольная аутентификация {#source-page-007} **На сервере** - пароль устанавливается при создании роли или позже - пользователю без пароля будет отказано в доступе - пароль хранится в системном каталоге pg\_authid - Ввод пароля на клиенте - вручную - из переменной окружения PGPASSWORD - из файла ~/.pgpass (строки в формате узел:порт:база:роль:пароль) ### Комментарии преподавателя Если используется аутентификация по паролю, для роли обязательно должен быть установлен пароль, иначе в доступе будет отказано. Хеш-код пароля хранится в системном каталоге в таблице pg\_authid. Пользователь может вводить пароль вручную, а может автоматизировать его ввод. Для этого есть две возможности. Во-первых, можно задать пароль в переменной окружения PGPASSWORD на клиенте. Однако это неудобно, если приходится подключаться к нескольким базам, а также не рекомендуется из соображений безопасности. Во-вторых, можно задать пароли в файле ~/.pgpass на клиенте. К файлу должен иметь доступ только его владелец (установлены права доступа 600), иначе PostgreSQL проигнорирует его. ## Page 8: Подключение {#source-page-008} Чтобы роль смогла подключиться к базе данных, она должна иметь не только атрибут LOGIN, но и разрешение в файле pg\_hba.conf. Располагается он обычно рядом с основным конфигурационным файлом: ```text student=# SHOW hba_file; ``` ```text hba_file ------------------------------------- /etc/postgresql/16/main/pg_hba.conf (1 row) ``` А прочитать его содержимое можно прямо из SQL: ```sql student=# SELECT type, database, user_name, address, auth_method FROM pg_hba_file_rules(); ``` ```text type | database | user_name | address | auth_method -------+---------------+-----------+-----------+--------------- local | {all} | {all} | | trust host | {all} | {all} | 127.0.0.1 | scram-sha-256 host | {all} | {all} | ::1 | scram-sha-256 local | {replication} | {all} | | trust host | {replication} | {all} | 127.0.0.1 | scram-sha-256 host | {replication} | {all} | ::1 | scram-sha-256 (6 rows) ``` (В зависимости от сборки сервера содержимое файла может отличаться.) Мы будем использовать подключение к localhost по TCP/IP (host). Такому подключению соответствует вторая строка выборки. Она предполагает аутентификацию по паролю. Роль alice была создана с паролем, но вообще его можно изменить в любой момент: ```sql student=# ALTER ROLE alice PASSWORD 'alicepass'; ``` ```sql ALTER ROLE ``` Попробуем установить соединение, указав в строке подключения всю необходимую информацию: ```sh student$ psql 'host=localhost user=alice dbname=access_overview password=alicepass' ``` ```sql alice=> \conninfo ``` ```text You are connected to database "access_overview" 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) ``` Получилось! ## Page 9: Привилегии {#source-page-009} **Привилегии определяют права доступа ролей к объектам** - Таблицы и представления - SELECT - чтение данных - INSERT - вставка строк - можно на уровне столбцов - UPDATE - изменение строк - REFERENCES - внешний ключ (для таблиц) - DELETE - удаление строк - TRUNCATE - опустошение (для таблиц) - TRIGGER - создание триггеров ### Комментарии преподавателя Привилегии устанавливают связь между субъектами (ролями) и объектами кластера. Они определяют действия, доступные для ролей в отношении этих объектов. Список возможных привилегий отличается для объектов различных типов. Привилегии для основных типов объектов приведены на этом и следующем слайдах. Больше всего привилегий определено для таблиц и представлений. Часть из них можно задать не только для всего отношения, но и для отдельных столбцов. ## Page 10: Привилегии {#source-page-010} ![Source slide page 10: Привилегии](images/page-010.png){#source-figure-010 caption="Source slide page 10: Привилегии"} **Табличные пространства, базы данных, схемы** - Последовательности - SELECT - currval - UPDATE - nextval - setval - USAGE - currval - nextval ### Комментарии преподавателя Возможно, несколько неожиданный набор привилегий имеют последовательности. Выбирая нужные, можно разрешить или запретить доступ к трем управляющим функциям. Для табличных пространств есть привилегия CREATE, разрешающая создание объектов в этом пространстве. Для баз данных привилегия CREATE разрешает создавать схемы в этой БД, а для схемы привилегия CREATE разрешает создавать объекты в этой схеме. Поскольку точное имя схемы для временных объектов заранее неизвестно, привилегия на создание временных таблиц вынесена на уровень БД (TEMPORARY). Привилегия USAGE схемы разрешает обращаться к объектам в этой схеме. Привилегия CONNECT базы данных разрешает подключение к этой БД. ## Page 11: Категории ролей {#source-page-011} **Суперпользователи** - полный доступ ко всем объектам — проверки не выполняются - Владельцы - изначально все привилегии для объекта (можно отозвать) - действия со своими объектами, не регламентируемые привилегиями, - например: удаление, выдача и отзыв привилегий и т. п. - Остальные роли - доступ в рамках выданных привилегий ### Комментарии преподавателя В целом можно сказать, что доступ роли к объекту определяется привилегиями. Но имеет смысл выделить три категории ролей и рассмотреть их по отдельности: 1. Роли с атрибутом SUPERUSER (суперпользователи). Такие роли могут делать все, что угодно — для них проверки разграничения доступа не выполняются. 2. Владелец объекта. Изначально это роль, создавшая объект, хотя потом его можно сменить. Владельцем считается не только сама роль-владелец, но и любая другая роль, включенная в нее. Владелец объекта сразу получает полный набор привилегий для этого объекта. В принципе, эти привилегии можно отозвать, но владелец объекта обладает также неотъемлемым правом совершать действия, не регламентируемые привилегиями. В частности, он может выдавать и отзывать привилегии (в том числе и себе самому), удалять объект и т. п. 3. Все остальные роли имеют доступ к объекту только в рамках выданных им привилегий. Чтобы проверить, есть ли у роли необходимая привилегия в отношении некоторого объекта, можно воспользоваться функциями has\_\*\_privilege: ## Page 12: Управление привилегиями {#source-page-012} **Выдача привилегий** ```text alice: GRANT привилегии ON объект TO bob; ``` - alice - bob - привилегии - на объект - Отзыв привилегий ```sql alice: REVOKE привилегии ON объект FROM bob; ``` ### Комментарии преподавателя Право выдачи и отзыва привилегий на объект имеет владелец этого объекта (и суперпользователь). Синтаксис команд GRANT и REVOKE достаточно сложен и позволяет указывать как отдельные, так и все возможные привилегии; как отдельные объекты, так и группы объектов, входящие в определенные схемы и т. п. ## Page 13: Привилегии {#source-page-013} ![Source slide page 13: Привилегии](images/page-013.png){#source-figure-013 caption="Source slide page 13: Привилегии"} Алиса смогла подключиться к базе данных. Теперь она хочет создать для себя схему и несколько объектов в ней. ```sql alice=> CREATE SCHEMA alice; ``` ```text ERROR: permission denied for database access_overview ``` В чем проблема? У Алисы нет привилегии для создания схем в БД. Выдадим ее: ```sql student=# GRANT CREATE ON DATABASE access_overview TO alice; ``` ```text GRANT ``` Пробуем еще раз: ```sql alice=> CREATE SCHEMA alice; ``` ```sql CREATE SCHEMA ``` Теперь, поскольку Алиса является владельцем своей схемы, она имеет все привилегии на нее и может создавать в ней любые объекты. По умолчанию будет использоваться именно эта схема: ```sql alice=> SELECT current_schemas(false); ``` ```text current_schemas ----------------- {alice,public} (1 row) ``` Алиса создает две таблицы. ```sql alice=> CREATE TABLE t1(n numeric); ``` ```sql CREATE TABLE ``` ```sql alice=> INSERT INTO t1 VALUES (1); ``` ```sql INSERT 0 1 ``` ```sql alice=> CREATE TABLE t2(n numeric, who text DEFAULT current_user); ``` ```sql CREATE TABLE ``` ```sql alice=> INSERT INTO t2(n) VALUES (1); ``` ```sql INSERT 0 1 ``` А суперпользователь создает другую роль для пользователя Боба, который будет обращаться к объектам, принадлежащим Алисе. ```sql student=# CREATE ROLE bob LOGIN PASSWORD 'bobpass'; ``` ```sql CREATE ROLE ``` ```sh student$ psql 'host=localhost user=bob dbname=access_overview password=bobpass' ``` Боб попробует обратиться к таблице t1. ```sql bob=> SELECT * FROM alice.t1; ``` ```sql ERROR: permission denied for schema alice LINE 1: SELECT * FROM alice.t1; ^ ``` В чем причина ошибки? Нет доступа к схеме, так как Боб не суперпользователь и не владелец этой схемы. Проверить права на доступ к схеме можно так (столбец Access privileges): ```text alice=> \x \dn+ \x ``` ## Page 14: Page 14 {#source-page-014} ![Source slide page 14: Page 14](images/page-014.png){#source-figure-014 caption="Source slide page 14: Page 14"} ```text Expanded display is on. List of schemas -[ RECORD 1 ]-----+--------------------------------------- Name | alice Owner | alice Access privileges | Description | -[ RECORD 2 ]-----+--------------------------------------- Name | public Owner | pg_database_owner Access privileges | pg_database_owner=UC/pg_database_owner+ | =U/pg_database_owner Description | standard public schema ``` ```text Expanded display is off. ``` Привилегии отображаются в формате: роль=привилегии/кем\_предоставлены. Каждая привилегия кодируется одним символом, в частности для схем: U = usage; C = create. Если имя роли опущено (как в последней строке), то имеется в виду псевдороль public. Обратите внимание, что на схему public у псевдороли public есть лишь привилегия usage. Здесь pg\_database\_owner соответствует владельцу базы данных. Если же опущено все поле (как в первой строке), то имеются в виду привилегии по умолчанию: alice имеет обе доступные привилегии на собственную схему, а остальные роли не имеют никаких. Предоставим доступ к схеме для Боба. Это может сделать Алиса как владелец. ```sql alice=> GRANT CREATE, USAGE ON SCHEMA alice TO bob; ``` ```text GRANT ``` Боб снова пробует обратиться к таблице: ```sql bob=> SELECT * FROM alice.t1; ``` ```text ERROR: permission denied for table t1 ``` Снова ошибка. В чем причина? На этот раз у Боба есть доступ к схеме, но нет доступа к самой таблице. Вот как проверить доступ: ```text alice=> \dp alice.t1 ``` ```text Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+------+-------+-------------------+-------------------+---------- alice | t1 | table | | | (1 row) ``` И снова видим пустое поле: доступ есть только у владельца, то есть у Алисы. Алиса предоставляет Бобу доступ на чтение и изменение: ```sql alice=> GRANT SELECT, UPDATE ON alice.t1 TO bob; ``` ```text GRANT ``` А для второй таблицы — доступ на вставку и чтение одного столбца: ```sql alice=> GRANT SELECT(n), INSERT ON alice.t2 TO bob; ``` ```text GRANT ``` Посмотрим, как изменились привилегии: ```text alice=> \dp alice.* ``` ```text Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+------+-------+---------------------+-------------------+---------- alice | t1 | table | alice=arwdDxt/alice+| | | | | bob=rw/alice | | alice | t2 | table | alice=arwdDxt/alice+| n: +| | | | bob=a/alice | bob=r/alice | (2 rows) ``` Теперь пустое поле «проявилось», и мы видим, что в нем находится полный перечень привилегий. Ниже — обозначения, ## Page 15: Page 15 {#source-page-015} ![Source slide page 15: Page 15](images/page-015.png){#source-figure-015 caption="Source slide page 15: Page 15"} не все они вполне очевидные: a = insert r = select w = update d = delete D = truncate x = reference t = trigger Привилегии для столбцов отображаются отдельно (столбец Column privileges). На этот раз попытки Боба увенчаются успехом. Чтобы не указывать каждый раз имя схемы, Боб добавляет его к своему пути поиска. ```sql bob=> ALTER ROLE bob SET search_path = public, alice; ``` ```sql ALTER ROLE ``` Теперь путь поиска будет устанавливаться в каждом сеансе Боба. ```sql bob=> \c ``` ```text You are now connected to database "access_overview" as user "bob". ``` ```text bob=> SHOW search_path; ``` ```text search_path --------------- public, alice (1 row) ``` ```sql bob=> UPDATE t1 SET n = n + 1; ``` ```sql UPDATE 1 ``` ```sql bob=> SELECT * FROM t1; ``` ```text n --- 2 (1 row) ``` Но другие операции по-прежнему запрещены: ```sql bob=> DELETE FROM t1; ``` ```text ERROR: permission denied for table t1 ``` И вторая таблица: ```sql bob=> INSERT INTO t2(n) VALUES (100); ``` ```sql INSERT 0 1 ``` ```sql bob=> SELECT n FROM t2; ``` ```text n ----- 1 100 (2 rows) ``` А чтение другого столбца запрещено: ```sql bob=> SELECT * FROM t2; ``` ```text ERROR: permission denied for table t2 ``` ## Page 16: Включение роли в роль {#source-page-016} ![Source slide page 16: Включение роли в роль](images/page-016.png){#source-figure-016 caption="Source slide page 16: Включение роли в роль"} **Включение в роль** ```text alice: GRANT dba TO bob; ``` - alice - bob - dba - псевдороль public неявно включает - в себя все остальные роли - Исключение из роли ```sql alice: REVOKE dba FROM bob; ``` ### Комментарии преподавателя В любую роль могут быть включены другие роли. В таком случае роль выступает в качестве группы. Отдельного понятия «группа» в PostgreSQL нет. Роль может быть включена в несколько ролей; включенная роль может, в свою очередь, включать в себя третьи роли, но циклы при этом не допускаются. По умолчанию роль наследует привилегии роли, в которую она включена. Это поведение можно изменить, указав роли атрибут NOINHERIT — тогда, чтобы воспользоваться привилегиями включающей роли, надо будет явно переключиться на нее с помощью SET ROLE. Атрибуты ролей не наследуются, но можно переключиться на включающую роль и воспользоваться ее атрибутами. Роли, включающие другие роли, обычно имеют атрибут NOLOGIN и называются «групповыми». Фактически это именованный набор привилегий, который удобно «выдать» обычной роли точно так же, как выдается одиночная привилегия. Это упрощает управление доступом и администрирование. Существует псевдороль public, которая неявно включает в себя все остальные роли. Если выдать какие-либо привилегии роли public, эти привилегии получат вообще все роли. ## Page 17: Предопределенные роли {#source-page-017} pg\_monitor - pg\_read\_all\_settings чтение всех параметров сервера - pg\_read\_all\_stats доступ к статистике pg\_stat\_scan\_tables мониторинг и блокировки таблиц - pg\_read\_all\_data чтение данных из всех таблиц pg\_write\_all\_data изменение данных во всех таблицах - pg\_read\_server\_files чтение файлов на сервере pg\_write\_server\_files запись в файлы на сервере - pg\_execute\_server\_programs выполнение программ на сервере - ... ### Комментарии преподавателя В PostgreSQL имеется набор предопределенных ролей, имеющих доступ к часто востребованным, но не общедоступным функциям и данным. Членство в этих ролях может быть предоставлено обычным пользователям для решения ряда задач администрирования — чтобы избежать наделения этих пользователей суперпользовательскими полномочиями. Количество предопределенных ролей увеличивается с каждой новой версией PostgreSQL. Полный список всех ролей, включая предопределенные, можно просмотреть с помощью команды \\duS в psql. Можно создавать и свои собственные «административные» роли, например, для выполнения задач резервного копирования. ## Page 18: Групповые роли {#source-page-018} ![Source slide page 18: Групповые роли](images/page-018.png){#source-figure-018 caption="Source slide page 18: Групповые роли"} Вспомним, что Боб не смог прочитать второй столбец таблицы: ```sql bob=> SELECT * FROM t2; ``` ```text ERROR: permission denied for table t2 ``` Суперпользователь включает Боба в предопределенную роль pg\_read\_all\_data: ```text student=# GRANT pg_read_all_data TO bob; ``` ```text GRANT ROLE ``` Теперь Боб сможет получить доступ на чтение ко всем таблицам так, как будто ему были выданы привилегии SELECT на все таблицы и USAGE на все схемы: ```sql bob=> SELECT * FROM t2; ``` ```text n | who -----+------- 1 | alice 100 | bob (2 rows) ``` Получить информацию о членстве в ролях можно с помощью команды \\drg утилиты psql: ```text student=# \drg ``` ```text List of role grants Role name | Member of | Options | Grantor -----------+------------------+--------------+---------- bob | pg_read_all_data | INHERIT, SET | postgres (1 row) ``` Столбец Options содержит информацию об атрибутах, описывающих включение. Здесь для роли bob, включенной в pg\_read\_all\_data, SET означает возможность переключения на групповую роль, а INHERIT — использование привилегий группы без явного переключения на нее. Исключим пользователя bob из группы: ```sql student=# REVOKE pg_read_all_data FROM bob; ``` ```text REVOKE ROLE ``` ## Page 19: Подпрограммы {#source-page-019} **Единственная привилегия для функций и процедур** - EXECUTE - выполнение - Характеристики безопасности - SECURITY INVOKER - выполняется с правами вызывающего - (по умолчанию) - SECURITY DEFINER - выполняется с правами владельца ### Комментарии преподавателя Для функций и процедур есть единственная привилегия EXECUTE, разрешающая выполнение этой подпрограммы. Тонкий момент связан с тем, от имени какого пользователя будет выполняться подпрограмма. Если подпрограмма объявлена как SECURITY INVOKER (по умолчанию), она выполняется с правами вызывающего пользователя. В этом случае операторы внутри подпрограммы смогут обращаться только к тем объектам, к которым у вызывающего пользователя есть доступ. Если же указать фразу SECURITY DEFINER, подпрограмма работает с правами ее владельца. Это способ позволить другим пользователям выполнять определенные действия над объектами, к которым у них нет непосредственного доступа. ## Page 20: Привилегии по умолчанию {#source-page-020} **Привилегии псевдороли public** - подключение к любой базе данных - доступ к системному каталогу - выполнение любых подпрограмм - привилегии выдаются автоматически для каждого нового объекта - Настраиваемые привилегии по умолчанию - возможность дополнительно выдать или отозвать привилегии - для вновь создаваемого объекта ### Комментарии преподавателя Как уже говорилось, псевдороль public включает в себя все остальные роли, которые, таким образом, пользуются всеми привилегиями, выданными для public. При этом public имеет довольно широкий спектр привилегий по умолчанию. В частности: ● право подключения к любой базе данных (именно поэтому роль alice смогла подключиться к базе данных несмотря на то, что привилегия CONNECT не выдавалась ей явно); ● доступ к системному каталогу; ● выполнение любых подпрограмм. Это, с одной стороны, позволяет комфортно работать, не задумываясь о привилегиях, а с другой — создает определенные сложности, если разграничение доступа действительно необходимо. Описанные выше привилегии появляются у public автоматически при создании новых объектов. То есть недостаточно, например, просто отозвать у public привилегию EXECUTE на все подпрограммы: как только появится новая, public немедленно получает привилегию на ее выполнение. Однако существует специальный механизм «привилегий по умолчанию», который позволяет автоматически выдавать и отзывать привилегии при создании нового объекта. Этот механизм можно использовать и для отзыва права выполнения функций у псевдороли public. ## Page 21: Подпрограммы и привилегии по умолчанию {#source-page-021} ![Source slide page 21: Подпрограммы и привилегии по умолчанию](images/page-021.png){#source-figure-021 caption="Source slide page 21: Подпрограммы и привилегии по умолчанию"} Алиса создает функцию: ```sql alice=> CREATE FUNCTION foo() RETURNS SETOF t2 AS $$ SELECT * FROM t2; $$ LANGUAGE sql STABLE; ``` ```sql CREATE FUNCTION ``` Сможет ли Боб вызвать ее, если Алиса не выдала ему привилегию EXECUTE? ```sql bob=> SELECT foo(); ``` ```text ERROR: permission denied for table t2 CONTEXT: SQL function "foo" statement 1 ``` Вызвать — да, но Боб не сможет получить доступ к объектам, на которые у него нет соответствующих привилегий. Если Боб создаст свою таблицу t2 в схеме public, функция будет работать для обоих пользователей — но с разными таблицами, поскольку у Алисы и Боба разные пути поиска. Чтобы Боб мог создать таблицу в схеме public, потребуется явно выдать ему привилегию CREATE на схему (начиная с версии PostgreSQL 15): ```sql student=# GRANT CREATE ON SCHEMA public TO bob; ``` ```text GRANT ``` ```sql bob=> CREATE TABLE t2(n numeric, who text DEFAULT current_user); ``` ```sql CREATE TABLE ``` ```sql bob=> INSERT INTO t2(n) VALUES (42); ``` ```sql INSERT 0 1 ``` ```sql bob=> SELECT foo(); ``` ```text foo ---------- (42,bob) (1 row) ``` ```sql alice=> SELECT foo(); ``` ```text foo ----------- (1,alice) (100,bob) (2 rows) ``` Другой доступный вариант — объявить функцию как работающую с правами ее владельца: ```sql alice=> ALTER FUNCTION foo() SECURITY DEFINER; ``` ```sql ALTER FUNCTION ``` В этом случае функция всегда будет выполняться с правами Алисы, независимо от того, кто ее вызывает. Боб удаляет свою таблицу... ```sql bob=> DROP TABLE t2; ``` ```sql DROP TABLE ``` ...и получает доступ к содержимому таблицы Алисы: ```sql bob=> SELECT foo(); ``` ```text foo ----------- (1,alice) (100,bob) (2 rows) ``` В этом случае Алисе надо внимательно следить за выданными привилегиями. Скорее всего, потребуется отозвать ## Page 22: Page 22 {#source-page-022} ![Source slide page 22: Page 22](images/page-022.png){#source-figure-022 caption="Source slide page 22: Page 22"} EXECUTE у роли public и выдавать ее явно только нужным ролям. ```sql alice=> REVOKE EXECUTE ON ALL ROUTINES IN SCHEMA alice FROM public; ``` ```text REVOKE ``` ```sql bob=> SELECT foo(); ``` ```text ERROR: permission denied for function foo ``` Но дело осложняется тем, что по умолчанию привилегия на выполнение автоматически выдается роли public на каждую вновь создаваемую функцию. Для того чтобы конкретные пользователи получали или, наоборот, лишались тех или иных привилегий на вновь создаваемые объекты, можно настроить привилегии по умолчанию: ```sql alice=> ALTER DEFAULT PRIVILEGES FOR ROLE alice REVOKE EXECUTE ON ROUTINES FROM public; ``` ```sql ALTER DEFAULT PRIVILEGES ``` ```sql alice=> ALTER DEFAULT PRIVILEGES FOR ROLE alice GRANT EXECUTE ON ROUTINES TO bob; ``` ```sql ALTER DEFAULT PRIVILEGES ``` ```text alice=> \ddp ``` ```text Default access privileges Owner | Schema | Type | Access privileges -------+--------+----------+------------------- alice | | function | alice=X/alice + | | | bob=X/alice (1 row) ``` Теперь Боб сразу получает привилегию на выполнение подпрограмм, создаваемых Алисой, а остальные пользователи не смогут их выполнять. ```sql alice=> CREATE FUNCTION bar() RETURNS integer LANGUAGE sql IMMUTABLE SECURITY DEFINER RETURN 1; ``` ```sql CREATE FUNCTION ``` ```sql bob=> SELECT bar(); ``` ```text bar ----- 1 (1 row) ``` ## Page 23: Итоги {#source-page-023} **Роли, атрибуты и привилегии — гибкий механизм, позволяющий по-разному организовать работу** - можно легко разрешить все всем - можно строго разграничить доступ, если это необходимо - При создании новых ролей надо позаботиться о возможности их подключения к серверу ## Page 24: Практика {#source-page-024} - 1. Создайте две роли (пароль должен совпадать с именем): – employee — сотрудник магазина, – buyer — покупатель. - Убедитесь, что созданные роли могут подключиться к БД. 2. Отзовите у роли public права выполнения всех функций и подключения к БД. 3. Разграничьте доступ таким образом, чтобы: – сотрудник мог только заказывать книги, а также – добавлять авторов и книги, – покупатель мог только приобретать книги. - Проверьте выполненные настройки в приложении. ### Комментарии преподавателя 1. Сотрудник — внутренний пользователь приложения, аутентификация выполняется на уровне СУБД. Покупатель — внешний пользователь. В реальном интернет-магазине управление такими пользователями ложится на приложение, а все запросы поступают в СУБД от одной «обобщенной» роли (buyer). Идентификатор конкретного покупателя может передаваться как параметр (но в нашем приложении мы этого не делаем). 3. Вообще говоря, разграничение доступа должно быть заложено, в том числе, в приложение. В нашем учебном приложении разграничение не сделано специально: вместо этого на веб-странице можно явно выбрать роль, от имени которой пойдет запрос в СУБД. Это позволяет посмотреть, как поведет себя серверная часть при некорректной работе приложения. Итак, пользователям нужно выдать: ● Право подключения к БД bookstore и доступ к схеме bookstore. ● Доступ к представлениям, к которым происходит непосредственное обращение. ● Доступ к функциям, которые вызываются как часть API. Если оставить функции SECURITY INVOKER, придется выдавать доступ и ко всем «нижележащим» объектам (таблицам, другим функциям). Однако удобнее просто объявить API-функции как SECURITY DEFINER. Разумеется, ролям нужно выдать привилегии только на те объекты, доступ к которым у них должен быть. ## Page 25: 1. Создание ролей {#source-page-025} ```sql => CREATE ROLE employee LOGIN PASSWORD 'employee'; ``` ```sql CREATE ROLE ``` ```sql => CREATE ROLE buyer LOGIN PASSWORD 'buyer'; ``` ```sql CREATE ROLE ``` Настройки по умолчанию разрешают подключение с локального адреса по паролю. Нас это устраивает. ### 2. Привилегии public У роли public надо отозвать лишние привилегии. ```sql => REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA bookstore FROM public; ``` ```text REVOKE ``` ```sql => REVOKE CONNECT ON DATABASE bookstore FROM public; ``` ```text REVOKE ``` ### 3. Разграничение доступа Функции с правами создавшего. ```sql => ALTER FUNCTION get_catalog(text,text,boolean) SECURITY DEFINER; ``` ```sql ALTER FUNCTION ``` ```sql => ALTER FUNCTION update_catalog() SECURITY DEFINER; ``` ```sql ALTER FUNCTION ``` ```sql => ALTER FUNCTION add_author(text,text,text) SECURITY DEFINER; ``` ```sql ALTER FUNCTION ``` ```sql => ALTER FUNCTION add_book(text,integer[]) SECURITY DEFINER; ``` ```sql ALTER FUNCTION ``` ```sql => ALTER FUNCTION buy_book(integer) SECURITY DEFINER; ``` ```sql ALTER FUNCTION ``` ```sql => ALTER FUNCTION book_name(integer,text,integer) SECURITY DEFINER; ``` ```sql ALTER FUNCTION ``` ```sql => ALTER FUNCTION authors(books) SECURITY DEFINER; ``` ```sql ALTER FUNCTION ``` Привилегии покупателя: покупатель должен иметь доступ к поиску книг и их покупке. ```text => GRANT CONNECT ON DATABASE bookstore TO buyer; ``` ```text GRANT ``` ```text => GRANT USAGE ON SCHEMA bookstore TO buyer; ``` ```text GRANT ``` ```text => GRANT EXECUTE ON FUNCTION get_catalog(text,text,boolean) TO buyer; ``` ```text GRANT ``` ```text => GRANT EXECUTE ON FUNCTION buy_book(integer) TO buyer; ``` ```text GRANT ``` Привилегии сотрудника: сотрудник должен иметь доступ к просмотру и добавлению книг и авторов, а также к каталогу для заказа книг. ```text => GRANT CONNECT ON DATABASE bookstore TO employee; ``` ```text GRANT ``` ## Page 26: Page 26 {#source-page-026} ```text => GRANT USAGE ON SCHEMA bookstore TO employee; ``` ```text GRANT ``` ```sql => GRANT SELECT,UPDATE(onhand_qty) ON catalog_v TO employee; ``` ```text GRANT ``` ```sql => GRANT SELECT ON authors_v TO employee; ``` ```text GRANT ``` ```sql => GRANT SELECT ON operations_v TO employee; ``` ```text GRANT ``` ```text => GRANT EXECUTE ON FUNCTION book_name(integer,text,integer) TO employee; ``` ```text GRANT ``` ```text => GRANT EXECUTE ON FUNCTION authors(books) TO employee; ``` ```text GRANT ``` ```text => GRANT EXECUTE ON FUNCTION author_name(text,text,text) TO employee; ``` ```text GRANT ``` ```text => GRANT EXECUTE ON FUNCTION add_book(text,integer[]) TO employee; ``` ```text GRANT ``` ```text => GRANT EXECUTE ON FUNCTION add_author(text,text,text) TO employee; ``` ```text GRANT ``` ## Page 27: Практика+ {#source-page-027} 1. Зарегистрируйте пользовательские роли alice и bob. 2. Отредактируйте файл pg\_hba.conf так, чтобы беспарольный вход был разрешен лишь для пользователей postgres и student. Убедитесь, что доступ для alice и bob запрещен. 3. Для пользователей alice и bob включите разрешение входить в сеанс, используя метод peer. Проверьте невозможность входа в сеанс без создания отображения на пользователя ОС. Для alice создайте такое отображение. 4. Проверьте возможность использовать одно и то же отображение для разных ролей. ### Комментарии преподавателя 2. С помощью текстового редактора вставьте перед первой незакомментированной директивой в pg\_hba.conf local all postgres,student trust Перечитайте конфигурацию. 3. Отредактируйте вставленную в pg\_hba.conf директиву, заменив ее следующим: local all postgres,student trust local all alice,bob peer В конец файла pg\_ident.conf добавьте: stmap student alice 4. Чтобы роли alice и bob использовали одно отображение, содержимое pg\_ident.conf должно быть следующим: stmap student alice stmap student bob ## Page 28: 1. Добавление ролей {#source-page-028} Зарегистрируем роли с правом входа в сеанс. ```sql => CREATE ROLE alice LOGIN; ``` ```sql CREATE ROLE ``` ```sql => CREATE ROLE bob LOGIN; ``` ```sql CREATE ROLE ``` ### 2. Ограничение использования trust Отредактируем содержимое pg\_hba.conf, разрешив метод trust лишь для postgres и student. ```sh student$ sudo sed -i 's/^local.*all.*all.*trust.*$/local all postgres,student trust\n/' /etc/postgresql/16/main/pg_hba.conf ``` Вот что получилось: ```sql => SELECT type,database,user_name,address,auth_method,error FROM pg_hba_file_rules ORDER BY rule_number; ``` ```text type | database | user_name | address | auth_method | error -------+---------------+--------------------+-----------+---------------+------- local | {all} | {postgres,student} | | trust | host | {all} | {all} | 127.0.0.1 | scram-sha-256 | host | {all} | {all} | ::1 | scram-sha-256 | local | {replication} | {all} | | trust | host | {replication} | {all} | 127.0.0.1 | scram-sha-256 | host | {replication} | {all} | ::1 | scram-sha-256 | (6 rows) ``` Применим конфигурацию. ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` Теперь alice и bob не могут подключиться: ```sh student$ psql -l -U alice ``` ```text psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "alice", database "postgres", no encryption ``` ```sh student$ psql -l -U bob ``` ```text 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 "postgres", no encryption ``` ### 3. Метод аутентификации peer Используя текстовый редактор, добавим еще одну строку с методом аутентификации peer, чтобы разрешить подключение пользователям alice и bob. ```sh student$ sudo sed -i '/^local.*all.*postgres,student.*$/alocal all alice,bob peer' /etc/postgresql/16/main/pg_hba.conf ``` Содержимое pg\_hba.conf: ```sql => SELECT type,database,user_name,address,auth_method,error FROM pg_hba_file_rules ORDER BY rule_number; ``` ## Page 29: Page 29 {#source-page-029} ```text type | database | user_name | address | auth_method | error -------+---------------+--------------------+-----------+---------------+------- local | {all} | {postgres,student} | | trust | local | {all} | {alice,bob} | | peer | host | {all} | {all} | 127.0.0.1 | scram-sha-256 | host | {all} | {all} | ::1 | scram-sha-256 | local | {replication} | {all} | | trust | host | {replication} | {all} | 127.0.0.1 | scram-sha-256 | host | {replication} | {all} | ::1 | scram-sha-256 | (7 rows) ``` Перечитаем конфигурацию. ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` Однако по-прежнему эти учетные записи не могут войти в сеанс, хотя сообщение об ошибке изменилось. ```sh student$ psql -l -U alice ``` ```text psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "alice" ``` ```sh student$ psql -l -U bob ``` ```text psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "bob" ``` Метод peer требует совпадения учетной записи в ОС с именем роли в PostgreSQL. Создадим отображение роли alice на пользователя ОС student, добавив строку в файл pg\_ident.conf. Для роли bob отображение задавать не будем. ```sh student$ echo 'stmap student alice' | sudo tee -a /etc/postgresql/16/main/pg_ident.conf ``` ```text stmap student alice ``` И допишем в добавленную строку параметр map, задающий имя отображения. ```sh student$ sudo sed -i 's/peer.*$/peer map=stmap/' /etc/postgresql/16/main/pg_hba.conf ``` Содержимое pg\_hba.conf: ```sql => SELECT type,database,user_name,address,auth_method,options,error FROM pg_hba_file_rules ORDER BY rule_number; ``` ```text type | database | user_name | address | auth_method | options | error -------+---------------+--------------------+-----------+---------------+-------------+--- ---- local | {all} | {postgres,student} | | trust | | local | {all} | {alice,bob} | | peer | {map=stmap} | host | {all} | {all} | 127.0.0.1 | scram-sha-256 | | host | {all} | {all} | ::1 | scram-sha-256 | | local | {replication} | {all} | | trust | | host | {replication} | {all} | 127.0.0.1 | scram-sha-256 | | host | {replication} | {all} | ::1 | scram-sha-256 | | (7 rows) ``` Перечитаем конфигурацию. ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` Теперь alice может подключиться к базе данных и выполнить команды. ```sh student$ psql -c '\conninfo' -U alice -d student ``` ```text You are connected to database "student" as user "alice" via socket in "/var/run/postgresql" at port "5432". ``` А bob — нет. ```sh student$ psql -c '\conninfo' -U bob -d student ``` ## Page 30: 4. Одно отображение для нескольких ролей {#source-page-030} ```text psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "bob" ``` Разрешим пользователю bob входить в сеанс на таких же условиях, как alice. ```sh student$ echo 'stmap student bob' | sudo tee -a /etc/postgresql/16/main/pg_ident.conf ``` ```text stmap student bob ``` Добавленные строки в pg\_ident.conf: ```sh student$ sudo tail -n2 /etc/postgresql/16/main/pg_ident.conf ``` ```text stmap student alice stmap student bob ``` Перечитаем конфигурацию. ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` Теперь и alice, и bob смогут подключиться. ```sh student$ psql -c '\conninfo' -U alice -d student ``` ```text You are connected to database "student" as user "alice" via socket in "/var/run/postgresql" at port "5432". ``` ```sh student$ psql -c '\conninfo' -U bob -d student ``` ```text You are connected to database "student" as user "bob" via socket in "/var/run/postgresql" at port "5432". ```