-- 156_revoke_raw_from_reader.sql
-- Revoke gendesign_reader direct SELECT on the 5 raw producer tables (#2138, step 3/3 — FINAL).
--
-- Context / gate:
--   #2138 moves the external cross-load ETL consumer (#976 newbuilding_crossload.py) off the
--   raw producer tables and onto the stable market.v_* contract views:
--     step 1/3 (155) — GRANT SELECT on market.v_* to gendesign_reader (additive).
--     step 2/3 (merged + run on prod: 8224 rows, 0 errors) — ETL now reads
--             market.v_houses + market.v_house_sources via the views, no raw reads left.
--     step 3/3 (this file) — REVOKE the raw-table SELECT grants from 101, so the reader
--             sees ONLY the contract, not the kitchen.
--
--   Raw grants being revoked were issued by 101_gendesign_reader_role.sql:
--     houses, house_sources, houses_price_dynamics, house_reliability_checks, house_reviews.
--
-- Scope (INTENTIONALLY narrow):
--   - REVOKE only the 5 raw producer tables above.
--   - listings + offer_price_history (granted by 102 for Site Finder #1191) are NOT touched —
--     they are a separate consumer contract, still in active use.
--   - market.v_* grants (155), schema market USAGE, yandex_jk_enrichment — NOT touched.
--   - USAGE ON SCHEMA public is kept: reader still needs it and it does not expose table data
--     on its own (market lives in a separate schema; SELECT is what gates the raw rows).
--
-- Dependencies:
--   - 101_gendesign_reader_role.sql (grants being revoked)
--   - 155_reader_grants_to_contract_views.sql (view grants the reader now relies on)
--
-- Idempotent: REVOKE is repeatable — re-running when the grant is already gone is a no-op,
--   never an error. Role existence checked fail-fast below for a clear message.
-- AUTO-APPLIED on prod via deploy-tradein.yml/_schema_migrations (strict).

BEGIN;

-- Fail-fast: role must exist (101 runs earlier by NN — guaranteed ok).
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'gendesign_reader') THEN
    RAISE EXCEPTION 'роль gendesign_reader отсутствует — сначала примените 101_gendesign_reader_role.sql';
  END IF;
END$$;

-- Reader now reads exclusively via market.v_* contract views — drop raw-table access.
REVOKE SELECT ON
    houses,
    house_sources,
    houses_price_dynamics,
    house_reliability_checks,
    house_reviews
FROM gendesign_reader;

COMMIT;