Unable to Set Up Vault extension on normal Postgres 15 but works fine on Supabase/Postgres 15.1.0.103 (Unmodified Postgres with some useful plugins)

[chikkujimmy]

Bug

Tried to build and run the Dockerfile from this repository which uses postgres:15 image, even though the docker file builds and deploys successfully with the extensions pgsoduim and supabase_vault, but when we try to insert into vault.secrets it gives the below error.

As per the above error we tried to set up pgsodium referring to its GitHub instructions by using the get key urandom script but did not help in resolving this error. After going through the documentation for vault we noticed that here it's using supabase/postgres and we modified the Dockerfile to use the supabase/postgres:15.1.0.103 docker image which comes with both vault and pgsodium installed and is working fine here. We assume some more dependencies needs to be installed while setting up on normal postgres to make it equivalent to supabase/postgres.

To Reproduce

Using Docker

1. Build and run using the Dockerfile
2. Login to the deployed container
3. Execute the query "INSERT INTO vault.secrets (secret) VALUES ('s3kre3t_k3y') RETURNING *;"

Using local deployment

1. Setup and install Postgres 15 locally
2. Install lib sodium "curl -s -L https://download.libsodium.org/libsodium/releases/libsodium-1.0.18.tar.gz | tar zxvf - && cd libsodium-1.0.18 && ./configure && make check && make -j 4 install"
3. Install pgsodium "git clone https://github.com/michelp/pgsodium.git && cd pgsodium && git checkout tags/v3.1.3 && make install"
4. Clone and install Vault (make && make install)
5. Login to postgres and run create extension for pgsoduim and supabase_vault
6. Execute the query "INSERT INTO vault.secrets (secret) VALUES ('s3kre3t_k3y') RETURNING *;"

[mirzap]

I just spent two days working on this issue, but I've figured it out. Starting from version 0.3.0, Supabase has removed the pgsodium dependency, as noted in this PR. Here’s what you need to do:

• Drop the pgsodium extension if you’re not using it directly.
• You can completely remove it by running the command: rm /usr/share/postgresql/17/extension/pgsodium*.
• Update your postgresql.conf configuration file.

Replace this:

shared_preload_libraries = 'pgsodium'
pgsodium.getkey_script = '/usr/lib/postgresql/17/bin/pgsodium_getkey.sh'

with this:

shared_preload_libraries = 'supabase_vault'
vault.getkey_script = '/usr/lib/postgresql/17/bin/pgsodium_getkey.sh'

This is an example of pgsodium_getkey.sh:

#!/bin/bash
KEY_FILE=/var/lib/postgresql/17/main/pgsodium_root.key

if [ ! -f "$KEY_FILE" ]; then
    head -c 32 /dev/urandom | od -A n -t x1 | tr -d ' \n' > $KEY_FILE
fi
cat $KEY_FILE

restart postgres:

systemctl restart postgresql

Recreate supabase_vault extension:

DROP EXTENSION supabase_vault CASCADE;
CREATE EXTENSION supabase_vault CASCADE;

Make sure that you have supabase_vault version >0.3.0:

SELECT extversion FROM pg_extension WHERE extname = 'supabase_vault';
 extversion 
------------
 0.3.1

And then try to create a secret:

postgres=# SELECT vault.create_secret('my_super_secret_value', 'my_secret_name', 'Test secret storage');
            create_secret             
--------------------------------------
 6bae8f7c-bbd0-4619-b2cb-b954103c9570
(1 row)