В курсе Администрирование PostgreSQL 10. Базовый курс есть большой раздел про управление доступом (роли, атрибуты, привилегии, и подключения). Информации много, она очень интересная, разнообразная. В курсе есть практика по каждому из занятий.
Я для себя сделал небольшую дополнительную пракитку, в которой хотел зафиксировать некоторые моменты из курса. Готовил это параллельно с изучением курса. Потом еще поделился этими черновиками. И вот уже решил что пусть будет в открытом доступе, может кому ещё пригодиться.
Использовать буду 13-ю версию PostgreSQL.
Если вкратце — ниже я создам несколько ролей и объектов БД (схема, таблица), настрою роли так, чтобы можно было пользоваться этими объектами и еще с групповыми ролями поработаю. Но подробно ничего говорить об этом не буду — всё есть в курсе.
А вот и оглавление:
- Создание ролей
- Замечание про именования объектов в PostgreSQL
- Создание базы данных и схемы
- Включение схемы в путь поиска
- Создание таблицы
- Выдача привилегий
- Включение в групповую роль
- Проверка групповых ролей
- Переключение SET ROLE
- Код без вывода
Создание ролей
Сначала посмотрим сколько ролей в системе уже есть (команда \du):
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Имеется только одна предопределенная роль (postgres), это суперпользователь (это тот, кто может переопределять любые ограничения доступа в базе данных). В статье документации Роли базы данных есть информация об этом (и еще много о чем).
Для демонстрации создам четыре роли (после названия роли перечислены её атрибуты):
CREATE ROLE mainrole LOGIN; CREATE ROLE grouprole CREATEDB; CREATE ROLE slaverole LOGIN; CREATE ROLE weakrole NOINHERIT LOGIN;
Проверим результат:
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
grouprole | Create DB, Cannot login | {}
mainrole | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
slaverole | | {}
weakrole | No inheritance | {}
Замечания про именования объектов в PostgreSQL
Информация ниже относится не только к названию ролей, но и к наименованию других пользовательских идентификаторов в PostgreSQL.
Названия ролей (по умолчанию) переводятся в нижний регистр. Если хотите чтобы роль была написана в верхнем (или часть названия роли) — нужно помещать название в двойные кавычки. То же относится к названиям, начинающимся с цифр. И вообще, в названии роли нужно использовать правила именования идентификаторов SQL:
postgres=# CREATE ROLE UPPERROLE LOGIN;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
grouprole | Create DB, Cannot login | {}
mainrole | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
slaverole | | {}
upperrole | | {}
weakrole | No inheritance | {}
postgres=# create role "UPPERROLE" login;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
UPPERROLE | | {}
grouprole | Create DB, Cannot login | {}
mainrole | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
slaverole | | {}
upperrole | | {}
weakrole | No inheritance | {}
И, как можно догадаться, роли upperrole и UPPERROLE — это разные роли.
Про начальные цифры в названии:
postgres=# CREATE ROLE 000 LOGIN;
2021-12-20 21:57:40.104 MSK [71143] ERROR: syntax error at or near "000" at character 13
2021-12-20 21:57:40.104 MSK [71143] STATEMENT: CREATE ROLE 000 LOGIN;
ERROR: syntax error at or near "000"
LINE 1: CREATE ROLE 000 LOGIN;
postgres=# CREATE ROLE "000" LOGIN;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
000 | | {}
UPPERROLE | | {}
grouprole | Create DB, Cannot login | {}
mainrole | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
slaverole | | {}
upperrole | | {}
weakrole | No inheritance | {}
Про удаление ролей аналогично:
postgres=# DROP ROLE 000;
2021-12-20 21:59:57.188 MSK [71143] ERROR: syntax error at or near "000" at character 11
2021-12-20 21:59:57.188 MSK [71143] STATEMENT: DROP ROLE 000;
ERROR: syntax error at or near "000"
LINE 1: DROP ROLE 000;
^
postgres=# DROP ROLE "000";
DROP ROLE
postgres=# DROP ROLE "UPPERROLE";
DROP ROLE
postgres=# DROP ROLE UPPERROLE;
DROP ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
grouprole | Create DB, Cannot login | {}
mainrole | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
slaverole | | {}
weakrole | No inheritance | {}
Создание базы данных и схемы
Создаю БД для дальнейшей демонстрации:
postgres=# CREATE DATABASE maindb;
Список ролей можно посмотреть с помощью команды \l:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
maindb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Подключаюсь к этой БД и создаю схему:
postgres=# \c maindb You are now connected to database "maindb" as user "postgres". maindb=# CREATE SCHEMA mainschema; CREATE SCHEMA
Посмотрим созданные схемы (\dn) и созданные таблицы (\dp):
maindb=# \dn
List of schemas
Name | Owner
------------+----------
mainschema | postgres
public | postgres
(2 rows)
maindb=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+------+-------------------+-------------------+----------
(0 rows)
Включение схемы в путь поиска
Чтобы не было проблем в дальнейшем, нужно созданную схему включить в путь поиска:
maindb=# SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
maindb=# SET search_path = mainschema, public;
SET
maindb=# SHOW search_path;
search_path
--------------------
mainschema, public
(1 row)
maindb=# SELECT current_schemas(true);
current_schemas
--------------------------------
{pg_catalog,mainschema,public}
(1 row)
Последний запрос выводит фактически использованный путь поиска.
Создание таблицы
Создаю тестовую таблицу, заполняю данными:
maindb=# CREATE TABLE maintable (id integer); CREATE TABLE maindb=# INSERT INTO maintable VALUES (1),(2),(3); INSERT 0 3 maindb=# SELECT * FROM maintable; id ---- 1 2 3 (3 rows)
Посмотрим список таблиц (\dp), там же выводятся их права доступа:
maindb=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
------------+-----------+-------+-------------------+-------------------+----------
mainschema | maintable | table | | |
(1 row)
Пустая строка означает что выданы права по умолчанию.
Выдача привилегий
Выдаю гранты:
maindb=# GRANT SELECT ON mainschema.maintable TO grouprole;
GRANT
maindb=# GRANT USAGE ON SCHEMA mainschema TO grouprole;
GRANT
maindb=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
------------+-----------+-------+---------------------------+-------------------+----------
mainschema | maintable | table | postgres=arwdDxt/postgres+| |
| | | grouprole=r/postgres | |
(1 row)
Включение в групповую роль
Включаем slaverole в групповую grouprole (можно запись читать справа-налево: включаем slaverole в grouprole):
maindb=# GRANT grouprole TO slaverole;
GRANT ROLE
maindb=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-------------
grouprole | Create DB, Cannot login | {}
mainrole | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
slaverole | | {grouprole}
weakrole | No inheritance | {}
--REVOKE grouprole FROM slaverole;
Включаем weakrole в групповую grouprole
maindb=# GRANT grouprole TO weakrole;
GRANT ROLE
maindb=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-------------
grouprole | Create DB, Cannot login | {}
mainrole | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
slaverole | | {grouprole}
weakrole | No inheritance | {grouprole}
Проверка групповых ролей
Заходим под пользователем slaverole (хотя явного атрибута login у этой роли нет):
maindb=# \c - slaverole You are now connected to database "maindb" as user "slaverole". maindb=> SELECT * FROM mainschema.maintable; id ---- 1 2 3 (3 rows)
Содержимое таблицы отображается.
Заходим под пользователем weakrole:
maindb=> \c - weakrole You are now connected to database "maindb" as user "weakrole". maindb=> SELECT * FROM mainschema.maintable; 2021-12-20 22:31:57.983 MSK [110069] ERROR: permission denied for schema mainschema at character 15 2021-12-20 22:31:57.983 MSK [110069] STATEMENT: SELECT * FROM mainschema.maintable; ERROR: permission denied for schema mainschema LINE 1: SELECT * FROM mainschema.maintable;
Нет доступа, потому что роль без наследования привилегий.
Переключаемся на пользователя postgres, возвращаем наследование для weakrole:
maindb=> \c - postgres You are now connected to database "maindb" as user "postgres". maindb=# ALTER ROLE weakrole INHERIT; ALTER ROLE maindb=# \c - weakrole You are now connected to database "maindb" as user "weakrole". maindb=> SELECT * FROM mainschema.maintable; id ---- 1 2 3 (3 rows)
Содержимое таблицы отображается.
Теперь проверим доступ к атрибутам групповой роли. Под пользователем weakrole попробуем создать БД:
maindb=> CREATE DATABASE db; 2021-12-20 22:40:34.860 MSK [110129] ERROR: permission denied to create database 2021-12-20 22:40:34.860 MSK [110129] STATEMENT: CREATE DATABASE db; ERROR: permission denied to create database
Переключение SET ROLE
Нужно явно переключиться на grouprole:
maindb=> SELECT session_user, current_user; session_user | current_user --------------+-------------- weakrole | weakrole (1 row) maindb=> SET ROLE grouprole; SET maindb=> SELECT session_user, current_user; session_user | current_user --------------+-------------- weakrole | grouprole (1 row)
Снова пробую создать базу данных:
maindb=> CREATE DATABASE db; CREATE DATABASE
База данных создана успешно.
Возвращаемся в роль weakrole:
maindb=> RESET ROLE; RESET maindb=> SELECT session_user, current_user; session_user | current_user --------------+-------------- weakrole | weakrole (1 row)
И пробую удалить созданную БД:
maindb=> DROP DATABASE db; DROP DATABASE
Код команд без вывода
\du CREATE ROLE mainrole LOGIN; CREATE ROLE grouprole CREATEDB; CREATE ROLE slaverole LOGIN; CREATE ROLE weakrole NOINHERIT LOGIN; \du CREATE DATABASE maindb; \l \c maindb CREATE SCHEMA mainschema; \dn \dp SHOW search_path; SET search_path = mainschema, public; SHOW search_path; SELECT current_schemas(true); CREATE TABLE maintable (id integer); INSERT INTO maintable VALUES (1),(2),(3); SELECT * FROM maintable; \dp GRANT SELECT ON mainschema.maintable TO grouprole; GRANT USAGE ON SCHEMA mainschema TO grouprole; \dp GRANT grouprole TO slaverole; \du --REVOKE grouprole FROM slaverole; GRANT grouprole TO weakrole; \du \c - slaverole SELECT * FROM mainschema.maintable; \c - weakrole SELECT * FROM mainschema.maintable; \c - postgres ALTER ROLE weakrole INHERIT; \c - weakrole SELECT * FROM mainschema.maintable; CREATE DATABASE db; SELECT session_user, current_user; SET ROLE grouprole; SELECT session_user, current_user; CREATE DATABASE db; RESET ROLE; SELECT session_user, current_user; DROP DATABASE db;
Leave a Reply