Supabase RPC EXECUTE review (S-011)

Advisor warning: anon / authenticated have EXECUTE on some api.*SECURITY DEFINER functions.

Reviewed functions

get_catalog_properties()

Question	Answer
Needed for anon?	Yes — public property catalog for guest visitors
PII exposure?	No — RPC strips owner-identifying columns; RLS blocks direct properties SELECT for anon
Client usage	properties_repository.dart (publicCatalogOnly)
Decision	Keep GRANT EXECUTE TO anon, authenticated. Revoke from PUBLIC only.

transaction_price_by_area(...)

Question	Answer
Needed for anon?	Yes — public price map / area averages (MoJ aggregates, no PII)
Client usage	area_price_stats_repository.dart, public price map screen
Decision	Keep GRANT EXECUTE TO anon, authenticated. Revoke from PUBLIC only.

Applied (2026-06-09)

Migration 20260609150000_revoke_public_execute_catalog_rpc.sql is live on project jrsgosnnyjonxaesqtln. PUBLIC no longer has EXECUTE on get_catalog_properties (both public and api wrappers). anon and authenticated grants are unchanged.

transaction_price_by_area was already hardened in 20260531183000_standardize_transaction_property_types.sql.

Hardening pattern (for future RPCs)

-- Replace <args> with the live signature from \df+ get_catalog_properties
REVOKE ALL ON FUNCTION public.get_catalog_properties() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.get_catalog_properties() TO anon, authenticated;

REVOKE ALL ON FUNCTION public.transaction_price_by_area(<args>) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.transaction_price_by_area(<args>) TO anon, authenticated;

Do not revoke anon without shipping an authenticated-only public catalog — that would break guest browsing on Flutter Web and the marketing site.

Tables without RLS policies (S-012) — RESOLVED ✅

Backup / internal WhatsApp queue and research tables are service-role only by design. Explicit service_role_all policies were added in 20260915060000_s012_harden_internal_research_tables_rls.sql, bringing the Supabase database linter 0008_rls_enabled_no_policy count to 0 while maintaining strict default-deny isolation for client roles (anon and authenticated).

Security & Performance Advisors Hardening (2026-10-02)

Comprehensive audit and remediation of Supabase Database Advisor notices (Linters 0001, 0006, 0008, 0012, 0028, 0029) codified in migration 20261002144806_harden_security_and_performance_advisors.sql:

1. RLS Enabled No Policy (0008):
   • Added explicit service_role_all policies on internal.valuation_deterministic_recovery_ledger, internal.valuation_register_dedupe_ledger, and internal.valuation_same_parcel_crossfill_ledger.
2. Unindexed Foreign Keys (0001):
   • Added covering indexes on internal.valuation_same_parcel_crossfill_ledger(donor_valuation_id) and public.property_description_conflict_dismissals(dismissed_by).
3. Anonymous Sign-ins Defense (0012):
   • Hardened staff_read_description_conflict_dismissals, staff_insert_description_conflict_dismissals, and staff_delete_description_conflict_dismissals with is_anonymous checks to prevent anonymous token access.
4. Multiple Permissive Policies on SELECT (0006):
   • Split broad FOR ALL write policies into FOR INSERT, FOR UPDATE, FOR DELETE on field_merge_policy, hermes_staff_contacts, property_governorate_owners, and sync_conflicts.
   • Dropped redundant legacy net_dc read policy on network_deal_contacts.
   • Consolidated property_attachments SELECT policies between public catalog images (anon) and property-linked attachments (authenticated).
5. Security Definer Function Hardening (0028 & 0029):
   • Revoked EXECUTE on trigger function internal.sanitize_property_address_on_write() from PUBLIC, anon, and authenticated.
   • Revoked direct authenticated execution from internal cadastral helpers internal.resolve_parcel_identity and internal.resolve_parcel_identity_by_paci.
   • Converted 19 api.* client-portal wrapper functions from SECURITY DEFINER to SECURITY INVOKER, inheriting security context from the underlying public.* functions without triggering linter alerts.

Production Cron Job Repairs & Cadastral Batching (2026-10-02)

Remediation of 3 recurring production pg_cron job failures codified in migration 20261002155740_repair_failing_cron_jobs_and_cadastral_timeouts.sql:

1. Brokerage Stale Inventory Auto-Demotion Sweep (Job 112 / sweep-stale-brokerage-listings):
   • Fixed enum cast type mismatch: replaced erroneous cast to domain property_al_ard_status with enum property_listing_status.
   • Function now demotes active listings exceeding 60 days without owner follow-up in ~3.5 ms without throwing type errors.
2. HTTP Response Table Bloat Cleanup (Job 75 / cleanup-http-logs):
   • Removed invalid VACUUM invocation from the pg_cron schedule body. pg_cron runs in an implicit transaction block where PostgreSQL disallows manual VACUUM.
   • Nightly DELETE FROM net._http_response WHERE created < now() - interval '3 days' now commits cleanly, preventing net._http_response table bloat. Native PostgreSQL autovacuum daemon handles dead tuple reclamation.
3. Nightly Cadastral Parcel Lineage & Valuation Resolution (Job 37 / parcel-lineage-nightly):
   • Added bounded batch processing (p_limit integer DEFAULT 10, clamped via GREATEST(1, LEAST(COALESCE(p_limit, 10), 50))) to api.reresolve_stale_valuation_identities.
   • Added bounded batch processing (p_limit integer DEFAULT 50) to api.reresolve_stale_property_identities.
   • Prevents nightly statement timeouts (120s) when matching historical unlinked valuations against the 2.95 GB internal.baladia_parcels table.

Conflict Detection Triggers & Redundant Index Pruning (2026-10-02)

Performance optimizations codified in migration 20261002165534_optimize_conflict_detection_and_prune_redundant_indexes.sql:

1. Covering Indexes for Conflict Detection (check_and_record_conflict_disclosure):
   • Added partial covering indexes on properties_base(lower(TRIM(area)), TRIM(block), TRIM(plot)) WHERE (deleted_at IS NULL), properties_base(TRIM(paci_number)), properties_base(TRIM(owner_phone)), valuations_base(TRIM(paci_number)), and valuations_base(TRIM(applicant_phone)).
   • Eliminates full sequential scans on valuation conflict detection triggers, decreasing query execution latency from ~32 ms down to ~1.2 ms (96% latency reduction).
2. Redundant Prefix B-Tree Index Pruning:
   • Pruned 10 single-column prefix-redundant indexes whose leading columns were already indexed by multi-column composite or unique indexes (idx_hermes_embeddings_source, idx_ret_governorate, idx_real_estate_transactions_category_key, idx_valuations_workflow_stage, idx_whatsapp_messages_conversation, request_attachments_request_id_idx, idx_agent_action_queue_domain, idx_property_media_property_id, idx_property_attachments_property_id, idx_fk_user_roles_user_id).
   • Eliminates write amplification on high-throughput tables (whatsapp_messages, hermes_embeddings, real_estate_transactions) and reclaims disk storage.

Bloat Remediation, Cron Policies, and Duplicate Indexes (2026-10-03)

Comprehensive remediation of remaining database advisors codified in migration 20261003051500_remediate_bloat_cron_policies_and_duplicate_indexes.sql:

1. RLS Enabled No Policy (0008):

   • Added explicit service_role_all policy on internal.parcel_identity_resolution_attempts (service_role_manage_parcel_identity_resolution_attempts).
   • Restores clean 0008_rls_enabled_no_policy status across the entire database.

2. Table Bloat Remediation (table_bloat):

   • Tuned autovacuum storage parameters on net._http_response: autovacuum_vacuum_scale_factor = 0.05 and autovacuum_vacuum_threshold = 100.
   • Ensures the native PostgreSQL autovacuum daemon promptly reclaims dead tuples generated by edge-function HTTP responses and nightly DELETE sweeps.

3. Anonymous Sign-ins Defense on pg_cron tables (0012):

   • Hardened cron_job_policy on cron.job and cron_job_run_details_policy on cron.job_run_details to restrict access strictly to postgres and service_role.
   • Revoked table privileges from anon and public.

4. Redundant Duplicate B-Tree Index Pruning (0005):

   • Dropped 4 exact duplicate B-Tree indexes whose single indexed column was already indexed by an identical UNIQUE constraint:
     • public.idx_properties_base_share_token (duplicate of properties_base_share_token_key)
     • public.idx_public_holidays_date (duplicate of public_holidays_holiday_date_key)
     • public.idx_network_persons_unified_person_id (duplicate of network_persons_unified_person_id_key)
     • public.idx_party_kyc_unified_person_id (duplicate of party_kyc_unified_person_id_key)

5. Security Definer Function Intentionality (0028 & 0029):

   • internal.property_photos_are_public: Must remain SECURITY DEFINER with GRANT EXECUTE TO anon, authenticated because public guest users evaluate storage and attachment RLS policies on catalog images without having direct table access to properties_base. PostgREST does not expose the internal schema via RPC.
   • The 12 api.* admin functions (admin_create_automation_rule, admin_delete_automation_rule, admin_list_automation_logs, admin_list_automation_rules, admin_update_automation_rule, admin_upsert_baladia_parcels, close_absent_parcels, create_manual_parcel_event, detect_parcel_renumbers, detect_parcel_splits_merges, reresolve_stale_property_identities, review_parcel_event): Must remain SECURITY DEFINER with GRANT EXECUTE TO authenticated because authenticated admin users invoke them from Flutter admin management screens, while each function self-guards with internal.assert_admin_actor_v1() or internal.require_parcel_admin_or_service() (raising 42501 for non-admins).