Пошук уроків, статей та іншого контенту
Створите користувачів і ролі, налаштуєте атрибути облікових записів та зрозумієте рольову модель PostgreSQL.
У PostgreSQL поняття роль використовується для керування обліковими записами та групами доступу.
Роль може:
дозволяти вхід до сервера;
не дозволяти вхід, але об’єднувати права інших ролей;
мати атрибути, наприклад право створювати бази даних;
бути членом іншої ролі;
отримувати права через членство в ролях.
Ролі з атрибутом LOGIN зазвичай називають користувачами. Ролі без LOGIN часто використовують як групи для надання прав.
CREATE ROLE reporting;
CREATE ROLE olena WITH LOGIN PASSWORD 'StrongPassword123';У цьому прикладі:
reporting — роль-група, яка не може виконати вхід;
olena — користувач, оскільки має атрибут LOGIN.
Для створення користувача можна використати CREATE USER:
CREATE USER olena WITH PASSWORD 'StrongPassword123';Команда CREATE USER є скороченим записом для створення ролі з атрибутом LOGIN. Тобто ці команди мають однаковий результат:
CREATE USER ivan WITH PASSWORD 'AnotherPassword456';
CREATE ROLE petro WITH LOGIN PASSWORD 'AnotherPassword456';Пароль у прикладі призначений лише для навчання. У реальному проєкті використовуйте надійні унікальні паролі та не зберігайте їх у відкритому коді.
Після підключення до PostgreSQL можна перевірити список ролей за допомогою команди \du у psql:
\duЦе команда клієнта psql, а не SQL-команда.
Також ролі можна переглянути через системне подання pg_roles:
SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole
FROM pg_roles
ORDER BY rolname;Роль-група не призначена для безпосереднього входу. Вона використовується для об’єднання прав.
CREATE ROLE developers NOLOGIN;Користувачі можуть бути членами цієї ролі. Права, надані developers, можна буде застосувати до всіх її членів.
Наприклад, створимо користувача та додамо його до групи:
CREATE ROLE developers NOLOGIN;
CREATE ROLE olena WITH LOGIN PASSWORD 'StrongPassword123';
GRANT developers TO olena;Після цього olena є членом ролі developers.
Список членств можна переглянути так:
SELECT
member.rolname AS member_name,
parent.rolname AS role_name
FROM pg_auth_members
JOIN pg_roles AS member
ON member.oid = pg_auth_members.member
JOIN pg_roles AS parent
ON parent.oid = pg_auth_members.roleid
ORDER BY member_name, role_name;Атрибути визначають, що роль може робити на рівні сервера PostgreSQL.
LOGIN і NOLOGINLOGIN дозволяє ролі підключатися до PostgreSQL:
CREATE ROLE app_user WITH LOGIN PASSWORD 'AppPassword123';NOLOGIN забороняє безпосередній вхід:
CREATE ROLE app_readonly NOLOGIN;Ролі без LOGIN зазвичай використовують як групи доступу.
Змінити атрибут можна командою ALTER ROLE:
ALTER ROLE app_readonly LOGIN;
ALTER ROLE app_readonly NOLOGIN;Якщо роль отримує LOGIN, для неї бажано встановити пароль або інший налаштований спосіб автентифікації.
SUPERUSER і NOSUPERUSERСуперкористувач має майже необмежені права в кластері PostgreSQL:
CREATE ROLE administrator
WITH LOGIN PASSWORD 'AdminPassword123' SUPERUSER;Надання SUPERUSER є небезпечним. Звичайним користувачам його не слід призначати.
За замовчуванням нові ролі не є суперкористувачами:
CREATE ROLE analyst
WITH LOGIN PASSWORD 'AnalystPassword123'
NOSUPERUSER;Змінити атрибут може лише роль із відповідними адміністративними правами:
ALTER ROLE analyst SUPERUSER;
ALTER ROLE analyst NOSUPERUSER;На практиці краще залишати NOSUPERUSER і надавати лише необхідні права.
CREATEDB і NOCREATEDBАтрибут CREATEDB дозволяє створювати бази даних:
CREATE ROLE db_creator
WITH LOGIN PASSWORD 'CreatorPassword123'
CREATEDB;Щоб заборонити створення баз даних:
ALTER ROLE db_creator NOCREATEDB;CREATEROLE і NOCREATEROLECREATEROLE дозволяє створювати ролі та змінювати деякі властивості ролей:
CREATE ROLE role_manager
WITH LOGIN PASSWORD 'ManagerPassword123'
CREATEROLE;Це адміністративний атрибут. Його потрібно надавати обмеженій кількості службових облікових записів.
ALTER ROLE role_manager NOCREATEROLE;INHERIT і NOINHERITЗа замовчуванням роль має атрибут INHERIT. Це означає, що користувач автоматично отримує права ролей, членом яких він є.
CREATE ROLE readers NOLOGIN;
CREATE ROLE olena WITH LOGIN PASSWORD 'StrongPassword123' INHERIT;
GRANT readers TO olena;Якщо readers має певні права, olena зможе використовувати їх завдяки членству.
Атрибут NOINHERIT вимикає автоматичне використання прав членських ролей:
ALTER ROLE olena NOINHERIT;У такому разі для тимчасового переходу до іншої ролі можна використовувати SET ROLE, якщо членство це дозволяє:
SET ROLE readers;Повернутися до початкової ролі:
RESET ROLE;Для початківців зазвичай достатньо залишати INHERIT, якщо немає спеціальної потреби в ручному перемиканні ролей.
CONNECTION LIMITCONNECTION LIMIT обмежує кількість одночасних підключень ролі:
CREATE ROLE application_user
WITH LOGIN
PASSWORD 'ApplicationPassword123'
CONNECTION LIMIT 10;Змінити обмеження можна так:
ALTER ROLE application_user CONNECTION LIMIT 20;Значення -1 означає відсутність обмеження:
ALTER ROLE application_user CONNECTION LIMIT -1;VALID UNTILАтрибут VALID UNTIL встановлює дату й час, після яких пароль ролі не можна використовувати для входу:
CREATE ROLE temporary_user
WITH LOGIN
PASSWORD 'TemporaryPassword123'
VALID UNTIL '2026-12-31 23:59:59+00';Це зручно для тимчасових облікових записів.
Щоб пароль не мав обмеження за датою:
ALTER ROLE temporary_user VALID UNTIL 'infinity';Для зміни атрибутів використовується ALTER ROLE.
ALTER ROLE olena
LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
CONNECTION LIMIT 5;Пароль змінюється окремо:
ALTER ROLE olena PASSWORD 'NewStrongPassword789';Також можна змінити ім’я ролі:
ALTER ROLE olena RENAME TO olena_dev;Після перейменування потрібно враховувати нове ім’я під час підключення та керування членством.
Додати користувача до ролі можна командою GRANT:
CREATE ROLE reporting NOLOGIN;
CREATE ROLE olena WITH LOGIN PASSWORD 'StrongPassword123';
GRANT reporting TO olena;Видалити членство можна командою REVOKE:
REVOKE reporting FROM olena;Один користувач може бути членом кількох ролей:
CREATE ROLE analysts NOLOGIN;
CREATE ROLE developers NOLOGIN;
GRANT analysts TO olena;
GRANT developers TO olena;Таку модель зручно використовувати, коли ролі описують функції працівників, а не окремих людей:
analysts — права аналітиків;
developers — права розробників;
application_user — права застосунку.
Тоді для зміни доступу достатньо змінити членство або права групової ролі, а не налаштовувати кожного користувача окремо.
Нижче наведено послідовний приклад. Команди потрібно виконувати під роллю, яка має право створювати ролі й керувати їхнім членством.
-- Створюємо ролі-групи без можливості входу
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_writer NOLOGIN;
-- Створюємо користувачів із можливістю входу
CREATE ROLE olena
WITH LOGIN
PASSWORD 'OlenaPassword123'
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
CONNECTION LIMIT 5;
CREATE ROLE ivan
WITH LOGIN
PASSWORD 'IvanPassword123'
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
CONNECTION LIMIT 5;
-- Додаємо користувачів до відповідних груп
GRANT app_readonly TO olena;
GRANT app_writer TO ivan;
-- Перевіряємо властивості ролей
SELECT
rolname,
rolcanlogin,
rolsuper,
rolcreatedb,
rolcreaterole,
rolinherit,
rolconnlimit
FROM pg_roles
WHERE rolname IN ('app_readonly', 'app_writer', 'olena', 'ivan');Цей приклад створює дві групи та двох користувачів із мінімальними адміністративними можливостями.
Права на бази даних, схеми й таблиці надаються окремими командами GRANT. У цьому прикладі налаштовані лише ролі та членство між ними.
Після підключення можна дізнатися, під якою роллю виконується сесія:
SELECT current_user, session_user;session_user — роль, під якою було встановлено з’єднання;
current_user — роль, чиї права використовуються зараз.
Після SET ROLE значення current_user змінюється:
SET ROLE app_readonly;
SELECT current_user, session_user;
RESET ROLE;Для видалення ролі використовується DROP ROLE:
DROP ROLE temporary_user;Команда спрацює лише тоді, коли роль не має об’єктів або залежностей, які перешкоджають видаленню.
Якщо роль володіє об’єктами, спочатку потрібно передати їх іншому власнику або видалити. Також потрібно перевірити видані ролі права та членство.
Для видалення членства перед видаленням ролі використовується:
REVOKE app_readonly FROM olena;Не слід видаляти користувача лише для того, щоб заблокувати йому вхід. Для цього краще вимкнути LOGIN:
ALTER ROLE olena NOLOGIN;Так зберігаються налаштування ролі та її зв’язки, але підключення під цим обліковим записом стає неможливим.
SUPERUSER усім користувачамСуперкористувач обходить звичайну систему перевірки прав. Не використовуйте SUPERUSER як спосіб швидко виправити проблеми з доступом.
Краще надати конкретні права потрібній груповій ролі.
У PostgreSQL користувач — це роль із LOGIN. Роль без LOGIN не може використовуватися для звичайного входу.
CREATE ROLE reports NOLOGIN;
CREATE ROLE report_user LOGIN PASSWORD 'ReportPassword123';
GRANT reports TO report_user;Пароль можна змінити адміністративною командою:
ALTER ROLE report_user PASSWORD 'NewReportPassword456';Якщо однакові права потрібні кільком користувачам, створіть групову роль і додайте користувачів до неї. Це спрощує підтримку та зменшує кількість помилок.
NOLOGIN у групової роліЯкщо роль використовується лише як група, явно створюйте її з NOLOGIN:
CREATE ROLE reporting NOLOGIN;Створення ролей і зміна важливих атрибутів потребують відповідних прав. Якщо PostgreSQL повідомляє про недостатні привілеї, потрібно підключитися під роллю з необхідними адміністративними можливостями.
У PostgreSQL користувачі та групи реалізовані через єдиний об’єкт — роль.
Роль із LOGIN може підключатися до сервера й зазвичай називається користувачем.
Роль із NOLOGIN зручно використовувати як групу прав.
Основні атрибути ролі: LOGIN, SUPERUSER, CREATEDB, CREATEROLE, INHERIT, CONNECTION LIMIT і VALID UNTIL.
GRANT role_name TO user_name додає користувача до ролі.
REVOKE role_name FROM user_name видаляє членство.
Для блокування входу достатньо виконати ALTER ROLE ... NOLOGIN.
Адміністративні атрибути слід надавати лише тоді, коли вони справді потрібні.