Пошук уроків, статей та іншого контенту
Керуватимете доступом до об’єктів бази даних за допомогою GRANT, REVOKE і привілеїв за замовчуванням.
У PostgreSQL доступ до об’єктів бази даних визначається привілеями. Привілей — це дозвіл виконувати певну дію над конкретним об’єктом.
Об’єктами можуть бути:
база даних;
схема;
таблиця;
послідовність;
функція;
представлення.
Користувачі PostgreSQL також є ролями. Роль може:
входити до бази даних;
читати дані;
змінювати дані;
створювати об’єкти;
отримувати права через членство в іншій ролі.
Власник об’єкта має повний контроль над ним і може передавати привілеї іншим ролям.
Команда GRANT надає ролі один або кілька привілеїв.
Загальний синтаксис:
GRANT привілей [, привілей ...]
ON тип_об’єкта об’єкт
TO роль [, роль ...];Наприклад:
GRANT SELECT
ON TABLE app.orders
TO report_reader;Тепер роль report_reader може читати таблицю app.orders.
| Привілей | Дія | |---|---| | SELECT | читання даних | | INSERT | додавання рядків | | UPDATE | оновлення рядків | | DELETE | видалення рядків | | TRUNCATE | очищення таблиці | | REFERENCES | створення зовнішніх ключів, що посилаються на таблицю | | TRIGGER | створення тригерів | | ALL PRIVILEGES | усі доступні привілеї |
Для початку зазвичай надають лише необхідні права. Наприклад, ролі для звітів достатньо SELECT, а не ALL PRIVILEGES.
GRANT SELECT, INSERT, UPDATE
ON TABLE app.orders
TO order_manager;Для звернення до таблиці в схемі роль зазвичай має отримати також привілей USAGE на цю схему:
GRANT USAGE
ON SCHEMA app
TO report_reader;Без USAGE на схему роль може мати SELECT на таблицю, але все одно не зможе звернутися до неї через ім’я app.orders.
Отже, для читання таблиці в окремій схемі часто потрібні два дозволи:
GRANT USAGE ON SCHEMA app TO report_reader;
GRANT SELECT ON TABLE app.orders TO report_reader;Щоб підключитися до бази даних, роль повинна мати привілей CONNECT:
GRANT CONNECT
ON DATABASE shop
TO report_reader;Це право лише дозволяє встановити підключення. Воно не надає доступу до таблиць.
Якщо потрібно надати доступ до всіх уже існуючих таблиць у схемі:
GRANT USAGE
ON SCHEMA app
TO report_reader;
GRANT SELECT
ON ALL TABLES IN SCHEMA app
TO report_reader;Команда ON ALL TABLES IN SCHEMA охоплює тільки таблиці, які вже існують. Для майбутніх таблиць потрібні привілеї за замовчуванням.
Команда REVOKE відкликає раніше надані привілеї.
REVOKE INSERT
ON TABLE app.orders
FROM report_reader;Після цього роль report_reader більше не може додавати рядки до app.orders, але може продовжувати читати її, якщо має SELECT.
Щоб відкликати всі привілеї:
REVOKE ALL PRIVILEGES
ON TABLE app.orders
FROM report_reader;Права на різні об’єкти відкликаються окремо:
REVOKE USAGE ON SCHEMA app FROM report_reader;
REVOKE SELECT ON TABLE app.orders FROM report_reader;REVOKE відкликає привілей лише з указаного джерела. Якщо роль отримала такий самий доступ через членство в іншій ролі, доступ може залишитися.
PUBLICPUBLIC — це спеціальна назва, що означає «усі ролі».
Наприклад:
GRANT SELECT
ON TABLE app.orders
TO PUBLIC;Тепер читати таблицю зможуть усі ролі, які можуть підключитися до бази даних і мають доступ до схеми.
Щоб прибрати такий загальний доступ:
REVOKE SELECT
ON TABLE app.orders
FROM PUBLIC;Під час налаштування дозволів важливо перевіряти права не лише безпосередньо ролі, а й PUBLIC.
Зручніше надавати права не кожному користувачу окремо, а створити роль із певними дозволами:
CREATE ROLE report_reader;
GRANT USAGE ON SCHEMA app TO report_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO report_reader;Потім користувачів можна додати до цієї ролі:
GRANT report_reader TO alice;
GRANT report_reader TO bob;Тепер alice і bob отримують права ролі report_reader.
Щоб забрати членство:
REVOKE report_reader FROM bob;Такий підхід спрощує адміністрування: дозволи змінюються в одному місці, а не для кожного користувача окремо.
Звичайний GRANT діє на вже існуючі об’єкти. Якщо після цього створити нову таблицю, вона не обов’язково автоматично отримає такі самі дозволи.
Для майбутніх об’єктів використовують ALTER DEFAULT PRIVILEGES.
Наприклад, щоб усі нові таблиці, створені роллю app_owner у схемі app, були доступні для читання ролі report_reader:
ALTER DEFAULT PRIVILEGES
FOR ROLE app_owner
IN SCHEMA app
GRANT SELECT ON TABLES TO report_reader;Після цього нова таблиця отримає SELECT автоматично:
CREATE TABLE app.customers (
id integer PRIMARY KEY,
full_name text NOT NULL
);Роль report_reader зможе виконати:
SELECT *
FROM app.customers;Але ALTER DEFAULT PRIVILEGES не змінює права вже існуючих таблиць. Для них потрібно виконати звичайний GRANT:
GRANT SELECT
ON ALL TABLES IN SCHEMA app
TO report_reader;Привілеї за замовчуванням належать ролі, яка створює об’єкти.
Наприклад, якщо таблицю створює app_owner, потрібно налаштувати:
ALTER DEFAULT PRIVILEGES
FOR ROLE app_owner
IN SCHEMA app
GRANT SELECT ON TABLES TO report_reader;Якщо таблицю створює інша роль, для неї потрібно налаштувати окремі default privileges.
Щоб нові таблиці більше не отримували певний дозвіл:
ALTER DEFAULT PRIVILEGES
FOR ROLE app_owner
IN SCHEMA app
REVOKE SELECT ON TABLES FROM report_reader;Це не відкликає SELECT із уже існуючих таблиць. Для них потрібен окремий REVOKE.
Нижче наведено навчальний приклад. Команди створення бази даних і ролей потрібно виконувати з адміністративними правами. Назву permissions_demo можна замінити на назву власної бази даних.
-- Виконати з правами адміністратора PostgreSQL
CREATE DATABASE permissions_demo;
-- У psql підключитися до створеної бази даних
-- \connect permissions_demo
-- Створюємо роль-власника об’єктів і роль для читання звітів
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE report_reader LOGIN;
-- Дозволяємо підключення до бази даних
GRANT CONNECT
ON DATABASE permissions_demo
TO report_reader;
-- Перемикаємося на роль, яка створюватиме об’єкти
SET ROLE app_owner;
-- Створюємо схему та таблицю
CREATE SCHEMA app AUTHORIZATION app_owner;
CREATE TABLE app.orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_name text NOT NULL,
total numeric(10, 2) NOT NULL
);
INSERT INTO app.orders (customer_name, total)
VALUES
('Олена', 1250.00),
('Андрій', 830.50);
-- Надаємо ролі для звітів доступ до схеми та існуючої таблиці
GRANT USAGE
ON SCHEMA app
TO report_reader;
GRANT SELECT
ON TABLE app.orders
TO report_reader;
-- Налаштовуємо SELECT для всіх майбутніх таблиць цієї ролі в цій схемі
ALTER DEFAULT PRIVILEGES
IN SCHEMA app
GRANT SELECT ON TABLES TO report_reader;
-- Створюємо нову таблицю після налаштування default privileges
CREATE TABLE app.customers (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name text NOT NULL
);
-- Повертаємо початкову роль
RESET ROLE;
-- Адміністратор може перевірити, що роль має читати обидві таблиці
SET ROLE report_reader;
SELECT *
FROM app.orders;
SELECT *
FROM app.customers;
-- Ця команда повинна завершитися помилкою:
-- роль не має права додавати рядки
-- INSERT INTO app.orders (customer_name, total)
-- VALUES ('Марія', 400.00);
RESET ROLE;У реальному середовищі для ролі з LOGIN також налаштовують спосіб автентифікації та пароль відповідно до політики безпеки. Не варто використовувати прості паролі або передавати зайві права.
У psql можна переглянути права на таблиці командою:
\dp app.ordersДля схем:
\dn+ appДля ролей:
\duТакож можна перевірити ефективний доступ спеціальними функціями:
SELECT has_table_privilege(
'report_reader',
'app.orders',
'SELECT'
);Результат буде true, якщо роль має відповідний привілей, і false, якщо не має.
Перевірка доступу до схеми:
SELECT has_schema_privilege(
'report_reader',
'app',
'USAGE'
);Для ролі, якій потрібно лише читати дані, зручно діяти так:
Надати CONNECT на базу даних.
Надати USAGE на потрібну схему.
Надати SELECT на існуючі таблиці.
Налаштувати ALTER DEFAULT PRIVILEGES для майбутніх таблиць.
Перевірити доступ від імені цієї ролі.
Не надавати INSERT, UPDATE, DELETE або ALL PRIVILEGES, якщо вони не потрібні.
Приклад:
GRANT CONNECT ON DATABASE shop TO report_reader;
GRANT USAGE ON SCHEMA app TO report_reader;
GRANT SELECT
ON ALL TABLES IN SCHEMA app
TO report_reader;
ALTER DEFAULT PRIVILEGES
IN SCHEMA app
GRANT SELECT ON TABLES TO report_reader;GRANT SELECT ON TABLE app.orders TO report_reader;Цього може бути недостатньо. Додайте:
GRANT USAGE ON SCHEMA app TO report_reader;GRANT діятиме на майбутні таблиціGRANT SELECT ON ALL TABLES IN SCHEMA app TO report_reader;Ця команда стосується лише вже існуючих таблиць. Для майбутніх таблиць потрібен ALTER DEFAULT PRIVILEGES.
Якщо таблиці створює app_owner, а default privileges налаштовані для іншої ролі, автоматичний доступ не буде наданий.
Потрібно вказати роль-власника об’єктів:
ALTER DEFAULT PRIVILEGES
FOR ROLE app_owner
IN SCHEMA app
GRANT SELECT ON TABLES TO report_reader;Якщо користувач отримує доступ через групову роль, команда:
REVOKE SELECT ON app.orders FROM alice;може не прибрати доступ. Потрібно також перевірити членство alice в інших ролях і права PUBLIC.
ALL PRIVILEGES без потребиGRANT ALL PRIVILEGES
ON TABLE app.orders
TO report_reader;Це надає значно ширший доступ, ніж потрібно ролі для звітів. Краще використовувати мінімальний набір:
GRANT SELECT
ON TABLE app.orders
TO report_reader;GRANT надає привілеї.
REVOKE відкликає привілеї.
Для доступу до таблиці у схемі зазвичай потрібні USAGE на схему та привілей на таблицю.
CONNECT дозволяє підключитися до бази даних, але не дає доступу до її таблиць.
PUBLIC означає всі ролі.
GRANT ... ON ALL TABLES IN SCHEMA діє лише на вже існуючі таблиці.
ALTER DEFAULT PRIVILEGES налаштовує права для майбутніх об’єктів.
Default privileges залежать від ролі, яка створює об’єкти.
Найбезпечніше надавати лише ті привілеї, які справді потрібні.