Часть 1: Управление ролями и правами доступа

Цель: Научиться создавать роли (пользовательские и групповые), назначать им привилегии и управлять доступом к объектам базы данных в PostgreSQL.

Уровень сложности: Начальный

Теоретические аспекты (кратко): В PostgreSQL сущность «роль» отвечает за аутентификацию и разграничение доступа. Роль может представлять пользователя (LOGIN-роль) или группу (NOLOGIN-роль). Привилегии (GRANT) позволяют определять права на объекты (таблицы, схемы и т.д.) для ролей. Стандартные права на таблицах – SELECT, INSERT, UPDATE, DELETE. Роль может наследовать привилегии, если ей выдана другая роль-группа. Суперпользователь (SUPERUSER) имеет все права без ограничений, роль с CREATEDB может создавать базы, а с CREATEROLE – других ролей.

Пошаговое задание с SQL-запросами:

1. Создайте новую базу данных для экспериментов:
   
   
   

• CREATE DATABASE hr_demo;

• Комментарий: В некоторых песочницах создание БД может быть недоступно. Если команда не срабатывает, можно пропустить этот шаг и работать в текущей БД.

1. Подключитесь к базе данных (если требуется):
   В среде psql: \c hr_demo. Или сразу продолжите в новой БД через интерфейс песочницы.

2. Создайте роли пользователей и группу:
   
   
   

• CREATE ROLE manager WITH LOGIN PASSWORD 'mgr_pass';
  CREATE ROLE analyst WITH LOGIN PASSWORD 'analyst_pass';
  CREATE ROLE hr_team NOLOGIN;

• Комментарий: manager и analyst – роли с возможностью входа, hr_team – групповая роль для объединения общих прав.

1. Назначьте роли в группу:
   
   
   

• GRANT hr_team TO manager;
  GRANT hr_team TO analyst;

• Это означает, что и manager, и analyst унаследуют привилегии роли hr_team.

1. Создайте таблицу и вставьте данные:
   
   
   

• CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  position TEXT,
  salary INTEGER
  );
  INSERT INTO employees (name, position, salary) VALUES
  ('Ivanov', 'HR', 50000),
  ('Petrov', 'Engineer', 70000),
  ('Sidorova', 'Analyst', 60000);

1. Предоставьте привилегии группе:
   
   
   

• GRANT SELECT ON employees TO hr_team;

• Теперь любая роль из hr_team сможет выполнять SELECT из таблицы employees.

1. Проверьте привилегии и права:
   
   
   

• -- Смена роли на manager (требует, чтобы текущий пользователь был членом manager)
  SET ROLE manager;
  -- Выполните запрос SELECT
  SELECT * FROM employees;
  -- Попробуйте обновить данные (должно выдать ошибку, т.к. UPDATE не предоставлено)
  UPDATE employees SET salary = salary + 1000 WHERE id = 1;
  -- Сброс роли на исходную
  RESET ROLE;
  -- Смена роли на analyst
  SET ROLE analyst;
  SELECT * FROM employees; -- должно работать
  -- Попробуйте вставить новую запись (должно выдать ошибку, INSERT не разрешено)
  INSERT INTO employees (name, position, salary) VALUES ('Ivanova', 'HR', 55000);
  RESET ROLE;

• Комментарий: Здесь мы проверяем, что роли manager и analyst могут просматривать (SELECT), но не изменять данные в таблице employees.

1. Отмените дополнительные привилегии (демонстрация):
   
   
   

• -- Убираем право SELECT у группы
  REVOKE SELECT ON employees FROM hr_team;
  -- Проверяем, что теперь даже SELECT запрещен
  SET ROLE manager;
  SELECT * FROM employees; -- ОШИБКА: недостаточно прав
  RESET ROLE;

• Комментарий: Так мы видим, как REVOKE отзывается права.

Задания для самостоятельного выполнения:

• Создайте роль intern (логин), дайте ей право только на чтение (SELECT) таблицы employees, и проверьте это.

• Создайте роль db_creator с атрибутом CREATEDB, затем попытайтесь создать от её имени новую базу данных.

• Попробуйте отменить привилегии: отзовите (REVOKE) у analyst право USAGE на схему public (проверьте через выборку из information_schema).

• (Опционально, требует прав суперпользователя) Создайте роль superadmin с атрибутом SUPERUSER и подключитесь под ней (в песочнице может быть невозможно).

Примечания по ограничениям песочницы: Некоторые песочницы могут не позволять создавать новую базу данных или суперпользовательские роли. Если команды CREATE DATABASE или CREATE ROLE ... SUPERUSER не работают, просто продолжайте использовать существующую БД и роли.

Часть 2: Схемы и разбиение таблиц (Partitioning)

Цель: Освоить работу с схемами (namespace), а также на практике создать разделённую (партиционированную) таблицу для эффективной организации данных.

Уровень сложности: Средний

Теоретические аспекты (кратко): Схема — это пространство имен для объектов (таблиц, функций и т.д.) внутри базы. Она помогает организовать объекты и разграничивать доступ. Partitioning (партиционирование) — это метод разбиения большой таблицы на подтаблицы (разделы) по определённому критерию (диапазону значений или списку). В PostgreSQL можно создавать таблицы с PARTITION BY RANGE или PARTITION BY LIST. Также существуют табличные пространства (tablespaces) — указатели на местоположения на диске, куда записываются файлы таблиц (часто требуют суперпользователя).

Пошаговое задание с SQL-запросами:

1. Создайте базу данных для продаж:
   
   
   

• CREATE DATABASE sales_db;

• Если не удалось, продолжайте в текущей БД (например, hr_demo).

1. Подключитесь к базе sales_db:
   В psql: \c sales_db.

2. Создайте схему и таблицу заказов:
   
   
   

• CREATE SCHEMA sales;
  CREATE TABLE sales.orders (
  order_id SERIAL PRIMARY KEY,
  order_date DATE NOT NULL,
  amount NUMERIC,
  region TEXT
  ) PARTITION BY RANGE (order_date);

• Здесь мы создали таблицу sales.orders, разделённую по диапазону даты order_date.

1. Создайте разделы (партиции) для разных периодов:
   
   
   

• CREATE TABLE sales.orders_q1 PARTITION OF sales.orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
  CREATE TABLE sales.orders_q2 PARTITION OF sales.orders
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

• Это создаст две подтаблицы: orders_q1 (январь–март 2024) и orders_q2 (апрель–июнь 2024).

1. Вставьте тестовые данные и проверьте размещение:
   
   
   

• INSERT INTO sales.orders (order_date, amount, region) VALUES
  ('2024-01-15', 100.50, 'East'),
  ('2024-02-20', 200.00, 'West'),
  ('2024-04-10', 150.75, 'North');
  -- Выводим все заказы с указанием, в каком разделе они хранятся
  SELECT tableoid::regclass AS table_name, * FROM sales.orders;

• Комментарий: В выводе вы увидите, что записи автоматически попали в orders_q1 или orders_q2 в зависимости от order_date.

1. Добавьте дополнительный раздел и распределите новые данные:
   
   
   

• CREATE TABLE sales.orders_q3 PARTITION OF sales.orders
  FOR VALUES FROM ('2024-07-01') TO ('2025-01-01');
  INSERT INTO sales.orders (order_date, amount, region) VALUES
  ('2024-09-05', 300.00, 'South');
  SELECT tableoid::regclass AS table_name, * FROM sales.orders;

1. Посмотрите информацию о разделах:
   
   
   

• SELECT inhrelid::regclass AS partition_table, inhparent::regclass AS parent_table
  FROM pg_inherits
  WHERE inhparent = 'sales.orders'::regclass;

• Здесь выводится список всех пар {партиция – родительская таблица} для sales.orders.

1. (Необязательно) Создайте таблицу в отдельном tablespace:
   
   
   

• -- Пример создания tablespace (требует прав суперпользователя и наличия директории на сервере)
  CREATE TABLESPACE fastspace LOCATION '/data/pg_fast';
  CREATE TABLE sales.orders_ft
  (order_id SERIAL, note TEXT)
  TABLESPACE fastspace;

• Комментарий: Если команда CREATE TABLESPACE не работает (нет нужных прав или доступа к файловой системе), пропустите этот шаг.

Задания для самостоятельного выполнения:

• Создайте таблицу sales.orders_q4 для четвёртого квартала 2024 и вставьте соответствующие записи.

• Попробуйте разбить таблицу по списковому (LIST) или хеш-методу: например, PARTITION BY LIST (region) и создайте разделы по регионам ('East', 'West').

• Реализуйте разделение таблицы с помощью таблицы-родителя и триггера (старый стиль), если интересно, и сравните с современным способом.

• (Опционально) Создайте дополнительную схему archive, переместите туда старые данные (архив), изменив значение search_path или используя квалифицированные имена схем.

Примечания по ограничениям песочницы: В большинстве онлайн-песочниц может не быть доступа к файловой системе сервера, поэтому команды CREATE TABLESPACE и указание реального пути, скорее всего, приведут к ошибке. Пропустите их или отметьте как теоретические. Команды по созданию базы данных тоже могут не работать — в этом случае используйте текущую базу по умолчанию.

Часть 3: Триггеры и хранимые процедуры

Цель: Научиться создавать триггеры и хранимые процедуры для автоматизации бизнес-логики и аудита изменений в базе данных.

Уровень сложности: Средний

Теоретические аспекты (кратко): Триггер – это механизм, который автоматически выполняет указанную функцию (процедуру) при событиях (INSERT, UPDATE, DELETE) над таблицей. Триггер может быть BEFORE или AFTER, строковый (FOR EACH ROW) или табличный (FOR EACH STATEMENT). Хранимая процедура (PostgreSQL PROCEDURE) – это программный блок (на языке PL/pgSQL), который выполняет серии операций; вызывается командой CALL и может содержать свои транзакции (в отличие от функции). Триггеры обычно используют для автоматического журналирования (аудита) изменений или дополнительных проверок, а процедуры – для сложных операций над данными.

Пошаговое задание с SQL-запросами:

1. Создайте таблицы для товаров и журнала изменений:
   
   
   

• CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name TEXT,
  price NUMERIC
  );
  CREATE TABLE products_log (
  log_id SERIAL PRIMARY KEY,
  product_id INTEGER,
  old_price NUMERIC,
  new_price NUMERIC,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );

1. Напишите функцию для логирования изменений цены:
   
   
   

• CREATE OR REPLACE FUNCTION log_price_change()
  RETURNS TRIGGER AS $$
  BEGIN
  -- Записываем в лог ID товара, старую и новую цену
  INSERT INTO products_log (product_id, old_price, new_price)
  VALUES (OLD.product_id, OLD.price, NEW.price);
  RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

1. Создайте триггер, вызывающий функцию после обновления:
   
   
   

• CREATE TRIGGER trg_price_update
  AFTER UPDATE OF price ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION log_price_change();

• Комментарий: Данный триггер сработает после любого изменения столбца price в таблице products и занесёт запись в products_log, если цена действительно изменилась.

1. Вставьте тестовый товар и выполните обновление:
   
   
   

• INSERT INTO products (name, price) VALUES ('Gadget', 100.00);
  UPDATE products SET price = 120.00 WHERE name = 'Gadget';

• После выполнения обновления триггер должен сработать.

1. Проверьте записи в журнале:
   
   
   

• SELECT * FROM products_log;

• В таблице products_log должна появиться строка с product_id, старой и новой ценой.

1. Создайте таблицу сотрудников и хранимую процедуру для повышения зарплаты:
   
   
   

• CREATE TABLE employees (
  emp_id SERIAL PRIMARY KEY,
  emp_name TEXT,
  salary INTEGER
  );
  INSERT INTO employees (emp_name, salary) VALUES
  ('Kozlov', 50000),
  ('Novikova', 60000);
  
  CREATE PROCEDURE raise_salary(emp INTEGER, percent NUMERIC)
  LANGUAGE plpgsql
  AS $$
  BEGIN
  UPDATE employees
  SET salary = salary * (1 + percent/100)
  WHERE emp_id = emp;
  END;
  $$;

1. Вызовите процедуру и проверьте результат:
   
   
   

• CALL raise_salary(1, 10); -- повышение зарплаты сотрудника с emp_id = 1 на 10%
  SELECT * FROM employees;

• В результате зарплата сотрудника Kozlov должна увеличиться на 10%.

1. (Необязательно) Создайте BEFORE-триггер для проверки данных:
   
   
   

• CREATE OR REPLACE FUNCTION ensure_positive_salary()
  RETURNS TRIGGER AS $$
  BEGIN
  IF NEW.salary < 0 THEN
  RAISE EXCEPTION 'Зарплата не может быть отрицательной';
  END IF;
  RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  CREATE TRIGGER trg_check_salary
  BEFORE INSERT OR UPDATE ON employees
  FOR EACH ROW
  EXECUTE FUNCTION ensure_positive_salary();

• Теперь попытка вставить отрицательную зарплату вызовет ошибку.

Задания для самостоятельного выполнения:

• Добавьте триггер AFTER DELETE на таблицу products, который будет записывать в products_log удалённые записи (например, сохранять старую цену и указатель что товар удалён).

• Напишите хранимую процедуру, которая переводит денежные средства между двумя счетами. Создайте для примера таблицу accounts(acc_id, balance), процедуру transfer(src INTEGER, dst INTEGER, amount NUMERIC) и протестируйте ее вызовом.

• Попробуйте изменить логику триггера так, чтобы он срабатывал ДО изменения (BEFORE UPDATE) и корректировал цену (например, запрещал снижение цены более чем на 50%).

• Изучите представление information_schema.triggers, чтобы увидеть информацию о созданных триггерах в базе.

Примечания по ограничениям песочницы: Обычно триггеры и процедуры полностью поддерживаются в онлайн-песочницах. Если вы сталкиваетесь с ошибками создания процедур, убедитесь, что используемый диалект PostgreSQL достаточно новый (версия 11 и выше для CREATE PROCEDURE). В примерах выше используется язык plpgsql, который встроен по умолчанию.

Часть 4: Расширения и статистика PostgreSQL

Цель: Познакомиться с расширениями PostgreSQL для дополнительной функциональности и средствами просмотра статистики сервера.

Уровень сложности: Средний

Теоретические аспекты (кратко): Расширения (CREATE EXTENSION) позволяют добавлять новые возможности (функции, операторы, типы) в PostgreSQL. Примеры: pg_stat_statements (сбор статистики по запросам), uuid-ossp (генерация UUID), hstore (ключ-значение), и др. Для просмотра настроек сервера есть представления pg_settings, pg_stat_database, pg_stat_user_tables и др. Используя команду EXPLAIN ANALYZE, можно получить план выполнения и время запросов.

Пошаговое задание с SQL-запросами:

1. Создайте расширение для генерации UUID:
   
   
   

• CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

• Затем создайте таблицу с полем UUID:
  
  
  

• CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  product TEXT
  );
  INSERT INTO orders (product) VALUES ('Widget'), ('Gadget');
  SELECT * FROM orders;

• Каждая запись должна получить уникальный UUID.

1. Активируйте сбор статистики запросов (pg_stat_statements):
   
   
   

• CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
  -- Выполните несколько произвольных запросов для накопления статистики
  SELECT * FROM orders;
  SELECT product, COUNT(*) FROM orders GROUP BY product;
  -- Просмотр статистики по выполненным запросам
  SELECT query, calls FROM pg_stat_statements ORDER BY calls DESC LIMIT 5;

• Комментарий: В некоторых песочницах может не получиться установить pg_stat_statements без дополнительных настроек сервера. Если команда CREATE EXTENSION выдаёт ошибку, пропустите этот шаг.

1. Проанализируйте запрос с помощью EXPLAIN:
   
   
   

• EXPLAIN ANALYZE SELECT * FROM orders WHERE product = 'Widget';

• В результате вы увидите план выполнения и фактическое время запроса.

1. Просмотрите настройки сервера:
   
   
   

• SHOW work_mem;
  SELECT name, setting FROM pg_settings WHERE name IN ('max_connections','work_mem','shared_buffers');

• Это позволит увидеть текущее значение этих параметров памяти и подключений.

1. (Необязательно) Создайте роль для репликации:
   
   
   

• -- Технически требует суперпользователя
  CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'repl_pass';

• Это пример создания роли с правами репликации. В песочнице обычно недоступно.

Задания для самостоятельного выполнения:

• Установите и опробуйте другие расширения, например hstore или tablefunc. Для hstore создайте таблицу с полем типа hstore и вставьте в него пары «ключ=значение».

• Выполните несколько сложных запросов на большом объёме данных (скопируйте строку INSERT много раз), затем используйте EXPLAIN ANALYZE для сравнения планов и времени.

• Просмотрите данные представления pg_stat_user_tables и pg_stat_all_tables для анализа статистики по таблицам (количество строк, последняя проверка и т.д.).

• (Опционально) Попробуйте изменить параметры сессии: SET work_mem = '50MB'; и посмотрите, как изменится поведение сложных запросов. Помните, что глобальные изменения системных настроек в песочнице недоступны.

Примечания по ограничениям песочницы: Установка расширений (CREATE EXTENSION) может быть ограничена. Расширения uuid-ossp и hstore обычно доступны по умолчанию или могут быть установлены, а pg_stat_statements требует, чтобы параметр shared_preload_libraries содержал pg_stat_statements (в песочнице может уже быть включён или нет). Если возникнут ошибки при создании расширений, пропускайте эти шаги. Изменение глобальных настроек (ALTER SYSTEM, изменение postgresql.conf) в онлайн-песочнице обычно недоступно.

Полезные ссылки: https://webprogr.ru/abd4.docx