-- =============================================================================
-- 2026_06_26_153000_rls_nullif_guc_hardening.sql
-- =============================================================================
-- Назначение: устранить класс отказов RLS на управляемой БД (PgBouncer transaction
-- pooling), когда GUC app.current_tenant_id ПУСТ или НЕ ЗАДАН на пуло-соединении.
--   - 22P02 invalid input syntax for type bigint: ""  (GUC = '')
--   - 42704 unrecognized configuration parameter        (GUC не задан)
-- Оба возникают при вычислении current_setting('app.current_tenant_id')::bigint
-- в политиках tenant_isolation. Инцидент 26.06.2026: вход в портал падал на
-- резолве users (60 ошибок) — см. db/CHANGELOG_schema.md.
--
-- Фикс: все приведения → NULLIF(current_setting('app.current_tenant_id', true), '')::bigint
--   - флаг ,true   → нет 42704 (не задан → NULL, не ошибка)
--   - NULLIF(...,'') → нет 22P02 (пусто → NULL, не ошибка)
--   Пустой/незаданный tenant → tenant_id = NULL → 0 строк (изоляция НЕ ослабляется).
--
-- Bootstrap-таблицы (читаются/пишутся ДО tenant-контекста — на auth-роутах БЕЗ
-- 'tenant'-middleware, где GUC пуст). Им добавлено разрешающее условие
-- "GUC пуст → видно/можно", т.к. на этом этапе tenant ещё не выставлен:
--   users               — резолв по email (вход) и id (сессия).
--   auth_log            — счётчики IP/email лок-аута + запись событий входа.
--   email_verifications — подтверждение почты при регистрации (RegistrationController).
--   user_recovery_codes — вход по резервному коду 2FA (TwoFactorController, public route).
--   user_sessions       — запись активной сессии при входе (UserSessionTracker).
-- Остальным 38 — НЕТ (пустой GUC → 0 строк).
-- БЕЗОПАСНОСТЬ (rls-reviewer 26.06): при ЗАДАННОМ tenant изоляция этих 5 не меняется
-- (условие IS NULL ложно). Разрешающая ветка открывается ТОЛЬКО при пустом GUC, что
-- штатно бывает лишь на auth-роутах (нет 'tenant'-middleware → SetTenantContext не
-- запускается). Доступ к этим таблицам там — по точному user_id/email/token, не перебором.
-- Trade-off vs строгий код-фикс (SET LOCAL в каждом auth-эндпоинте): выбран DB-вариант
-- ради надёжности (ни один вызов не теряется, нет хрупкой правки auth-кода). Документировано.
--
-- Идемпотентно (DROP POLICY IF EXISTS). На dev без ролей crm_* безопасно
-- (политики те же, current_setting ведёт себя одинаково).
-- Применять под владельцем таблиц (crm_migrator) или членом его роли.
-- =============================================================================
BEGIN;

-- users: bootstrap-видимость (резолв при входе, когда tenant ещё не задан) + NULLIF
DROP POLICY IF EXISTS tenant_isolation ON users;
CREATE POLICY tenant_isolation ON users USING (
  NULLIF(current_setting('app.current_tenant_id', true), '') IS NULL
  OR tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::bigint
);

DROP POLICY IF EXISTS tenant_isolation ON public.activity_log;
CREATE POLICY tenant_isolation ON public.activity_log FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.api_keys;
CREATE POLICY tenant_isolation ON public.api_keys FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.auth_log;
CREATE POLICY tenant_isolation ON public.auth_log FOR ALL USING (((NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text) IS NULL) OR (((actor_type)::text = 'tenant_user'::text) AND (tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint))));

DROP POLICY IF EXISTS tenant_isolation ON public.balance_freeze_log;
CREATE POLICY tenant_isolation ON public.balance_freeze_log FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.balance_transactions;
CREATE POLICY tenant_isolation ON public.balance_transactions FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.comment_templates;
CREATE POLICY tenant_isolation ON public.comment_templates FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.deal_tag_pivot;
CREATE POLICY tenant_isolation ON public.deal_tag_pivot FOR ALL USING ((tag_id IN ( SELECT deal_tags.id
   FROM deal_tags
  WHERE (deal_tags.tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint)))) WITH CHECK ((tag_id IN ( SELECT deal_tags.id
   FROM deal_tags
  WHERE (deal_tags.tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint))));

DROP POLICY IF EXISTS tenant_isolation ON public.deal_tags;
CREATE POLICY tenant_isolation ON public.deal_tags FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.deals;
CREATE POLICY tenant_isolation ON public.deals FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.email_verifications;
CREATE POLICY tenant_isolation ON public.email_verifications FOR ALL USING ((NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text) IS NULL OR user_id IN ( SELECT users.id
   FROM users
  WHERE (users.tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint))));

DROP POLICY IF EXISTS tenant_isolation ON public.failed_webhook_jobs;
CREATE POLICY tenant_isolation ON public.failed_webhook_jobs FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.impersonation_tokens;
CREATE POLICY tenant_isolation ON public.impersonation_tokens FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.import_log;
CREATE POLICY tenant_isolation ON public.import_log FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.import_unknown_statuses;
CREATE POLICY tenant_isolation ON public.import_unknown_statuses FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.in_app_notifications;
CREATE POLICY tenant_isolation ON public.in_app_notifications FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.lead_charges;
CREATE POLICY tenant_isolation ON public.lead_charges FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint)) WITH CHECK ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.outbound_webhook_deliveries;
CREATE POLICY tenant_isolation ON public.outbound_webhook_deliveries FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.outbound_webhook_subscriptions;
CREATE POLICY tenant_isolation ON public.outbound_webhook_subscriptions FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.pd_processing_log;
CREATE POLICY tenant_isolation ON public.pd_processing_log FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.project_limit_adjustments;
CREATE POLICY tenant_isolation ON public.project_limit_adjustments FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS project_routing_snapshots_tenant_isolation ON public.project_routing_snapshots;
CREATE POLICY project_routing_snapshots_tenant_isolation ON public.project_routing_snapshots FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.project_suppliers;
CREATE POLICY tenant_isolation ON public.project_suppliers FOR ALL USING ((project_id IN ( SELECT projects.id
   FROM projects
  WHERE (projects.tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint))));

DROP POLICY IF EXISTS tenant_isolation ON public.project_user_assignments;
CREATE POLICY tenant_isolation ON public.project_user_assignments FOR ALL USING ((project_id IN ( SELECT projects.id
   FROM projects
  WHERE (projects.tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint)))) WITH CHECK ((project_id IN ( SELECT projects.id
   FROM projects
  WHERE (projects.tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint))));

DROP POLICY IF EXISTS tenant_isolation ON public.projects;
CREATE POLICY tenant_isolation ON public.projects FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.push_subscriptions;
CREATE POLICY tenant_isolation ON public.push_subscriptions FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.refund_requests;
CREATE POLICY tenant_isolation ON public.refund_requests FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.report_jobs;
CREATE POLICY tenant_isolation ON public.report_jobs FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.saas_invoice_items;
CREATE POLICY tenant_isolation ON public.saas_invoice_items FOR ALL USING ((invoice_id IN ( SELECT saas_invoices.id
   FROM saas_invoices
  WHERE (saas_invoices.tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint)))) WITH CHECK ((invoice_id IN ( SELECT saas_invoices.id
   FROM saas_invoices
  WHERE (saas_invoices.tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint))));

DROP POLICY IF EXISTS tenant_isolation ON public.saas_invoices;
CREATE POLICY tenant_isolation ON public.saas_invoices FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.saas_transactions;
CREATE POLICY tenant_isolation ON public.saas_transactions FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.saas_upd_documents;
CREATE POLICY tenant_isolation ON public.saas_upd_documents FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.supplier_lead_deliveries;
CREATE POLICY tenant_isolation ON public.supplier_lead_deliveries FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS support_requests_tenant_isolation ON public.support_requests;
CREATE POLICY support_requests_tenant_isolation ON public.support_requests FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.tariff_subscriptions;
CREATE POLICY tenant_isolation ON public.tariff_subscriptions FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.tenant_consents;
CREATE POLICY tenant_isolation ON public.tenant_consents FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.tenant_custom_domains;
CREATE POLICY tenant_isolation ON public.tenant_custom_domains FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.tenant_operations_log;
CREATE POLICY tenant_isolation ON public.tenant_operations_log FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_requisites_tenant_isolation ON public.tenant_requisites;
CREATE POLICY tenant_requisites_tenant_isolation ON public.tenant_requisites FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint)) WITH CHECK ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.tenant_status_overrides;
CREATE POLICY tenant_isolation ON public.tenant_status_overrides FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenants_self_isolation ON public.tenants;
CREATE POLICY tenants_self_isolation ON public.tenants FOR ALL USING ((id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

DROP POLICY IF EXISTS tenant_isolation ON public.user_recovery_codes;
CREATE POLICY tenant_isolation ON public.user_recovery_codes FOR ALL USING ((NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text) IS NULL OR user_id IN ( SELECT users.id
   FROM users
  WHERE (users.tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint))));

DROP POLICY IF EXISTS tenant_isolation ON public.user_sessions;
CREATE POLICY tenant_isolation ON public.user_sessions FOR ALL USING ((NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text) IS NULL OR user_id IN ( SELECT users.id
   FROM users
  WHERE (users.tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint))));

DROP POLICY IF EXISTS tenant_isolation ON public.webhook_dedup_keys;
CREATE POLICY tenant_isolation ON public.webhook_dedup_keys FOR ALL USING ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint)) WITH CHECK ((tenant_id = (NULLIF(current_setting('app.current_tenant_id'::text, true), ''::text))::bigint));

COMMIT;
