Netpulse_SasS/server/migrations/0050_audit_read.sql

-- =====================================================================
-- NetPulse :: 0050_audit_read.sql
-- Журнал аудиту стає таблицею, яку ЧИТАЮТЬ.
--
-- До цього core.audit_log була місцем, куди пишуть. Індекси з 0001
-- відповідають рівно на це: (tenant_id, ts DESC) для «останнє в
-- кабінеті» і (object_type, object_id, ts DESC) для «історія одного
-- об'єкта». Обидва потрібні, обох замало, щойно з'являється сторінка,
-- яка ставить до журналу питання слідчого: «хто, коли, над чим і чи
-- згадується тут це ім'я хоста».
--
-- Різниця не в зручності, а в порядку величини. Виміряно на одноразовій
-- базі 500 тис. рядків (3 кабінети, 2 роки, 105 чанків), EXPLAIN
-- (ANALYZE, BUFFERS):
--
--   рідкісна дія за 2 роки        113 мс / 261 535 буферів → 4.4 мс / 256
--   рідкісна дія + актор           88 мс / 261 535         → 3.5 мс / 250
--   рідкісний актор за 2 роки      42 мс /  86 411         → 2.9 мс / 131
--   пошук по вмісту за 2 роки     702 мс / 505 182         →  64 мс / 6 016
--
-- 261 тисяча буферів — це два гігабайти читання заради двадцяти рядків
-- на екрані. Журнал не має стелі росту (політики retention на ньому
-- свідомо немає — у цьому й суть журналу), тож ця цифра лише зростає.
-- =====================================================================

-- ---------------------------------------------------------------------
-- Право
-- ---------------------------------------------------------------------

-- audit:read уже існує з 0010 і вже виданий лише власнику (a1) та
-- адміну (a2) — у 0010 owner отримує всі права переліком, а admin усі,
-- крім billing:manage. Нове право заводити не треба й не можна: другий
-- ключ на ту саму річ означав би дві ролі з різною відповіддю на
-- питання «чи бачить ця людина журнал».
--
-- Рядки нижче нічого не змінюють на наявних інсталяціях і потрібні
-- рівно для одного: зробити прив'язку ЯВНОЮ. У 0010 admin отримав
-- audit:read випадково — як побічний наслідок «усі права, крім
-- білінгу», обчисленого на момент 0010. Наступний, хто читатиме, звідки
-- узявся доступ до журналу, має знайти рішення, а не арифметику.
--
-- Інженер (a3) не отримує навмисно, і це та сама межа, що у 0036/0037,
-- лише різкіша: ncm:exec і ncm:delete відповідають на «що людині вільно
-- робити», audit:read — на «чиї дії їй вільно розглядати». Журнал
-- показує, хто що робив; доступ до нього сам собою чутливий і має бути
-- рішенням власника, а не наслідком посади.
INSERT INTO core.permissions (key, description) VALUES
  ('audit:read', 'Перегляд журналу аудиту')
ON CONFLICT (key) DO NOTHING;

INSERT INTO core.role_permissions (role_id, permission_key) VALUES
  ('00000000-0000-0000-0000-0000000000a1', 'audit:read'),
  ('00000000-0000-0000-0000-0000000000a2', 'audit:read')
ON CONFLICT DO NOTHING;

-- ---------------------------------------------------------------------
-- Пагінація: (tenant_id, ts DESC, id DESC)
-- ---------------------------------------------------------------------

-- Сторінки беруться курсором `(ts, id) < (остання_ts, останній_id)`, а
-- не OFFSET. Індекс під це має нести обидві колонки порівняння в тому
-- самому порядку, інакше id доводиться перевіряти вже на купі.
--
-- id у ключі не про унікальність часу, а про стабільність межі. Дві
-- події однієї транзакції отримують один ts із точністю до мікросекунди
-- (див. масові дії: рядок аудиту пишеться після відповіді), і курсор
-- лише за ts на такій парі або загубив би другий рядок, або показав би
-- перший двічі — залежно від того, строге порівняння чи ні. Пара
-- (ts, id) впорядкована повністю, і межа однозначна.
--
-- Виміряно, 100 000-й рядок кабінету:
--   курсор        1.3 мс /     147 буферів
--   OFFSET 100000  46 мс / 100 416 буферів
-- Різниця не в 35 разів, а в тому, що ліва цифра не залежить від
-- глибини, а права росте разом із нею.
CREATE INDEX audit_tenant_ts_id_idx ON core.audit_log (tenant_id, ts DESC, id DESC);

-- Старий (tenant_id, ts DESC) — префікс нового, тож усе, що вміло
-- працювати через нього, працює й через новий. Тримати обидва означало
-- б платити другим записом на кожній події за жодну нову вибірку.
DROP INDEX IF EXISTS core.audit_tenant_ts_idx;

-- ---------------------------------------------------------------------
-- Дія й актор
-- ---------------------------------------------------------------------

-- Два питання, з якими на сторінку приходять: «покажи всі видалення» і
-- «покажи все, що робила ця людина». Обидва — голка в стозі: дія на
-- кшталт ncm.config.delete це одиниці рядків на десятки тисяч, і саме
-- через це відбір без індексу коштує найдорожче — сканувати доводиться
-- геть усе, бо зупинитись раніше немає на чому.
--
-- ts DESC, id DESC у хвості обох індексів — щоб та сама вибірка ще й
-- поверталась у потрібному порядку й з тим самим курсором, без сортування.
CREATE INDEX audit_tenant_action_ts_idx ON core.audit_log
  (tenant_id, action, ts DESC, id DESC);

-- Частковий: у рядка, який лишив по собі машинний токен інтеграції,
-- actor_user_id порожній, і фільтр «хто саме (користувач)» такий рядок
-- не шукає ніколи. Виключення NULL прибирає з індексу ту частину
-- журналу, яка через нього не читається.
CREATE INDEX audit_tenant_actor_ts_idx ON core.audit_log
  (tenant_id, actor_user_id, ts DESC, id DESC)
  WHERE actor_user_id IS NOT NULL;

-- Індексів під object_type та actor_ip свідомо немає.
--
-- object_type — шість-сім значень на весь продукт: відбір за ним
-- відкидає п'ять шостих, а не 99,99%, і індекс тут програє звичайному
-- фільтру поверх (tenant_id, ts). actor_ip у типовій інсталяції — це
-- одна адреса NAT офісу на всіх; за нею ходять не «знайти рідкісне», а
-- «звузити вже знайдене». Обидва лишаються фільтрами в запиті. Кожен
-- зайвий індекс на таблиці, яка росте вічно, — це не лише місце, а ще
-- один запис на кожну подію.

-- ---------------------------------------------------------------------
-- Пошук по вмісту
-- ---------------------------------------------------------------------

-- У before/after/meta лежить те, заради чого журнал і читають: імена
-- хостів, команди, знімок фільтра. Питання до нього ставлять підрядком
-- («де тут Миронівка», «хто гнав display version»), а підрядок
-- усередині значення — це рівно те, чого не вміє жоден jsonb-індекс:
-- GIN за jsonb_path_ops шукає ключі та цілі значення, а не «щось, що
-- містить оці літери».
--
-- Тому триграми поверх текстового подання всіх трьох колонок одним
-- виразом. Одним, а не трьома індексами: умова в запиті теж одна, і
-- три ILIKE через OR планувальник звів би до трьох окремих сканів із
-- BitmapOr замість одного.
--
-- Ціна: 98 МБ на 500 тис. рядків при 303 МБ самої таблиці, тобто
-- близько третини. Для журналу, у який пишуть десятки разів на добу,
-- вартість вставки в GIN неістотна; вартістю тут є місце, і воно
-- окуповується єдиним числом: 702 мс → 64 мс на пошуку за два роки.
CREATE INDEX audit_search_trgm_idx ON core.audit_log
  USING gin ((coalesce(meta::text, '') || ' ' ||
              coalesce(before::text, '') || ' ' ||
              coalesce(after::text, '')) gin_trgm_ops);

-- ---------------------------------------------------------------------
-- Стиснення: 30 днів → 365
-- ---------------------------------------------------------------------

-- Без цієї зміни всі індекси вище працюють рівно 30 днів.
--
-- У 0005 стиснення роздано всім гіпертаблицям однією політикою: через
-- 30 днів, segmentby = tenant_id, orderby = ts DESC. Для телеметрії це
-- правильно — її питають діапазоном часу в межах кабінету, і саме ці
-- дві колонки стиснення й лишає придатними для відбору. Для журналу та
-- сама політика має зворотний ефект: його питають за дією, за актором і
-- за вмістом, а на стиснутому чанку жоден індекс за цими колонками не
-- діє — їх там просто немає, є пакет по 1000 рядків, який доводиться
-- розпакувати цілком.
--
-- Виміряно на тій самій базі, пошук по вмісту:
--   чанки нестиснуті   64 мс
--   чанки стиснуті    576 мс   (індекс не використовується взагалі)
--   після зміни, за рік  35 мс
--
-- Тобто журнал старший за місяць мовчки переставав шукатись. «Мовчки» —
-- ключове слово: сторінка не повідомляє, що глибина пошуку скінчилась,
-- вона просто довго думає й повертає те саме.
--
-- Рік — межа, у якій уміщається практично будь-який розбір («що
-- змінилось перед тим, як воно зламалось», «хто мав доступ торік»).
-- Глибше журнал читають рідко, і там повільна відповідь прийнятна;
-- місце ж економиться саме на глибині — стиснення дає близько
-- семикратного виграшу (482 МБ → 69 МБ на 500 тис. рядків), і воно
-- лишається, просто починається пізніше.
--
-- Уже стиснуті чанки цим не чіпаються: політика керує лише тим, що
-- стискатимуть далі. Розтискати їх міграцією було б довгою й
-- непередбачуваною за часом дією на чужих даних; там, де це потрібно,
-- це робиться руками через decompress_chunk.
SELECT remove_compression_policy('core.audit_log', if_exists => TRUE);
SELECT add_compression_policy('core.audit_log', INTERVAL '365 days', if_not_exists => TRUE);

-- ---------------------------------------------------------------------
-- Тільки додавання
-- ---------------------------------------------------------------------

-- Журнал, який можна виправити, доводить рівно нічого: перше, що зробить
-- той, чиї дії в ньому записані, — виправить запис. Тому заборона стоїть
-- не в коді, а в базі: код можна дописати необережно, а тригер спрацює
-- на будь-якому запиті з будь-якого місця.
--
-- Тригер, а не самі лише GRANT: у цьому розгортанні застосунок ходить у
-- базу роллю-власником таблиці (netpulse), а власникові REVOKE не
-- перешкода — він завжди може повернути собі права. Тригер зупиняє й
-- власника.
--
-- Перевірено на одноразовій базі, що заборона не ламає TimescaleDB:
-- compress_chunk усередині робить TRUNCATE чанка (тригер рядків його не
-- бачить, а тригер TRUNCATE — не заважає: стиснення й розтиснення
-- проходять), drop_chunks видаляє чанк як таблицю, а INSERT, зокрема в
-- уже стиснутий чанк, працює як раніше.
--
-- Чого це НЕ дає: людина з доступом до бази під власником може прибрати
-- сам тригер. Захист від адміністратора СУБД — це вже інша задача
-- (окреме сховище журналу, підпис ланцюжком), і чесніше сказати це тут,
-- ніж вдавати, що тригер її розв'язує.
CREATE FUNCTION core.audit_log_append_only() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'core.audit_log дозволяє лише додавання (спроба %)', TG_OP
    USING ERRCODE = 'raise_exception';
END $$;

CREATE TRIGGER audit_log_append_only
  BEFORE UPDATE OR DELETE ON core.audit_log
  FOR EACH ROW EXECUTE FUNCTION core.audit_log_append_only();

CREATE FUNCTION core.audit_log_no_truncate() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'core.audit_log дозволяє лише додавання (спроба TRUNCATE)'
    USING ERRCODE = 'raise_exception';
END $$;

-- Окремим тригером, бо TRUNCATE не є подією рядка: тригер вище його не
-- бачить узагалі, і без цього рядка «не можна видаляти» означало б
-- «не можна видаляти по одному».
CREATE TRIGGER audit_log_no_truncate
  BEFORE TRUNCATE ON core.audit_log
  FOR EACH STATEMENT EXECUTE FUNCTION core.audit_log_no_truncate();

-- Друга лінія — для інсталяцій, які ходять у базу обмеженими ролями, як
-- і задумано в 0011. Там тригер навіть не знадобиться: права просто
-- немає. У 0001 UPDATE/DELETE роздано їм разом з усім іншим — не тому,
-- що вони комусь потрібні, а тому, що GRANT писався одним рядком.
REVOKE UPDATE, DELETE, TRUNCATE ON core.audit_log FROM netpulse_app, netpulse_worker;

COMMENT ON TABLE core.audit_log IS
  'Журнал аудиту: тільки додавання, UPDATE/DELETE/TRUNCATE заборонені '
  'тригером. RLS тут не діє (гіпертаблиця, а роль застосунку до того ж '
  'BYPASSRLS), тому ізоляцію кабінетів тримає явний предикат tenant_id '
  'у кожному запиті читання.';