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

Псевдороль public неявно включает в себя все остальные роли. Её нельзя найти в списке ролей, но она существует.

У ролей есть несколько атрибутов. Эти атрибуты указываются при создании роли:

  • LOGIN / NOLOGIN – возможность подключения;
  • SUPERUSER / NOSUPERUSER – суперпользователь;
  • CREATEDB / NOCREATEDB – возможность создавать базы данных;
  • CREATEROLE / NOCREATEROLE – возможность создавать роли;
  • REPLICATION / NOREPLICATION– использование протокола репликации;
  • и другие.

Создают роль следующим способом:

CREATE ROLE <роль> [WITH] <атрибуты через запятую>;

Если при создании роли не указать атрибуты, то роль получит запрещающие атрибуты (NOLOGIN, NOSUPERUSER) автоматом.

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

GRANT <групповая роль> TO <роль>;

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

REVOKE <групповая роль> FROM <роль>;

Право включать роли в другие роли имеют роли с одним из следующих атрибутов:

  • Роль может включить в саму себя любую другую роль;
  • SUPERUSER – может включать любую роль в другую любую роль;
  • CREATEROLE – может включать любую роль в любую групповую роль, кроме суперпользовательской. Это сделано для того, чтобы роль с атрибутом CREATEROLE не мог себе или другому дать привилегии суперпользователя.

При включении в роль можно дать этой роли право управлять групповой ролью, то-есть включать в неё другие роли. Это делается с помощью опции WITH ADMIN OPTION при включении в роль:

GRANT <групповая роль> TO <роль> WITH ADMIN OPTION;

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

REVOKE ADMIN OPTION FOR <групповая роль> FROM <роль>;

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

Владелец объекта – это роль, которая этот объект создала, а также роли включённые в неё.

Владельца можно переназначить с помощью ALTER:

ALTER [TABLE, VIEW] <название объекта> OWNER TO <роль>;

Практика

Создадим новую роль:

postgres@s-pg13:~$ psql
Timing is on.
psql (13.3)
Type "help" for help.

postgres@postgres=# CREATE ROLE alice LOGIN CREATEROLE;
CREATE ROLE
Time: 0,971 ms

Переподключмся под пользователем alice:

postgres@postgres=# \c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=>

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

Далее под ролью alice создадим другую роль bob и разрешим ему только подключаться к база данных:

alice@postgres=> CREATE ROLE bob LOGIN;
CREATE ROLE
Time: 0,670 ms

Попробуем под bob создать другую роль:

alice@postgres=> \c - bob
You are now connected to database "postgres" as user "bob".

bob@postgres=> CREATE ROLE charlie LOGIN;
ERROR:  permission denied to create role
Time: 0,369 ms

Посмотрим, какие роли есть в кластере:

bob@postgres=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 alice     | Create role                                                | {}
 bob       |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Из вывода мы видим:

  • Role name – имя роли;
  • Attributes – их атрибуты;
  • Member of – в какие роли входит эта роль.

Или тоже самое можно посмотреть в представлении pg_user из системного каталога:

bob@postgres=> SELECT * FROM pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          |
 alice    |    16554 | f           | f        | f       | f            | ******** |          |
 bob      |    16555 | f           | f        | f       | f            | ******** |          |
(3 rows)

Time: 0,503 ms

Отнимем у Боба право подключаться к базам и проверим это:

bob@postgres=> \c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> ALTER ROLE bob NOLOGIN;
ALTER ROLE
Time: 0,713 ms

alice@postgres=> \c - bob
FATAL:  role "bob" is not permitted to log in
Previous connection kept

Алиса у самой себя отберёт право создавать роли:

alice@postgres=> ALTER ROLE alice NOCREATEROLE;
ALTER ROLE
Time: 0,455 ms

alice@postgres=> CREATE ROLE charlie LOGIN;
ERROR:  permission denied to create role
Time: 0,267 ms

Подключимся под ролью postgres и включим Алису в группу postgres:

alice@postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".

postgres@postgres=# GRANT postgres TO alice;
GRANT ROLE
Time: 0,943 ms

postgres@postgres=# \du
                                    List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+------------
 alice     |                                                            | {postgres}
 bob       | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Раз мы включили Алису в группу Суперпользователя, будем заносить все её запросы в журнал. Вначале включим параметр log_min_duration_statement=0 для Алисы, затем сбросим его. И в конце концов назначим этот же параметр, но только когда Алиса подключена к базе postgres:

postgres@postgres=# ALTER ROLE alice SET log_min_duration_statement=0;
ALTER ROLE
Time: 0,550 ms

postgres@postgres=# ALTER ROLE alice RESET log_min_duration_statement;
ALTER ROLE
Time: 0,482 ms

postgres@postgres=# ALTER ROLE alice IN DATABASE postgres SET log_min_duration_statement=0;
ALTER ROLE
Time: 0,416 ms

Хоть Алиса и входит в группу postgres, но просто так привилегиями она воспользоваться не сможет. Вначале Алисе нужно выполнить SET ROLE postgres:

postgres@postgres=# \c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> ALTER ROLE bob LOGIN;
ERROR:  permission denied
Time: 0,317 ms

alice@postgres=> SET ROLE postgres;
SET
Time: 0,336 ms

alice@postgres=# ALTER ROLE bob LOGIN;
ALTER ROLE
Time: 0,676 ms

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

alice@postgres=# SELECT session_user;
 session_user
--------------
 alice
(1 row)
Time: 0,344 ms

alice@postgres=# SELECT current_user;
 current_user
--------------
 postgres
(1 row)
Time: 0,196 ms

Вывод из предыдущего примера означает, что Алиса использует сейчас не свою роль, а роль postgres. Другими словами она выполнила SET ROLE postgres.

Вернемся к прежней роли:

alice@postgres=# RESET ROLE;
RESET
Time: 0,193 ms

alice@postgres=> SELECT current_user;
 current_user
--------------
 alice
(1 row)
Time: 0,254 ms

Создадим под Алисой таблицу test и посмотрим, кто её владелец (предварительно удалим созданную ранее таблицу test):

alice@postgres=> DROP TABLE test;
DROP TABLE
Time: 1,483 ms

alice@postgres=> CREATE TABLE test(id integer);
CREATE TABLE
Time: 1,939 ms

alice@postgres=> \dt test
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | test | table | alice
(1 row)

Из вывода выше мы видим, что для таблицы test владелец (Owner) это Алиса.

Чтобы удалить роль, у неё не должно быть объектов которыми она владеет во всём кластере:

alice@postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".

postgres@postgres=# DROP ROLE alice;
ERROR:  role "alice" cannot be dropped because some objects depend on it
DETAIL:  owner of table test
Time: 0,477 ms

Из вывода выше мы видим, что Алиса владеет таблицей test и поэтому Алису нельзя удалить.

Можно все объекты одной роли передать другой роли с помощью REASSIGN OWNED. Например передадим все объекты Алисы к Бобу:

postgres@postgres=# REASSIGN OWNED BY alice TO bob;
REASSIGN OWNED
Time: 1,192 ms

postgres@postgres=# \dt test
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | test | table | bob
(1 row)

postgres@postgres=# DROP ROLE alice;
DROP ROLE
Time: 0,557 ms

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


Сводка
Роли и атрибуты в PostgreSQL
Имя статьи
Роли и атрибуты в PostgreSQL
Описание
В PostgreSQL и пользователи и группы - это роли. Одна роль может быть членом другой роли. Роли в PostgreSQL не связаны с пользователями в ОС

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *