fix(security): remove explain_generic SECURITY DEFINER helper (RCE)

Dmitry Fominrequested to merge
security/remove-explain-generic into main

Summary

postgres_ai.explain_generic was a SECURITY DEFINER helper (owned by the provisioning superuser/rds_superuser) that ran EXPLAIN on caller-supplied SQL. This is a confirmed RCE / data-exfiltration vector for the low-privilege monitoring role — see confidential issue #387 (closed) (empirically reproduced: a folded IMMUTABLE function leaked a planted secret through plan output, and explain_generic('select ssn from customer_secrets') returned a full plan while a direct SELECT was denied).

Why it's exploitable

EXPLAIN without ANALYZE still plans, and the planner evaluates functions at plan time:

  • eval_const_expressions folds IMMUTABLE functions with constant args → executes them.
  • selectivity estimation evaluates STABLE functions in WHERE (e.g. ... WHERE x = evil()).

Both run in the definer's superuser context. The semicolon guard is no obstacle (the function-call path needs no semicolon), and this cannot be closed by text validation.

Why removal (not SECURITY INVOKER)

There is no Postgres privilege that lets a role plan/EXPLAIN a query without holding SELECT on the referenced tables — which is exactly why the helper needed DEFINER and why INVOKER fails outright. Any in-place "secure" version must either hold broad data-SELECT (breaks the metadata-only promise) or require explicit per-relation grants (an opt-in product change). Removing the helper kills the RCE now and preserves the metadata-only contract; the opt-in-grant design can be revisited separately.

This supersedes the SECURITY INVOKER attempt in !541.

Changes

  • cli/sql/06.helpers.sql — drop explain_generic + its grant. table_describe retained (bounded catalog reader, runs no caller SQL — handled separately).
  • cli/sql/uninit/01.helpers.sql — already drops explain_generic, so re-prepare / uninit removes the function from existing installs (no migration needed CLI-side).
  • cli/lib/init.ts, cli/lib/supabase.ts — remove the EXECUTE on explain_generic health-checks (would otherwise fail verification post-removal). Other has_function_privilege checks (table_describe, RDS tools, optional perms) are unchanged.
  • cli/test/init.integration.test.ts — red→green flip: assert prepare-db does not create explain_generic.

Follow-ups (out of scope for this MR)

  • platform-all: db/functions/supabase_prepare_db.sql still emits the function — needs its own removal + migration, and !541 repurposed there.
  • table_describe definer-enumeration smell (not RCE) — handle separately.
  • Keep #387 (closed) confidential until both CLI and platform-all removals ship.

Closes #387 (closed)

Merge request reports