Роли и минимальные права
Приложение читает публичный каталог, редактор изменяет карточки, а владелец базы проводит миграции. Если все три операции выполняются одним владельцем, ошибка обычного запроса получает слишком широкие возможности. Права PostgreSQL позволяют выразить часть этой границы непосредственно в базе: доступ к схеме, объекту и даже выбранным колонкам.
Продолжаем на PostgreSQL 17.11. Самостоятельная папка catalog-db-advanced/lesson-29 находится в архиве продолжения. Основные данные прежние: шесть курсов, двенадцать уроков, один черновой курс. Роли и grants подготовлены текстом, административные действия не выполнялись. Перед будущим ручным опытом нужен отдельный локальный экземпляр; роли существуют на уровне кластера, поэтому одного похожего имени базы для изоляции недостаточно.
Роль, вход и владение
Роль может обозначать пользователя, группу разрешений или владельца объекта. Наличие LOGIN отвечает за возможность соединения с такой ролью; оно не выдаёт автоматически доступ ко всем таблицам. В нашем опыте catalog_reader и catalog_editor имеют NOLOGIN. Это две группы возможностей, а не готовые учётные записи с паролями.
Административный файл создания групп выполнялся бы один раз владельцем лаборатории с необходимым правом управления ролями:
CREATE ROLE catalog_reader NOLOGIN;
CREATE ROLE catalog_editor NOLOGIN;
GRANT catalog_reader, catalog_editor TO professorweb_student;Учебный student получает членство, чтобы показать SET ROLE в уже открытом соединении. Он также владеет учебной схемой для предыдущих занятий. Это удобство лаборатории нельзя переносить как конфигурацию публичного приложения: production-подключение чтения не должно одновременно владеть таблицами и наследовать группу редактора. Иначе ограниченный SQL становится лишь добровольной договорённостью клиента.
Правила членства, наследования и переключения роли изложены в документации PostgreSQL. В упражнении переключение явное. Сначала важно различить session user и текущую роль, а затем рассуждать о правах конкретного запроса. Проверка от имени владельца не доказывает, что ограниченный читатель сможет выполнить ту же команду.
Доступ к схеме и публичная витрина
USAGE схемы позволяет обращаться к находящимся в ней объектам, но само по себе не разрешает читать все строки. Для публичной библиотеки создадим представление с явным списком полей и условием публикации:
CREATE OR REPLACE VIEW catalog.public_courses
WITH (security_barrier = true) AS
SELECT id, slug, title, topic_id
FROM catalog.courses
WHERE status = 'published';
GRANT USAGE ON SCHEMA catalog TO catalog_reader;
GRANT SELECT ON catalog.public_courses TO catalog_reader;После SET ROLE читатель обращается к витрине:
SET ROLE catalog_reader;
SELECT id, slug, title FROM catalog.public_courses ORDER BY id;
RESET ROLE;Ожидается пять карточек с id 1, 2, 3, 5 и 6. Курс производительности с id 4 не опубликован, поэтому в этом наборе его нет. Витрина не превращает черновик в удалённую запись: редактор и владелец продолжают видеть его при разрешённом обращении к исходной таблице.
У нашего view применяется обычная проверка прав к исходной таблице через владельца представления. Читателю выдан SELECT на view, а не SELECT на courses. Включение security_invoker поменяло бы этот договор и потребовало бы учитывать права вызывающего на базовые отношения. Здесь такой опции нет. Поведение представлений и security_barrier описано в CREATE VIEW.
Это учебная публичная проекция. Она не реализует персональные кабинеты, распределение редакторов по темам или полноценную модель доступа каждой строки. Если появятся приватные курсы для разных пользователей, нужно отдельно спроектировать авторизацию приложения и, при необходимости, row security. Нельзя считать один фильтр published ответом на все будущие требования.
Редактор ограниченного набора полей
Редактору дадим чтение полей, нужных форме, и изменение названия, плана и версии:
GRANT USAGE ON SCHEMA catalog TO catalog_editor;
GRANT SELECT (id, title, planned_lessons, version)
ON catalog.courses TO catalog_editor;
GRANT UPDATE (title, planned_lessons, version)
ON catalog.courses TO catalog_editor;Идентификатор нужен WHERE, версия нужна защите от устаревшей формы, новое название возвращается через RETURNING. Таким образом, чтение здесь относится также к выражениям команды изменения. Если просто разрешить UPDATE без необходимых прав SELECT, запрос с условием или возвращаемыми колонками может быть отвергнут.
Подготовленный пример сохраняет временное название и откатывает его:
SET ROLE catalog_editor;
BEGIN;
UPDATE catalog.courses
SET title = 'JavaScript: редакционная правка', version = version + 1
WHERE id = 2 AND version = 1
RETURNING id, title, version;
ROLLBACK;
RESET ROLE;Внутри транзакции ожидается id 2 с новым названием и version 2. После ROLLBACK исходное название и версия 1 сохраняются. Возврат к исходной роли обязателен для дальнейших административных действий учебного владельца, но не является частью клиентской бизнес-операции. При ошибке в транзакции сначала выполняется ROLLBACK, затем RESET ROLE.
Редактору не выданы UPDATE статуса или slug, DELETE, INSERT и права на lessons. Запрос изменения status должен быть запрещён этой конфигурацией даже при корректном значении статуса. Однако такая граница действует только если группа не получила более широкое разрешение другим путём. Табличное UPDATE перекрыло бы ограничение по колонкам; права складываются, а не работают как запрещающие правила.
Разрешение SQL и пользовательское право
Колонковые grants ограничивают техническую возможность редактора, но не знают, какой человек открыл форму. Если десять сотрудников используют одно серверное соединение catalog_editor, база видит общую роль. Решение «этот сотрудник может править только свою тему» в нашем примере отсутствует. Его нельзя получить автоматически из того, что SELECT перечисляет нужные поля.
Зато техническая граница помогает уменьшить последствия ошибки конкретного пути. Форма смены названия не нуждается в удалении уроков или смене устойчивого slug. Поэтому эти операции не выдаются ей ради удобства. Когда появляется отдельная функция публикации, для неё следует сформулировать собственный ограниченный договор, а не расширить все существующие группы до владельца.
Проверять права полезно под конкретной текущей ролью и тем же выражением, которое использует клиент. Например, SELECT * требует чтения всех колонок и не является эквивалентом разрешённого SELECT id,title,version. Ошибка такой команды не доказывает, что настроенная форма сломана; она может показывать, что диагностический запрос шире реального договора.
Витрина возвращает только заявленные поля. Новая конфиденциальная колонка исходной таблицы не появится в ней автоматически, поскольку мы не использовали звёздочку. При изменении публичного ответа список полей пересматривается сознательно. Так настройка прав и форма результата поддерживают друг друга, вместо того чтобы один удачный GRANT случайно разрешал весь будущий каталог.
Права существующих и будущих объектов
В полном admin-grants.sql сначала снимаются права PUBLIC на учебную схему и таблицы. PUBLIC обозначает все роли, а не конкретного пользователя интерфейса. При анализе разрешений нужно учитывать прямые grants, членство и владение. Отсутствие одной строки GRANT рядом с приложением ещё не доказывает отсутствие доступа через другую группу.
Разрешения на объекты и смысл отдельных привилегий изложены в разделе о правах. Наш файл назначает возможности конкретным уже созданным объектам. Новая таблица после очередной миграции не обязана получить тот же договор автоматически. Для будущих объектов существует отдельная настройка default privileges, привязанная к роли, которая их создаёт; её нельзя путать с повторным GRANT существующим таблицам.
Учебный reset удаляет catalog CASCADE и создаёт объекты заново. Их старые разрешения исчезают вместе с объектами, тогда как роли кластера остаются. Поэтому создание групп не повторяют бесшумно при каждом reset, а файл назначения прав применяют отдельно к восстановленной схеме. Эта последовательность делает повтор опыта понятным: известны и данные, и права, и текущая роль соединения.
Практическое изменение можно оценить через конкретный запрос. Публичному чтению нужен набор пяти карточек и только четыре заявленных поля. Редактору нужна одна контролируемая правка с проверкой version. Миграции требуется владение схемой, которое не добавляется в группу ради удобства формы. Когда эти обязанности названы отдельно, проще определить, какой запрос сломался из-за недостающего разрешения, а какой обнаружил действительно лишнюю возможность.