Supabase Vault: Store Secrets Securely in Postgres

Supabase Vault is a Postgres extension that encrypts secrets at rest using Transparent Column Encryption, making them accessible only through SQL functions or the Supabase SDK. It stores API keys, tokens, and credentials directly in your database without requiring external services.

This approach sits between two common alternatives:

1. Manual encryption: You manage encryption keys and implement encrypt/decrypt logic yourself
2. External services: AWS Secrets Manager or HashiCorp Vault add infrastructure complexity and cost

Vault gives you encrypted storage without leaving your database. For most SaaS applications storing user API keys or integration credentials, it's the pragmatic choice.

In this tutorial, we'll create SQL functions to insert, read, and delete secrets from Vault, then call them from TypeScript using the Supabase SDK.

For more details on the encryption mechanics, see the official Supabase Vault documentation.

Prerequisites

You need a Supabase project. Create one at supabase.com if you don't have one.

This guide was tested with Supabase 2.x and the @supabase/supabase-js SDK. The Vault API is stable across versions.

Enable the Vault extension

Run this in your Supabase SQL editor:

create extension if not exists vault with schema vault;

That's it. Vault uses pgsodium under the hood for encryption, but you don't need to configure it directly.

Note: While pgsodium is being phased out as a standalone extension, the Vault API remains stable. Supabase will migrate Vault's internals without changing the interface you use.

Create the function to insert secrets

This function inserts a secret into the Vault with a name you can use to retrieve it later:

create or replace function insert_secret(name text, secret text)
returns uuid
language plpgsql
security definer
set search_path = public
as $$
begin
  return vault.create_secret(secret, name);
end;
$$;
-- Only service_role can execute this function
revoke execute on function insert_secret from public;
revoke execute on function insert_secret from anon;
revoke execute on function insert_secret from authenticated;
grant execute on function insert_secret to service_role;

Verify the function was created:

select proname from pg_proc where proname = 'insert_secret';
-- Expected: one row with 'insert_secret'

The security definer clause lets the function access the vault schema with elevated privileges. The permission grants ensure only server-side code with the service role key can execute this function.

Run this to regenerate your TypeScript types:

supabase gen types typescript --local > database.types.ts

Adjust the output path to match your project structure.

Insert secrets with the Supabase SDK

Call the function using an RPC:

import { createClient } from '@supabase/supabase-js';
async function insertSecret(name: string, secret: string) {
  const client = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  );
  const { data, error } = await client.rpc('insert_secret', {
    name,
    secret,
  });
  if (error) throw error;
  return data;
}

Important: Use the service role key, not the anon key. The function rejects calls that aren't from the service role.

Build unique secret names to avoid collisions:

async function storeOpenAiApiKey(userId: string, apiKey: string) {
  return insertSecret(`openai_api_key_${userId}`, apiKey);
}

Read secrets from the Vault

Create a function to retrieve decrypted secrets:

create or replace function read_secret(secret_name text)
returns text
language plpgsql
security definer
set search_path = public
as $$
declare
  secret text;
begin
  select decrypted_secret
  from vault.decrypted_secrets
  where name = secret_name
  into secret;
  return secret;
end;
$$;
-- Only service_role can execute this function
revoke execute on function read_secret from public;
revoke execute on function read_secret from anon;
revoke execute on function read_secret from authenticated;
grant execute on function read_secret to service_role;

The vault.decrypted_secrets view handles decryption automatically. You get the plaintext value without managing encryption keys.

Note: This function returns NULL if no secret exists with the given name. Always check for this in your application code.

Read secrets with the Supabase SDK

import { createClient } from '@supabase/supabase-js';
async function getSecret(secretName: string): Promise<string | null> {
  const client = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  );
  const { data, error } = await client.rpc('read_secret', {
    secret_name: secretName,
  });
  if (error) throw error;
  return data;
}
async function getOpenAiApiKey(userId: string): Promise<string | null> {
  return getSecret(`openai_api_key_${userId}`);
}

Use the secret in your application

import OpenAI from 'openai';
async function createChatCompletion(userId: string, prompt: string) {
  const apiKey = await getOpenAiApiKey(userId);
  if (!apiKey) {
    throw new Error('OpenAI API key not found for user');
  }
  const openai = new OpenAI({ apiKey });
  return openai.chat.completions.create({
    model: 'gpt-4o',
    messages: [{ role: 'user', content: prompt }],
    max_tokens: 500,
  });
}

Delete secrets from the Vault

Create a function to remove secrets:

create or replace function delete_secret(secret_name text)
returns void
language plpgsql
security definer
set search_path = public
as $$
begin
  delete from vault.secrets where name = secret_name;
end;
$$;
-- Only service_role can execute this function
revoke execute on function delete_secret from public;
revoke execute on function delete_secret from anon;
revoke execute on function delete_secret from authenticated;
grant execute on function delete_secret to service_role;

Delete secrets with the Supabase SDK

import { createClient } from '@supabase/supabase-js';
async function deleteSecret(secretName: string): Promise<void> {
  const client = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  );
  const { error } = await client.rpc('delete_secret', {
    secret_name: secretName,
  });
  if (error) throw error;
}
async function deleteOpenAiApiKey(userId: string): Promise<void> {
  return deleteSecret(`openai_api_key_${userId}`);
}

Pitfalls to avoid

When working with Supabase Vault, watch out for these common issues:

• Using the anon key instead of service role: The SQL functions only grant execute permissions to service_role. Calls from anon or authenticated roles will fail with a permission denied error. Always use SUPABASE_SERVICE_ROLE_KEY for Vault operations.
• Not handling NULL returns: read_secret returns NULL if no secret exists with that name. Check for NULL before using the value to avoid runtime errors.
• Statement logging exposing plaintext: By default, Supabase logs SQL statements. INSERT statements containing secrets appear in logs unencrypted. Disable statement logging for sensitive operations or use the Supabase dashboard for manual entry.
• Forgetting to regenerate types: After adding these functions, run supabase gen types typescript to get proper TypeScript autocomplete for your RPC calls.
• Calling vault.create_secret directly: It works, but bypasses your authorization checks. Use the wrapper functions for consistent access patterns and audit trails.

When to use Vault vs. environment variables

Use environment variables for:

• Application-wide secrets (database URLs, service API keys)
• Secrets that don't change per user
• Secrets needed at build time

Use Vault for:

• Per-user API keys and credentials
• Secrets that users provide and manage
• Integration tokens that vary by account

Avoid Vault for:

• Secrets requiring high-frequency rotation (consider external managers)
• Secrets needed before the database connection is established

If unsure: Start with environment variables for application-wide secrets. Use Vault only when you need per-user or per-account secret storage.

For a production API key system with scopes, rotation, and usage tracking, see the API key management guide.

Security considerations

Vault encrypts secrets at rest, but you're still responsible for access control:

1. Only expose functions through service role: The permission grants in each function prevent client-side access
2. Never return secrets to the client: Process secrets server-side and return results, not the secrets themselves
3. Audit secret access: Consider logging when secrets are read for compliance requirements
4. Rotate secrets regularly: Build workflows for users to update their stored credentials

For security best practices in SaaS applications, including Row Level Security and data validation, see the security documentation.

Conclusion

Supabase Vault provides encrypted secret storage without external dependencies. The pattern is simple: create SQL functions with permission grants restricted to service_role, call them via RPC from your server-side code, and let Vault handle encryption.

For more advanced use cases like scoped API keys with usage tracking, check out the complete API key management system guide.