Пошук уроків, статей та іншого контенту
Організуєте об’єкти в схемах, налаштуєте search_path і розмежуєте простори імен та доступ.
Схема в PostgreSQL — це іменований простір для об’єктів бази даних. У схемі можуть зберігатися:
таблиці;
подання;
послідовності;
функції;
типи даних;
інші об’єкти PostgreSQL.
Одна база даних може містити багато схем. Схема належить конкретній базі даних, тому одна й та сама схема недоступна безпосередньо з іншої бази даних.
Повне ім’я об’єкта записують через крапку:
назва_схеми.назва_об’єктаНаприклад:
sales.ordersТут sales — назва схеми, а orders — назва таблиці.
У різних схемах можуть існувати об’єкти з однаковими іменами:
sales.orders
archive.ordersЦе дві різні таблиці.
Створити схему можна командою CREATE SCHEMA:
CREATE SCHEMA sales;За замовчуванням схему створює поточний користувач, і він стає її власником.
Щоб видалити схему:
DROP SCHEMA sales;Якщо схема містить об’єкти, PostgreSQL не видалить її без додаткової вказівки. Для видалення схеми разом з усім її вмістом використовують CASCADE:
DROP SCHEMA sales CASCADE;CASCADE потрібно використовувати обережно, оскільки таблиці, подання та інші залежні об’єкти будуть видалені.
Якщо потрібно створити схему лише тоді, коли її ще немає:
CREATE SCHEMA IF NOT EXISTS sales;Таблицю можна створити, вказавши схему явно:
CREATE TABLE sales.orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_name text NOT NULL,
total numeric(10, 2) NOT NULL
);Звернення до таблиці також може бути повним:
INSERT INTO sales.orders (customer_name, total)
VALUES ('Олена', 1250.00);
SELECT *
FROM sales.orders;Такий запис однозначний: PostgreSQL точно знає, у якій схемі шукати таблицю.
publicУ кожній новій базі даних зазвичай існує схема public. Вона часто використовується як стандартне місце для об’єктів, якщо схему не вказано явно.
Наприклад:
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);Така таблиця створюється в схемі, яка першою підходить для створення об’єктів у поточному search_path. У типовій конфігурації це часто схема public.
Явне зазначення схеми робить код зрозумілішим:
CREATE TABLE public.products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);Для великих або спільних проєктів краще не покладатися лише на public, а створювати окремі схеми за призначенням.
Наприклад:
app — таблиці застосунку;
reporting — об’єкти для звітів;
archive — архівні таблиці.
search_pathsearch_path — це список схем, у яких PostgreSQL шукає об’єкти, якщо в запиті не вказано схему явно.
Переглянути поточне значення можна так:
SHOW search_path;Приклад зміни шляху пошуку:
SET search_path TO sales, public;Після цього запит:
SELECT *
FROM orders;спочатку шукатиме таблицю sales.orders, а потім — public.orders.
Те саме можна записати однозначно:
SELECT *
FROM sales.orders;Порядок схем має значення. Якщо в кількох схемах є таблиці з однаковим іменем, PostgreSQL використає таблицю з першої схеми в search_path, де її знайде.
Повернути стандартне значення параметра можна командою:
RESET search_path;SETКоманда SET змінює параметр для поточного підключення до бази даних:
SET search_path TO sales, public;Зміна діє, доки:
підключення не буде закрито;
не буде виконано RESET;
не буде встановлено інше значення.
Для зміни лише в межах поточної транзакції використовують SET LOCAL:
BEGIN;
SET LOCAL search_path TO sales, public;
SELECT *
FROM orders;
COMMIT;Після завершення транзакції тимчасове значення зникає.
Нижче наведено повний приклад створення схеми, таблиці та роботи з search_path.
-- Видаляємо навчальну схему, якщо вона вже існує
DROP SCHEMA IF EXISTS lesson_shop CASCADE;
-- Створюємо окрему схему для таблиць магазину
CREATE SCHEMA lesson_shop;
-- Створюємо таблицю з повним ім'ям
CREATE TABLE lesson_shop.products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
price numeric(10, 2) NOT NULL CHECK (price >= 0)
);
-- Додаємо приклади товарів
INSERT INTO lesson_shop.products (name, price)
VALUES
('Клавіатура', 1800.00),
('Миша', 950.00);
-- Звертаємося до таблиці через повне ім'я
SELECT *
FROM lesson_shop.products;
-- Додаємо схему до шляху пошуку
SET search_path TO lesson_shop, public;
-- Тепер назву схеми можна не вказувати
SELECT *
FROM products;
-- Переглядаємо поточний шлях пошуку
SHOW search_path;
-- Повертаємо попереднє значення
RESET search_path;У цьому прикладі lesson_shop.products і products після SET search_path — це звернення до тієї самої таблиці.
Схема створює окремий простір імен для об’єктів. Це означає, що назви об’єктів потрібно розглядати разом із назвою схеми.
Наприклад, ці таблиці не конфліктують:
CREATE TABLE lesson_shop.products (
id integer
);
CREATE TABLE archive.products (
id integer
);До них можна звертатися окремо:
SELECT *
FROM lesson_shop.products;
SELECT *
FROM archive.products;Якщо звернутися лише до products, PostgreSQL використає порядок схем із search_path. Тому в коді, де важлива точність, краще вказувати схему явно.
Схема допомагає розділити простори імен, але сама по собі не є повною системою захисту. Для роботи з об’єктами користувач повинен мати відповідні привілеї.
Основні привілеї для схеми:
USAGE — дозволяє звертатися до об’єктів схеми, якщо користувач має права на самі об’єкти;
CREATE — дозволяє створювати об’єкти в схемі.
Наприклад, адміністратор може надати користувачу доступ до схеми:
GRANT USAGE ON SCHEMA reporting TO analyst;А потім дозволити читати конкретну таблицю:
GRANT SELECT ON reporting.monthly_sales TO analyst;USAGE на схемі не означає автоматичний доступ до всіх таблиць у ній. Права на схему та права на її об’єкти — окремі речі.
Щоб дозволити створення об’єктів у схемі:
GRANT USAGE, CREATE ON SCHEMA reporting TO developer;Ці команди потрібно виконувати користувачем, який має право змінювати привілеї, зазвичай власником об’єкта або адміністратором бази даних.
Якщо потрібно, щоб об’єкти без явного зазначення схеми створювалися в певній схемі, її можна поставити першою в search_path:
SET search_path TO lesson_shop, public;
CREATE TABLE categories (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);Таблиця categories буде створена в lesson_shop, оскільки ця схема є першою в шляху пошуку.
Перевірити схему таблиці можна через системний каталог:
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name = 'categories';Для постійного налаштування search_path конкретного користувача використовують ALTER ROLE:
ALTER ROLE app_user
SET search_path TO app, public;Після нового підключення користувача app_user PostgreSQL застосовуватиме цей шлях пошуку за замовчуванням.
search_pathЯкщо в кількох схемах є таблиця users, запит:
SELECT *
FROM users;може звертатися не до тієї таблиці, яку очікує розробник.
Для важливих запитів використовуйте повне ім’я:
SELECT *
FROM app.users;Схема не є окремою базою даних. Кілька схем працюють у межах однієї бази даних і можуть мати спільні налаштування та з’єднання.
USAGE відкриває доступ до таблицьПривілей USAGE на схемі не надає дозволу виконувати SELECT, INSERT, UPDATE або DELETE. Для цього потрібні відповідні права на таблицю.
CASCADEКоманда:
DROP SCHEMA lesson_shop CASCADE;видаляє схему разом із її об’єктами. Не використовуйте CASCADE, якщо не впевнені, що всі залежні об’єкти можна видалити.
search_path без розуміння області діїSET search_path змінює налаштування лише поточного підключення. Інші підключення не отримають цю зміну, якщо не налаштувати параметр для ролі або бази даних.
Схема — це простір імен для об’єктів PostgreSQL.
Повне ім’я об’єкта має формат схема.об’єкт.
В одній базі даних може бути багато схем.
Схема public часто існує за замовчуванням, але для проєктів з кількома частинами краще створювати окремі схеми.
search_path визначає, де PostgreSQL шукає об’єкти без явної назви схеми.
Порядок схем у search_path впливає на те, який об’єкт буде знайдено.
Привілеї на схему та привілеї на таблиці налаштовуються окремо.
Для однозначності й безпеки важливі об’єкти краще вказувати разом зі схемою.