# ITMO DB lesson 5

Блок 2. Занятие 5. Администрирование, визуализация структур и физическая реализация БД (PostgreSQL)

31 slides, by john. https://easeweb.host/movie/itmo-db-lesson-5-2/1/

## Slide 1. Занятие 5. Администрирование и физическая реализация БД
(id 361, https://easeweb.host/movie/itmo-db-lesson-5-2/1/)
- БАЗЫ ДАННЫХ И ХРАНИЛИЩА · БЛОК 2. АДМИНИСТРИРОВАНИЕ · ЗАНЯТИЕ 5
- Администрирование и физическая реализация БД
- От логической схемы dbdiagram.io к живому PostgreSQL в Docker, клиентам psql и DBeaver, ролям доступа RBAC и проверке бэкапов pg_dump / pg_restore
- Санеев Святослав Олегович · 2026

## Slide 2. План занятия
(id 362, https://easeweb.host/movie/itmo-db-lesson-5-2/2/)
- НАВИГАЦИЯ
- План занятия
- От схемы DBML к физической БД в Docker
- 1
- Как превратить картинку из dbdiagram.io в строгий SQL DDL: суррогатные ключи GENERATED ALWAYS AS IDENTITY, именованные CHECK, составные внешние ключи, политика ON DELETE и автоинициализация контейнера.
- 2
- Клиенты командной строки (CLI) и графические (GUI)
- Инженерный минимум в psql (метакоманды инспекции, ON_ERROR_STOP), подключение DBeaver к контейнеру на 127.0.0.1 и проверка схемы обратной ER-диаграммой (Reverse Engineering).
- 3
- Роли, схемы и права доступа (RBAC)
- Почему микросервис никогда не должен ходить под postgres. Разделение ролей на game_owner, game_app и game_readonly, защита таблиц покупок от удаления и представления VIEW.
- 4
- Резервное копирование, восстановление и перенос данных
- Логический бэкап pg_dump -Fc, выборочное восстановление и тест в чистую базу через pg_restore, а также массовая выгрузка и загрузка CSV через клиентский \copy.

## Slide 3. Глоссарий 1/2: физическая схема, DDL и целостность
(id 363, https://easeweb.host/movie/itmo-db-lesson-5-2/3/)
- СЛОВАРЬ ТЕРМИНОВ · ЧАСТЬ 1
- Глоссарий 1/2: физическая схема, DDL и целостность
- Новые термины первого часа — сверяйтесь с этим слайдом по ходу разбора DDL
- DDL (Data Definition Language)
- GENERATED ALWAYS AS IDENTITY
- Команды определения структуры БД (CREATE, ALTER, DROP). Превращают логическую модель в физические таблицы, типы, ключи и ограничения.
- Стандартный SQL-механизм автогенерации суррогатного первичного ключа через системную последовательность. Запрещает случайную ручную вставку ID.
- Именованный CHECK
- Политика ON DELETE
- Явное правило проверки домена строки (например, CONSTRAINT chk_items_price CHECK (base_price >= 0)), имя которого сразу видно в логах бэкенда.
- Правило внешнего ключа при удалении родительской записи: RESTRICT (запретить), SET NULL (обнулить ссылку) или CASCADE (удалить дочерние строки).
- Составной внешний ключ
- Миграция схемы
- Ссылка по паре колонок сразу — например, (match_id, killer_id) → match_participants(match_id, player_id). Гарантирует согласованность фактов.
- Версионированный скрипт перехода схемы из состояния N в N+1 (up / down), позволяющий обновлять живую БД без потери накопленных данных.

## Slide 4. Глоссарий 2/2: клиенты, права доступа и резервные копии
(id 364, https://easeweb.host/movie/itmo-db-lesson-5-2/4/)
- СЛОВАРЬ ТЕРМИНОВ · ЧАСТЬ 2
- Глоссарий 2/2: клиенты, права доступа и резервные копии
- Термины второго часа — администрирование, безопасность и эксплуатация БД
- Обратная ER-диаграмма
- Роль (Role) и RBAC
- Визуальная схема таблиц и связей, построенная GUI-клиентом (DBeaver) напрямую по системному каталогу живой СУБД (Reverse Engineering).
- Субъект безопасности в PostgreSQL. По принципу наименьших привилегий права выдаются ролям (owner / app / readonly), а не отдельным людям.
- Схема (Schema / Namespace)
- Представление (VIEW)
- Именованное пространство объектов внутри одной БД (например, CREATE SCHEMA game), отделяющее таблицы сервиса от системных и чужих объектов.
- Сохраненный именованный SQL-запрос, ведущий себя как виртуальная таблица. Позволяет дать аналитикам доступ к агрегатам, скрыв секретные поля.
- pg_dump -Fc и pg_restore
- Серверный COPY vs клиентский \copy
- Инструменты логического резервного копирования PostgreSQL: -Fc создаёт сжатый архив с оглавлением, а pg_restore восстанавливает его целиком или по таблицам.
- Механизм потокового экспорта/импорта таблиц (CSV). Команда \copy в psql читает и пишет файл на машине клиента без прав суперпользователя.

## Slide 5. От схемы в dbdiagram.io к физическому DDL
(id 365, https://easeweb.host/movie/itmo-db-lesson-5-2/5/)
- ЧАСТЬ 1 · ОТ ЛОГИЧЕСКОЙ СХЕМЫ К ФИЗИЧЕСКОЙ БД
- От схемы в dbdiagram.io к физическому DDL в PostgreSQL
- На Занятии 2 мы спроектировали реляционную схему в 3НФ. Почему автоэкспорт из dbdiagram.io нельзя сразу катить в прод?
- Что есть на диаграмме dbdiagram.io (DBML)
- Что обязан добавить инженер в физическом DDL
- • Названия сущностей и атрибутов (players, clans, purchases, kill_log)
  • Базовые типы и пометки [pk], [unique], [not null]
  • Линии связей Ref между родительскими и дочерними таблицами
  • Удобно для обсуждения архитектуры, но недостаточно для защиты живой БД
- • Изолированную схему проекта (CREATE SCHEMA game) вместо свалки в public
  • Строгий стандарт генерации ID: GENERATED ALWAYS AS IDENTITY
  • Точные типы времени и денег (timestamptz, integer / numeric)
  • Именованные CONSTRAINT chk_... CHECK (...) и политики ON DELETE

```sql
-- В физическом DDL для PostgreSQL мы превращаем таблицу предметов в самозащищённый контракт:
CREATE TABLE game.items (
    item_id     integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    item_code   varchar(64)  NOT NULL UNIQUE,
    title       varchar(120) NOT NULL,
    rarity      varchar(16)  NOT NULL,
    base_price  integer      NOT NULL,
    CONSTRAINT chk_items_rarity CHECK (rarity IN ('common', 'rare', 'epic', 'legendary')),
    CONSTRAINT chk_items_price  CHECK (base_price >= 0)
);
```
- Главный принцип: код бэкенда меняют десятки разработчиков, но физические ограничения DDL в СУБД — это последний рубеж обороны данных.

## Slide 6. Суррогатные ключи и выбор физических типов PostgreSQL
(id 366, https://easeweb.host/movie/itmo-db-lesson-5-2/6/)
- ЧАСТЬ 1 · ТИПЫ ДАННЫХ И КЛЮЧИ
- Суррогатные ключи и выбор физических типов PostgreSQL
- Четыре решения на уровне типов колонок, которые избавляют от классических багов на продакшене
- 1. GENERATED ALWAYS AS IDENTITY вместо serial
- 2. timestamptz вместо timestamp
- • Старый serial позволяет случайно вставить явный ID (INSERT ... VALUES (5)), после чего последовательность падает с duplicate key.
  • GENERATED ALWAYS AS IDENTITY — стандарт SQL: база сама выдаёт ID и блокирует ручную вставку без OVERRIDING SYSTEM VALUE.
- • timestamp without time zone не помнит часовой пояс — при переезде контейнера время событий сдвигается.
  • timestamptz хранит абсолютный момент в UTC и корректно сравнивает события матчей и покупок между любыми серверами.
- 3. integer / numeric вместо float для экономики
- 4. varchar(N) + CHECK для кодов и статусов
- • Числа с плавающей точкой (real, double precision) дают ошибки округления двоичной дроби (0.1 + 0.2 ≠ 0.3).
  • Игровую валюту и цены храним только в целых единицах (integer / bigint) или в точном десятичном типе numeric(12,2).
- • В PostgreSQL varchar(32) защищает от случайной записи мегабайтной строки в nickname или clan_tag.
  • Для перечислимых статусов (rarity, mode) связка varchar(16) + CHECK (rarity IN (...)) проще в миграциях, чем ENUM.
- Запомнить: всегда называйте ограничения явно (CONSTRAINT chk_...) — когда бэкенд упадёт с ошибкой, в логе будет понятное имя правила.

## Slide 7. Ограничения целостности и составные внешние ключи
(id 367, https://easeweb.host/movie/itmo-db-lesson-5-2/7/)
- ЧАСТЬ 1 · ССЫЛОЧНАЯ И ДОМЕННАЯ ЦЕЛОСТНОСТЬ
- Ограничения целостности и составные внешние ключи
- Как перенести бизнес-правила из Занятия 2 («в kill_log попадают только реальные участники этого матча») в код таблицы

```sql
-- 1. Составной первичный ключ в таблице участников матча
CREATE TABLE game.match_participants (
    match_id   integer     NOT NULL,
    player_id  integer     NOT NULL,
    team       varchar(16) NOT NULL,
    score      integer     NOT NULL DEFAULT 0,
    CONSTRAINT pk_match_participants PRIMARY KEY (match_id, player_id),
    CONSTRAINT chk_mp_team  CHECK (team IN ('red', 'blue')),
    CONSTRAINT chk_mp_score CHECK (score >= 0)
);

-- 2. Журнал фрагов ссылается СОСТАВНЫМ FK по (match_id, player_id)!
CREATE TABLE game.kill_log (
    kill_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    match_id   integer NOT NULL,
    killer_id  integer NOT NULL,
    victim_id  integer NOT NULL,
    weapon     varchar(48) NOT NULL,
    CONSTRAINT chk_kill_not_self CHECK (killer_id <> victim_id),
    CONSTRAINT fk_kill_killer FOREIGN KEY (match_id, killer_id)
        REFERENCES game.match_participants(match_id, player_id) ON DELETE CASCADE,
    CONSTRAINT fk_kill_victim FOREIGN KEY (match_id, victim_id)
        REFERENCES game.match_participants(match_id, player_id) ON DELETE CASCADE
);
```
- Что даёт составной внешний ключ?
- • Если бы killer_id ссылался просто на players(player_id), в kill_log можно было бы записать игрока, который вообще не заходил в этот матч.
  • Составной FK (match_id, killer_id) физически запрещает такую аномалию на уровне ядра PostgreSQL.
- Защита от логических противоречий
- • CHECK (killer_id <> victim_id) не позволит записать фраг самого себя из-за бага сетевого кода.
  • В таблице matches правило CHECK (finished_at IS NULL OR finished_at >= started_at) защищает от отрицательной длительности боя.
- Итог: хорошая физическая схема делает невозможным сохранение невалидного состояния — даже если в коде сервиса допущена ошибка.

## Slide 8. Политика удаления: ON DELETE RESTRICT, SET NULL и CASCADE
(id 368, https://easeweb.host/movie/itmo-db-lesson-5-2/8/)
- ЧАСТЬ 1 · ЖИЗНЕННЫЙ ЦИКЛ СВЯЗАННЫХ ЗАПИСЕЙ
- Политика удаления: ON DELETE RESTRICT, SET NULL и CASCADE
- Бездумно написать ON DELETE CASCADE на всех связях — верный способ потерять финансовую историю проекта
- ON DELETE RESTRICT
- ON DELETE SET NULL
- ON DELETE CASCADE
- Удалять родителя НЕЛЬЗЯ
- Когда связь необязательна
- Дочерняя строка — часть родителя
- Пример в схеме:
  purchases → players и purchases → items
  Почему:
  • Покупки — это финансовый и аудит-журнал.
  • Если предмет убирают с витрины или аккаунт блокируют, история транзакций обязана остаться.
  • СУБД заблокирует DELETE FROM game.items, если предмет уже кто-то купил.
- Пример в схеме:
  players.clan_id → clans(clan_id)
  Почему:
  • Игрок может состоять в клане, а может играть один (clan_id IS NULL).
  • При роспуске гильдии (DELETE FROM game.clans) удалять самих игроков нельзя!
  • СУБД аккуратно проставит бывшим участникам clan_id = NULL.
- Пример в схеме:
  match_participants → matches и kill_log → match_participants
  Почему:
  • Участие в матче и список фрагов внутри боя не имеют смысла без самого матча.
  • При очистке тестового матча СУБД автоматически удалит его состав и лог фрагов.
- Правило ревью DDL: каждый внешний ключ обязан иметь осознанный ON DELETE. По умолчанию PostgreSQL ставит NO ACTION / RESTRICT.

## Slide 9. Порядок создания таблиц и автоинициализация в Docker
(id 369, https://easeweb.host/movie/itmo-db-lesson-5-2/9/)
- ЧАСТЬ 1 · ВОСПРОИЗВОИЗВОДИМЫЙ КОНТЕЙНЕР POSTGRESQL
- Порядок создания таблиц и автоинициализация в Docker
- Как сделать так, чтобы любой разработчик или проверяющий поднял готовую БД одной командой docker compose up -d
- Топологический порядок DDL (от родителей к детям)
- Каталог /docker-entrypoint-initdb.d/ и его ловушка
- PostgreSQL проверяет целевую таблицу в момент выполнения REFERENCES. Создаём строго по уровням:
  1. Справочники без внешних связей: clans, items, matches
  2. Сущности 1-го уровня: players (ссылается на clans)
  3. Связующие таблицы и факты: purchases, match_participants
  4. Зависимые журналы: kill_log (ссылается на match_participants)
- • Официальный образ postgres:16 автоматически выполняет все *.sql и *.sh из /docker-entrypoint-initdb.d/ в алфавитном порядке.
  • Важнейший нюанс: скрипты инициализации запускаются ТОЛЬКО при первом старте на пустом volume!
  • Если вы поменяли init.sql, обычный restart ничего не изменит — нужен docker compose down -v.

```bash
# docker-compose.yml (из стенда Lesson 5/dba/)
services:
  postgres:
    image: postgres:16
    container_name: itmo_pg_dba
    ports:
      - "127.0.0.1:5435:5432"   # Публикуем порт ТОЛЬКО на localhost!
    volumes:
      - pg_dba_data:/var/lib/postgresql/data
      - ./live_admin_pg.sql:/docker-entrypoint-initdb.d/01_live_admin_pg.sql:ro
```
- Совет для Windows + Git: добавьте *.sql text eol=lf в .gitattributes, чтобы переводы строк CRLF не мешали контейнерам Linux.

## Slide 10. Эволюция схемы: стартовый init.sql против миграций
(id 370, https://easeweb.host/movie/itmo-db-lesson-5-2/10/)
- ЧАСТЬ 1 · УПРАВЛЕНИЕ ИЗМЕНЕНИЯМИ СХЕМЫ
- Эволюция схемы: стартовый init.sql против миграций
- Удалить volume через docker compose down -v можно на локальном стенде, но на продакшене живут данные игроков
- Стартовый скрипт (init.sql / 01_schema.sql)
- Версионированные миграции (Flyway, Goose, Liquibase, Alembic)
- Для чего подходит:
  • Первичное развёртывание локального стенда с нуля
  • Автотесты в CI/CD, где чистая БД поднимается на каждый прогон
  • Демонстрация полной эталонной структуры на защите практики
  Ограничение:
  • Описывает только итоговое состояние. Не отвечает на вопрос: «как безопасно добавить колонку season_id в таблицу с 10 миллионами матчей без потери данных?»
- Как устроены в реальном проекте:
  • Каждое изменение схемы оформляется отдельным файлом с номером версии: V001__init.sql, V002__add_clan_rating.sql
  • В самой БД хранится служебная таблица schema_migrations со списком уже применённых версий и их контрольными суммами
  • При деплое инструмент сравнивает файлы в Git с schema_migrations и накатывает только новые изменения в транзакции
  • В PostgreSQL большинство DDL-команд транзакционны: если миграция упала посередине, вся схема откатится назад!
- Преимущество PostgreSQL: в отличие от MySQL, в PostgreSQL даже CREATE TABLE и ALTER TABLE выполняются внутри транзакции BEGIN ... COMMIT.

## Slide 11. Инструменты инженера: когда нужен CLI, а когда GUI
(id 371, https://easeweb.host/movie/itmo-db-lesson-5-2/11/)
- ЧАСТЬ 2 · КЛИЕНТЫ КОМАНДНОЙ СТРОКИ И ГРАФИЧЕСКИЕ КЛИЕНТЫ
- Инструменты инженера: когда нужен CLI, а когда GUI
- В арсенале разработчика и администратора должны быть оба класса инструментов — у каждого своя зона ответственности
- Консольные клиенты (CLI): psql, redis-cli, mongosh
- Графические клиенты (GUI): DBeaver, MongoDB Compass
- • Работают везде: внутри Docker-контейнера, по SSH на закрытом сервере без графики, в пайплайнах CI/CD и cron-скриптах.
  • Нулевой оверхед и автоматизация: принимают скрипты из stdin, возвращают строгий код возврата (exit code) при ошибках.
  • Потоковая передача данных: клиентский \copy в psql качает гигабайты CSV напрямую без зависания интерфейса.
- • Наглядная визуализация: дерево схем, таблиц, внешних ключей, индексов и ролей на одном экране.
  • Обратная ER-диаграмма (Reverse Engineering): автоматическая отрисовка реальных связей живой базы данных.
  • Удобство исследования: фильтрация табличных выборок, подсветка типов документов в Compass, визуальный план запроса.
- Карта клиентов по СУБД нашего курса
- • PostgreSQL: CLI — psql (плюс утилиты pg_dump, pg_restore) · GUI — DBeaver, DataGrip, pgAdmin
  • MongoDB (Занятие 3): CLI — mongosh (плюс mongodump / mongorestore) · GUI — MongoDB Compass (анализ схемы и индексов), DBeaver
  • Redis и ClickHouse (Занятия 3–4): CLI — redis-cli, clickhouse-client · GUI — Redis Insight, DBeaver (по HTTP-порту 8123)
- Правило: проектируем и проверяем связи глазами в DBeaver, а накатываем миграции, дампы и проверки в psql с флагом -v ON_ERROR_STOP=1.

## Slide 12. Инспекция живой базы в psql: метакоманды и строгий режим
(id 372, https://easeweb.host/movie/itmo-db-lesson-5-2/12/)
- ЧАСТЬ 2 · КОНСОЛЬНЫЙ КЛИЕНТ PSQL
- Инспекция живой базы в psql: метакоманды и строгий режим
- Заходя на незнакомый сервер, инженер за 30 секунд узнаёт всё о базе пятью метакомандами psql

```bash
# 1. Интерактивный вход в контейнер PostgreSQL
docker exec -it itmo_pg_dba psql -U postgres -d gamedb

# 2. Главные метакоманды ревизии внутри psql:
\dn                 -- список схем (увидим схему game)
\dt game.*          -- список всех таблиц в схеме game
\dv game.*          -- список представлений (VIEW) в схеме game
\d+ game.purchases  -- колонки, типы, PK, FK, CHECK и ссылки!
\du                 -- список ролей (game_owner, game_app, game_readonly)
\dp game.*          -- матрица прав доступа (Access privileges)

# 3. Неинтерактивный запуск со строгой остановкой на первой ошибке:
docker exec -i itmo_pg_dba \
  psql -v ON_ERROR_STOP=1 -U postgres -d gamedb < live_admin_pg.sql
```
- Зачем нужен -v ON_ERROR_STOP=1?
- • По умолчанию psql при чтении SQL-файла печатает ошибку и продолжает выполнять следующие строки!
  • Если CREATE TABLE players упал из-за опечатки, скрипт пойдёт дальше, упадёт на FK и все равно вернёт код 0.
  • Флаг -v ON_ERROR_STOP=1 немедленно прерывает выполнение с ненулевым кодом ошибки.
- Как читать вывод \dp (права доступа)
- В колонке Access privileges запись вида game_app=arwd/game_owner означает:
  • a (append) — INSERT · r (read) — SELECT
  • w (write) — UPDATE · d (delete) — DELETE
  Если у таблицы покупок стоит game_app=ar/game_owner — сервис может только читать и добавлять записи!
- Лайфхак в psql: команда \x auto включает вертикальный режим вывода для широких таблиц, чтобы строки не переносились.

## Slide 13. Обратная ER-диаграмма в DBeaver: сверка мечты и реальности
(id 373, https://easeweb.host/movie/itmo-db-lesson-5-2/13/)
- ЧАСТЬ 2 · ГРАФИЧЕСКИЙ КЛИЕНТ И ОБРАТНАЯ ER-ДИАГРАММА
- Обратная ER-диаграмма в DBeaver: сверка мечты и реальности
- Диаграмма в dbdiagram.io показывает то, что вы хотели сделать. Диаграмма в DBeaver показывает то, что реально создано в СУБД
- Шаг 1. Подключаем DBeaver к контейнеру
- • Создаём соединение PostgreSQL: Host localhost, Port 5435, Database gamedb, User postgres.
  • На вкладке PostgreSQL включаем галочку Show all databases, чтобы видеть все тестовые базы (включая gamedb_restore_check).
- Шаг 2. Открываем Reverse Engineering ER Diagram
- • В дереве Database Navigator раскрываем: gamedb → Schemas → game.
  • Двойной клик по схеме game → переходим на вкладку ER Diagram в верхней части окна.
  • DBeaver считывает системный каталог pg_catalog / pg_constraint и автоматически рисует таблицы со всеми связями.
- Шаг 3. Что сразу выдаёт ошибки в DDL на обратной диаграмме
- • Таблица висит в воздухе без стрелок: забыли прописать FOREIGN KEY ... REFERENCES при переносе из DBML.
  • Нет значка ключа у колонок связующей таблицы: забыли составной PRIMARY KEY (match_id, player_id).
  • Связь в kill_log одиночная вместо двойной: внешний ключ повесили только на match_id вместо пары (match_id, killer_id).
- Артефакт для Практики 5: экспорт обратной ER-диаграммы из DBeaver в PNG — обязательное доказательство того, что все FK реально созданы в БД.

## Slide 14. Безопасное подключение: почему нельзя открывать 0.0.0.0:5432
(id 374, https://easeweb.host/movie/itmo-db-lesson-5-2/14/)
- ЧАСТЬ 2 · СЕТЕВАЯ БЕЗОПАСНОСТЬ ПРИ АДМИНИСТРИРОВАНИИ
- Безопасное подключение: почему нельзя открывать 0.0.0.0:5432
- Одна строчка в docker-compose.yml определяет, видна ли ваша база всему интернету в обход файрвола
- Опасная ошибка: ports: - "5435:5432"
- Правильный стандарт: только 127.0.0.1 + SSH-туннель
- • Запись без IP-адреса привязывает порт к 0.0.0.0 (всем сетевым интерфейсам хоста).
  • На Linux-сервере Docker пишет правила напрямую в цепочку DOCKER в iptables — часто в обход стандартного файрвола ufw!
  • Через несколько минут сканеры находят открытый порт СУБД и начинают перебор паролей.
- • В docker-compose.yml всегда указываем петлевой интерфейс: "127.0.0.1:5435:5432".
  • На локальной машине разработчика DBeaver подключается к 127.0.0.1:5435 напрямую.
  • На удалённом сервере порт 5435 снаружи не виден — администратор включает вкладку SSH Tunnel в DBeaver.

```bash
# Как работает подключение DBeaver к удалённому серверу без открытия порта БД наружу:
# [Ноутбук: DBeaver] ===(Зашифрованный SSH, порт 22)===> [Сервер: sshd] ---> [127.0.0.1:5435 (Postgres)]

# Эквивалентная команда проброса порта из терминала:
ssh -N -L 15435:127.0.0.1:5435 deploy@game-prod.example.com
# После этого локальный DBeaver подключается к localhost:15435 как к локальной базе!
```
- Правило эксплуатации: порт СУБД (5432, 6379, 27017, 9000) никогда не должен торчать в публичную сеть — только внутренняя сеть Docker или 127.0.0.1.

## Slide 15. Почему сервис не должен ходить в БД под ролью postgres
(id 375, https://easeweb.host/movie/itmo-db-lesson-5-2/15/)
- ЧАСТЬ 3 · РОЛИ, СХЕМЫ И ПРАВА ДОСТУПА (RBAC)
- Почему сервис не должен ходить в БД под ролью postgres
- Принцип наименьших привилегий (Principle of Least Privilege) — основа безопасности данных
- Что происходит, когда в .env сервиса прописан User=postgres
- Как строится ролевая модель (RBAC) в PostgreSQL
- 1. Любая ошибка или SQL-инъекция фатальна:
  • Суперпользователь postgres обходит все проверки прав (GRANT / REVOKE и политики Row-Level Security).
  • Случайный вызов тестовой очистки или инъекция может выполнить DROP SCHEMA game CASCADE или TRUNCATE.
  2. Нет защиты неизменяемых журналов:
  • Баг в коде магазина может перезаписать цену старой покупки (UPDATE purchases) или удалить чек игрока (DELETE FROM purchases).
  3. Невозможно отличить трафик в мониторинге:
  • В pg_stat_activity все соединения выглядят одинаково — непонятно, кто нагрузил базу: бэкенд, мигратор или аналитик в DBeaver.
- В PostgreSQL пользователи и группы объединены в понятие ROLE:
  • Роль с правом LOGIN — учётная запись, под которой подключается сервис или человек.
  • Групповая роль (NOLOGIN) — набор привилегий, который можно выдать другим ролям через GRANT role_name TO user_name.
  Три уровня прав, которые проверяет PostgreSQL по порядку:
  1. CONNECT ON DATABASE — пустить ли вообще в базу данных.
  2. USAGE ON SCHEMA — видит ли роль объекты внутри схемы game (без этого даже при наличии прав на таблицу будет отказ!).
  3. SELECT / INSERT / UPDATE / DELETE на конкретные таблицы и USAGE, SELECT на последовательности (SEQUENCES).
- Запомнить: суперпользователь postgres используется только для первоначального создания БД и ролей — ни один прикладной процесс под ним не работает.

## Slide 16. Три роли здорового проекта: owner, app и readonly
(id 376, https://easeweb.host/movie/itmo-db-lesson-5-2/16/)
- ЧАСТЬ 3 · МАТРИЦА РОЛЕЙ ИГРОВОГО ПРОЕКТА
- Три роли здорового проекта: owner, app и readonly
- Чёткое разделение обязанностей между мигратором схемы, игровым бэкендом и аналитикой
- 1. game_owner (Владелец схемы)
- 2. game_app (Игровой бэкенд)
- 3. game_readonly (Аналитика и BI)
- Кто: CI/CD-мигратор, DBA
- Кто: API-сервис, воркеры
- Кто: DBeaver стажёра, BI
- Разрешено:
  • Владеет схемой AUTHORIZATION game_owner и всеми таблицами внутри неё
  • Выполняет CREATE / ALTER / DROP TABLE, создаёт индексы и представления
  Когда подключается:
  • Только в момент раскатки релиза (миграции). В обычное время не держит открытых соединений.
- Разрешено:
  • USAGE ON SCHEMA game + права на последовательности
  • SELECT, INSERT, UPDATE, DELETE на операционные таблицы (players, clans, matches)
  • ТОЛЬКО SELECT, INSERT на аудит-журналы (purchases, kill_log)
  Запрещено: любой DDL и удаление чеков покупок!
- Разрешено:
  • USAGE ON SCHEMA game
  • Только SELECT на разрешённые таблицы и витринные представления (VIEW)
  Запрещено:
  • Любая модификация данных (INSERT / UPDATE / DELETE) и любой DDL. Безопасно для ручных аналитических запросов.
- Безопасность по умолчанию: начиная с PostgreSQL 15, обычные роли не имеют права CREATE в схеме public — и мы дополнительно закрываем её через REVOKE ALL ON SCHEMA public FROM PUBLIC.

## Slide 17. Настройка прав в SQL и защита журналов покупок
(id 377, https://easeweb.host/movie/itmo-db-lesson-5-2/17/)
- ЧАСТЬ 3 · РЕАЛИЗАЦИЯ RBAC В SQL
- Настройка прав в SQL и защита журналов покупок (Append-Only)
- Полный шаблон настройки привилегий из стенда Lesson 5/dba/live_admin_pg.sql

```sql
-- 1. Закрываем схему public и даём доступ к схеме game
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA game TO game_app, game_readonly;

-- 2. Операционные таблицы: полный DML для сервиса
GRANT SELECT, INSERT, UPDATE, DELETE ON
    game.clans, game.players, game.items,
    game.matches, game.match_participants
TO game_app;

-- 3. Финансовый и боевой журналы: ТОЛЬКО дозапись (Append-Only)!
GRANT SELECT, INSERT ON game.purchases, game.kill_log TO game_app;

-- 4. Важно: доступ к последовательностям для генерации IDENTITY ID
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA game TO game_app;

-- 5. Чтение для аналитиков и права по умолчанию на будущие таблицы
GRANT SELECT ON ALL TABLES IN SCHEMA game TO game_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE game_owner IN SCHEMA game
    GRANT SELECT ON TABLES TO game_readonly;
```
- Зачем права на SEQUENCES?
- • Под капотом каждая колонка GENERATED ALWAYS AS IDENTITY крутит системный счётчик (sequence).
  • Если выдать INSERT на таблицу, но забыть GRANT USAGE, SELECT ON ALL SEQUENCES, при первой вставке сервис получит permission denied for sequence.
- Проверка роли через SET ROLE
- • Внутри сессии psql команда SET ROLE game_app; мгновенно переключает эффективные права на роль сервиса.
  • Команда RESET ROLE; возвращает исходные права. Так мы тестируем отказы прямо в скрипте!
- Обратите внимание на ALTER DEFAULT PRIVILEGES: обычный GRANT ... ON ALL TABLES действует только на уже созданные таблицы, а DEFAULT PRIVILEGES — и на будущие.

## Slide 18. Представления (VIEW) как витрина и граница безопасности
(id 378, https://easeweb.host/movie/itmo-db-lesson-5-2/18/)
- ЧАСТЬ 3 · ПРЕДСТАВЛЕНИЯ (VIEW) В АДМИНИСТРИРОВАНИИ
- Представления (VIEW) как витрина и граница безопасности
- Как дать стажёру или внешнему сервису статистику по игрокам, не раскрывая их email и не заставляя писать сложные JOIN

```sql
-- Создаём представление от имени владельца схемы (game_owner):
CREATE VIEW game.v_player_summary AS
SELECT
    p.player_id,
    p.nickname,          -- email сюда НЕ включён!
    c.clan_tag,
    p.level,
    COUNT(pu.purchase_id)           AS purchases_cnt,
    COALESCE(SUM(pu.price_paid), 0) AS total_spent
FROM game.players p
LEFT JOIN game.clans c      ON c.clan_id = p.clan_id
LEFT JOIN game.purchases pu ON pu.player_id = p.player_id
GROUP BY p.player_id, p.nickname, c.clan_tag, p.level;

GRANT SELECT ON game.v_player_summary TO game_readonly;
```
- Две суперсилы VIEW для администратора
- 1. Защита персональных данных (PII):
  • Можно запретить роли прямое чтение таблицы game.players (где лежат email), но разрешить чтение game.v_player_summary.
  • По умолчанию VIEW выполняется с правами его владельца (game_owner) на базовые таблицы!
  2. Стабильный контракт при рефакторинге:
  • Если внутри БД таблицы переименуют, отчёты продолжат делать SELECT * FROM game.v_player_summary.
- Обычный VIEW против MATERIALIZED VIEW в PostgreSQL
- • Обычный VIEW не хранит данные на диске — он на лету подставляет свой SQL-запрос при каждом чтении (всегда 100% свежие данные).
  • MATERIALIZED VIEW физически сохраняет результат расчёта на диск и обновляется по команде REFRESH MATERIALIZED VIEW CONCURRENTLY — удобно для тяжёлых дашбордов.
- В Приложении П6 (в конце презентации) вы можете прямо в браузере нажать Run и выполнить запрос к game.v_player_summary.

## Slide 19. Почему Docker volume и реплика — это НЕ резервная копия
(id 379, https://easeweb.host/movie/itmo-db-lesson-5-2/19/)
- ЧАСТЬ 4 · РЕЗЕРВНОЕ КОПИРОВАНИЕ И ВОССТАНОВЛЕНИЕ
- Почему Docker volume и реплика — это НЕ резервная копия
- Разница между отказоустойчивостью (High Availability) и аварийным восстановлением (Disaster Recovery)
- Заблуждение 1: «У меня данные в Docker volume»
- Заблуждение 2: «У нас есть синхронная реплика БД»
- • Docker volume защищает данные только от пересоздания самого контейнера (docker rm).
  • Если диск сервера выйдет из строя, или кто-то выполнит docker compose down -v, или скрипт миграции сделает ошибочный DELETE FROM players — данные в volume исчезнут мгновенно.
- • Реплика спасает при физическом падении первого сервера (High Availability).
  • Но реплика за миллисекунды повторяет все команды мастера! Если на мастере выполнили DROP TABLE purchases или испортили балансы багом, реплика тут же станет такой же сломанной.
- Два класса настоящих бэкапов в PostgreSQL
- • Логический бэкап (pg_dump / pg_dumpall): снимает непротиворечивый снимок схемы и данных на момент старта (через механизм MVCC, не блокируя чтение и запись игроков!). Позволяет восстановить как всю БД, так и одну конкретную таблицу.
  • Физический бэкап + архивация WAL (pg_basebackup, pgBackRest, WAL-G): поблочная копия файлов БД + поток журнала предзаписи (WAL). Нужен для больших баз (сотни ГБ и ТБ) и восстановления на конкретную секунду в прошлом (Point-in-Time Recovery, PITR).
- Как pg_dump делает дамп живой базы без стоп-крана? Он запускает транзакцию с уровнем изоляции REPEATABLE READ и видит согласованный снимок на секунду старта.

## Slide 20. Форматы pg_dump: текстовый SQL (-Fp) и сжатый архив (-Fc)
(id 380, https://easeweb.host/movie/itmo-db-lesson-5-2/20/)
- ЧАСТЬ 4 · ЛОГИЧЕСКОЕ РЕЗЕРВНОЕ КОПИРОВАНИЕ PG_DUMP
- Форматы pg_dump: текстовый SQL (-Fp) и сжатый архив (-Fc)
- Почему в инженерной практике стандартом для pg_dump является формат Custom (-Fc)
- Формат Plain SQL (-Fp, по умолчанию)
- Формат Custom (-Fc, промышленный стандарт)
- • На выходе — обычный текстовый файл backup.sql с командами CREATE TABLE и COPY ... FROM stdin.
  • Чем восстанавливается: обычным клиентом psql -f backup.sql (утилита pg_restore его не принимает!).
  • Плюс: удобно открыть глазами в редакторе или положить в Git как эталон схемы (--schema-only).
- • На выходе — бинарный сжатый архив backup.dump с внутренним оглавлением всех объектов (каталогом).
  • Чем восстанавливается: строго утилитой pg_restore.
  • Суперсилы: автоматическое сжатие, просмотр оглавления (pg_restore -l), выборочное восстановление одной таблицы (-t purchases) и многопоточность (-j 4)!

```bash
# 1. Снимаем полный сжатый дамп (-Fc) базы gamedb внутри контейнера:
docker exec itmo_pg_dba pg_dump -U postgres -d gamedb -Fc -f /tmp/gamedb.dump

# 2. Снимаем только схему без данных (--schema-only) в текстовый SQL для Git-репозитория:
docker exec itmo_pg_dba pg_dump -U postgres -d gamedb --schema-only -n game > schema_snapshot.sql

# 3. Смотрим оглавление бинарного архива без подключения к БД:
docker exec itmo_pg_dba pg_restore -l /tmp/gamedb.dump | head -n 20
```
- Важная деталь для PowerShell на Windows: оператор > в старом PowerShell 5 может перекодировать бинарный поток в UTF-16! Надёжнее писать через -f /tmp/gamedb.dump и копировать через docker cp.

## Slide 21. Золотое правило DBA: проверка восстановления через pg_restore
(id 381, https://easeweb.host/movie/itmo-db-lesson-5-2/21/)
- ЧАСТЬ 4 · ПРОВЕРКА ВОССТАНОВЛЕНИЯ (RESTORE DRILL)
- Золотое правило DBA: проверка восстановления через pg_restore
- Битый или неполный файл дампа ничем не отличается от его отсутствия — проверяем восстановление в чистую БД

```bash
# Шаг 1. Снимаем архив внутри контейнера (безопасно для любой ОС и кодировки терминала)
docker exec itmo_pg_dba pg_dump -U postgres -d gamedb -Fc -f /tmp/gamedb.dump

# Шаг 2. Создаём абсолютно чистую проверочную базу gamedb_restore_check
docker exec itmo_pg_dba psql -v ON_ERROR_STOP=1 -U postgres \
  -c "DROP DATABASE IF EXISTS gamedb_restore_check;" \
  -c "CREATE DATABASE gamedb_restore_check;"

# Шаг 3. Восстанавливаем архив с остановкой на первой же ошибке (--exit-on-error)
docker exec itmo_pg_dba pg_restore --exit-on-error -U postgres -d gamedb_restore_check /tmp/gamedb.dump

# Шаг 4. Сравниваем количество строк в таблицах gamedb и gamedb_restore_check!
```
- Глобальные объекты кластера (Роли) и pg_dump
- Точечное спасение одной таблицы
- • Помните: pg_dump дампит только одну базу данных, а роли (game_owner, game_app) принадлежат всему кластеру PostgreSQL!
  • При восстановлении на новый сервер сначала переносят роли через pg_dumpall --roles-only, либо добавляют --no-owner --no-privileges.
- • Если разработчик случайно очистил справочник предметов, не нужно откатывать всю базу на вчерашний день:
  • Достаточно вытащить из архива одну таблицу: pg_restore -U postgres -d gamedb --clean -t items /tmp/gamedb.dump.
- В Практике 5 этот цикл (снятие .dump → заливка в restore_check → сверка COUNT(*)) вы выполните для базы своего командного проекта.

## Slide 22. Массовый перенос данных: серверный COPY и клиентский \copy
(id 382, https://easeweb.host/movie/itmo-db-lesson-5-2/22/)
- ЧАСТЬ 4 · ЭКСПОРТ И ИМПОРТ ДАННЫХ (CSV)
- Массовый перенос данных: серверный COPY и клиентский \copy
- Как за секунду выгрузить отчёт для геймдизайнера в CSV или загрузить стартовый каталог предметов
- SQL-команда COPY (выполняется сервером СУБД)
- Метакоманда \copy в psql (выполняется на клиенте)
- Синтаксис: COPY game.items TO '/tmp/items.csv' CSV HEADER;
  • Путь к файлу ищется на файловой системе самого сервера PostgreSQL (внутри Docker-контейнера).
  • Требует прав суперпользователя postgres или системной роли pg_read_server_files / pg_write_server_files.
- Синтаксис: \copy (SELECT ...) TO 'report.csv' WITH CSV HEADER
  • Читает и пишет файл на машине, где запущен клиент psql, перегоняя поток через сетевое соединение.
  • Не требует прав суперпользователя — работает даже под ролью game_readonly для выгрузки отчётов!

```sql
-- 1. Выгружаем витрину игроков в CSV-файл от имени аналитика (game_readonly):
\copy (SELECT * FROM game.v_player_summary ORDER BY total_spent DESC) TO '/tmp/player_report.csv' WITH CSV HEADER

-- 2. Загружаем новые предметы из CSV в таблицу game.items (без колонки item_id — её сгенерирует БД!):
\copy game.items(item_code, title, rarity, base_price) FROM '/tmp/new_items.csv' WITH CSV HEADER

-- 3. ВАЖНО: если вы когда-либо загружали CSV с явными ID (OVERRIDING SYSTEM VALUE), синхронизируйте sequence:
SELECT setval(pg_get_serial_sequence('game.items', 'item_id'), MAX(item_id)) FROM game.items;
```
- Почему ломается sequence после ручной загрузки ID? Счётчик не знает о вставленных вручную числах — вызов setval(...) переводит его на MAX(id).

## Slide 23. Частые ошибки при реализации схемы и администрировании
(id 383, https://easeweb.host/movie/itmo-db-lesson-5-2/23/)
- РАЗБОР ГРАБЛЕЙ · ЧТО ПРОВЕРИТЬ ПЕРЕД СДАЧЕЙ
- Частые ошибки при реализации схемы и администрировании
- Шесть классических ловушек, на которых спотыкаются команды при переносе схемы в живой PostgreSQL
- 1. Расхождение DBML и реальной БД
- 2. Изменили init.sql, но не пересоздали том
- Нарисовали связи в dbdiagram.io, но в SQL забыли FOREIGN KEY. Лечится проверкой обратной ER-диаграммы в DBeaver.
- Поправили скрипт в /docker-entrypoint-initdb.d/ и сделали restart. На непустом volume он не выполнится — нужен docker compose down -v.
- 3. Забыли GRANT USAGE на схему и sequence
- 4. ON DELETE CASCADE на покупках
- Выдали GRANT SELECT, INSERT ON TABLE, но забыли USAGE ON SCHEMA и USAGE, SELECT ON ALL SEQUENCES — сервис падает при вставке.
- Поставили каскадное удаление на purchases или финансовые транзакции — удаление предмета стирает историю платежей.
- 5. Попытка залить дамп -Fc через psql
- 6. Порт 0.0.0.0:5432 и работа под postgres
- Сняли бинарный архив pg_dump -Fc и подали его в psql < file.dump. Текстовый парсер выдаст ошибку — нужен pg_restore!
- Опубликовали порт контейнера на все интерфейсы и прописали суперпользователя в конфиг бэкенда вместо 127.0.0.1 и роли game_app.

## Slide 24. Что забрать с занятия и что делаем на Практике 5
(id 384, https://easeweb.host/movie/itmo-db-lesson-5-2/24/)
- ИТОГИ ЗАНЯТИЯ 5 · ПЕРЕХОД К ПРАКТИКЕ
- Что забрать с занятия и что делаем на Практике 5
- Опирайтесь на эту презентацию и стенд Lesson 5/dba/ при развёртывании собственной БД команды
- Чек-лист архитектора и DBA
- Задание на Практику 5 (10 баллов, по командам)
- • Физический DDL строже картинки: используем GENERATED ALWAYS AS IDENTITY, timestamptz, именованные CHECK и составные FK.
  • Осознанный ON DELETE: RESTRICT для денег и чеков, SET NULL для необязательных групп, CASCADE для внутренних деталей матча.
  • Проверка глазами и консолью: \d+ и \dp в psql + обратная ER-диаграмма в DBeaver.
  • Три роли RBAC: owner (DDL), app (DML + append-only на чеки) и readonly (SELECT и VIEW).
  • Бэкап проверен только после восстановления: pg_dump -Fc → pg_restore в чистую проверочную БД.
- Разворачиваем локально реляционную БД своего игрового проекта (из Практики 2):
  1. Docker + DDL + Seed (3 б.): поднять PostgreSQL на 127.0.0.1, воплотить все таблицы и связи в схеме game / app, заполнить стартовыми данными.
  2. Визуализация и верификация (2 б.): подключить DBeaver, снять скриншот обратной ER-диаграммы, доказать работу CHECK и FK.
  3. Роли и права RBAC (2,5 б.): настроить owner / app / readonly, закрыть журнал от DELETE, показать permission denied.
  4. Бэкап и CSV (2,5 б.): снять pg_dump -Fc, восстановить в restore_check, выгрузить отчёт через \copy.
- Дальше на следующих слайдах (П1–П6): интерактивный стенд PostgreSQL прямо в браузере — нажимайте Run и проверяйте запросы вживую!

## Slide 25. Контрольные вопросы по Занятию 5
(id 385, https://easeweb.host/movie/itmo-db-lesson-5-2/25/)
- САМОПРОВЕРКА
- Контрольные вопросы по Занятию 5
- Проверьте, можете ли вы уверенно ответить на эти 8 вопросов при защите Практики 5
- 1. IDENTITY vs serial
- 2. Составной внешний ключ
- Чем GENERATED ALWAYS AS IDENTITY отличается от устаревшего макроса serial и какую ошибку он предотвращает?
- Зачем в kill_log делать составной FK (match_id, killer_id) вместо одиночной ссылки на players(player_id)?
- 3. Политика ON DELETE
- 4. Ловушка Docker-тома
- В каких таблицах игрового проекта нужно ставить ON DELETE RESTRICT, где SET NULL, а где CASCADE?
- Почему изменение файла в /docker-entrypoint-initdb.d/ не применяется после обычного docker compose restart?
- 5. Роли и SEQUENCES
- 6. Защита журнала покупок
- Почему роли game_app недостаточно выдать только GRANT INSERT ON TABLE для вставки в таблицу с IDENTITY?
- Как на уровне привилегий PostgreSQL сделать таблицу purchases доступной сервису только на дозапись (Append-Only)?
- 7. pg_dump -Fp vs -Fc
- 8. COPY vs \copy
- В чём разница между форматами -Fp и -Fc в pg_dump и почему бинарный дамп нельзя подать в psql?
- Чем серверная команда COPY отличается от метакоманды \copy в psql с точки зрения прав и расположения файла?

## Slide 26. П1. Ревизия физической схемы и политик ON DELETE
(id 386, https://easeweb.host/movie/itmo-db-lesson-5-2/26/)
- ПРИЛОЖЕНИЕ · ЖИВОЙ СТЕНД POSTGRESQL · СЛАЙД П1
- П1. Ревизия физической схемы и политик ON DELETE
- Нажмите Run — проверим созданные в схеме game таблицы, внешние ключи и их политики ON DELETE

```sql
-- Сводка внешних ключей (FOREIGN KEY) и политик ON DELETE в схеме game:
SELECT
    tc.table_name       AS child_table,
    tc.constraint_name,
    ccu.table_name      AS parent_table,
    rc.delete_rule      AS on_delete_policy
FROM information_schema.table_constraints tc
JOIN information_schema.referential_constraints rc
  ON tc.constraint_name = rc.constraint_name
 AND tc.constraint_schema = rc.constraint_schema
JOIN (SELECT DISTINCT constraint_schema, constraint_name, table_name
      FROM information_schema.constraint_column_usage) ccu
  ON ccu.constraint_name = tc.constraint_name
 AND ccu.constraint_schema = tc.constraint_schema
WHERE tc.constraint_schema = 'game' AND tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name, tc.constraint_name;
```
- Что показывает этот запрос?
- • Именно из таблиц information_schema и pg_catalog графический клиент DBeaver строит обратную ER-диаграмму.
  • Обратите внимание на колонку on_delete_policy:
  — purchases → items / players имеют RESTRICT
  — players → clans имеет SET NULL
  — kill_log и match_participants имеют CASCADE
- Все 7 таблиц на месте
- Самопроверка в Практике 5
- Альтернатива в psql
- В схеме game развёрнуты таблицы clans, players, items, purchases, matches, match_participants и kill_log.
- Запустите такой же запрос на своей БД — если он вернул пустой результат, значит в вашем DDL пропущены FOREIGN KEY.
- В интерактивной сессии psql те же связи для конкретной таблицы мгновенно показывает команда \d+ game.purchases.

## Slide 27. П2. Проверка именованных правил CHECK в системном каталоге
(id 387, https://easeweb.host/movie/itmo-db-lesson-5-2/27/)
- ПРИЛОЖЕНИЕ · ЖИВОЙ СТЕНД POSTGRESQL · СЛАЙД П2
- П2. Проверка именованных правил CHECK в системном каталоге
- Нажмите Run — посмотрим все проверочные ограничения (CHECK), защищающие домен нашей игры

```sql
-- Проверяем все именованные ограничения CHECK в схеме game:
SELECT
    rel.relname AS table_name,
    con.conname AS check_name,
    pg_get_constraintdef(con.oid) AS definition
FROM pg_constraint con
JOIN pg_class rel ON rel.oid = con.conrelid
JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
WHERE nsp.nspname = 'game'
  AND con.contype = 'c'
ORDER BY rel.relname, con.conname;
```
- Почему мы видим понятные имена?
- • Каждое правило объявлено через CONSTRAINT chk_... CHECK (...).
  • Здесь видны все доменные инварианты нашего игрового проекта:
  — неотрицательные цены (base_price >= 0, price_paid >= 0)
  — валидная редкость предмета (common / rare / epic / legendary)
  — запрет фрага самого себя (killer_id <> victim_id)
- pg_constraint
- Читаемые ошибки в API
- Никаких «магических» строк
- Системный каталог pg_constraint хранит первичные (p), внешние (f), уникальные (u) и проверочные (c) ограничения.
- Когда игрок пытается совершить невалидное действие, бэкенд ловит код ошибки PostgreSQL 23514 (check_violation) и имя констрейнта.
- Опечатка в rarity = 'legndary' или team = 'green' будет отбита самой базой до записи на диск.

## Slide 28. П3. Тестируем защиту БД: как CHECK и составной FK ловят баги
(id 388, https://easeweb.host/movie/itmo-db-lesson-5-2/28/)
- ПРИЛОЖЕНИЕ · ЖИВОЙ СТЕНД POSTGRESQL · СЛАЙД П3
- П3. Тестируем защиту БД: как CHECK и составной FK ловят баги
- Нажмите Run — проверим, как база перехватывает отрицательную цену и фраг от чужого игрока

```sql
DO 
BEGIN
    -- 1. Баг скидки пытается записать отрицательную цену покупки (-50)
    BEGIN
        INSERT INTO game.purchases (player_id, item_id, price_paid) VALUES (1, 1, -50);
    EXCEPTION WHEN check_violation THEN
        RAISE NOTICE [CHECK OK] Отрицательная цена отбита: %, SQLERRM;
    END;
    -- 2. Игрок #4 НЕ играл в матче #1 — пробуем записать ему фраг в матче #1
    BEGIN
        INSERT INTO game.kill_log (match_id, killer_id, victim_id, weapon) VALUES (1, 4, 2, railgun);
    EXCEPTION WHEN foreign_key_violation THEN
        RAISE NOTICE [FK OK] Чужой игрок отбит составным FK: %, SQLERRM;
    END;
END ;
SELECT COUNT(*) AS valid_kills_in_match_1 FROM game.kill_log WHERE match_id = 1;
```
- Что произошло при нажатии Run?
- • Первая вставка нарушила chk_purchases_price (price_paid >= 0) — база выбросила исключение check_violation.
  • Вторая вставка попыталась записать в матч #1 игрока player_id = 4 (он играл только в матче #2). Составной FK fk_kill_killer (match_id, killer_id) мгновенно заблокировал фальшивый фраг!
  • В таблице kill_log остались только 3 валидных фрага.
- Обязательный пункт Практики 5
- Атомарность проверки
- Почему блок DO $$?
- В отчёте по Практике 5 вы точно так же демонстрируете 2–3 намеренно невалидных INSERT / DELETE, которые отбивает ваша схема.
- Невалидная строка ни на микросекунду не становится видимой другим транзакциям — она отвергается до коммита.
- Блок DO $$ ... EXCEPTION ... $$ позволяет перехватить ожидаемую ошибку, вывести её в NOTICE и продолжить выполнение скрипта.

## Slide 29. П4. Проверяем ON DELETE RESTRICT и SET NULL вживую
(id 389, https://easeweb.host/movie/itmo-db-lesson-5-2/29/)
- ПРИЛОЖЕНИЕ · ЖИВОЙ СТЕНД POSTGRESQL · СЛАЙД П4
- П4. Проверяем ON DELETE RESTRICT и SET NULL вживую
- Нажмите Run — попробуем удалить купленный предмет и распустить клан, в котором состоят игроки

```sql
-- 1. Пробуем удалить предмет item_id = 1 ('Plasma Rifle'), который уже покупали:
DO $$
BEGIN
    DELETE FROM game.items WHERE item_id = 1;
EXCEPTION WHEN foreign_key_violation THEN
    RAISE NOTICE '[RESTRICT OK] Купленный предмет защищён: %', SQLERRM;
END $$;

-- 2. В транзакции с откатом (ROLLBACK) распускаем клан [SHADOW]:
BEGIN;
DELETE FROM game.clans WHERE clan_tag = 'SHADOW';
SELECT player_id, nickname, clan_id FROM game.players ORDER BY player_id;
ROLLBACK;
```
- Разбор результата
- • 1. ON DELETE RESTRICT спас историю покупок: попытка удалить item_id = 1 остановлена внешним ключом fk_purchases_item.
  • 2. ON DELETE SET NULL обнулил ссылку: после удаления клана SHADOW игроки ShadowFox и Valkyrie остались в таблице, а их clan_id стал NULL!
  • В конце стоит ROLLBACK, чтобы вы могли нажимать Run сколько угодно раз.
- Эксперименты в транзакции
- Soft Delete в реальных играх
- Каскад на матчах
- Оборачивая тест в BEGIN ... ROLLBACK, вы проверяете разрушающие операции на живых данных и чисто откатываете изменения назад.
- Вместо физического удаления предмета из items в таблицу обычно добавляют флаг is_archived boolean DEFAULT false.
- А вот удаление чернового матча (DELETE FROM game.matches) благодаря ON DELETE CASCADE автоматически очистит его участников и фраги.

## Slide 30. П5. Матрица прав RBAC и защита журнала покупок (Append-Only)
(id 390, https://easeweb.host/movie/itmo-db-lesson-5-2/30/)
- ПРИЛОЖЕНИЕ · ЖИВОЙ СТЕНД POSTGRESQL · СЛАЙД П5
- П5. Матрица прав RBAC и защита журнала покупок (Append-Only)
- Нажмите Run — проверим матрицу привилегий для ролей game_app и game_readonly по всем 7 таблицам

```sql
-- Проверяем матрицу привилегий ролей game_app и game_readonly:
SELECT
    t.table_name,
    game.has_role_privilege('game_app',      t.table_name, 'SELECT') AS app_select,
    game.has_role_privilege('game_app',      t.table_name, 'INSERT') AS app_insert,
    game.has_role_privilege('game_app',      t.table_name, 'DELETE') AS app_delete,
    game.has_role_privilege('game_readonly', t.table_name, 'SELECT') AS ro_select,
    game.has_role_privilege('game_readonly', t.table_name, 'INSERT') AS ro_insert
FROM information_schema.tables t
WHERE t.table_schema = 'game'
  AND t.table_type = 'BASE TABLE'
ORDER BY t.table_name;
```
- Смотрим на колонку app_delete!
- • Для операционных таблиц (clans, items, matches, players) у роли game_app стоит app_delete = true.
  • А для защищённых журналов kill_log и purchases стоит app_insert = true, но app_delete = false!
  • У аналитической роли game_readonly везде разрешён только ro_select = true, а ro_insert = false.
- Проверка матрицы доступа
- Append-Only журнал
- Тест через SET ROLE
- Один SQL-запрос наглядно показывает проверяющему всю картину разграничения прав по каждой таблице вашей схемы.
- Даже если злоумышленник или баг в микросервисе попытается выполнить DELETE FROM game.purchases, база ответит permission denied.
- В локальном контейнере выполните SET ROLE game_app; DELETE FROM game.purchases; и убедитесь в отказе своими глазами.

## Slide 31. П6. Аналитическая витрина VIEW и отчёт по покупкам и матчам
(id 391, https://easeweb.host/movie/itmo-db-lesson-5-2/31/)
- ПРИЛОЖЕНИЕ · ЖИВОЙ СТЕНД POSTGRESQL · СЛАЙД П6
- П6. Аналитическая витрина VIEW и отчёт по покупкам и матчам
- Нажмите Run — обратимся к представлению game.v_player_summary, которое использует роль game_readonly

```sql
-- Запрашиваем витрину игроков (колонка email скрыта представлением!):
SELECT
    player_id,
    nickname,
    COALESCE(clan_tag, '— без клана —') AS clan,
    level,
    purchases_cnt,
    total_spent,
    kills_cnt
FROM game.v_player_summary
ORDER BY total_spent DESC, kills_cnt DESC;
```
- Итог работы представления
- • Аналитик или стажёр с ролью game_readonly выполняет простейший SELECT к game.v_player_summary.
  • Он получает готовую сводку по кланам, покупкам, сумме трат и фрагам каждого игрока, не имея доступа к приватному полю email из таблицы game.players.
  • Именно этот запрос в Практике 5 удобно выгружать в CSV командой \copy!
- Готово к выгрузке в CSV
- Все материалы в репозитории
- Успехов на Практике 5!
- Оберните этот запрос в \copy (SELECT ...) TO 'report.csv' WITH CSV HEADER — и отчёт для геймдизайнера готов.
- Полный скрипт стенда лежит в папке Lesson 5/dba/live_admin_pg.sql, а подробная шпаргалка команд — в Lesson 5/dba/appendix.md.
- Разверните свою схему из Практики 2 в Docker, проверьте её в DBeaver, настройте 3 роли и проведите учебное восстановление pg_restore.