Explain the internal working of Supabase Vault for storing encrypted secrets in Postgres?

Vault is a Postgres extension that stores secrets encrypted at rest using authenticated encryption, backed by a root key that never lives inside the database itself, so a raw database dump or a leaked backup doesn't expose the plaintext secret alongside it.

Secrets are inserted through a dedicated function rather than a plain INSERT, for example select vault.create_secret('sk_live_...', 'stripe_api_key');, which stores the ciphertext in an internal table. Reading a secret back requires querying the vault.decrypted_secrets view, which itself can be restricted with Row Level Security or role grants so only specifically authorized database roles (typically a SECURITY DEFINER function used by an Edge Function, not arbitrary client roles) can ever see the decrypted value.

The practical benefit over a plain table column is that "store the value" and "read the plaintext back" are two distinctly permissioned operations rather than one — a role with SELECT on a regular table sees everything in plaintext, whereas a role without explicit access to the decrypted view sees only ciphertext, which is the difference between a genuine secrets-management layer and a table that merely holds sensitive data unprotected.