В 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
Здесь мы разобрали некоторый минимум. Если хотите знать больше, можете почитать о ролях в документации.
Если понравилась статья, подпишись на мой канал в VK или Telegram.