Як соло-розробник автоматизує верифікацію SaaS: e2e-observability, failover та реальний RLS-баг у transaction pooling

💡 Усі статті, обговорення, новини про DevOps — в одному місці. Приєднуйтесь до DevOps спільноти!

Всім доброго дня! Мене звати Андрій, я Full-Stack Product Engineer та System Architect.

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

Хочу сказати, що зелені юніт-тести на локалхості ефективні для перевірки ізольованої логіки, але вони сліпі до дефектів транзакційної ізоляції під навантаженням та особливостей роботи пулу з’єднань БД. Вони не можуть відповісти на питання «чи працює OTel → Tempo pipeline прямо зараз?» або «чи протікає Row-Level Security (RLS) при перевантаженні пулу PgBouncer?»

На ці питання відповідає одне: реплікований staging з автоматизованою верифікацією. Саме це я й побудував для SmartHire — multi-tenant B2B SaaS-платформи для бізнесу.

Ця стаття — про інженерний підхід: як автоматизувати staging verification, як на практиці ламається RLS при transaction-level pooling, та як надійно закрити цю дірку кодом і статичним аналізом.

1. Анатомія verify_staging.py: п’ять фаз верифікації за хвилину

Проблема ручного підходу

Staging-середовище SmartHire складається з 22 сервісів, включаючи три репліки бота, PostgreSQL з пулером PgBouncer, Redis Sentinel та повний стек спостережуваності (Prometheus / Grafana / Tempo / Loki). Перевіряти це вручну — це запускати curl у кількох вікнах терміналу та вгадувати, чи система готова до тестів.

verify_staging.py вирішує це за один запуск: паралельна перевірка 11 сервісів, валідація 18 Prometheus-метрик, snapshot стану інфраструктури, перевірка leader election через Redis і E2E-верифікація трасування через OTel → Tempo.

П’ять фаз в одному gate

Замість ручного чекліста скрипт проганяє п’ять перевірок:

1. Паралельні health checks — API, Prometheus, Grafana, Tempo, Loki, Alertmanager, nginx, PgBouncer exporter і три опціональні bot replicas.
2. Prometheus instrumentation check — 18 ключових метрик перевіряються на присутність. Якщо метрика ABSENT — або exporter не підключений, або instrumentation зламана.
3. Leader election через Redis — прямий запит до smarthire:startup_lock. Якщо лок FREE на трьох запущених інстансах — distributed locking зламаний.
4. Infrastructure snapshot — baseline по DB pool, Redis memory, API latency, HTTP requests, active tenants перед навантаженням.
5. E2E OTel → Tempo — GET /health генерує span, потім скрипт шукає trace у Tempo API. Якщо traces_found = 0, проблема десь між FastAPI instrumentation, OTel Collector і Tempo storage.

Один запуск генерує повний MONITORING_STAGING_REPORT.md за 45–90 секунд. Це обов’язковий gate перед будь-яким деплоєм.

Як виглядає MONITORING_STAGING_REPORT.md:

2. Анатомія багу: Row-Level Security у Transaction Pooling

Row-Level Security (RLS) в PostgreSQL часто використовується в multi-tenant архітектурах для ізоляції даних клієнтів. SmartHire використовує RLS-політики спільно з сесійними змінними конфігурації (GUC):

ALTER TABLE vacancies ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON vacancies
    USING (tenant_id = current_setting('app.tenant_id', true));

Важливе зауваження щодо ролі БД. За замовчуванням RLS-політики в PostgreSQL не застосовуються до суперкористувачів (superuser) та власників таблиць (owner). Для того, щоб RLS почав діяти, застосунок у продакшені обов’язково повинен підключатися до бази під обмеженою рольовою моделлю без прав власника (наприклад, smarthire_app). В іншому випадку RLS буде мовчазно проігноровано (якщо явно не виконано ALTER TABLE ... FORCE ROW LEVEL SECURITY). У моєму тестовому стенді для цього створено окрему обмежену роль smarthire_test.

У застосунку при оренді з’єднання з пулу робиться запит:

await conn.execute("SELECT set_config('app.tenant_id', $1, false)", tenant_id)

А при поверненні з’єднання виконується очищення:

await conn.execute("RESET app.tenant_id")

Гіпотеза вразливості

Параметр false в set_config(..., false) означає, що налаштування є сесійним — воно живе на фізичному з’єднанні PostgreSQL до закінчення сесії (поки з’єднання не закриється).

Якщо використовується PgBouncer у режимі Transaction Pooling (pool_mode = transaction) — що є стандартом де-факто для хмарних продакшенів з великою кількістю реплік — клієнтське з’єднання (session) не володіє фізичним з’єднанням (backend) ексклюзивно. PgBouncer перехоплює запити на рівні транзакцій і може відправити послідовні запити одного застосунку на різні фізичні бекенди PostgreSQL.

Це породжує дві критичні проблеми ізоляції:

• FAIL_CLOSED (мовчазна помилка) — запит на встановлення контексту set_config йде в транзакції 1 на бекенд А. Наступний бізнес-запит SELECT поза явним блоком транзакції маршрутизується PgBouncer на бекенд Б, де app.tenant_id порожній. Клієнт бачить 0 записів (ніби дані зникли).
• LEAK (витік даних) — коли бекенд А повертається в загальний пул PgBouncer, сесійна змінна app.tenant_id залишається брудною. Наступний запит від іншого тенента маршрутизується на бекенд А і отримує доступ до чужих даних без встановлення власного контексту.

Емпіричне підтвердження (Custom Probe)

Для перевірки цієї гіпотези було розроблено спеціальний інструмент — rls_isolation_probe.py. Щоб зробити доказ абсолютно чесним та розділити ризики інфраструктури від логіки коду, скрипт верифікації підтримує два режими запуску:

• RAW режим (сирий драйвер) — прямі запити через asyncpg.Connection із використанням сесійних GUC (false у set_config) та без транзакційної обгортки. Він демонструє наявність небезпеки (hazard) на рівні драйвера.
• APP режим (через код додатку) — навантаження гоняється через реальний DatabasePool та репозиторії, успадковані від BaseGateway. Цей режим перевіряє працездатність фіксу коду додатку (транзакційні GUC та обов’язкові транзакційні читання).

Утиліта-зонд перевіряє два сценарії:

• Scenario 1 (steady-state) — 40 паралельних воркерів роблять запити під різними tenant_id (8 тенентів) і перевіряють, чи бачать вони тільки свої дані (LEAKS > 0) і чи бачать їх взагалі (FAIL_CLOSED > 0).
• Scenario 2 (admin-bleed) — симулює ситуацію, коли один з воркерів підіймає привілеї (app.is_admin = true для міграцій або фонових завдань) і вивільняє з’єднання без скидання прапорця (наприклад, через виникнення винятку в коді).

Результат 1: Тест проти staging-конфігурації (Session mode + DISCARD ALL)

На staging за замовчуванням PgBouncer працює в session mode і конфіг містить server_reset_query = DISCARD ALL.

Результат: PASS (0 leaks, 0 fail-closed) для обох режимів (RAW та APP).

Висновок: інфраструктура (PgBouncer-level reset) маскувала проблему коду застосунку.

Результат 2: Тест проти sidecar PgBouncer у Transaction mode без serverresetquery

Я підняв sidecar-контейнер PgBouncer з pool_mode = transaction (імітація типового продакшен-налаштування).

RAW режим: FAIL (повний крах ізоляції на рівні сирого драйвера).

• Scenario 1: 3322 витоків даних (LEAKS) та 7169 порожніх відповідей (FAIL_CLOSED) з 8000 запитів.
• Scenario 2: 3443 витоків даних та 6989 порожніх відповідей з 8000 запитів.

APP режим: PASS (0 leaks, 0 fail-closed). Код додатку захистив дані.

RAW mode — це відтворюваний negative control. Він показує, що тест спроможний ловити проблему: якщо фікс колись зламається, цей тест знову впаде.

Механізм маршрутизації з’єднань (Backend PID Rotation)

Щоб перевірити, чому саме це відбувається, я додав логування pg_backend_pid() в кожному запиті воркера. Результат показав, що при роботі через один asyncpg connection wrapper під transaction mode PgBouncer послідовні запити отримують абсолютно різні PostgreSQL процеси:

Connection lease 1:
  - Query 1 (SET GUC):  executed on PID 2178
  - Query 2 (SELECT):   executed on PID 2185  ← GUC app.tenant_id is missing!
  - Query 3 (RESET):    executed on PID 2178

Оскільки PgBouncer робить перерозподіл на межі транзакцій, будь-який запит поза явним BEGIN ... COMMIT ламає сесійний стан.

Ще один підводний камінь: кеш підготовлених операторів (prepared statements)

За замовчуванням asyncpg кешує підготовлені оператори на рівні з’єднання. У режимі transaction pooling PgBouncer може направити наступний запит на інший backend PostgreSQL, де цей prepared statement не існує. PostgreSQL тоді кидає:

Prepared statement "XX" does not exist

Це призводить до лавини 5xx помилок під навантаженням. Єдине надійне рішення — повністю відключити кеш підготовлених операторів на етапі створення пулу:

await asyncpg.create_pool(..., statement_cache_size=0)

Це здається регресією продуктивності, але під transaction pooling це єдиний спосіб уникнути неконсистентного стану prepared statements між різними backend-ами. Практичний вплив на latency write-операцій під Locust (200 concurrent users) — незначний (~2—3%), тоді як лавина "prepared statement does not exist" робить тест непридатним.

Суміжні вектори вразливості

В процесі аналізу коду db/pool.py було виявлено ще дві архітектурні вразливості:

1. Ковтання помилок у _reset_connection_context (db/pool.py:748):

except (_PostgresError, _InterfaceError, OSError) as e:
    logger.debug("Failed to reset connection context: %s", e)

Якщо під час очищення з’єднання (наприклад, при розриві мережі) база викидала виняток, очищення GUC (RESET app.tenant_id, RESET app.is_admin) просто ігнорувалося. З’єднання поверталося в пул брудним. Я підтвердив це в юніт-тесті test_reset_connection_context_swallows_exception.

2. Відсутність дефенсивного очищення в _setup_connection_context: при оренді нового з’єднання застосунок сподівався, що попередній орендар за собою прибрав. Якщо очищення в минулому кроці впало з помилкою, нова оренда успадковувала старий контекст. Особливо небезпечно це для app.is_admin, оскільки давало повний обхід RLS.

Архітектурне рішення (Фікс)

Для усунення вразливості я вніс три зміни в db/pool.py:

1. Перехід на transaction-local GUC. Змінив третій параметр у set_config з false на true:

await conn.execute("SELECT set_config('app.tenant_id', $1, true)", tenant_id)

true (is_local) каже PostgreSQL, що параметр діє тільки в межах поточної транзакції. Як тільки транзакція завершується (COMMIT/ROLLBACK), база сама очищує GUC. Це знімає потребу в ручному RESET та гарантує очищення навіть при падінні з’єднання.

Зауваження: це вимагає від застосунку загортати всі запити (включаючи читання) у явні транзакційні блоки:

async with conn.transaction():
    await _set_tenant_context(conn, tenant_id)
    recs = await conn.fetch(...)

Так, загортання read-only запитів у транзакційні блоки створює мінімальний оверхед на старт транзакції в PostgreSQL. Проте для B2B SaaS гарантована безпека та залізобетонна ізоляція даних клієнтів є вищим пріоритетом, ніж економія кількох мікросекунд на BEGIN/COMMIT.

2. Дефенсивний reset при ініціалізації оренди. У самий початок _setup_connection_context додано примусове сесійне очищення адмін-прав:

await conn.execute("SELECT set_config('app.is_admin', 'false', false)")

Це гарантує, що навіть якщо при минулому циклі оренди очищення зламалося, нове з’єднання стартує з безпечного стану.

Для transaction pooling цього недостатньо: reset поза BEGIN може потрапити на інший backend, ніж бізнес-запит. Тому всередині transaction-local setup я додатково нейтралізую адмін-прапорець вже на backend поточної транзакції:

await conn.execute("SELECT set_config('app.is_admin', 'false', true)")

3. Defensive reset при виході. Було прибрано некоректний коментар «asyncpg already resets GUCs» та збережено скидання контексту на рівні коду пулу.

Після застосування фіксів проба у режимі APP під transaction mode PgBouncer показала стабільний PASS (0 leaks, 0 fail-closed) проти sidecar PgBouncer без server_reset_query.

Додатковий крок — захист legacy-викликів get_db() та get_connection() (db/base.py), які використовувалися в деяких публічних endpoint’ах поза репозиторіями BaseGateway. Ці виклики також переключено на transaction-local GUC (is_local=true) з автоматичною транзакційною обгорткою при PG_POOL_MODE == 'transaction'. Це закриває останній зазор для автокомміт-запитів через asyncpg → PgBouncer.

Що pipeline знайшов вже під час підготовки статті

Під час фінальної перевірки для цієї статті test_isolation_app_mode один раз упав з 1 leak на 1000 запитів, хоча основний фікс із transaction-local app.tenant_id вже був у коді.

Причина виявилася тоншою: lease-level reset app.is_admin=false виконувався поза BEGIN. У PgBouncer transaction pooling це означає, що reset міг потрапити на backend А, а бізнес-запит — на backend Б, де ще жив старий session-level app.is_admin=true від попереднього admin/bypass сценарію.

Тому фінальний фікс складається не тільки з app.tenant_id як transaction-local GUC, а й з примусової нейтралізації admin-прапорця вже всередині транзакційного контексту:

if is_local:
    await conn.execute("SELECT set_config('app.is_admin', 'false', true)")

Після цього повний gate під transaction sidecar дає очікувану картину: APP-сценарії проходять, RAW-сценарії позначені як xfail negative control.

Це найважливіший практичний висновок: verification pipeline не просто підтвердив вже написаний текст, а знайшов ще один edge case до релізу.

Continuous Regression Gates

Щоб захистити архітектуру від повторного виникнення цієї помилки, було впроваджено два інструменти:

1. pytest-гейт (tests/integration/test_rls_isolation.py): інтеграційний тест проганяє APP mode через transaction-mode PgBouncer sidecar і перевіряє відсутність leaks та fail-closed. RAW mode використовується як negative control (доказ наявності вразливості на рівні драйвера). Будь-який leak у APP mode блокує збірку.

tests/integration/test_rls_isolation.py::test_isolation_app_mode PASSED
tests/integration/test_rls_isolation.py::test_admin_bleed_app PASSED
============================== 2 passed in 8.74s ==============================

RAW-тести (test_isolation_raw_driver, test_admin_bleed_raw) навмисне падають під transaction sidecar — це negative control, який підтверджує, що тест спроможний ловити проблему.

2. Semgrep статичне правило (.semgrep/smarthire.yml): правило smarthire-set-config-transaction-local забороняє використання set_config('app.*', ..., false) для не-false значень:

- id: smarthire-set-config-transaction-local
  patterns:
    - pattern-regex: 'set_config\(\s*["'']app\.[a-zA-Z0-9_]+["'']\s*,\s*[^,]+\s*,\s*(false|False)\s*\)'
    - pattern-not-regex: 'set_config\(\s*["'']app\.is_admin["'']\s*,\s*["'']false["'']\s*,\s*(false|False)\s*\)'
  message: |
    Forbidden: set_config('app.*', ..., false) with non-false values.
    All tenant/admin RLS context settings MUST use transaction-local scoping (is_local=true)
    inside a transaction block to prevent PgBouncer transaction-mode connection state leakage.

3. Тестування стабільності та базової стійкості під обмеженнями staging

Замість того, щоб малювати міфічні цифри «хайлоаду на 200 користувачів» (що для продакшену є звичайним базовим фоном), я підійшов до перевірки стійкості з іншого боку: як переконатися, що додаток працює без помилок і деградації під лімітами staging-середовища.

Staging-застосунок запущено з одним Uvicorn-процесом та жорстким Docker memory limit у 512 MB на контейнер.

Я оновив Locust-сценарій (hire_flow.py), який реалізує реалістичний сценарій кандидата через реальні публічні endpoint’и:

GET /api/v1/public/apply/{tenant_slug} → GET /api/v1/recruiting/{tenant_slug}/vacancies → POST /api/v1/public/apply/{tenant_slug}/submit → перевірка статусу заявки.

Розподіл запитів зміщено у бік читання (read-heavy weighting): 80% читання, 20% запису.

Одна з неочевидних вразливостей write-шляху — динамічні форми кандидатів (CandidateForm). Віджет FileWidget може упаковувати резюме в Base64 DataURL і включати їх у JSON-поле answers. Ми вбудували @model_validator(mode="before") у Pydantic-схему AnketaAnswersSchema, який:

• відкидає будь-які data:...;base64,... рядки ще до серіалізації в JSONB;
• обмежує довжину рядків (MAX_STRING_LEN = 50 000) і глибину вкладеності (MAX_NESTED_ITEMS = 500).

Без цього guard-а під навантаженням у 200–500 паралельних користувачів синхронна JSON-десеріалізація мегабайтних Base64-рядків блокувала б event loop Python на сотні мілісекунд. Метрики p99 latency в Grafana стрибали б у космос, а поди падали б по таймаутам.

Результати тестів під навантаженням

1. Multi-tenant test (200 concurrent users, 3 хвилини)

2. Soak test (20 хвилин, 4 Gunicorn workers, memory limit 1g)

3. Single-tenant contention (200 concurrent users, всі запити до одного тенента, 3 хвилини)

Висновки. 200 concurrent users на єдиний tenant створюють інтенсивну write-контенцію (~85 сумарних RPS). Після комплексного фікса (DB_POOL_SIZE=5 → 20, захист post_fork від зайвого скидання pool, graceful handling pool=None у HostTenantResolverMiddleware) error rate знизився з 1.12% до 0.01% — у 112 разів. Connection reset зникли повністю. Latency стабілізувалася: P99 впав з 12 000 ms до 480 ms. Load shedder (2× 503) працює коректно як захист від cascade failure.

4. Failover: що буде, якщо вбити лідера в розпал бою

У SmartHire три бот-інстанси працюють паралельно, але тільки один з них є лідером — він ініціалізує міграції та виконує APScheduler задачі. Лідер утримує TTL-лок в Redis:

async def try_acquire_startup_lock_redis() -> bool:
    token = f"{os.getpid()}:{uuid.uuid4().hex}"
    acquired = await redis.set(STARTUP_LOCK_KEY, token, nx=True, ex=15)  # TTL=15s, refreshed every 5s

Я провів тест на відновлення після збоїв:

Вебхук-трафік при цьому не переривався, оскільки інші дві репліки продовжували приймати запити. Практичний failover SLO — до 30 секунд.

Контроль лідерства (leader election) критичний не тільки для APScheduler, але й для стабільності пулу з’єднань. Коли лідер падає і репліка перехоплює лок, відбувається лавиноподібне перепідключення до PgBouncer. Якщо б RLS-контекст був сесійним, у цей перехідний момент ризик витоків між клієнтами на рівні брудних з’єднань різко зростав би. Транзакційний is_local=true нівелює цей ризик.

5. Чекліст для multi-tenant PostgreSQL

Якщо у вас SaaS із RLS та PgBouncer, я б перевірив мінімум такі речі:

• [ ] Застосунок підключається не під superuser і не під owner таблиць.
• [ ] Для RLS-таблиць увімкнено FORCE ROW LEVEL SECURITY, якщо owner-роль може виконувати app-запити.
• [ ] Ви точно знаєте, який pool_mode використовує PgBouncer у production: session, transaction чи різні режими для різних entrypoint’ів.
• [ ] tenant_id/admin GUC не зберігаються як session-level state під transaction pooling.
• [ ] Усі read-запити, які залежать від transaction-local GUC, виконуються всередині явного BEGIN ... COMMIT.
• [ ] Admin/bypass прапорці (app.is_admin, app.bypass_rls) примусово нейтралізуються всередині поточного transaction context.
• [ ] Є negative control: тест, який падає на небезпечній RAW-схемі, а не просто завжди зелений happy path.
• [ ] Кеш підготовлених операторів відключено (statement_cache_size=0) при використанні PgBouncer transaction pooling.
• [ ] Base64-вкладення та oversized strings у JSONB-полях відсікаються на рівні Pydantic model_validator до серіалізації.
• [ ] Всі legacy-виклики get_db() / get_connection() використовують transaction-local GUC замість session-level.
• [ ] Є статичний gate, який забороняє повернути set_config('app.*', ..., false) у tenant/admin context.

6. Висновок

Головний урок цієї історії — безпека та стабільність не будуються на вірі.

Робота RLS-ізоляції в multi-tenant системах критично залежить від режиму роботи пулу з’єднань (PgBouncer). Без переходу на transaction-local GUC (is_local=true) та явних транзакцій ризик витоку даних під transaction pooling стає системним.

verify_staging.py та автоматизовані проби дають більше впевненості перед релізом, ніж сотні ручних curl-запитів та години читання коду.

Стек проекту: Python / FastAPI / asyncpg · PostgreSQL 16 + PgBouncer · Redis Sentinel · OTel → Grafana Tempo · Prometheus + Grafana · Docker Compose.

Питання до читачів: як ви тестуєте поведінку RLS та пулінгу перед виходом в прод? Чи використовуєте автоматизовані хаос-тести на staging? Поділіться досвідом.

Мій LinkedIn · SmartHire Showcase on GitHub