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)

[Radiergummi]

Would it be possible to add at least a little guidance on using the extension on vanilla Postgres to the project readme? This issue helped me to create a working Dockerfile—appended here, in case it case it helps someone else:

Dockerfile

# syntax=docker/dockerfile:1
FROM postgres:17 AS base
FROM base AS builder
ARG VAULT_VERSION=0.3.1
ENV VAULT_VERSION=${VAULT_VERSION}

WORKDIR /build

RUN --mount=type=cache,target=/var/cache/apt,sharing=locked \
    --mount=type=cache,target=/var/lib/apt,sharing=locked \
  <<EOF
  set -eux
  apt-get update
  apt-get install --yes --no-install-recommends \
    "postgresql-server-dev-${PG_MAJOR}" \
    ca-certificates \
    build-essential \
    libsodium-dev \
    wget \
  ;
  wget --quiet --output-document=- \
    "https://github.com/supabase/vault/archive/refs/tags/v${VAULT_VERSION}.tar.gz" \
      | tar \
        --strip-components=1 \
        --directory=./ \
        --extract \
        --file=- \
        --gzip
  make --jobs=$(nproc)
EOF

FROM base
ENV POSTGRES_INITDB_ARGS="--set=shared_preload_libraries='supabase_vault, pg_cron' --set=include_dir='/etc/postgresql/'"

# Install runtime dependencies
RUN --mount=type=cache,target=/var/cache/apt,sharing=locked \
    --mount=type=cache,target=/var/lib/apt,sharing=locked \
  <<EOF
  set -eux
  apt-get update
  apt-get install --yes --no-install-recommends \
    "postgresql-${PG_MAJOR}-pgvector" \
    "postgresql-${PG_MAJOR}-cron" \
    libsodium23 \
  ;
EOF

COPY --link ./init.sql /docker-entrypoint-initdb.d/00_init.sql

# region Cron Extension
COPY --link ./pg_cron/pg_cron.conf /etc/postgresql/pg_cron.conf
# endregion

# region Supabase Vault Extension
COPY --link --from=builder /build/sql /usr/share/postgresql/17/extension/
COPY --link --from=builder /build/*.so /usr/lib/postgresql/17/lib/
COPY --link --from=builder /build/*.control* /usr/share/postgresql/17/extension/
COPY --link ./supabase_vault/supabase_vault.conf /etc/postgresql/supabase_vault.conf
COPY --link --chown=postgres:postgres ./supabase_vault/pgsodium_get_key.sh /opt/pgsodium_get_key.sh
# endregion

STOPSIGNAL SIGINT
VOLUME ["/var/lib/postgresql/data"]
EXPOSE 5432
CMD ["postgres"]

The contents of init.sql:

create schema if not exists extensions;
create extension if not exists "vector" schema extensions;

create extension "pg_cron" schema pg_catalog;
grant usage on schema "cron" to postgres;

create extension "supabase_vault" cascade;

This enables all extensions installed in the Dockerfile.

supabase_vault/pgsodium_get_key.sh:

#!/usr/bin/env sh

if [ -n "${PGSODIUM_KEY}" ]; then
  echo "${PGSODIUM_KEY}"
elif [ -n "${PGSODIUM_KEY_FILE}" ] && [ -f "${PGSODIUM_KEY_FILE}" ]; then
  cat "${PGSODIUM_KEY_FILE}"
elif [ -f /run/secrets/pgsodium_key ]; then
  cat /run/secrets/pgsodium_key
else
  echo "PGSODIUM_KEY not set or secret not found"
  exit 1
fi

This script automatically reads the pgsodium root key from a secret mount or an environment variable.

supabase_vault/supabase_vault.conf:

vault.getkey_script = '/opt/pgsodium_get_key.sh'

Configures the getkey script above.

Edit: I actually published this as a public image on GitHub: colibri-hq/postgres, should anyone have a need for that or require inspiration for your own project.