Защитите чтение и запись участников ярмарки

Введение

Fern Studio и Cedar Works готовятся к небольшой ярмарке ремёсел. Каждый участник должен вести собственные записи о завозе грузов в одной общей таблице PostgreSQL. При этом организаторам необходимо видеть всех участников и назначать им места. Фильтр в клиентской команде удобен для просмотра, но участник может его убрать; ограничение должна обеспечивать сама база данных.

Вы сохраните и примените политики строк, а затем заполните реальные записи через отдельные обычные учётные записи с парольной аутентификацией. PostgreSQL и учётные записи для упражнения уже подготовлены. Вам понадобятся базовые навыки навигации в терминале и знание SELECT/WHERE, INSERT, UPDATE и таблиц PostgreSQL. Роли, защита на уровне строк, выражения политик, транзакции и необходимый синтаксис оболочки объясняются до их использования. Программировать на Python не требуется. На протяжении всей работы используйте Terminal. Продолжительность 95 минут — авторская оценка с учётом чтения и обычных ошибок, а не результат замера прохождения новичками.

Работайте в /home/labex/project/exhibitor-desk. Создайте read-access.sql и write-access.sql, измените политики строк для fair.registrations и создавайте или исправляйте только две новые записи, указанные далее. Сохраните все исходные записи, саму таблицу и её определения, подготовленные права и учётные записи, учётные данные, инструменты и службы. Файлы политик должны содержать самодостаточный SQL: без команд оболочки, смены подключения и подключения внешних файлов. Допускаются другие имена политик и эквивалентные корректные сочетания политик. Вы выступаете оператором учебной среды и можете намеренно выбирать любую из подготовленных тестовых учётных записей; упражнение защищает одни идентичности базы данных от других, но не скрывает учётные данные от оператора VM с sudo. Оно не реализует аутентификацию на сайте и не обещает защиты от всех побочных каналов утечки информации.

Ограничьте видимость, сохранив доступ организаторов

На этом шаге вы предоставите каждому участнику полный доступ к просмотру собственных записей и сохраните возможность организаторов работать с записями всех участников. Запись для участников останется недоступной, пока на следующем шаге не появятся соответствующие политики.

Начните в Terminal. cd меняет каталог; этот абсолютный путь работает независимо от текущего каталога:

cd /home/labex/project/exhibitor-desk

./ запускает программу из текущего каталога. Подготовленная программа запуска db устанавливает настоящее подключение к PostgreSQL с аутентификацией и запускает клиент PostgreSQL psql. Она не фильтрует строки и не подменяет результаты. Выведите список алиасов учётных записей:

./db accounts

Алиасы выбирают следующие отдельно подготовленные учётные записи:

Алиас удобен вам как оператору; это не разрешение базы данных, выданное Fern. Выбирая cedar, вы намеренно используете отдельные учётные данные Cedar. Внутри подключения Fern переход к роли Cedar должен оставаться запрещённым.

-c передаёт одну строку команды. Двойные кавычки сохраняют её пробелы для оболочки. session_user указывает учётную запись, прошедшую аутентификацию; current_user — роль, чьи разрешения сейчас использует запрос. В этих обычных подключениях они должны совпадать:

./db fern -c "SELECT session_user, current_user;"

В обоих столбцах ожидается fern_studio. Сначала прочитайте известную обычную запись Fern. Число ящиков — целое количество; ноль означает известное нулевое количество, а не отсутствие записи:

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations WHERE registration_id = 'R101';"

R101 принадлежит Fern: контакт Fern desk, 2 ящика, место A1 и сбор 1200 центов. fair — схема, объединяющая таблицы. Эти исходные деловые данные должны остаться неизменными.

Теперь уберите необязательный клиентский фильтр:

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

Сначала Fern видит R101/R102 и записи Cedar R201/R202. WHERE owner_role = 'fern_studio' скрыл бы их в одном запросе, но не помешал бы выполнить этот запрос без фильтра. У Cedar та же проблема.

В PostgreSQL здесь важны два уровня разрешений. GRANT разрешает операцию с таблицей или указанными столбцами; подготовленные права уже позволяют этим пользователям обращаться к необходимым операциям. Защита на уровне строк, или RLS, дополнительно определяет, какие строки разрешено читать или менять. Если включить RLS без подходящей политики, доступ к строкам по умолчанию запрещён; правило владения автоматически не появляется.

editor владеет этой учебной таблицей и может менять политики. Владельцы обычно обходят RLS, поэтому успешный запрос редактора не доказывает, что участник ограничен. Используйте редактора для просмотра определений, а обычные учётные записи — для проверки результата. \d — команда описания psql, а не SQL:

./db editor -c "\d fair.registrations"

В таблице шесть столбцов: registration_id, owner_role, contact, crates, booth_label и fee_cents. Новая запись по умолчанию получает место Pending и сбор 0. Подготовленные права на столбцы разрешают участникам вставлять ID/владельца/контакт/число ящиков и менять владельца/контакт/число ящиков. Они не могут менять ID, места или сборы, удалять строки или очищать таблицу через truncate. Политика всё равно должна запрещать смену владельца, хотя право UPDATE позволяет обращаться к этому столбцу.

Просмотрите существующие политики через системное представление PostgreSQL pg_policies:

./db editor -c "SELECT policyname, roles, cmd, qual, with_check FROM pg_policies WHERE schemaname = 'fair' AND tablename = 'registrations' ORDER BY policyname;"

legacy_open_access разрешает доступ всем. Обычно политики разрешительные: применимые политики объединяются через OR. Если добавить узкую политику рядом с этой широкой, широкий доступ сохранится. Удалите устаревшую политику в рамках изменения.

Выражение USING в политике определяет, видна ли существующая строка. owner_role = current_user сравнивает владельца строки с реальной идентичностью базы данных. FOR SELECT ограничивает эту политику чтением. Организатору нужна отдельная политика с областью действия TO fair_team, разрешающая его законные операции со всеми строками; true означает, что каждая строка проходит проверку для этой конкретной роли.

Сохраните первую миграцию. cat > read-access.sql записывает следующий текст в файл, заменяя предыдущую копию. <<'SQL' начинает here-document: маркер в кавычках сохраняет вложенный SQL буквально до появления SQL на отдельной строке. BEGIN и COMMIT объединяют изменение политик в одну транзакцию: команды либо фиксируются вместе, либо откатываются при ошибке. DROP POLICY IF EXISTS также позволяет исправлять и повторно выполнять эту миграцию:

cat > read-access.sql <<'SQL'
BEGIN;
ALTER TABLE fair.registrations ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS legacy_open_access ON fair.registrations;
DROP POLICY IF EXISTS own_read ON fair.registrations;
DROP POLICY IF EXISTS organizer_access ON fair.registrations;
CREATE POLICY own_read ON fair.registrations
  FOR SELECT TO fern_studio, cedar_works
  USING (owner_role = current_user);
CREATE POLICY organizer_access ON fair.registrations
  TO fair_team USING (true) WITH CHECK (true);
COMMIT;
SQL

Если оболочка продолжает ждать из-за пропущенного завершающего маркера, введите SQL на отдельной строке. Прочитайте сохранённый файл перед выполнением:

cat read-access.sql

-f указывает psql читать команды из файла. Примените его от имени владельца учебной таблицы:

./db editor -f read-access.sql

Ожидаются сообщения BEGIN, ALTER TABLE, DROP POLICY, CREATE POLICY и COMMIT. Уведомление о том, что одна из новых политик ещё не существовала, безвредно. Программа запуска включает ON_ERROR_STOP: синтаксическая ошибка в файле останавливает выполнение с кодом psql 3. При закрытии подключения незавершённая транзакция откатывается. Исправьте файл и запустите его снова; сбрасывать VM не нужно. При ошибке имени файла или пути выполните cd с абсолютным путём, запустите ls, прочитайте фактическое имя файла и повторите попытку. Удалять или пересоздавать исходные строки не требуется.

Проверьте полный разрешённый набор от имени каждой обычной учётной записи:

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

Fern видит R101/R102 с количеством ящиков 2/0. Cedar видит R201/R202 с количеством 3/1. Организатор по-прежнему видит все четыре исходные записи:

./db organizer -c "SELECT registration_id, owner_role, booth_label FROM fair.registrations ORDER BY registration_id;"

Это намеренное промежуточное состояние: у участников есть политика SELECT, но пока нет политик INSERT/UPDATE. Не меняйте подготовленные права. Запустите проверку результата; sudo позволяет подготовленному проверяющему скрипту прочитать защищённое состояние. Он читает текущие деловые данные и проверяет SQL политик на одноразовых данных, не выполняя за вас недостающую работу:

sudo python3 /opt/labex-exhibitors/verify.py visibility

Ожидается подтверждение, что обе учётные записи видят собственные записи, а организатор может продолжать работу. Далее добавьте отдельные правила для предлагаемых изменений.

Проверьте существующие строки и новые значения

На этом шаге вы разрешите участникам добавлять и обновлять собственные записи, не позволяя создавать записи для другого владельца или передавать их ему. Начните в Terminal, в /home/labex/project/exhibitor-desk.

Одна политика SELECT не разрешает INSERT. Политика записи должна учитывать два момента. Для UPDATE выражение USING выбирает существующие строки, которые роль может менять; WITH CHECK проверяет предлагаемую новую строку. У INSERT нет старой строки, поэтому его политика использует только WITH CHECK. В обоих случаях предлагаемый владелец должен совпадать с current_user.

Сохраните миграцию записи в том же формате here-document с маркером в кавычках. Она добавляет политики к таблице, уже защищённой через read-access.sql:

cat > write-access.sql <<'SQL'
BEGIN;
DROP POLICY IF EXISTS own_insert ON fair.registrations;
DROP POLICY IF EXISTS own_update ON fair.registrations;
CREATE POLICY own_insert ON fair.registrations
  FOR INSERT TO fern_studio, cedar_works
  WITH CHECK (owner_role = current_user);
CREATE POLICY own_update ON fair.registrations
  FOR UPDATE TO fern_studio, cedar_works
  USING (owner_role = current_user)
  WITH CHECK (owner_role = current_user);
COMMIT;
SQL

Прочитайте файл, затем примените его от имени редактора:

cat write-access.sql

./db editor -f write-access.sql

Ожидается фиксация двух команд CREATE POLICY. Исправление и повторное выполнение файла заменяет только эти именованные политики записи; записи о завозе не сбрасываются. Если вы заново применяете сохранённые файлы политик, сначала примените read-access.sql, затем write-access.sql.

Эквивалентные сочетания политик тоже могут работать. Политика ALL или UPDATE только с USING неявно использует это выражение вместо отсутствующего WITH CHECK; отсутствие этой части само по себе не является ошибкой. Здесь отдельные политики команд делают каждую задачу явной. Важно поведение каждой разрешённой и запрещённой операции, а не имена политик или точное написание команд.

Сначала выполните разрешённую запись, не оставляя тестовых данных. BEGIN начинает транзакцию. RETURNING показывает строку, фактически сохранённую INSERT/UPDATE. ROLLBACK отменяет изменения транзакции, сохраняя четыре исходные записи:

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FERN', 'fern_studio', 'Loading test', 0); UPDATE fair.registrations SET crates = 2 WHERE registration_id = 'TRY-FERN' RETURNING registration_id, owner_role, crates; ROLLBACK;"

Ожидаются TRY-FERN, владелец fern_studio и 2 ящика, затем ROLLBACK. Обычная вставка принимает настоящий ноль, а обновление работает; правило, запрещающее любую запись, не выполнило бы это требование.

Теперь проверьте скрытую существующую строку. Команда должна выполниться успешно, но не найти ни одной разрешённой для обновления строки:

./db fern -c "UPDATE fair.registrations SET contact = 'Wrong contact' WHERE registration_id = 'R201' RETURNING registration_id;"

Ожидаются отсутствие возвращённых строк и UPDATE 0. R201 принадлежит Cedar. Результат с нулём строк — это фильтрация строк, а не сбой подключения или ошибка базы данных.

Следующие две команды должны завершиться ошибкой SQL. Обе оборачивают пробу в транзакцию: если неверная политика случайно её разрешит, завершающий ROLLBACK всё равно отменит изменения. Если ошибка остановит команду до ROLLBACK, закрытие подключения также отменит незавершённую транзакцию.

Попробуйте создать запись с владельцем Cedar, войдя как Fern. Намеренно опустите RETURNING: проверка возвращённых строк политикой SELECT не должна подменять правило записи, проверяющее новую строку:

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FOREIGN', 'cedar_works', 'Wrong owner', 1); ROLLBACK;"

Ожидается new row violates row-level security policy. $? — код завершения непосредственно предшествующей команды оболочки; выведите его до выполнения любых других команд:

echo $?

Для этой ошибочной SQL-команды с -c ожидается код 1. Код 2 с ошибкой подключения или аутентификации означает другой сбой и не доказывает, что политика строк сработала. При каждом запуске программа выбирает подготовленные учётные данные; используйте ./db accounts и повторите документированный алиас, а не редактируйте файлы учётных данных.

Затем попробуйте передать видимые строки Fern владельцу Cedar. Этот UPDATE с постоянным значением не содержит WHERE или RETURNING, читающих столбец таблицы. Он непосредственно обращается к проверке новой строки; окружающая транзакция позволяет отменить даже ошибочно разрешённую пробу:

./db fern -c "BEGIN; UPDATE fair.registrations SET owner_role = 'cedar_works'; ROLLBACK;"

echo $?

Снова ожидаются ошибка защиты строк и код 1. Не делайте вывод, что защищать нужно только обновления с RETURNING: допустимый SQL может его не содержать.

Разрешения столбцов задают другую границу. Участник может менять свой контакт и количество, но места и сборы назначают организаторы. Эта попытка изменить место должна вызвать ошибку прав на таблицу даже для собственных строк Fern:

./db fern -c "BEGIN; UPDATE fair.registrations SET booth_label = 'WRONG'; ROLLBACK;"

Ожидается permission denied for table registrations, а не ошибка политики проверки новой строки. Аналогично подключение Fern не может стать Cedar, просто указав эту роль:

./db fern -c "SET ROLE cedar_works;"

Ожидается permission denied to set role. Подготовленные участники — обычные учётные записи без членства в ролях друг друга, владения таблицей или BYPASSRLS. Изменение произвольно задаваемого параметра приложения не аутентифицировало бы другую учётную запись; политика сравнивает реальную роль базы данных.

Убедитесь, что обратимые пробы сохранили исходные деловые данные:

./db organizer -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

Четыре исходные строки, контакты, количества, места и сборы должны совпадать с начальными значениями. Запустите проверку границ записи:

sudo python3 /opt/labex-exhibitors/verify.py writes

Она проверяет обе обычные учётные записи, разрешённые операции записи, изменённые наборы данных, запись с чужим владельцем и доступ организатора. Полное прохождение означает, что теперь защищённую таблицу можно использовать для обычной работы.

Завершите регистрацию через защищённые учётные записи

На этом шаге вы используете работающие разрешения, чтобы подготовить две настоящие записи о завозе. Начните в Terminal, в /home/labex/project/exhibitor-desk. Эти две новые записи — единственные деловые строки, которые разрешено добавлять или исправлять; сохраните каждую исходную строку.

Fern начинает новую запись с известным нулевым количеством ящиков. Значения по умолчанию оставляют место Pending и сбор 0, пока организатор не назначит их:

./db fern -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R301', 'fern_studio', 'Fern loading', 0) RETURNING registration_id, owner_role, contact, crates, booth_label, fee_cents;"

Ожидаются R301, fern_studio, Fern loading, 0, Pending, 0. Это настоящий зафиксированный INSERT, в отличие от прежних транзакционных проб. Его повтор вызовет ошибку дублирования ключа; эта команда создания не идемпотентна.

Установите итоговое количество ящиков — четыре — обычным адресным обновлением:

./db fern -c "UPDATE fair.registrations SET crates = 4 WHERE registration_id = 'R301' RETURNING registration_id, owner_role, crates;"

Cedar создаёт собственную новую запись с одним ящиком:

./db cedar -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R302', 'cedar_works', 'Cedar freight', 1) RETURNING registration_id, owner_role, crates;"

Ожидаются R302, cedar_works и 1. Теперь каждый участник видит три записи, включая две исходные.

Организатор через свою обычную учётную запись назначает согласованные место и сбор каждой новой записи. Сборы указаны в целых центах; заданные суммы — данные сценария, а не налоговый или бухгалтерский расчёт:

./db organizer -c "UPDATE fair.registrations SET booth_label = 'C3', fee_cents = 1800 WHERE registration_id = 'R301' RETURNING registration_id, booth_label, fee_cents;"

./db organizer -c "UPDATE fair.registrations SET booth_label = 'D2', fee_cents = 600 WHERE registration_id = 'R302' RETURNING registration_id, booth_label, fee_cents;"

Ожидаются R301/C3/1800 и R302/D2/600. Эта работа выполняется через fair_team, а не владельца таблицы; обход защиты владельцем не доказал бы, что политика организатора остаётся пригодной для работы.

Прочитайте полные итоговые записи от имени каждого участника:

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

Fern видит R101/R102/R301: у новой записи R301 количество 4, место C3 и сбор 1800. Cedar видит R201/R202/R302: у новой записи R302 количество 1, место D2 и сбор 600. Исходные записи должны остаться неизменными.

Если вы случайно повторили INSERT, просмотрите эту конкретную новую запись через соответствующую учётную запись. Если она уже существует, выполните адресный UPDATE контакта и количества вместо удаления записи или сброса базы данных. Например, новую запись Fern можно исправить командой UPDATE fair.registrations SET contact = 'Fern loading', crates = 4 WHERE registration_id = 'R301';. Исправляйте только указанные новые записи; при ошибке в назначении места или сбора повторите адресную команду организатора. Сохранённые миграции также можно исправить и применить повторно без пересоздания данных.

Запустите итоговую проверку результата:

sudo python3 /opt/labex-exhibitors/verify.py business

Ожидается подтверждение, что обе новые записи готовы и имеют правильных владельцев, количества, места и сборы. У вас есть повторно используемый SQL политик и общий реестр, с которым действительно могут работать обычные участники и организатор. В самостоятельном задании вы примените те же границы к другому реестру мастеров.

Резюме

Вы отделили разрешения на таблицу и столбцы от владения строками, удалили широкую разрешительную политику и проверили существующие строки и предлагаемые изменения через настоящие обычные учётные записи. Две новые записи и назначенные организатором места теперь доступны для работы без раскрытия или изменения записей другого участника.

Граница защиты — идентичность базы данных, подтверждённая аутентификацией. Владельцы таблиц и администраторы остаются доверенными лицами; упражнение не предоставляет вход на сайт и не гарантирует защиты от всех побочных каналов базы данных.