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

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

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

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

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

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

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

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

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

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

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

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

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

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 slide 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 slide 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 slide 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 slide 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 slide 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 slide page 16: Право передачи{#source-figure-016 caption=“Source slide page 16: Право передачи”}

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

  • alice

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

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

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

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

Source slide 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 slide 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 slide 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 slide 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. Создайте пользователей для Алисы и Боба.
    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 slide 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: Практика+

  1. Создайте пользователя для Алисы и установите для него нулевое значение параметра 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