Files
mtusi2026lab7-fork/migrations/006_security.sql

50 lines
2.7 KiB
SQL

-- 006 (lab7): общий для всех реплик rate limit входа + раздельные роли БД.
--
-- login_rate_limits: счётчики фиксированного окна. bucket = 'u:'||sha256(username)
-- или 'ip:'||sha256(ip) — открытые username/IP не хранятся. Старые окна удаляет
-- фоновая очистка API (delete_old_rate_limits).
CREATE TABLE IF NOT EXISTS login_rate_limits (
bucket text NOT NULL,
window_start timestamptz NOT NULL,
hits integer NOT NULL,
PRIMARY KEY (bucket, window_start)
);
-- Роли приложения создаёт infra/postgres/initdb/10-roles.sh при первой инициализации
-- volume (пароли — из secrets оператора). Если миграция идёт на volume, где ролей
-- ещё нет, создаём их без права входа: GRANT ниже не упадёт, а пароль/LOGIN выдаст
-- `python scripts/ops.py db-roles`.
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'messenger_api') THEN
CREATE ROLE messenger_api NOLOGIN;
END IF;
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'messenger_worker') THEN
CREATE ROLE messenger_worker NOLOGIN;
END IF;
END
$$;
-- Ни у одной роли приложения нет DDL: схемой владеет только администратор (migrate).
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM messenger_api, messenger_worker;
GRANT USAGE ON SCHEMA public TO messenger_api, messenger_worker;
-- API: обязательный синхронный путь (пользователи, сессии, чаты, сообщения,
-- идемпотентность, вложения, задания/outbox для relay) — только DML, без TRUNCATE.
GRANT SELECT, INSERT, UPDATE, DELETE ON
users, chats, chat_members, messages, sessions, session_cache_invalidations,
idempotency_keys, attachments, message_attachments, attachment_variants,
attachment_jobs, job_outbox, dead_letters, job_events, login_rate_limits
TO messenger_api;
GRANT SELECT ON schema_migrations TO messenger_api;
-- worker: только конвейер вложений. НЕТ доступа к users (password_hash), sessions
-- (token hashes), chats, messages, idempotency_keys, login_rate_limits.
GRANT SELECT, INSERT, UPDATE, DELETE ON
attachments, attachment_variants, attachment_jobs, job_outbox, dead_letters, job_events
TO messenger_worker;
GRANT SELECT ON message_attachments, schema_migrations TO messenger_worker;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO messenger_api, messenger_worker;