Vault Secrets Pattern

Enforces pgsodium Vault for secret storage accessed only via SECURITY DEFINER functions on service_role.

Overview

This tile eliminates hardcoded API keys and credentials by mandating the pgsodium Vault extension as the sole secret-storage mechanism. Secrets are stored encrypted in vault.secrets and accessed exclusively through SECURITY DEFINER functions owned by postgres and granted only to service_role. No other role may read decrypted secret values.

Reference

Core Vault Functions

Accessor Function Template

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;

Required Extensions

Dependencies

• supabase-mcp-verification — MUST pass before any Vault operations execute via MCP.
• service-role-boundary — MUST be installed to enforce role-based access restrictions on accessor functions.

Composition Position

This tile operates after MCP verification confirms connectivity. It provides secret-access primitives that downstream tiles (e.g., Edge Functions calling external APIs) consume. It does not manage the secrets' business logic — only secure storage and retrieval.