Vault keeps secrets encrypted in Postgres; read them in definer code

The answer. Some secrets have to be used from inside the database: a webhook base URL that differs per environment, an API key a pg_net call must send, a wrapper's credential. Vault is the extension for that. vault.create_secret(value, name, description) stores the value encrypted (the key lives outside the database, so a dump or a backup does not contain plaintext), and vault.decrypted_secrets is a view that decrypts on read. The whole security model is who can select from that view. The roles the API runs as must not have it; database code that runs as the owner (a security definer function, a trigger, a cron job) can read the one secret it needs by name. If application code needs a value, expose a tiny RPC gated on auth.role() = 'service_role' rather than granting the view.

The pattern.

select vault.create_secret('https://hooks.example.com', 'WEBHOOK_BASE_URL', 'per-environment base URL');
select vault.create_secret('sk_live_…', 'STRIPE_SECRET_KEY');

create or replace function internal.webhook_base_url() returns text
language sql stable security definer set search_path = '' as $$
  select decrypted_secret from vault.decrypted_secrets where name = 'WEBHOOK_BASE_URL'
$$;
revoke execute on function internal.webhook_base_url() from public, anon, authenticated;
-- a trigger in a private schema can call it; the API cannot

Watch out.

Rotate by vault.update_secret(id, new_value, …), not by creating a second row with the same name; readers select by name.
On projects still using the legacy JWT secret, rotating that secret regenerated the anon and service_role keys as well; with asymmetric signing keys, key rotation leaves the publishable and secret keys alone.
Edge Function secrets (supabase secrets set) are a different store; a value needed both in SQL and in Deno lives in both, deliberately.