Storing secrets with Vault

Sometimes SQL needs a secret: a cron job that calls a webhook with a bearer token, a trigger that posts to a payment API, a function that signs a request. Pasting the token into the function body leaves it readable by anyone who can see your schema, and it ends up in every dump and migration file. Vault stores it encrypted instead and decrypts it only when a query asks for it.

Vault is the supabase_vault Postgres extension. Secrets live in the vault.secrets table in encrypted form, and the vault.decrypted_secrets view decrypts them at query time. Data at rest, including in backups and pg_dump exports, stays encrypted.

Vault or function secrets?

If both need the same key, store it in both places.

Check Vault is available

Vault ships with the Supabase Postgres image. Confirm it's installed in your project's database:

select extname, extversion from pg_extension where extname = 'supabase_vault';

If nothing comes back, enable it:

create extension if not exists supabase_vault;

Create a secret

vault.create_secret(secret, name, description) stores a value and returns its id. The name is optional but must be unique, and it's how you'll look the secret up later.

select vault.create_secret(
  'whsec_live_replace_me',
  'invoicing_webhook_token',
  'Bearer token for the invoicing provider webhook'
);

Run this in Studio's SQL editor (Dashboard → your project → Database tab → Open Studio) rather than in a migration file. A migration would put the plain value into your repository and into supabase_migrations.schema_migrations, which keeps the statements it ran.

Read a secret

Select from the view by name:

select decrypted_secret
from vault.decrypted_secrets
where name = 'invoicing_webhook_token';

vault.secrets on its own shows only the encrypted bytes. To list what you've stored without exposing values:

select id, name, description, created_at, updated_at
from vault.secrets
order by name;

Use a secret in a function

A typical pattern: a trigger that tells an outside service when an invoice is paid, using pg_net for the HTTP call.

create or replace function public.notify_invoice_paid()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  token text;
begin
  select decrypted_secret into token
  from vault.decrypted_secrets
  where name = 'invoicing_webhook_token';

  perform net.http_post(
    url := 'https://hooks.example-invoicing.com/paid',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || token
    ),
    body := jsonb_build_object('invoice_id', new.id, 'total', new.total)
  );
  return new;
end;
$$;

revoke execute on function public.notify_invoice_paid() from public, anon, authenticated;

create trigger invoice_paid
  after update of status on public.invoices
  for each row
  when (new.status = 'paid' and old.status is distinct from 'paid')
  execute function public.notify_invoice_paid();

The function is security definer so it can read Vault even when the update came from a signed-in user through the Data API. Because it can read secrets, keep its search_path empty and don't expose it as an RPC. More on pg_net in Database webhooks and HTTP requests with pg_net.

Use a secret in a cron job

Cron jobs run as postgres, which can read Vault directly. This job calls an Edge Function every night with the project's service_role key, stored in Vault first:

select vault.create_secret('<service_role key from Connect → App>', 'service_role_key');

select cron.schedule(
  'nightly-invoice-reminders',
  '0 6 * * *',
  $$
  select net.http_post(
    url := 'https://app-one.acme.supahost.space/functions/v1/send-reminders',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || (
        select decrypted_secret from vault.decrypted_secrets where name = 'service_role_key'
      )
    ),
    body := '{}'::jsonb
  );
  $$
);

The service_role key is shown under Dashboard → your project → Connect tab → App, behind the Reveal button (owner only). See Running functions on a schedule for more.

Update and delete secrets

vault.update_secret(id, new_secret, new_name, new_description) changes a secret in place. Pass null for anything you want to keep:

select vault.update_secret(
  (select id from vault.secrets where name = 'invoicing_webhook_token'),
  'whsec_live_new_value',
  null,
  null
);

To remove one:

delete from vault.secrets where name = 'invoicing_webhook_token';

Keep the view locked down

Anyone who can select from vault.decrypted_secrets can read every secret. Keep it that way by default:

• Never grant vault schema access to anon or authenticated.
• Read secrets inside security definer functions with set search_path = '', and revoke execute on those functions from anon and authenticated unless they're meant to be called by users.
• Don't return a decrypted value from a function the Data API exposes.

The vault schema isn't one of the schemas the Data API serves, so it can't be queried over REST directly.

Vault and moving projects

The one-click import from Supabase Cloud doesn't copy Vault secrets. It lists them for you, and you re-create them with vault.create_secret after the import. If you move a database some other way, such as restoring a pg_dump into a different server, check that each secret still decrypts and re-create any that don't.

Related

• Database functions
• Scheduled jobs with pg_cron
• Keeping keys safe