Pre-Conditions

• The pgsodium extension MUST be enabled in the target Supabase project.
• The vault schema MUST exist (Supabase enables this by default with pgsodium).
• The agent MUST have MCP execute_sql access via service_role.
• The supabase-mcp-verification tile MUST be installed and passing.

Execution Phases

Phase 1 — Validate Vault Availability

1. Run SELECT extname FROM pg_extension WHERE extname = 'pgsodium'; — HALT if empty.
2. Run SELECT count(*) FROM information_schema.tables WHERE table_schema = 'vault' AND table_name = 'secrets'; — HALT if 0.

Phase 2 — Insert Secret into Vault

1. Use vault.create_secret(new_secret text, new_name text) to store the secret.
2. The new_name parameter MUST be a descriptive key (e.g., openai_api_key).
3. NEVER pass the raw secret value as a SQL literal in any logged or echoed statement.
4. Store the returned id (UUID) for reference in the accessor function.

Phase 3 — Create SECURITY DEFINER Accessor Function

Execute the following as a single SQL block:

CREATE OR REPLACE FUNCTION get_secret(secret_name text)
RETURNS text
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT decrypted_secret
  FROM vault.decrypted_secrets
  WHERE name = secret_name
  LIMIT 1;
$$;

ALTER FUNCTION get_secret(text) OWNER TO postgres;

REVOKE EXECUTE ON FUNCTION get_secret(text) FROM PUBLIC, anon, authenticated;
GRANT EXECUTE ON FUNCTION get_secret(text) TO service_role;

Phase 4 — Validate Access Boundary

1. As service_role, run SELECT get_secret('openai_api_key'); — MUST return the decrypted value.
2. As anon, run SELECT get_secret('openai_api_key'); — MUST return a permission-denied error.
3. If the anon call succeeds, HALT — the security boundary is broken.

Phase 5 — Verification Report

1. Confirm: secret stored in vault.secrets (yes/no).
2. Confirm: accessor function exists with SECURITY DEFINER (yes/no).
3. Confirm: service_role access succeeds (yes/no).
4. Confirm: anon access denied (yes/no).
5. Report any function ownership or grant deviations.