forked from mtusi2026/mtusi2026lab7
50 lines
2.7 KiB
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;
|