🛡️ Red Team RLS Triaaž · 25 tabelit / 8 view / 34 funktsioon

1. Hüpervision SAMM 12 (PR #124)

session_manifests.payload.hypervision_status = 'PASS_TIER2' · Tier 1 DEGRADED (PR #130 trigger viimase 24h 572 health_check), Tier 2 Supabase MCP otse LIVE. Paralleelseid status='running' sub-sessioone tuvastatud: vs_eirik koord 12 (id=74) = parent — ootuspärane.

2. KOOND verdict

3. Per-tabel triaaž (25 tabelit)

4. SECURITY DEFINER vaadete triaaž (8 vaadet)

5. Function search_path triaaž

Supabase advisor näitas 34 funktsiooni. Tegelikult ~30 user-funktsiooni (ülejäänud on PostGIS extension, ei puuduta). Kriitilisemad on 7 SECURITY DEFINER user-funktsiooni — neid kasutatakse RLS-bypass-iga, mistõttu mutable search_path = SQL-injection vektor.

6. Faas 1 migration SQL (autonomne, PR #111 Recommended)

-- Faas 1: 17 sisemise tabeli RLS + service_role-only policy
-- 0 äpi-mõju (pole anon-key kaudu kasutusel)
-- Apply: Supabase Studio → SQL Editor

BEGIN;

-- 1. LVM ETL tabelid (3)
ALTER TABLE public.lvm_works ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.lvm_cirsmas ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.lvm_service_providers ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.lvm_etl_sync_log ENABLE ROW LEVEL SECURITY;

-- 2. Market data (5)
ALTER TABLE public.sec_filings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.market_quotes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.market_fundamentals ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.nse_corporate_filings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.insider_transactions ENABLE ROW LEVEL SECURITY;

-- 3. Mälu-agent + Hypervision (5)
ALTER TABLE public.pos_deep_memory_l2 ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.inventory_files ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.jetson_jobs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.process_state_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.agent_runs ENABLE ROW LEVEL SECURITY;

-- 4. Tundlikud partner-tabelid (3)
ALTER TABLE public.santa_saatelehed ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.timbertrace_deliveries ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.toftan_warehouse_stock ENABLE ROW LEVEL SECURITY;

-- 5. Skills + integration (2)
ALTER TABLE public.skills_registry ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.integration_inventory ENABLE ROW LEVEL SECURITY;

-- service_role-only policy bulk (PostgreSQL alates 17 saab kasutada DO-blokis;
-- 16 või vanem — kirjuta need 18 lauset käsitsi)
DO $$
DECLARE t TEXT;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'lvm_works','lvm_cirsmas','lvm_service_providers','lvm_etl_sync_log',
    'sec_filings','market_quotes','market_fundamentals','nse_corporate_filings','insider_transactions',
    'pos_deep_memory_l2','inventory_files','jetson_jobs','process_state_log','agent_runs',
    'santa_saatelehed','timbertrace_deliveries','toftan_warehouse_stock',
    'skills_registry','integration_inventory'
  ] LOOP
    EXECUTE format('CREATE POLICY service_role_all ON public.%I FOR ALL TO service_role USING (true) WITH CHECK (true);', t);
  END LOOP;
END $$;

COMMIT;

-- Validatsioon
SELECT tablename, count(*) AS policy_count FROM pg_policies WHERE schemaname='public'
  AND tablename IN ('lvm_works','lvm_cirsmas','lvm_service_providers','lvm_etl_sync_log',
    'sec_filings','market_quotes','market_fundamentals','nse_corporate_filings','insider_transactions',
    'pos_deep_memory_l2','inventory_files','jetson_jobs','process_state_log','agent_runs',
    'santa_saatelehed','timbertrace_deliveries','toftan_warehouse_stock',
    'skills_registry','integration_inventory')
GROUP BY tablename;
-- Eeldus: 19 rida, igaüks policy_count=1

7. Faas 2 migration SQL (Erki kinnitusega — avalikud tabelid + vaated)

-- Faas 2: 4 avaliku tabeli anon_read/insert + agent_task_locks otsus + view INVOKER (valikuline)

BEGIN;

-- 1. LVM kaart (Janis-äpp anon-key SELECT)
ALTER TABLE public.lvm_parcels ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.lvm_fells ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.lvm_nogabali ENABLE ROW LEVEL SECURITY;
CREATE POLICY anon_read ON public.lvm_parcels FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY anon_read ON public.lvm_fells FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY anon_read ON public.lvm_nogabali FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY service_role_all ON public.lvm_parcels FOR ALL TO service_role USING (true) WITH CHECK (true);
CREATE POLICY service_role_all ON public.lvm_fells FOR ALL TO service_role USING (true) WITH CHECK (true);
CREATE POLICY service_role_all ON public.lvm_nogabali FOR ALL TO service_role USING (true) WITH CHECK (true);

-- 2. PersonalOS waitlist (avalik signup vorm)
ALTER TABLE public.personalos_waitlist ENABLE ROW LEVEL SECURITY;
-- anon saab INSERT (signup) AGA EI saa SELECT (e-maile ei loe kõik)
CREATE POLICY anon_insert ON public.personalos_waitlist
  FOR INSERT TO anon, authenticated WITH CHECK (true);
-- count-päring vajab eraldi RPC funktsiooni VÕI policy USING(false) + count erand
-- Lihtsam: loo RPC SECURITY DEFINER `personalos_waitlist_count()`
CREATE OR REPLACE FUNCTION public.personalos_waitlist_count()
RETURNS integer LANGUAGE sql SECURITY DEFINER SET search_path = public, pg_catalog
AS $$ SELECT count(*)::int FROM personalos_waitlist; $$;
GRANT EXECUTE ON FUNCTION public.personalos_waitlist_count() TO anon, authenticated;
-- service_role full access
CREATE POLICY service_role_all ON public.personalos_waitlist FOR ALL TO service_role USING (true) WITH CHECK (true);
-- TODO: uuenda personalos.html:599 päring RPC kutsele

-- 3. agent_task_locks — KAS eirik_live.html on avalik?
-- VARIANT A (avalik dashboard, Erki valib): anon_read
-- ALTER TABLE public.agent_task_locks ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY anon_read ON public.agent_task_locks FOR SELECT TO anon USING (true);
-- CREATE POLICY service_role_all ON public.agent_task_locks FOR ALL TO service_role USING (true) WITH CHECK (true);
-- VARIANT B (sisemine, Erki valib): service_only + muuda eirik_live.html service_role peale
ALTER TABLE public.agent_task_locks ENABLE ROW LEVEL SECURITY;
CREATE POLICY service_role_all ON public.agent_task_locks FOR ALL TO service_role USING (true) WITH CHECK (true);

-- 4. spatial_ref_sys — JÄTA RLS OFF (PostGIS standard, advisor false-positive)
-- (ei tee mingit muudatust)

-- 5. SECURITY DEFINER → INVOKER konversioon (valikuline, soovituslik PG15+)
-- Variant A — täielik:
ALTER VIEW public.v_active_skills SET (security_invoker=true);
ALTER VIEW public.v_active_price_lists SET (security_invoker=true);
ALTER VIEW public.v_active_coefficient SET (security_invoker=true);
ALTER VIEW public.v_layer_registry_summary SET (security_invoker=true);
GRANT SELECT ON public.v_layer_registry_summary TO anon, authenticated;
-- v_agent_live + v_changelog + v_pending_invoices + data_freshness_status — JÄTA DEFINER
-- (anon-kasutus baastabelite vastu vajab täiendavat policy-tuningut, eraldi triaaž)

COMMIT;

8. Faas 3 migration SQL (autonomne — function search_path bulk-lock)

-- Faas 3: 30 user-funktsiooni search_path lukk
-- PostGIS funktsioone EI muuda (extension management)

BEGIN;

-- 7 SECURITY DEFINER kriitilist
ALTER FUNCTION public.hypervision_record_api_key_usage SET search_path = public, pg_catalog;
ALTER FUNCTION public.hypervision_verify_api_key SET search_path = public, pg_catalog;
ALTER FUNCTION public.insert_tma SET search_path = public, pg_catalog;
ALTER FUNCTION public.set_default_client_id SET search_path = public, pg_catalog;
ALTER FUNCTION public.trigger_embed_message SET search_path = public, pg_catalog;
ALTER FUNCTION public.trigger_red_team_check SET search_path = public, pg_catalog;

-- 23 SECURITY INVOKER user-funktsiooni
ALTER FUNCTION public.acquire_agent_lock SET search_path = public, pg_catalog;
ALTER FUNCTION public.audit_check_source SET search_path = public, pg_catalog;
ALTER FUNCTION public.audit_immutable SET search_path = public, pg_catalog;
ALTER FUNCTION public.auto_cleanup SET search_path = public, pg_catalog;
ALTER FUNCTION public.block_legacy_watchdog SET search_path = public, pg_catalog;
ALTER FUNCTION public.calculate_coefficient SET search_path = public, pg_catalog;
ALTER FUNCTION public.check_response_format SET search_path = public, pg_catalog;
ALTER FUNCTION public.claim_work SET search_path = public, pg_catalog;
ALTER FUNCTION public.find_skill SET search_path = public, pg_catalog;
ALTER FUNCTION public.os_reports_update_search_text SET search_path = public, pg_catalog;
ALTER FUNCTION public.query_memory SET search_path = public, pg_catalog;
ALTER FUNCTION public.search_bge_m3 SET search_path = public, pg_catalog;
ALTER FUNCTION public.search_memory_text SET search_path = public, pg_catalog;
ALTER FUNCTION public.search_memory_vec SET search_path = public, pg_catalog;
ALTER FUNCTION public.search_pos_deep_memory SET search_path = public, pg_catalog;
ALTER FUNCTION public.search_pos_memory SET search_path = public, pg_catalog;
ALTER FUNCTION public.tma_load_stems SET search_path = public, pg_catalog;
ALTER FUNCTION public.tma_load_stems_json SET search_path = public, pg_catalog;
ALTER FUNCTION public.tma_load_tracking_json SET search_path = public, pg_catalog;
ALTER FUNCTION public.trg_layer_registry_updated_at SET search_path = public, pg_catalog;
ALTER FUNCTION public.trigger_embed_agent_message SET search_path = public, pg_catalog;
ALTER FUNCTION public.trigger_sync SET search_path = public, pg_catalog;
ALTER FUNCTION public.update_coefficient SET search_path = public, pg_catalog;
ALTER FUNCTION public.update_skills_registry_updated_at SET search_path = public, pg_catalog;
ALTER FUNCTION public.watchdog_check SET search_path = public, pg_catalog;
ALTER FUNCTION public.watchdog_daily_report SET search_path = public, pg_catalog;

-- search_memory + query_memory on overload-vorm (mitu signatuuri) — peab käsitsi viiteid:
-- Kontroll \df+ search_memory ENNE.

COMMIT;

-- Validatsioon
SELECT proname, array_to_string(proconfig, ', ') AS cfg
FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace
WHERE n.nspname='public' AND p.proname IN (
  'hypervision_record_api_key_usage','hypervision_verify_api_key','insert_tma',
  'set_default_client_id','trigger_embed_message','trigger_red_team_check',
  'calculate_coefficient','search_pos_deep_memory','tma_load_stems_json','watchdog_check'
);
-- Eeldus: kõik cfg sisaldab 'search_path=public, pg_catalog'

9. Erijuhud + märkused

10. Audit-jälg (PR #115)

• ✅ start_run agent_id=spawn_red_team_rls_2026-05-27 (id=76, psl=63)
• ✅ set_read_only(True) Kiht 1 LIVE
• ✅ Hüpervision SAMM 12 (PR #124) — parent koord 12 (id=74) tuvastatud
• ⏳ register_edit raporti faili kohta (sub-agent lõpu samm)
• ⏳ pos_deep_memory category='otsus' tags=['red_team','rls_triage','pr132','vahimees'] (sub-agent lõpu samm)
• ⏳ agent_messages task_done priority=2 to_agent='vs_eirik' (sub-agent lõpu samm)
• ⏳ Telegram push_chip (PR #109) (sub-agent lõpu samm)
• ⏳ state.finish(status='completed') (sub-agent lõpu samm)

spawn_red_team_rls_2026-05-27 · Eirik OS K0 sub-agent · PR #115 audit-jälg + PR #112 Kiht 1 + PR #124 SAMM 12 + PR #81 Red Team verdict mall
Generated 2026-05-27 · Tier 2 Supabase MCP (Tier 1 DEGRADED PR #130)