Зміна структури та керування доступом

Створена таблиця майже ніколи не залишається незмінною назавжди. У системі з'являються нові вимоги, уточнюються типи даних, додаються правила цілісності. Одночасно не кожен користувач повинен мати однакові можливості: наприклад, студент може читати каталог курсів, редактор - змінювати його, а адміністратор - керувати структурою.

У цій лекції розглянемо, як змінювати схему PostgreSQL без зайвого ризику та як надавати ролям лише необхідні права.

Цілі лекції

Після опрацювання матеріалу ви зможете:

• застосовувати основні варіанти ALTER TABLE;
• планувати безпечну послідовність зміни таблиці з наявними даними;
• пояснювати різницю між DROP TABLE і TRUNCATE;
• враховувати залежності та небезпеку CASCADE;
• надавати й скасовувати права командами GRANT і REVOKE;
• пояснювати призначення ролей і принцип найменших привілеїв;
• описувати міграцію схеми як контрольовану версію змін, не прив'язуючись до конкретного інструмента.

Передумови

Потрібні базові знання CREATE TABLE, типів PostgreSQL та обмежень PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT, NOT NULL. Приклади можна виконувати в PostgreSQL через psql, pgAdmin або DBeaver.

1. Навчальна схема

Подальші приклади використовують окрему схему db11_demo, щоб не змішувати навчальні об'єкти з іншими таблицями. Наступний блок можна виконати повністю в порожній або навчальній базі:

DROP SCHEMA IF EXISTS db11_demo CASCADE;
CREATE SCHEMA db11_demo;

CREATE TABLE db11_demo.departments (
    department_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE db11_demo.courses (
    course_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    hours integer NOT NULL CHECK (hours > 0),
    department_id integer REFERENCES db11_demo.departments(department_id)
);

INSERT INTO db11_demo.departments (name)
VALUES ('Програмна інженерія'), ('Комп\'ютерні системи');

INSERT INTO db11_demo.courses (title, hours, department_id)
VALUES
    ('Бази даних', 150, 1),
    ('Комп\'ютерні мережі', 120, 2);

Перший DROP SCHEMA призначений лише для повторного запуску навчального прикладу: він навмисно видаляє попередню демонстраційну схему. У робочій базі не можна застосовувати такий шаблон без аналізу наслідків.

2. ALTER TABLE: зміна структури таблиці

Загальна форма команди:

ALTER TABLE schema_name.table_name
    дія;

Одна команда змінює визначення наявної таблиці. Конкретна дія вказує, що саме потрібно додати, перейменувати, перетворити або видалити.

2.1. Додавання стовпця

Додамо необов'язковий опис курсу:

ALTER TABLE db11_demo.courses
ADD COLUMN description text;

Наявні рядки отримають NULL, бо значення ще не визначені. Якщо для нових рядків потрібне стандартне значення, можна задати DEFAULT:

ALTER TABLE db11_demo.courses
ADD COLUMN status text DEFAULT 'planned';

DEFAULT визначає значення для майбутніх вставок, у яких стовпець не вказано. Це не забороняє явно записати інше значення або NULL, доки немає відповідного обмеження.

2.2. Перейменування стовпця й таблиці

ALTER TABLE db11_demo.courses
RENAME COLUMN title TO course_title;

ALTER TABLE db11_demo.courses
RENAME TO study_courses;

Після перейменування повне ім'я таблиці - db11_demo.study_courses. PostgreSQL оновлює внутрішні залежності, наприклад визначення представлень, але текст зовнішньої програми або SQL-файла самостійно не перепише. Перед перейменуванням потрібно знайти код, який використовує старе ім'я.

Щоб наступні приклади знову використовували початкові назви, повернемо їх:

ALTER TABLE db11_demo.study_courses
RENAME TO courses;

ALTER TABLE db11_demo.courses
RENAME COLUMN course_title TO title;

2.3. Зміна типу даних

PostgreSQL може виконати просте сумісне перетворення автоматично:

ALTER TABLE db11_demo.courses
ALTER COLUMN hours TYPE bigint;

Якщо автоматичного перетворення немає або потрібне власне правило, застосовують USING. Наприклад, перетворимо текстовий статус на логічну ознаку активності:

ALTER TABLE db11_demo.courses
ALTER COLUMN status TYPE boolean
USING status = 'active';

Результатом виразу status = 'active' є true або false. Перед такою зміною треба перевірити всі наявні значення та вирішити, чи не втрачається важливий зміст. Зміна типу може переписувати таблицю й блокувати роботу з нею, тому для великої таблиці її планують окремо.

2.4. DEFAULT і NOT NULL

Стандартне значення можна встановити або прибрати:

ALTER TABLE db11_demo.courses
ALTER COLUMN description SET DEFAULT 'Опис готується';

ALTER TABLE db11_demo.courses
ALTER COLUMN description DROP DEFAULT;

Спроба одразу встановити NOT NULL завершиться помилкою, якщо хоча б один наявний рядок містить NULL. Безпечна базова послідовність така:

UPDATE db11_demo.courses
SET description = 'Опис готується'
WHERE description IS NULL;

SELECT count(*) AS rows_without_description
FROM db11_demo.courses
WHERE description IS NULL;

ALTER TABLE db11_demo.courses
ALTER COLUMN description SET NOT NULL;

Спочатку заповнюємо пропуски, потім перевіряємо результат і лише після цього посилюємо обмеження. За потреби обмеження можна послабити:

ALTER TABLE db11_demo.courses
ALTER COLUMN description DROP NOT NULL;

2.5. Додавання, перейменування й видалення обмежень

Дамо обмеженню змістовне ім'я:

ALTER TABLE db11_demo.courses
ADD CONSTRAINT courses_status_check
CHECK (status IN (true, false));

Для типу boolean така перевірка навчальна й фактично не звужує допустимі ненульові значення. Важлива тут форма ADD CONSTRAINT. Перейменування і видалення виконують так:

ALTER TABLE db11_demo.courses
RENAME CONSTRAINT courses_status_check TO courses_status_valid;

ALTER TABLE db11_demo.courses
DROP CONSTRAINT courses_status_valid;

Для великої таблиці PostgreSQL дає змогу додати деякі перевірки без негайної перевірки старих рядків, а потім перевірити їх окремим кроком:

ALTER TABLE db11_demo.courses
ADD CONSTRAINT courses_hours_limit
CHECK (hours <= 1000) NOT VALID;

ALTER TABLE db11_demo.courses
VALIDATE CONSTRAINT courses_hours_limit;

Після NOT VALID нові або змінені рядки вже мають відповідати правилу, але старі дані ще не підтверджені. Лише успішний VALIDATE CONSTRAINT засвідчує, що перевірку пройшла вся таблиця.

2.6. Видалення стовпця

ALTER TABLE db11_demo.courses
DROP COLUMN description;

Разом зі стовпцем втрачаються його значення. Якщо від нього залежить інший об'єкт, PostgreSQL відхилить звичайну команду. Варіант DROP COLUMN ... CASCADE може видалити залежні об'єкти, тому його не слід додавати лише для того, щоб «помилка зникла».

3. Безпечна еволюція схеми

Еволюція схеми - це послідовна зміна структури бази зі збереженням потрібних даних і працездатності системи. Синтаксично правильна команда ще не гарантує безпечної зміни.

Перед зміною варто відповісти на запитання:

1. Які дані вже є в таблиці?
2. Які представлення, обмеження, зовнішні ключі та програми залежать від об'єкта?
3. Чи сумісний старий код із новою схемою?
4. Чи потребуватиме команда тривалого блокування або переписування таблиці?
5. Як перевірити результат і як повернутися до попереднього стану?
6. Чи є актуальна резервна копія та перевірена процедура відновлення для критичної зміни?

Приклад поетапної зміни

Потрібно додати до заповненої таблиці обов'язковий код курсу. Небезпечна спроба виглядає так:

-- Помилка для таблиці з рядками: старі записи не мають значення code.
ALTER TABLE db11_demo.courses
ADD COLUMN code text NOT NULL;

Безпечніша послідовність розділяє зміну на етапи:

-- 1. Розширення: новий стовпець поки необов'язковий.
ALTER TABLE db11_demo.courses
ADD COLUMN code text;

-- 2. Заповнення наявних рядків.
UPDATE db11_demo.courses
SET code = 'COURSE-' || course_id
WHERE code IS NULL;

-- 3. Перевірка результату.
SELECT course_id, title, code
FROM db11_demo.courses
ORDER BY course_id;

-- 4. Посилення правил.
ALTER TABLE db11_demo.courses
ALTER COLUMN code SET NOT NULL;

ALTER TABLE db11_demo.courses
ADD CONSTRAINT courses_code_unique UNIQUE (code);

У реальному застосунку між етапами може бути випущена версія коду, яка вже записує code, але ще вміє працювати зі старою схемою. Після заповнення та перевірки даних обмеження посилюють, а застарілі елементи видаляють окремою зміною.

Міграції як журнал змін

Міграція схеми - це збережений і пронумерований набір команд, який переводить базу з однієї відомої версії структури до наступної. Міграції зберігають разом із кодом, перевіряють на тестовій базі та застосовують у визначеному порядку. Спеціальні інструменти можуть вести облік виконаних версій, але в цій лекції важливий сам принцип, а не налаштування Flyway, Liquibase чи іншого засобу.

Навіть якщо PostgreSQL дозволяє відкотити багато DDL-команд транзакцією, це не замінює плану відновлення: зовнішній код, тривалі операції та зміни даних можуть ускладнити повернення.

4. DROP TABLE і TRUNCATE

Обидві команди можуть призвести до втрати всіх рядків, але змінюють базу по-різному.

Створимо тимчасову навчальну таблицю:

CREATE TABLE db11_demo.import_buffer (
    row_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    raw_value text NOT NULL
);

INSERT INTO db11_demo.import_buffer (raw_value)
VALUES ('рядок 1'), ('рядок 2');

TRUNCATE TABLE db11_demo.import_buffer RESTART IDENTITY;

INSERT INTO db11_demo.import_buffer (raw_value)
VALUES ('новий рядок');

SELECT * FROM db11_demo.import_buffer;

Після TRUNCATE таблиця існує, а через RESTART IDENTITY новий row_id знову починається з початкового значення. TRUNCATE не приймає WHERE: це операція очищення всієї таблиці. Якщо треба видалити лише частину рядків, застосовують DELETE ... WHERE, який належить до DML.

У PostgreSQL TRUNCATE можна відкотити, якщо виконати його всередині явної транзакції та зробити ROLLBACK:

BEGIN;
TRUNCATE TABLE db11_demo.import_buffer;
SELECT count(*) FROM db11_demo.import_buffer;
ROLLBACK;

SELECT count(*) FROM db11_demo.import_buffer;

Однак можливість відкату не робить команду безпечною автоматично. Вона бере сильне блокування, а випадковий COMMIT закріпить очищення.

Тепер видалимо таблицю як об'єкт:

DROP TABLE db11_demo.import_buffer;

Наступний SELECT із неї вже завершився б помилкою, бо таблиці не існує.

5. Залежності, RESTRICT і обережність із CASCADE

Об'єкти бази пов'язані залежностями. Представлення може використовувати таблицю, зовнішній ключ - посилатися на неї, а обмеження чи індекс - належати їй.

CREATE VIEW db11_demo.course_catalog AS
SELECT course_id, title, hours
FROM db11_demo.courses;

Спроба видалити таблицю без урахування представлення буде відхилена:

DROP TABLE db11_demo.courses RESTRICT;

RESTRICT означає: не виконувати видалення, якщо є залежні об'єкти. Для DROP TABLE така поведінка є типовою навіть без явного слова RESTRICT.

Команда нижче прибрала б і таблицю, і залежне представлення:

-- Не виконуйте в основній демонстрації: команда руйнує таблицю courses.
DROP TABLE db11_demo.courses CASCADE;

CASCADE не означає «безпечне виправлення залежностей». Воно означає «поширити видалення на залежні об'єкти». Перед його використанням треба прочитати повідомлення PostgreSQL, перевірити залежності та явно погодити кожен наслідок. Часто правильніше спочатку змінити або видалити залежний об'єкт, а потім виконати заплановану DDL-команду.

Для продовження прикладів видалимо лише створене представлення:

DROP VIEW db11_demo.course_catalog;

6. DCL: ролі та права доступу

DCL (Data Control Language) охоплює керування доступом. У PostgreSQL користувачі й групи прав представлені ролями. Роль із атрибутом LOGIN може входити до сервера; роль без LOGIN зручно використовувати як набір привілеїв для інших ролей.

Наступні команди створення ролей вимагають адміністративного атрибута CREATEROLE або вищих повноважень. Виконуйте їх лише на власному навчальному сервері під відповідним обліковим записом:

CREATE ROLE db11_reader NOLOGIN;
CREATE ROLE db11_editor NOLOGIN;
CREATE ROLE db11_student LOGIN PASSWORD 'change_me_in_training';

Не використовуйте навчальний пароль у реальній системі й не зберігайте справжні паролі у відкритих SQL-файлах.

6.1. GRANT: надання привілеїв

Щоб роль могла звернутися до об'єктів у схемі, їй потрібне право USAGE на схему та відповідні права на таблиці:

GRANT USAGE ON SCHEMA db11_demo TO db11_reader, db11_editor;

GRANT SELECT
ON TABLE db11_demo.courses, db11_demo.departments
TO db11_reader;

GRANT SELECT, INSERT, UPDATE
ON TABLE db11_demo.courses
TO db11_editor;

Надамо ролі входу набір читацьких прав через членство:

GRANT db11_reader TO db11_student;

Тепер db11_student успадковує привілеї db11_reader і може читати вказані таблиці, але не отримує автоматично права змінювати їхню структуру.

Для таблиці з identity-стовпцем роль, яка виконує INSERT, може також потребувати прав на пов'язану послідовність. У нашому прикладі їх можна надати так:

GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA db11_demo
TO db11_editor;

Можна обмежити оновлення окремими стовпцями:

GRANT UPDATE (title, hours)
ON TABLE db11_demo.courses
TO db11_editor;

Право на всю таблицю ширше за право на перелічені стовпці. Якщо раніше вже надано загальне UPDATE, стовпцеве обмеження саме по собі його не звузить: спочатку потрібно скасувати ширше право.

6.2. REVOKE: скасування привілеїв і членства

REVOKE INSERT
ON TABLE db11_demo.courses
FROM db11_editor;

REVOKE db11_reader FROM db11_student;

Перша команда скасовує конкретний привілей на таблицю, друга - членство в ролі. REVOKE прибирає право, надане цим шляхом, але роль може все ще мати таке саме право через інше членство, через PUBLIC або як власник об'єкта. Тому результат перевіряють, а не припускають.

Ключове слово PUBLIC означає всі ролі. Наприклад, для явного закриття доступу до схеми можна застосувати:

REVOKE ALL ON SCHEMA db11_demo FROM PUBLIC;

Після цього потрібні права надають конкретним робочим ролям. Власник об'єкта має особливі можливості керування ним, тому для прикладних підключень зазвичай не використовують роль-власника.

6.3. Принцип найменших привілеїв

Принцип найменших привілеїв вимагає надати ролі лише ті можливості, які потрібні для її поточної задачі, і не більше.

Практичний порядок:

1. визначити задачі: читання, внесення даних, адміністрування;
2. створити групові ролі без LOGIN для наборів прав;
3. надати права на конкретні схеми, таблиці й операції;
4. включити ролі входу до потрібних групових ролей;
5. регулярно перевіряти й скасовувати зайві права.

Не варто видавати ALL PRIVILEGES, якщо застосунок виконує лише SELECT. Не варто підключати звичайний застосунок як власника таблиць або суперкористувача. Обмежені права зменшують наслідки помилки в коді, викраденого пароля чи SQL-ін'єкції.

Права на вже наявні таблиці не поширюються автоматично на всі майбутні таблиці. Для цього власник може окремо налаштувати ALTER DEFAULT PRIVILEGES, але це адміністративна тема, яку детальніше буде розглянуто пізніше.

Практичні завдання

Завдання 1. Розширення таблиці без втрати даних

Відновіть навчальну схему з розділу 1. Додайте до db11_demo.courses стовпець level типу text. Для наявних рядків установіть значення beginner, перевірте відсутність NULL, установіть NOT NULL і додайте обмеження, яке дозволяє лише beginner, intermediate або advanced. Покажіть фінальну структуру або SQL-команди та результат SELECT.

Завдання 2. Перевірка різниці між очищенням і видаленням

Створіть таблицю db11_demo.sandbox з identity-ідентифікатором і текстовим значенням. Додайте два рядки. Усередині транзакції виконайте TRUNCATE ... RESTART IDENTITY, перевірте нульову кількість рядків і зробіть ROLLBACK. Переконайтеся, що рядки повернулися. Після цього видаліть таблицю командою DROP TABLE. Одним реченням поясніть різницю результатів.

Завдання 3. Матриця мінімальних прав

Для каталогу курсів потрібні три ролі: catalog_reader лише читає courses і departments; catalog_editor читає обидві таблиці та змінює title і hours у courses; catalog_admin керує структурою навчальної схеми. Складіть SQL із CREATE ROLE, GRANT і, де потрібно, REVOKE. Позначте команди, для яких потрібні адміністративні повноваження. Поясніть, чому не слід просто видати всім ролям ALL PRIVILEGES.

Підсумок

• ALTER TABLE додає, перейменовує, змінює й видаляє елементи структури таблиці.
• Для таблиці з даними зміни виконують поетапно: розширення, заповнення, перевірка, посилення правил і лише потім прибирання застарілих елементів.
• Зміна типу, перевірка обмежень і деякі DDL-команди можуть бути тривалими та блокувати таблицю.
• TRUNCATE видаляє всі рядки, але залишає таблицю; DROP TABLE видаляє сам об'єкт разом із даними.
• RESTRICT зупиняє небезпечне видалення за наявності залежностей; CASCADE поширює видалення й потребує свідомої перевірки наслідків.
• У PostgreSQL доступ організовують через ролі, GRANT і REVOKE.
• Принцип найменших привілеїв вимагає давати кожній ролі лише права, необхідні для її задач.
• Міграції фіксують і впорядковують версії зміни схеми; конкретні інструменти міграцій вивчатимуться окремо.