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 ;**"