Local CLI : can't insert a secret in order to obtain key_id to be used with fdw

[mtimour]

Describe the bug this example SQL , from Supabase docs https://supabase.com/docs/guides/database/extensions/wrappers/mssql

fails when executed in fresh environment instantiated using local CLI:

insert into vault.secrets (name, secret) values ( 'mssql', 'Server=localhost,1433;User=sa;Password=my_password;Database=master;IntegratedSecurity=false;TrustServerCertificate=true;encrypt=DANGER_PLAINTEXT;ApplicationName=wrappers' ) returning key_id;

Error: [42501] ERROR: permission denied for function _crypto_aead_det_noncegen

To Reproduce Steps to reproduce the behavior:

1. supabase init
2. supabase start
3. Execute SQL statement
4. See error

Expected behavior It used to work , but unfortunately I don't know which version broke it.

Screenshots N/A

System information

• Ticket ID:
• Version of OS:MaxOS 15.3.2
• Version of CLI: 2.20.5
• Version of Docker: 4.39.0 (184744)
• Versions of services: N/A

Additional context

[mtimour]

I think this error is related to the recent PostgreSQL upgrade and the removal of the "pgsodium" extension.

I settled on the following workaround based on the original doc's suggestion. Only updated to use vault's decrypted secret instead of key_id

./supabase/config.toml

[db.vault]
server_connection = "env(server_connection)"

[db.seed]
enabled = true
sql_paths = ["./seed/*.sql"]

./supabase/.env server_connection="Server=localhost,1433;User=sa;Password=my_password;Database=master;IntegratedSecurity=false;TrustServerCertificate=true;encrypt=DANGER_PLAINTEXT;ApplicationName=wrappers"

./supabase/seed/1-seed.sql:

create foreign data wrapper fserver_wrapper
  handler mssql_fdw_handler
  validator mssql_fdw_validator;

create server fserver
    foreign data wrapper fserver_wrapper;


create or replace function public.set_fserver_connection() returns void as $$
  declare
    fserver_connection text;
  begin

    select decrypted_secret into fserver_connection
    from vault.decrypted_secrets
    where name = 'server_connection';

    execute 'alter server fserver options (conn_string ' || quote_literal(server_connection) || ')';

  end;
$$ language plpgsql;

./supabase/seed/2-seed.sql:

select public.set_fserver_connection();

-- connect remote tables

[sweatybridge]

Yup using config.toml is preferred here since you probably don't want MySQL password to be stored in a migration file.

I will update docs accordingly.

[pascalwhoop]

I must admit it's unclear to me how to inject secrets into the vault in a self hosted setup via docker compose and environment variables. Which is partially due to the unclear existence of config.toml which appears to be CLI only, while the ENV variables for the compose setup aren't really documented.

Can I just do

VAULT_SOME_VALUE=secret

and that appears in vault? can it be that easy?

We want to bootstrap these secrets and the common way I'm used to is via env variables, e.g. injected from k8s secrets or via GCP secrets manager or whatever infra layer you're based on. Anything that can be automated and does not require logging into the UI and "running the SQL" from the dashbaord

[sweatybridge]

You can actually use the CLI to migrate your self-hosted database via --db-url flag so the same config.toml should work. For eg.

supabase db push --db-url 'postgres://...'

Under the hood, CLI essentially runs the sql functions exposed by vault to create or update secrets declared in [db.vault] block. So you can also replicate the same behaviour using psql or other migration tools.

[ScottAtRedHawk]

I had this issue since I upgraded my remote DB from 15.8.1.093 -> 15.8.1.094 and my local DB from 15.8.1.093 -> 15.8.1.094 -> 15.8.1.095 (because of #3632).

What fixed the Error: [42501] ERROR: permission denied for function _crypto_aead_det_noncegen for me was unfortunately changing some old migration files (I know, terrible). One of the original migrations was similar to this

do $$
begin
  -- Check if environment_name already exists
  if exists (select 1 from vault.secrets where name = 'environment_name') then
    return;
  end if;

  -- Add the environment_name to the vault if it doesn't exist
  insert into vault.secrets (name, secret, description)
  values ('environment_name', 'local', 'Environment name');
end $$;

The new migration file looks like this (with the original code included for future reference)

do $$
begin
  -- Check if environment_name already exists
  if exists (select 1 from vault.secrets where name = 'environment_name') then
    return;
  end if;

  -- BELOW WAS ORIGINAL MIGRATION CODE
  -- Add the environment_name to the vault if it doesn't exist
  -- insert into vault.secrets (name, secret, description)
  -- values ('environment_name', 'local', 'Environment name');
  -- ABOVE WAS ORIGINAL MIGRATION CODE

  -- BELOW IS NEW MIGRATION CODE
  perform vault.create_secret('environment_name', 'local', 'Environment name');
  -- ABOVE IS NEW MIGRATION CODE
end $$

Then I just had to repair the migration using the Supabase CLI. Apparently, something changed with doing a direct insert into the vault.secrets table vs calling vault.create_secret(). This might need to be a new GitHub issue?

Hope this helps