Using Supabase Vault to store secrets

Supabase Vault is a Postgres extension that allows you to store secrets in your database. This is a great way to store API keys, tokens, and other sensitive information. In this tutorial, we'll use Supabase Vault to store our API keys

It's very common for any SaaS application to need to store API keys, tokens, and other sensitive information. There are various alternatives to do so practically and securely, but you're likely making some tradeoffs with each approach.

  1. Manually encrypting secrets in your DB: One of the approaches can be to encrypt the secrets and store them in your database. This is a good approach, but you'll need to manage the encryption and decryption of the secrets. You'll also need to manage the encryption keys and make sure they're secure.
  2. Using external services: Another approach is to use an external Secrets Manager like AWS Secrets Manager or Hashicorp Vault. This is a great approach, with the added complexity of managing another service, and the cost of the service itself.

In this tutorial I introduce Supabase Vault, an alternative approach to storing secrets in your Postgres database, which is both secure and cost-effective. Supabase Vault is a Postgres extension that allows you to store secrets in your database. This is a great way to store API keys, tokens, and other sensitive information.

To make working with Supabase Vault, we will be defining functions to store and retrieve secrets from the Vault that we can easily execute using the Supabase SDK. We will create simple SQL functions to store and retrieve secrets from Supabase Vault, and we will use these functions to store and retrieve our API keys.

While in this post we focus on DX, if you want to know more about the nitty and gritty of Supabase Vault, you can read the official documentation.

Prerequisites

Before we get started, you'll need to have a Supabase project. If you don't have one, you can create one for free at supabase.io.

Enable the Vault extension

Once you have your Supabase project, you'll need to enable the Vault extension. You can do this by going to the SQL editor and running the following query:

create extension vault with schema vault;

This is pretty much all you need to do to enable the Vault extension.

Now that we have the Vault extension enabled, we can create the functions to store, retrieve and delete secrets from the Vault.

Create the function to insert secrets into Supabase Vault

The first function we'll create is the function to insert secrets into the Vault. This function will take two parameters, the key and the value of the secret. The key will be the name of the secret, and the value will be the actual secret.

create or replace function insert_secret(name text, secret text)
returns uuid
language plpgsql
security definer
set search_path = public
as $$
begin
  if current_setting('role') != 'service_role' then
    raise exception 'authentication required';
  end if;
  return vault.create_secret(secret, name);
end;
$$;

Run the following command to update your database types, so that you can use the function in your queries:

supabase gen types typescript --local > app/database.types.t

NB: Tweak the path to the generated types file to match your project structure.

Inserting a secret using the Supabase SDK

Now that we have the function to insert secrets into the Vault, we can use it in our Supabase project. We'll use the Supabase SDK to insert the secret into the Vault using an RPC call. We assume you can create a Supabase client instance using the function createSupabaseClient.

The RPC call to insert a secret into the Vault will look like this:

function insertSecret(name: string, secret: string) {
  const client = createSupabaseClient();
  client.rpc('insert_secret', {
    name,
    secre,
  });
}

As you can see, we're passing the name and the secret to the RPC call. The name parameter will be the name of the secret, and the secret parameter will be the actual secret.

You can now use the insertSecret function to insert secrets into the Vault.

Assuming you're storing a password or an API Key, you can use the insertSecret function like this:

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

Remember to always build a unique name for your secrets - for example, by adding the user ID (or some other unique identifier) to the name of the secret.

Reading a secret using the Supabase SDK

First, we want to define the SQL function in our Supabase project. The function takes a secret_name parameter that allows us to query the Vault for the secret.

create function read_secret(secret_name text)
returns text
language plpgsql
security definer set search_path = public
as $$
declare
  secret text;
begin
  if current_setting('role') != 'service_role' then
    raise exception 'authentication required';
  end if;
  select decrypted_secret from vault.decrypted_secrets where name =
  secret_name into secret;
  return secret;
end;
$$;

Now that we have the function to insert secrets into the Vault, we can use it in our Supabase project. We'll use the Supabase SDK to insert the secret into the Vault using an RPC call.

Reading secrets using the Supabase Client

To read a secret from the Vault, we'll use the read_secret function. This function takes one parameter, the name of the secret.

function getSecret(
  secretName: string
) {
  const client = createSupabaseClient();
  return client.rpc('read_secret', {
    secret_name: secretName,
  });
}

Now we can use this function to read secrets from the Vault. For example, if we want to read the OpenAI API key, we can do it like this:

export async function getOpenAiApiKey(
  userId: string
) {
  return getSecret(
    buildApiKeyName(userId)
  );
}

You can now use the getOpenAiApiKey function to read secrets from the Vault.

import { Configuration, OpenAIApi } from 'openai-edge';
function getOpenAIClient(apiKey: string) {
  const configuration = new Configuration({ apiKey });
  return new OpenAIApi(configuration);
}
async function getCompletions(
  userId: string,
  prompt: string
) {
  const apiKey = await getOpenAiApiKey(userId);
  const client = getOpenAIClient(apiKey);
  return client.createChatCompletion({
    model: 'gpt-3.5-turbo',
    messages: [
      {
      role: 'user',
      content: prompt,
    },
    ],
    max_tokens: 180,
    temperature: 1,
  });
}

Deleting a secret using the Supabase SDK

Finally, we want to make sure we can delete secrets from the Vault. This is useful if we want to revoke access to a secret, or if we want to delete a secret for any other reason.

First, we want to define the SQL function in our Supabase project. The function takes a secret_name parameter that allows us to query the Vault for the secret.

create function delete_secret(secret_name text)
returns text
language plpgsql
security definer set search_path = public
as $$
begin
  if current_setting('role') != 'service_role' then
    raise exception 'authentication required';
  end if;
  return delete from vault.decrypted_secrets where name = secret_name;
end;
$$;

Now that we have the function to insert secrets into the Vault, we can use it in our Supabase project. We'll use the Supabase SDK to insert the secret into the Vault using an RPC call.

After adding this function, remember to update your database types, so that you can use the function in your queries.

Deleting secrets using the Supabase Client

To delete a secret from the Vault, we'll use the delete_secret function. This function takes one parameter, the name of the secret.

export function deleteSecret(
  secretName: string
) {
  const client = createSupabaseClient();
  return client.rpc('delete_secret', {
    secret_name: secretName,
  });
}

Now we can use this function to delete secrets from the Vault. For example, if we want to delete the OpenAI API key, we can do it like this:

export async function deleteOpenAiApiKey(
  userId: string
) {
  return deleteSecret(
    buildApiKeyName(userId)
  );
}

Conclusion

In this tutorial we've learned how to use Supabase Vault to store secrets in our Postgres database. We've created functions to insert, read and delete secrets from the Vault, and we've used these functions to store and retrieve our API keys.

To learn more about Supabase Vault, check out the Supabase official documentation.