❇️ UPDATE JUNE 2023 ❇️
Vault is now available on every Supabase project. Check it out
Transparent Column Encryption with Postgres is a blog post that describes the technology behind Vault - libsodium and pgsodium. Now, we will go through a quick example of storing a secret, like a service access token, into the Vault.
Until now, the industry-standard for PostgreSQL encryption is a built-in extension called
Like most cloud providers, Supabase offers
pgcrypto for developers to use in their applications.
pgcrypto has been around for a long time,
and while it supports some basic encryption and decryption abilities, it lacks features like public key signing, key derivation APIs, streaming encryption,
and other modern features required by security-first applications.
Problems with raw keys
Databases often store sensitive information, and they need tools that guarantee this data is stored and backed-up in an encrypted form.
A fundamental issue with
pgcrypto is that it lacks the ability to derive keys from outside of SQL. Instead you must have the raw encryption key for the algorithm you wish to use:
_12create extension pgcrypto;_12_12create table users (_12id serial primary key,_12email varchar not null unique_12);_12_12insert into users_12(email)_12values_12(pgp_sym_encrypt('firstname.lastname@example.org', 's3kr3t_k3y')),_12(pgp_sym_encrypt('email@example.com', 's3kr3t_key'));
pgcrypto works with raw keys. In order to encrypt the data with pgcrypto you must pass the key directly to the encryption function. Leaking those raw keys is all too easy - logs, files, clients, tables, replication streams - you name it. Wouldn't it be great if you could encrypt data, but instead of specifying the raw key you reference it indirectly, like with a key ID?
Supabase Vault allows you to store secrets without leaking any sensitive information.
The Vault is a good place to put things like API keys, access tokens, and other secrets from external services that you need to access within your database. The core of the Supabase Vault is a table with some metadata and an encrypted text column where you can put your secrets and any metadata related to them.
We take the pain out of key management by pre-generating a unique, per-database key that is used by default - a “root” key - which is stored outside of the SQL language, accessibly only internally in the Postgres server by the libsodium library. This root key is managed by the pgsodium Postgres extension when the server boots using Server Key Management.
pgsodium provides an Encryption and Key Derivation API based on the libsodium library and can get it's root key from a
variety of sources depending on how you configure it. Supabase generates and preserves your project's root key behind the scenes, so you don't have to worry about it.
If you install pgsodium locally the default is to generate a random root key from the
/dev/urandom device which is then saved in a file in your Postgres data directory.
vault extension is the same as any other Postgres extension:
_10create extension vault with schema vault;
Once enabled, you can insert secrets into the
_10insert into vault.secrets_10(secret, associated)_10values_10('s3kr3t_k3y', 'This is the secret API service key.');
Now when you look in the
vault.secrets table, the secret is encrypted:
_10select * from vault.secrets;
Notice how the row has a
key_id column. This is the ID of the internally derived key that is used to encrypt the secret, not the key itself.
The actual raw key is not available to you in SQL, it is managed entirely outside of the SQL language in the Postgres server.
At Supabase, we manage this key for your project automatically and generate a unique default Key ID for you in the
For self-hosting, pgsodium supports a variety of ways to place the root key into Postgres.
To see the decrypted data, there is a special view created called
_10select * from vault.decrypted_secrets;
Now you can see a new
decrypted_secret column that contains the decrypted secret we originally inserted into the table.
vault.decrypted_secrets view automatically decrypts rows in the
vault.secrets table “on-the-fly” as you query them, but the secret is stored on disk in encrypted form.
If you take a backup, or pause your project, that data remains encrypted. We will keep your hidden root key safe in our backend systems for when you need to restore or un-pause your projects.
If you wish to use your own Key ID for different secrets, instead of the default Key ID we've generated, you can create one using the
_10select * from pgsodium.create_key('This is a comment for the new key');
Now you can encrypt table secrets with this new key by inserting its ID explicitly:
_10insert into vault.secrets_10(secret, associated, key_id)_10values_10(_10'apikey_XaYrurzcquqhEdBjzfTzfwAZqpd',_10'This is some different associated data.',_10'f9f176eb-7069-4743-9403-582c04354ffc'_10)_10returning *;
The type of encryption used by the Vault is called Authenticated Encryption with Associated Data.
The data you insert into the
associated column, which is up to you, is combined with the encrypted text when libsodium creates the authentication signature for the secret.
This means that when you read the secret, you know that the associated data is also authentic. The associated data could be an account ID or some
information that ties your system to the secret. And as always, you can refer to rows in the secrets table by their primary key UUID.
If you only want to store secrets that you know are encrypted on disk and in backups, then all you need to know is shown above. Just insert secrets into the table, optionally creating new keys, and select them from the view when you want to use them.
Going Beyond the Vault
The Vault is good for a reasonable amount of secure data, like API keys, access tokens, or environment variables. But if you have a lot more sensitive information, like personally Identifiable Information (PII), you may want to break them out into side-tables using pgsodium's Transparent Column Encryption which we will describe soon in a follow-up blog post. Stay tuned!