Роли

В PostgreSQL любой сеанс является подключением зарегистрированной в СУБД роли к конкретной базе данных. В ранних версиях PostgreSQL были понятия пользователя и группы, как в Unix-подобных ОС. Однако сейчас имеются лишь роли, которые могут представлять конкретного пользователя базы данных или абстрактного владельца объектов, а в случае, если в роль входят несколько членов, то и целую группу. Роль — глобальный объект кластера баз данных.

примечание

Обратите внимание, что в PostgreSQL с версии 16 система ролей претерпела значительные изменения.

На схеме выше приведен пример, когда роль представляет собой пользователя и группу. Роли university, group_1 и group_2 — группы ролей (далее будем называть такие роли групповыми для удобства). Роли teacher, student_1.1, student_1.2 и student_2.1 уже обычные пользователи.

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

• атрибутами;
• членством в групповых ролях;
• владением объектами;
• и привилегиями.

Атрибуты определяют ее взаимодействие с системой аутентификации пользователя и СУБД на верхнем уровне (может ли создавать базы данных и другие роли). Членство в групповых ролях позволяет гибко настроить права для каждой группы пользователей. Все объекты принадлежат каким-то ролям, и это владение устанавливает определенный круг действий, который роль может выполнить над объектом. Если же объект роли не принадлежит, но какое-либо действие все равно необходимо выполнить, выдаются привилегии на это действие.

Создание роли

При создании кластера, которое выполняется средствами команды initdb, создается основная встроенная роль postgres. Она обладает полными правами на все объекты кластера баз данных и может выполнять любые действия в СУБД.

Важно

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

Создать роль можно командой CREATE ROLE с дополнительными опциями (атрибутами ролей) или с помощью ее аналога — CREATE USER. Данный вариант равносилен CREATE ROLE ... LOGIN.

Добавим групповую роль grp_students, которая не будет иметь права входа в сеанс. Для данного ограничения используется атрибут NOLOGIN. Подробнее об атрибутах будет рассказано дальше.

postgres@postgres=# CREATE ROLE grp_students NOLOGIN;

CREATE ROLE

Поскольку роль grp_students была задумана как групповая, добавим в нее роль student с возможностью входить в сеанс. Для включения роли в другую роль после перечисления атрибутов указывается ключевое слово IN ROLE и роль, которая станет групповой.

postgres@postgres=# CREATE ROLE student LOGIN PASSWORD 'student' IN ROLE grp_students;

При использовании атрибута PASSWORD нужно указать сам пароль, в нашем случае это student.

CREATE ROLE

Посмотреть список всех ролей можно с помощью метакоманды \du.

postgres@postgres=# \du

                                            List of roles
  Role name   |                               Attributes                                |   Member of
--------------+-------------------------------------------------------------------------+----------------
 grp_students | Cannot login                                                            | {}
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS student      | {}
 student      |                                                                         | {grp_students}

Кроме списка самих ролей данная метакоманда также показывает их атрибуты и групповые роли, а при применении метакоманды \du+ будет показан столбец с описанием ролей. В нашем случае он будет пустым, поэтому была применена стандартная метакоманда.

Атрибуты ролей

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

Полный список атрибутов:

• SUPERUSER | NOSUPERUSER — является ли роль суперпользователем или нет;
• CREATEDB | NOCREATEDB — может ли роль создавать базы данных или нет;
• CREATEROLE | NOCREATEROLE — может ли роль создавать другие роли или нет;
• INHERIT | NOINHERIT — наследует ли роль привилегии групповой роли или нет;
• LOGIN | NOLOGIN — может ли роль входить в сеанс или нет;
• REPLICATION | NOREPLICATION — может ли роль взаимодействовать со слотами репликации или нет;
• BYPASSRLS | NOBYPASSRLS — игнорирует ли роль RLS или нет;
• CONNECTION LIMIT — ограничено ли количество подключений к роли или нет;
• PASSWORD 'password' | PASSWORD NULL — будет ли пароль у роли или нет;
• VALID UNTIL — временное ограничение действия пароля роли.

к сведению

Атрибут CONNECTION LIMIT в PostgreSQL ограничивает количество разрешенных подключений для данной роли. В Pangolin реализована возможность резервирования количества доступных подключений для ролей, что гарантирует пользователю наличие свободных подключений. Настроить резервирование соединения можно для конфигурации standalone, изменяя параметры в файле pg_quota.conf. После редактирования этого файла требуется выполнить перезагрузку СУБД.

Рассмотрим столбец с атрибутами из примера выше подробнее. Для удобства продублируем результат метакоманды \du здесь.

                                            List of roles
  Role name   |                               Attributes                                |   Member of
--------------+-------------------------------------------------------------------------+----------------
 grp_students | Cannot login                                                            | {}
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS student      | {}
 student      |                                                                         | {grp_students}

У роли grp_students стоит описание Cannot login, которое соответствует атрибуту NOLOGIN, запрещающий вход в сеанс непосредственно от имени этой роли. И действительно, если попытаться подключиться к базе данных через grp_students, появится ошибка.

postgres@postgres=# \c - grp_students

Синтаксис метакоманды \c

Напомним, что полный синтаксис метакоманды \c:

\c or \connect [ -reuse-previous=on|off ] [ dbname [ username ] [ host ] [ port ] | conninfo ]

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

connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "grp_students" is not permitted to log in Previous connection kept

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

Для атрибута LOGIN в PostgreSQL описания не предусмотрено, поэтому строчка student пуста.

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

Присвоение атрибута

Установить для существующей роли атрибут можно с помощью команды ALTER ROLE. После нее указывается роль, которой нужно добавить право, а после — сам атрибут.

Выдадим роли grp_students право на создание баз данных.

postgres@postgres=# ALTER ROLE grp_students CREATEDB;

ALTER ROLE

Проверим, что атрибут CREATEDB появился только у grp_students, но не student.

postgres@postgres=# \du *student*

    Role name | Attributes                | Member of
--------------+---------------------------+----------------
 grp_students | Create DB, Cannot login   | {}
      student |                           | {grp_students}

Благодаря использованию шаблона по имени, в результате выполнения метакоманды \du отобразились только роли grp_students и student. Обратите внимание, атрибут CREATEDB есть только у групповой роли.

Подключимся через роль student и попробуем создать базу данных.

postgres@postgres=# \c - student

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

student@postgres=> CREATE DATABASE my_db;

ERROR: permission denied to create database

Поскольку у роли student нет атрибута CREATEDB, запрос на создание базы данных вернул ошибку с отказом в доступе. Роли, входящие в состав групповых ролей, не наследуют автоматически их атрибуты. Именно поэтому, несмотря на имеющееся право CREATEDB у grp_students, student не может им воспользоваться.

Использование атрибутов группы

Чтобы групповые роли действительно позволяли гибко настраивать права каждой группы пользователей, необходим механизм передачи их атрибутов дочерним ролям. Такой механизм есть — команда SET ROLE. Она позволяет роли пользоваться правами группы, в которую она входит.

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

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

При управлении правами с помощью групповых ролей обычно каждому пользователю выдается роль с минимальными правами (вход в сессию по паролю). Групповой роли же выдаются права, которые могут потребоваться входящим в нее ролям в процессе работы. Таким образом происходит разграничение: права роли пользователя используются для подключения (session_user), а права групповой роли — для взаимодействия с СУБД (current_user).

Однако помним, что по умолчанию пометку current_user получает та же роль, что и session_user. С помощью команды SET ROLE можно установить в качестве текущей роли (current_user) групповую роль, членом которой является роль session_user.

к сведению

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

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

student@postgres=> SELECT session_user, current_user;

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

Ранее к базе данных postgres было совершено подключение от имени роли student, соответственно, в качестве session_user, начавшего сессию пользователя, указан именно student. И эта же роль по умолчанию была записана в current_user.

Теперь используем SET ROLE, чтобы установить grp_students в качестве current_user.

student@postgres=> SET ROLE grp_students;

SET

Поскольку роль student входит в групповую роль grp_students, установка новой роли прошла успешно. Проверим еще раз значения session_user и current_user.

student@postgres=> SELECT session_user, current_user;

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

Теперь все действия в данной сессии будут выполняться от имени grp_students.

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

student@postgres=> CREATE DATABASE my_db;

CREATE DATABASE

База данных my_db успешно создана, так как у текущего пользователя, grp_students, есть атрибут CREATEDB.

Работа от имени grp_students будет идти до тех пор, пока не будет выполнено переподключение от имени другой роли или использована команда RESET ROLE. По умолчанию команда RESET ROLE делает значение current_user равным session_user, но есть и другие варианты поведения данной команды.

Вернем роль student в качестве значения current_user.

student@postgres=> RESET ROLE;

RESET

Проверим, что теперь все действия в сессии будут выполняться от имени student.

student@postgres=> SELECT session_user, current_user;

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

Владельцы объектов

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

Ранее была создана база данных my_db, посмотрим информацию по ней c помощью метакоманды \l.

student@postgres=> \l my*

                                               List of databases
 Name  |    Owner     | Encoding  |   Collate  |    Ctype   | ICU Locale |  Locale Provider | Access privileges
-------+--------------+-----------+------------+------------+------------+------------------+-------------------
 my_db | grp_students | UTF8      | en_US.UTF8 | en_US.UTF8 |            | libc             |
(1 row)

Обратите внимание на столбец Owner, в нем записан владелец базы данных — групповая роль grp_students. По умолчанию в качестве владельца указывается роль, создавшая объект. Так произошло и в нашем случае. Несмотря на то, что сессию начала роль student и она же была текущем пользователем изначально, после применения команды SET ROLE все действия в сессии выполнялись от имени grp_students. Соответственно, данная роль и была указана в качестве владельца my_db.

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

ALTER [ DATABASE, INDEX, TABLE, ... ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

Создадим в my_db объект и поменяем для него пользователя.

Для начала подключимся к базе данных my_db как student и создадим таблицу studentab.

student@postgres=> \c my_db

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

student@my_db=> CREATE TABLE studentab(id int, str text);

CREATE TABLE

Проверим, что владельцем новой таблицы указана роль student. Информацию о владельце можно получить с помощью метакоманды \d или ее расширенного варианта \d+. В нашем случае достаточно просто \d.

student@my_db=> \d

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

Владельцем studentab действительно указан student. Теперь сменим владельца на grp_students.

student@my_db=> ALTER TABLE studentab OWNER TO grp_students;

ALTER TABLE

Убедимся, что владелец сменился.

student@my_db=> \d

                   List of relations
 Schema |      Name | Type    | Owner
--------+-----------+---------+--------------
 public | studentab | таблица | grp_students
(1 row)

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

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

student@my_db=> ALTER TABLE studentab OWNER TO postgres;

ERROR:  must be member of role "postgres"

Удаление роли

При управлении ролями нужно уметь правильно не только настраивать доступы, но и удалять сами роли. PostgreSQL не разрешит удаление, пока роль владеет какими-либо объектами, атрибутами или привилегиями. Для объектов предварительно нужно сменить владельца или удалить их; атрибуты и привилегии нужно изъять. Если роль является групповой, входящие в нее роли нужно переместить или также удалить. Удалить пустую роль может лишь пользователь с атрибутом CREATEROLE (в том числе суперпользователь) с помощью команды DROP ROLE.

Попробуем удалить роль grp_students.

student@my_db=> DROP ROLE grp_students;

ERROR:  role "grp_students" cannot be dropped because some objects depend on it
DETAIL:  owner of database my_db
owner of table studentab

При попытке удаления роли grp_students появляется предупреждение, что есть объекты, зависящие от данной роли, и далее детали с перечислением этих объектов. Заметьте, в описании деталей ошибки нет упоминания, что данная роль является групповой. Когда удаляется групповая роль, все роли, входящие в нее, будут автоматически исключены из группы. И наоборот, когда происходит удаление роли, входящей в групповую роль, она просто исключается из группы.

Для демонстрации удаления роли с необходимой подготовкой создадим роль user_role с правом начала сессии в grp_students. От имени user_role внутри базы данных my_db — таблицу user_table.

Попробуйте самостоятельно выполнить описанные выше действия.

Далее приведен пример выполнения описанных выше действий.

student@my_db=> \c - postgres

You are now connected to database "my_db" as user "postgres".

postgres@my_db=# CREATE ROLE user_role LOGIN IN ROLE grp_students;

CREATE ROLE

postgres@my_db=# \c - user_role

You are now connected to database "my_db" as user "user_role".

user_role@my_db=> CREATE TABLE user_table (id int);

CREATE TABLE

Убедимся, что все роли и объекты созданы.

user_role@my_db=> \d

             List of relations
 Schema |   Name     | Type  |    Owner
--------+------------+-------+--------------
 public | studentab  | table | grp_students
 public | user_table | table | user_role
(5 rows)

user_role@my_db=> \du

                                         List of roles
    Role name     |                         Attributes                         |   Member of
------------------+------------------------------------------------------------+----------------
 abiturient       | No inheritance                                             | {grp_students}
 aspirant         |                                                            | {}
 grp_students     | Create DB, Cannot login                                    | {}
 postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 student          |                                                            | {grp_students}
 user_role        |                                                            | {grp_students}

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

user_role@my_db=> \c - postgres

You are now connected to database "my_db" as user "postgres".

postgres@my_db=# DROP ROLE user_role;

ERROR:  role "user_role" cannot be dropped because some objects depend on it
DETAIL:  owner of table user_table

Снова видим ошибку из-за зависимых объектов, в нашем случае — таблицы user_table.

Удалим таблицу и попробуем вновь удалить роль.

postgres@my_db=# DROP TABLE user_table;

DROP TABLE

postgres@my_db=# DROP ROLE user_role;

DROP ROLE

После удаления зависимой таблицы роль user_role стала пустой и доступной для удаления. Проверим список ролей.

postgres@my_db=# \du

                                         List of roles
    Role name     |                         Attributes                         |   Member of
------------------+------------------------------------------------------------+----------------
 abiturient       | No inheritance                                             | {grp_students}
 aspirant         |                                                            | {}
 grp_students     | Create DB, Cannot login                                    | {}
 postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 student          |                                                            | {grp_students}

Итоги

Ключевые моменты по ролям:

• CREATE ROLE создает роль, CREATE USER — аналог с атрибутом LOGIN по умолчанию;
• Атрибут SUPERUSER дает неограниченные права, исходным суперпользователем является postgres;
• Атрибуты CREATEDB и CREATEROLE разрешают создавать базы данных и роли соответственно;
• Атрибут LOGIN разрешает вход в сессию, NOLOGIN обычно устанавливается для групповых ролей;
• Атрибуты INHERIT и NOINHERIT управляют наследованием привилегий от групповых ролей;
• Роли не наследуют атрибуты групповых ролей — для использования атрибутов группы применяется SET ROLE;
• Владелец объекта обладает полными правами, включая удаление; по умолчанию владельцем становится роль, создавшая объект;
• Команда ALTER … OWNER TO меняет владельца — обычный владелец может передать право только групповой роли, членом которой является;
• DROP ROLE удаляет роль только после удаления всех зависимых объектов и изъятия привилегий.

Самопроверка

Вопрос 1

В PostgreSQL команда CREATE USER является синонимом какой команды?

CREATE ROLE ... LOGINCREATE ROLE ... NOLOGINCREATE ROLE ... SUPERUSERCREATE GROUP ... LOGIN

Вопрос 2

Какие из перечисленных атрибутов могут быть установлены при создании роли? Выберите все верные варианты.

CREATEDB

CREATEROLE

REPLICATION

CONNECTION LIMIT

BYPASSRLS

Вопрос 3

Подключившись от имени student, была выполнена команда SET ROLE grp_students. Какие роли будут в session_user и current_user?

session_user=student, current_user=grp_studentssession_user=grp_students, current_user=grp_studentssession_user=student, current_user=studentsession_user=postgres, current_user=grp_students

Вопрос 4

При создании базы данных my_db от имени роли grp_students (через SET ROLE) кто указан как владелец базы?

grp_studentsstudentpostgrespublic

Вопрос 5

Владелец таблицы student пытается передать владение на роль postgres:

student@my_db=> ALTER TABLE studentab OWNER TO postgres;

Какой будет результат?

ERROR: must be member of role "postgres"Владелец изменен успешноERROR: permission deniedERROR: role "postgres" does not exist

Вопрос 6

Роль user_role владеет таблицей user_table. Что произойдет при попытке DROP ROLE user_role?

ERROR: role "user_role" cannot be dropped because some objects depend on itРоль удалена, таблица перешла postgresРоль удалена, таблица удалена автоматическиERROR: permission denied to drop role