supabase-postgres.git / summary / log / commit / refs
commit 7207d7af7dfe10f8d3271f4a8749d0fc45eea107
Author: Bobbie Soedirgo <bobbie@soedirgo.dev>
Commit: Bobbie Soedirgo <31685197+soedirgo@users.noreply.github.com>
Date: Wed Feb 05 05:48:55 2025 +0000
feat: vault sans pgsodium
.../pg_upgrade_scripts/complete.sh | 37 ++++++++++++++++++++
.../supabase_vault/after-create.sql | 3 ++
ansible/files/sodium_extension.sql | 6 ----
ansible/vars.yml | 4 +--
ebssurrogate/files/unit-tests/unit-test-01.sql | 10 ++----
.../20221207154255_create_pgsodium_and_vault.sql | 40 ----------------------
.../db/migrations/20221207154255_create_vault.sql | 17 +++++++++
.../20230529180330_alter_api_roles_for_inherit.sql | 2 --
migrations/tests/database/privs.sql | 4 ---
nix/ext/vault.nix | 13 +++----
nix/tests/postgresql.conf.in | 2 +-
nix/tools/run-server.sh.in | 2 ++
12 files changed, 71 insertions(+), 69 deletions(-)
diff --git a/ansible/files/admin_api_scripts/pg_upgrade_scripts/complete.sh b/ansible/files/admin_api_scripts/pg_upgrade_scripts/complete.sh
index 515c490f..c2367116 100755
--- a/ansible/files/admin_api_scripts/pg_upgrade_scripts/complete.sh
+++ b/ansible/files/admin_api_scripts/pg_upgrade_scripts/complete.sh
@@ -150,6 +150,43 @@ EOF
run_sql -c "$PATCH_PGMQ_QUERY"
run_sql -c "update pg_extension set extowner = 'postgres'::regrole where extname = 'pgmq';"
+
+ # Patch to handle upgrading to pgsodium-less Vault
+ REENCRYPT_VAULT_SECRETS_QUERY=$(cat <<EOF
+ DO \$\$
+ BEGIN
+ IF EXISTS (SELECT FROM pg_available_extension_versions WHERE name = 'supabase_vault' AND version = '0.3.0')
+ AND EXISTS (SELECT FROM pg_extension WHERE extname = 'supabase_vault')
+ THEN
+ IF (SELECT extversion FROM pg_extension WHERE extname = 'supabase_vault') != '0.2.8' THEN
+ GRANT USAGE ON SCHEMA vault TO postgres WITH GRANT OPTION;
+ GRANT SELECT, DELETE ON vault.secrets, vault.decrypted_secrets TO postgres WITH GRANT OPTION;
+ GRANT EXECUTE ON FUNCTION vault.create_secret, vault.update_secret, vault._crypto_aead_det_decrypt TO postgres WITH GRANT OPTION;
+ END IF;
+ -- Do an explicit IF EXISTS check to avoid referencing pgsodium objects if the project already migrated away from using pgsodium.
+ IF EXISTS (SELECT FROM vault.secrets WHERE key_id IS NOT NULL) THEN
+ UPDATE vault.secrets s
+ SET
+ secret = encode(
+ vault._crypto_aead_det_encrypt(
+ message := pgsodium.crypto_aead_det_decrypt(decode(s.secret, 'base64'), convert_to(s.id || s.description || s.created_at || s.updated_at, 'utf8'), s.key_id, s.nonce),
+ additional := convert_to(s.id::text, 'utf8'),
+ key_id := 0,
+ context := 'pgsodium'::bytea,
+ nonce := s.nonce
+ ),
+ 'base64'
+ ),
+ key_id = NULL
+ WHERE
+ key_id IS NOT NULL;
+ END IF;
+ END IF;
+ END
+ \$\$;
+EOF
+ )
+ run_sql -c "$REENCRYPT_VAULT_SECRETS_QUERY"
fi
run_sql -c "grant pg_read_all_data, pg_signal_backend to postgres"
diff --git a/ansible/files/postgresql_extension_custom_scripts/supabase_vault/after-create.sql b/ansible/files/postgresql_extension_custom_scripts/supabase_vault/after-create.sql
new file mode 100644
index 00000000..7ca37637
--- /dev/null
+++ b/ansible/files/postgresql_extension_custom_scripts/supabase_vault/after-create.sql
@@ -0,0 +1,3 @@
+grant usage on schema vault to postgres with grant option;
+grant select, delete on vault.secrets, vault.decrypted_secrets to postgres with grant option;
+grant execute on function vault.create_secret, vault.update_secret, vault._crypto_aead_det_decrypt to postgres with grant option;
diff --git a/ansible/files/sodium_extension.sql b/ansible/files/sodium_extension.sql
deleted file mode 100644
index a19cabf7..00000000
--- a/ansible/files/sodium_extension.sql
+++ /dev/null
@@ -1,6 +0,0 @@
-create schema if not exists pgsodium;
-create extension if not exists pgsodium with schema pgsodium cascade;
-
-grant pgsodium_keyiduser to postgres with admin option;
-grant pgsodium_keyholder to postgres with admin option;
-grant pgsodium_keymaker to postgres with admin option;
diff --git a/ansible/vars.yml b/ansible/vars.yml
index fe8ec2d3..206cb139 100644
--- a/ansible/vars.yml
+++ b/ansible/vars.yml
@@ -8,8 +8,8 @@ postgres_major:
# Full version strings for each major version
postgres_release:
- postgresorioledb-17: "17.0.1.34-orioledb"
- postgres15: "15.8.1.038"
+ postgresorioledb-17: "17.0.1.035-orioledb"
+ postgres15: "15.8.1.039"
# Non Postgres Extensions
pgbouncer_release: "1.19.0"
diff --git a/ebssurrogate/files/unit-tests/unit-test-01.sql b/ebssurrogate/files/unit-tests/unit-test-01.sql
index f3d47459..c466af12 100644
--- a/ebssurrogate/files/unit-tests/unit-test-01.sql
+++ b/ebssurrogate/files/unit-tests/unit-test-01.sql
@@ -17,7 +17,6 @@ BEGIN
extension_array := ARRAY[
'plpgsql',
'pg_stat_statements',
- 'pgsodium',
'pgtap',
'pg_graphql',
'pgcrypto',
@@ -30,7 +29,6 @@ BEGIN
extension_array := ARRAY[
'plpgsql',
'pg_stat_statements',
- 'pgsodium',
'pgtap',
'pg_graphql',
'pgcrypto',
@@ -44,7 +42,7 @@ BEGIN
PERFORM set_config('myapp.extensions', array_to_string(extension_array, ','), false);
END $$;
-SELECT plan(8);
+SELECT no_plan();
SELECT extensions_are(
string_to_array(current_setting('myapp.extensions'), ',')::text[]
@@ -56,9 +54,5 @@ SELECT has_schema('pg_catalog');
SELECT has_schema('information_schema');
SELECT has_schema('public');
-SELECT function_privs_are('pgsodium', 'crypto_aead_det_decrypt', array['bytea', 'bytea', 'uuid', 'bytea'], 'service_role', array['EXECUTE']);
-SELECT function_privs_are('pgsodium', 'crypto_aead_det_encrypt', array['bytea', 'bytea', 'uuid', 'bytea'], 'service_role', array['EXECUTE']);
-SELECT function_privs_are('pgsodium', 'crypto_aead_det_keygen', array[]::text[], 'service_role', array['EXECUTE']);
-
SELECT * FROM finish();
-ROLLBACK;
\ No newline at end of file
+ROLLBACK;
diff --git a/migrations/db/migrations/20221207154255_create_pgsodium_and_vault.sql b/migrations/db/migrations/20221207154255_create_pgsodium_and_vault.sql
deleted file mode 100644
index 3d3867ab..00000000
--- a/migrations/db/migrations/20221207154255_create_pgsodium_and_vault.sql
+++ /dev/null
@@ -1,40 +0,0 @@
--- migrate:up
-
-DO $$
-DECLARE
- pgsodium_exists boolean;
- vault_exists boolean;
-BEGIN
- pgsodium_exists = (
- select count(*) = 1
- from pg_available_extensions
- where name = 'pgsodium'
- and default_version in ('3.1.6', '3.1.7', '3.1.8', '3.1.9')
- );
-
- vault_exists = (
- select count(*) = 1
- from pg_available_extensions
- where name = 'supabase_vault'
- );
-
- IF pgsodium_exists
- THEN
- create extension if not exists pgsodium;
-
- grant pgsodium_keyiduser to postgres with admin option;
- grant pgsodium_keyholder to postgres with admin option;
- grant pgsodium_keymaker to postgres with admin option;
-
- grant execute on function pgsodium.crypto_aead_det_decrypt(bytea, bytea, uuid, bytea) to service_role;
- grant execute on function pgsodium.crypto_aead_det_encrypt(bytea, bytea, uuid, bytea) to service_role;
- grant execute on function pgsodium.crypto_aead_det_keygen to service_role;
-
- IF vault_exists
- THEN
- create extension if not exists supabase_vault;
- END IF;
- END IF;
-END $$;
-
--- migrate:down
diff --git a/migrations/db/migrations/20221207154255_create_vault.sql b/migrations/db/migrations/20221207154255_create_vault.sql
new file mode 100644
index 00000000..d4f71418
--- /dev/null
+++ b/migrations/db/migrations/20221207154255_create_vault.sql
@@ -0,0 +1,17 @@
+-- migrate:up
+
+DO $$
+BEGIN
+ IF EXISTS (select from pg_available_extensions where name = 'supabase_vault')
+ THEN
+ create extension if not exists supabase_vault;
+
+ -- for some reason extension custom scripts aren't run during AMI build, so
+ -- we manually run it here
+ grant usage on schema vault to postgres with grant option;
+ grant select, delete on vault.secrets, vault.decrypted_secrets to postgres with grant option;
+ grant execute on function vault.create_secret, vault.update_secret, vault._crypto_aead_det_decrypt to postgres with grant option;
+ END IF;
+END $$;
+
+-- migrate:down
diff --git a/migrations/db/migrations/20230529180330_alter_api_roles_for_inherit.sql b/migrations/db/migrations/20230529180330_alter_api_roles_for_inherit.sql
index 4df82e3f..013a074f 100644
--- a/migrations/db/migrations/20230529180330_alter_api_roles_for_inherit.sql
+++ b/migrations/db/migrations/20230529180330_alter_api_roles_for_inherit.sql
@@ -4,7 +4,5 @@ ALTER ROLE authenticated inherit;
ALTER ROLE anon inherit;
ALTER ROLE service_role inherit;
-GRANT pgsodium_keyholder to service_role;
-
-- migrate:down
diff --git a/migrations/tests/database/privs.sql b/migrations/tests/database/privs.sql
index d164e0cb..ea4f1318 100644
--- a/migrations/tests/database/privs.sql
+++ b/migrations/tests/database/privs.sql
@@ -2,10 +2,6 @@ SELECT database_privs_are(
'postgres', 'postgres', ARRAY['CONNECT', 'TEMPORARY', 'CREATE']
);
-SELECT function_privs_are('pgsodium', 'crypto_aead_det_decrypt', array['bytea', 'bytea', 'uuid', 'bytea'], 'service_role', array['EXECUTE']);
-SELECT function_privs_are('pgsodium', 'crypto_aead_det_encrypt', array['bytea', 'bytea', 'uuid', 'bytea'], 'service_role', array['EXECUTE']);
-SELECT function_privs_are('pgsodium', 'crypto_aead_det_keygen', array[]::text[], 'service_role', array['EXECUTE']);
-
-- Verify public schema privileges
SELECT schema_privs_are('public', 'postgres', array['CREATE', 'USAGE']);
SELECT schema_privs_are('public', 'anon', array['USAGE']);
diff --git a/nix/ext/vault.nix b/nix/ext/vault.nix
index c822fcd5..2cbd7e7a 100644
--- a/nix/ext/vault.nix
+++ b/nix/ext/vault.nix
@@ -1,23 +1,24 @@
-{ lib, stdenv, fetchFromGitHub, postgresql }:
+{ lib, stdenv, fetchFromGitHub, libsodium, postgresql }:
stdenv.mkDerivation rec {
pname = "vault";
- version = "0.2.9";
+ version = "0.3.1";
- buildInputs = [ postgresql ];
+ buildInputs = [ libsodium postgresql ];
src = fetchFromGitHub {
owner = "supabase";
repo = pname;
rev = "refs/tags/v${version}";
- hash = "sha256-kXTngBW4K6FkZM8HvJG2Jha6OQqbejhnk7tchxy031I=";
+ hash = "sha256-MC87bqgtynnDhmNZAu96jvfCpsGDCPB0g5TZfRQHd30=";
};
installPhase = ''
mkdir -p $out/{lib,share/postgresql/extension}
- cp sql/*.sql $out/share/postgresql/extension
- cp *.control $out/share/postgresql/extension
+ install -D *${postgresql.dlSuffix} $out/lib
+ install -D -t $out/share/postgresql/extension sql/*.sql
+ install -D -t $out/share/postgresql/extension *.control
'';
meta = with lib; {
diff --git a/nix/tests/postgresql.conf.in b/nix/tests/postgresql.conf.in
index ef860afc..3ff7aea4 100644
--- a/nix/tests/postgresql.conf.in
+++ b/nix/tests/postgresql.conf.in
@@ -718,7 +718,7 @@ default_text_search_config = 'pg_catalog.english'
#local_preload_libraries = ''
#session_preload_libraries = ''
-shared_preload_libraries = 'pg_stat_statements, pgaudit, plpgsql, plpgsql_check, pg_cron, pg_net, pgsodium, timescaledb, auto_explain, pg_tle, plan_filter, pg_backtrace' # (change requires restart)
+shared_preload_libraries = 'pg_stat_statements, pgaudit, plpgsql, plpgsql_check, pg_cron, pg_net, pgsodium, timescaledb, auto_explain, pg_tle, plan_filter, pg_backtrace, supabase_vault' # (change requires restart)
jit_provider = 'llvmjit' # JIT library to use
diff --git a/nix/tools/run-server.sh.in b/nix/tools/run-server.sh.in
index 75c5f8de..172f62db 100644
--- a/nix/tools/run-server.sh.in
+++ b/nix/tools/run-server.sh.in
@@ -209,6 +209,8 @@ sed -e "1i\\
include = '$DATDIR/supautils.conf'" \
-e "\$a\\
pgsodium.getkey_script = '$PGSODIUM_GETKEY_SCRIPT'" \
+-e "\$a\\
+vault.getkey_script = '$PGSODIUM_GETKEY_SCRIPT'" \
-e "s|data_directory = '/var/lib/postgresql/data'|data_directory = '$DATDIR'|" \
-e "s|hba_file = '/etc/postgresql/pg_hba.conf'|hba_file = '$DATDIR/pg_hba.conf'|" \
-e "s|ident_file = '/etc/postgresql/pg_ident.conf'|ident_file = '$DATDIR/pg_ident.conf'|" \
[parent: 45e80ac037cc]