Cannot fetch api_key_id from vault using stripe fdw

[maxmckenzie]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.

  i'm not totally sure if I can give a definite yes to that above checkbox but i think so?

• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

When trying to set up the stripe fdw with the api key in the vault schema (only tested locally) The stripe fdw does not show any data and returns invalid secret id <key_ID>: failed to parse a UUID

I've followed the docs to set up the stripe FDW locally, I've created a migration with the cli supabase migration new stripe-fdw

Then written a basic migration

Example

create schema if not exists stripe;
create extension if not exists wrappers with schema extensions;
create foreign data wrapper stripe_wrapper
  handler stripe_fdw_handler
  validator stripe_fdw_validator;

insert into vault.secrets (name, secret)
values (
  'stripe',
  'sk_test_1234' -- replace with your sk test key this is just an example
)
returning key_id;

create server stripe_server
  foreign data wrapper stripe_wrapper
  options (
    api_key_id '<key_ID>'
  );

create foreign table stripe.events (
  id text,
  type text,
  api_version text,
  created timestamp,
  attrs jsonb
)
  server stripe_server
  options (
    object 'events'
  );

However i get this error everytime i try to view the data in the stripe schema. the key is indeed present in the vault table.

Error: ERROR:  HV000: invalid secret id "<key_ID>": failed to parse a UUID
DETAIL:  Wrappers
Try refreshing your browser, but if the issue persists, please reach out to us via support.

However if you hard code the key like below it works.

create server stripe_server
  foreign data wrapper stripe_wrapper
  options (
    api_key_id 'sk_test_1234'  -- replace with your sk test key this is just an example
  );

Attempted to fix in the following ways

• refresh the page (didnt work)
• use the variable like this api_key_id '<key_ID>' or api_key_id '<key_id>' or this api_key_id <key_id> or this api_key_id key_id or api_key_id 'key_id' or api_key_id <key_ID> api_key_id 'key_ID' nope (didnt work)
• return as on the vault statement returning key_id as key_id (didnt work)
• ask chatGPT/gemini (didnt work just got sent further down an SQL syntax error rabbit hole as both me and AI got more confused haha)

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. set up a new supabase project mkdir test && cd test && supabase init
2. start supabase supabase start
3. create a migration with the CLI supabase migration new stripe-fdw
4. paste the SQL example from above
5. reset the db to run the migration supabase db reset
6. go to the studio URL and check the stripe schema and the events table
7. it should error and not return results.

Expected behavior

I expect the stripe data to be there and for it to correctly retireve the api key form the vault

System information

• OS: macOS
• Browser n/a
• Version of supabase-js n/a
• Version of Node.js: n/a

[imor]

The key_id returned by this query:

insert into vault.secrets (name, secret)
values (
  'stripe',
  'sk_test_1234' -- replace with your sk test key this is just an example
)
returning key_id;

Should be used in place of <key_id> in the following code:

create server stripe_server
  foreign data wrapper stripe_wrapper
  options (
    api_key_id '<key_id>'
  );

So that the actual query looks something like:

create server stripe_server
  foreign data wrapper stripe_wrapper
  options (
    api_key_id '34661cd0-6e94-490a-8269-e3475446a9b5'
  );

Assuming the insert into vault.secrets... query returned 34661cd0-6e94-490a-8269-e3475446a9b5 key_id.

[maxmckenzie]

The key_id returned by this query:

insert into vault.secrets (name, secret)
values (
  'stripe',
  'sk_test_1234' -- replace with your sk test key this is just an example
)
returning key_id;

Should be used in place of <key_id> in the following code:

create server stripe_server
  foreign data wrapper stripe_wrapper
  options (
    api_key_id '<key_id>'
  );

So that the actual query looks something like:

create server stripe_server
  foreign data wrapper stripe_wrapper
  options (
    api_key_id '34661cd0-6e94-490a-8269-e3475446a9b5'
  );

Assuming the insert into vault.secrets... query returned 34661cd0-6e94-490a-8269-e3475446a9b5 key_id.

Thanks for the reply.

It's worth noting i'm running this as a migration that runs on supabase start, So i'm trying to retrieve the key_id and then have it passed to the create server command from just an SQL statement without me being involved (I'm not running any sql statements via the supabase dashboard).

I think i need to store the key_id as a variable in my SQL statement with DECLARE? This is what i thought was happening in the docs, but i misunderstood.

[imor]

Yeah, you'll need to store it in a variable and use that.