forked from mtusi2026/mtusi2026lab7
45 lines
3.3 KiB
SQL
45 lines
3.3 KiB
SQL
-- lab7: раздельные роли PostgreSQL с минимальными правами. Роли (LOGIN, пароль из OpenBao, CONNECTION LIMIT)
|
|
-- создаёт migrate.py ДО этой миграции; здесь только права. Владелец таблиц — messenger (bootstrap/миграции),
|
|
-- приложение им не пользуется. Права на столбцы/операции — по фактическим запросам каждого процесса
|
|
-- (проверено регрессией lab1–6: отказ прав виден как permission denied в тестах).
|
|
|
|
REVOKE ALL ON DATABASE messenger FROM PUBLIC;
|
|
REVOKE ALL ON SCHEMA public FROM PUBLIC;
|
|
GRANT CONNECT ON DATABASE messenger TO messenger_api, messenger_worker, messenger_dispatcher, messenger_janitor;
|
|
GRANT USAGE ON SCHEMA public TO messenger_api, messenger_worker, messenger_dispatcher, messenger_janitor;
|
|
|
|
-- Все процессы ждут готовую схему (missing_migrations).
|
|
GRANT SELECT ON schema_migrations TO messenger_api, messenger_worker, messenger_dispatcher, messenger_janitor;
|
|
|
|
-- API: пользователи/чаты/сообщения/вложения; задание на обработку — только INSERT в outbox и журнал.
|
|
-- UPDATE users — только столбец password_hash (rehash Argon2id при входе), остальное неизменяемо.
|
|
GRANT SELECT, INSERT ON users TO messenger_api;
|
|
GRANT UPDATE (password_hash) ON users TO messenger_api;
|
|
GRANT SELECT, INSERT ON chats, chat_members, messages, message_attachments TO messenger_api;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON attachments TO messenger_api;
|
|
GRANT SELECT ON attachment_variants TO messenger_api;
|
|
GRANT SELECT, INSERT, UPDATE ON attachment_jobs TO messenger_api;
|
|
GRANT INSERT ON job_events, outbox TO messenger_api;
|
|
|
|
-- Worker: захват/аренда/результат задания, варианты, повтор через outbox. Пользователей, чатов и
|
|
-- сообщений не видит вовсе.
|
|
GRANT SELECT, UPDATE ON attachments, attachment_jobs TO messenger_worker;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON attachment_variants TO messenger_worker;
|
|
GRANT INSERT ON job_events TO messenger_worker;
|
|
GRANT SELECT, INSERT ON outbox TO messenger_worker;
|
|
|
|
-- Dispatcher (и ops.py в его контейнере): публикация outbox, восстановление аренды, replay, реестр failed.
|
|
GRANT SELECT, INSERT, UPDATE ON outbox TO messenger_dispatcher;
|
|
GRANT SELECT, UPDATE ON attachment_jobs, attachments TO messenger_dispatcher;
|
|
GRANT SELECT, INSERT ON job_events TO messenger_dispatcher;
|
|
|
|
-- bigserial-ключи журнала и outbox: USAGE на sequence тем, кто вставляет строки.
|
|
GRANT USAGE ON SEQUENCE job_events_id_seq TO messenger_api, messenger_worker, messenger_dispatcher, messenger_janitor;
|
|
GRANT USAGE ON SEQUENCE outbox_id_seq TO messenger_api, messenger_worker, messenger_dispatcher;
|
|
|
|
-- Janitor: брошенные загрузки, очистка staging/outbox, сверка объектов S3 с БД.
|
|
GRANT SELECT, UPDATE, DELETE ON attachments TO messenger_janitor;
|
|
GRANT SELECT ON attachment_variants, attachment_jobs TO messenger_janitor;
|
|
GRANT SELECT, DELETE ON outbox TO messenger_janitor;
|
|
GRANT INSERT ON job_events TO messenger_janitor;
|