Implement user API keys with Supabase

implement-user-api-keys-with-supabase.md

Implement user API keys with Supabase

Rationale

JWTs are at the heart of Supabase authorization, but sometimes we wanna build an app that also gives users access via API keys; or perhaps only exclusively via API keys. As you may know, using JWTs as API keys makes them difficult to revoke and therefore a security issue.

We also want to ensure this doesn't significantly add to RLS polices, if at all.

Finally, we'd love to have this handled by Supabase and do as little as possible in our framework. This simplifies our code and reduces third-party libraries.

Solution

The following set of functions and triggers are used to securly manage JWTs and provide API keys to users. Cleanup is also handled, in the event a user is deleted.

In general, it creates JWTs which effectively never expire, and then hashs them to create an API key. When we receive an API key in a user request we pass it into a Supabase client, make our database request, and return the result. If an API key is revoked, the JWT is deleted - rendering both unusable.

Of course, you can tweak this solution as desired. Feedback welcome.

Security

Here is what we do to ensure the security of JWTs, API keys, and secrets:

1. JWTs, and the secrets used to hash them, are stored in your encrypted Vault.
2. API keys are not stored in Supabase. Rather, we store a hash of the API key.
3. The protected auth schema is used to store info in the database.
4. Only references to JWTs are stored in the database.
5. There are checks to ensure users can only manipulate their API keys; as you'd expect.

Roadmap

✅ Recommend a custom JWT secret, to sign JWTs.

⬜ When storing a user's api key secret, upon user creation, prefix the Vault secret's name to avoid future naming clashes. Right now, we just use the user's id, which a dev may want to use as a Vault secret name for another purpose. e.g api_key_secret_00000000-0000-0000-0000-000000000000

Heads-Up

• Most of the functions require one or more parameters. When calling these functions from your framework, you must use the same parameter naming as defined in the functions.
• Users need a way to create, copy, and revoke API keys on the frontend. Implementing these are outside the scope of this gist.

JWT structure

This is an example decoded JWT.

Header:

{
  "alg": "HS256",
  "typ": "JWT"
}

Payload:

{
  "aud": "authenticated",
  "exp": 4844944207,
  "iat": 1689184207,
  "iss": "supabase",
  "jti": "88888888-8888-8888-8888-888888888888",
  "sub": "00000000-0000-0000-0000-000000000000",
  "role": "authenticated"
}

• exp is 100 years by default. Apparently we don't want these JWTs to expire! When an API key is revoked, it's corresponding JWT is deleted from Vault - therefore neither are useable.
• jti is the JWT ID. It's used to ensure there are no duplicate JWTs created. Without this field, if a user was somehow able to send two api key creation requests, that arrived at your database and processed within the same second, the JWTs and their hash's would be identical. Adding the jti field, with a UUID, solves this issue.
• sub is the id of the user this JWT was created for. This field is relevant when using the alternative API key method, so your RLS policy will work.

Alternatives

You can tweak these functions to store the API keys in Vault, instead of the JWTs. The reason this solution stores the JWTs is so you have the option of receiving an API key within your API server and then exchanging it for a JWT and then passing that JWT to Supabase. This requires an extra network request, but allows you to utilize the standard RLS policy of (auth.uid() = user_id).

Create a table for JWT references

This stores user ids and references to JWT secrets. We create this table in the auth schema for extra security. If the user is deleted from auth.users, all of their references in this table will be deleted. Likewise, if a user revokes an api key, the associated row in this table will be deleted. Paste the below section of code into your SQL Editor on your Supabase dashboard.

create table
  auth.jwts (
    secret_id uuid not null,
    user_id uuid null,
    constraint jwts_pkey primary key (secret_id),
    constraint jwts_secret_id_fkey foreign key (secret_id) references vault.secrets (id) on delete cascade
  ) tablespace pg_default;

Create the project api key secret and store it in Vault

This secret is used to hash a user's api key, for purposes explained further below. Paste the below code into your SQL Editor on your Supabase dashboard.

insert into vault.secrets (secret, name) values (
  encode(digest(gen_random_bytes(32), 'sha512'), 'hex'), 
  'project_api_key_secret'
);

Create or add a JWT secret in Vault

This is used to sign JWTs. There are two choices here: either create your own or use your Supabase project's JWT secret.

Create your own JWT secret

This is the recommended method. The advantage is that it decouples your API key JWTs from regular Supabase auth JWTs. So, if you ever had to rotate your project's JWT secret, it would not affect your API key system. The downside is that you can't use the typical (auth.uid() = user_id) RLS policy or the exchange jwt method.

Paste the below code into your SQL Editor on your Supabase dashboard.

insert into vault.secrets (secret, name) values (
  encode(digest(gen_random_bytes(32), 'sha256'), 'hex'), 
  'project_jwt_secret'
);

Or use your Supabase project's JWT secret

This is no longer recommended, and I'm tempted to remove it from this gist.

The only reason to do this is if you want to use the typical (auth.uid() = user_id) RLS policy and the exchange jwt method - which also requires an extra network request to Supabase. The risk is that if you ever had to rotate your project's JWT secret, and you're using that RLS policy and exchange jwt method, existing api keys would stop working and you'd have to update Vault with the new JWT secret.

Copy the secret from your Supabase dashboard at Project Settings > API > JWT Settings, then paste the below code into your SQL Editor on your Supabase dashboard, inserting the secret where designated.

insert into vault.secrets (secret, name) values (
  'your-project-jwt-secret-here', 
  'project_jwt_secret'
);

Create a function and trigger for handling new users

When a new user is created, we'll trigger a function which creates an api key secret for the user. The api key secret is stored in Vault, and later used to hash the user's JWTs in order to create api keys.

Paste the below sections of code into your SQL Editor on your Supabase dashboard.

1. This creates and stores the user's api key secret in Vault, with a unique name reference of the user's id.

   create or replace function create_user_api_key_secret()
   returns trigger
   language plpgsql
   security definer
   set search_path = extensions
   as $$
   declare
     rand_bytes bytea := gen_random_bytes(32);
     user_api_key_secret text := encode(digest(rand_bytes, 'sha512'), 'hex');
   begin
     insert into vault.secrets (secret, name) values (user_api_key_secret, new.id);
     return new;
   end;
   $$;

2. This trigger fires when a new user is created. It runs the function we created above. Feel free to change the trigger's name, but the function name must match the value from step 1. (Yes, that's a double underscore in the trigger name)

   create trigger on_user_created__create_user_api_key_secret after insert 
     on auth.users for each row execute function create_user_api_key_secret();

Create a function and trigger for handling deleted users

There are caveats to handling user deletion - especially if the user is an owner of any objects in Supabase Storage. But all things being equal, the below will ensure that any Vault secrets with a name equal to the user's id will be removed. It also checks the auth.jwts table and removes any Vault secrets referenced. At that point, deleting these "api key" secrets in Vault executes a cascade delete on the entries in auth.jwts - finishing the cleanup.

1. This deletes the user's api key secret and all JWTs representing their API keys.

   create or replace function remove_user_vault_secrets()
   returns trigger
   language plpgsql
   security definer
   set search_path = public
   as $$
   declare
     jwt_record record;
   begin
     delete from vault.secrets where name=old.id::text;
     for jwt_record IN 
       select secret_id
       from auth.jwts 
       where user_id=old.id
     loop
       delete from vault.secrets where id=jwt_record.secret_id;
     end loop;
     return old;
   end;
   $$;

2. This trigger fires when a user is delete.

   create trigger on_user_deleted__remove_user_vault_secrets after delete 
     on auth.users for each row execute function remove_user_vault_secrets();

Functions to handle API keys.

Paste the below sections of plpgsql code into your SQL Editor on your Supabase dashboard.

Create an API key

When a user requests an api key to be created, we actually create a JWT for them. However, the name of this function is create_api_key to be more consistent with the other function names.

• The JWT is signed with the previously provided JWT secret.
• Then we hash the JWT with the user's api key secret to create the actual api key.
• Immediately after the user's api key is created, we hash it with the project's api key secret - dubbed a "project hash". This is done to avoid storing the user's api key and still gives us a unique name for referencing their JWT later.
• Then the JWT is stored in Vault, with the project hash as the secret's unique name.
• Finally, saving the secret in Vault returns an id for the secret; which we save within the database's auth.jwts table as a secret_id, along with the user's user_id.

create or replace function create_api_key(id_of_user text, key_description text)
returns void
language plpgsql
security definer
set search_path = extensions
as $$
declare
  api_key text;
  expires bigint;
  jti uuid := gen_random_uuid();
  jwt text;
  jwt_body jsonb;
  project_hash text;
  project_jwt_secret text;
  project_api_key_secret text;
  secret_uuid uuid;
  time_stamp bigint;
  user_api_key_secret text;
begin
  if auth.uid() = id_of_user::uuid then
    select into time_stamp trunc(extract(epoch from now()), 0);
    select into expires time_stamp + trunc(extract(epoch from interval '100 years'), 0);
    jwt_body := jsonb_build_object(
      'role', 'authenticated', 
      'aud', 'authenticated', 
      'iss', 'supabase', 
      'sub', to_jsonb(id_of_user), 
      'iat', to_jsonb(time_stamp), 
      'exp', to_jsonb(expires), 
      'jti', to_jsonb(jti));
    select decrypted_secret into user_api_key_secret from vault.decrypted_secrets where name=id_of_user;
    select decrypted_secret into project_api_key_secret from vault.decrypted_secrets where name='project_api_key_secret';
    select decrypted_secret into project_jwt_secret from vault.decrypted_secrets where name='project_jwt_secret';
    select into jwt sign(jwt_body::json, project_jwt_secret);
    api_key := encode(hmac(jwt, user_api_key_secret, 'sha512'), 'hex');
    project_hash := encode(hmac(api_key, project_api_key_secret, 'sha512'), 'hex');
    insert into vault.secrets (secret, name, description) 
      values (jwt, project_hash, key_description) returning id into secret_uuid;
    insert into auth.jwts (secret_id, user_id) values (secret_uuid, id_of_user::uuid);
  end if;
end;
$$;

Load API keys

There are two options presented here. One is highly secure, and the other is only slightly less secure.

Secure

This method requires you to create an additional function - get_api_key() - so if a user wants to copy their API key, then you can retrieve and return it, then likely copy it to their clipboard with frontend code. The get_api_key function is provided further below.

Returns only references to and descriptions of api keys for the logged-in user. This means there won't be API keys in client-side code.

• All of the secret_ids are grabbed from the auth.jwts table where the user_id matches the passed-in id_of_user.
• Then we loop through every secret_id and grab the corresponding JWT's description from Vault.
• This info is then constructed into a JSON object and returned.

Returns an array of objects:

{
  "description": string;
  "id": string;
}

create or replace function load_api_keys(id_of_user text)
returns text[]
language plpgsql
security definer
set search_path = extensions
as $$
declare
  current_set jsonb;
  jwt_record record;
  key_info jsonb[];
  user_api_key_secret text;
  vault_record record;
begin
  if auth.uid() = id_of_user::uuid then
    for jwt_record IN 
      select secret_id
      from auth.jwts 
      where user_id=id_of_user::uuid
    loop
      select description into vault_record from vault.decrypted_secrets where id=jwt_record.secret_id;
      current_set := jsonb_build_object(
        'description', to_jsonb(vault_record.description), 
        'id', to_jsonb(jwt_record.secret_id)
      );
      select into key_info array_append(key_info, current_set);
    end loop;
  end if;

  return key_info;
end;
$$;

Returns a string:

create or replace function get_api_key(id_of_user text, secret_id text)
returns text
language plpgsql
security definer
set search_path = extensions
as $$
declare
  jwt text;
  key text;
  user_api_key_secret text;
begin
  if auth.uid() = id_of_user::uuid then
    select decrypted_secret into user_api_key_secret from vault.decrypted_secrets where name=id_of_user;
    select decrypted_secret into jwt from vault.decrypted_secrets where id=secret_id::uuid;
    key := encode(hmac(jwt, user_api_key_secret, 'sha512'), 'hex');
  end if;

  return key;
end;
$$;

Alternative to Secure method

Returns api keys for the logged-in user, along with the standard info. This means there will be API keys somewhere in client-side code.

• All of the secret_ids are grabbed from the auth.jwts table where the user_id matches the passed-in id_of_user.
• Then we loop through every secret_id and grab each JWT decrypted_secret and it's description from Vault.
• This info is then constructed into a JSON object - hashing the JWT in the process - and returned.

Returns an array of objects:

{
  "description": string;
  "id": string;
  "key": string;
}

create or replace function load_api_keys(id_of_user text)
returns text[]
language plpgsql
security definer
set search_path = extensions
as $$
declare
  current_set jsonb;
  jwt_record record;
  keys jsonb[];
  user_api_key_secret text;
  vault_record record;
begin
  if auth.uid() = id_of_user::uuid then
    select decrypted_secret into user_api_key_secret from vault.decrypted_secrets where name=id_of_user;

    for jwt_record IN 
      select secret_id
      from auth.jwts 
      where user_id=id_of_user::uuid
    loop
      select decrypted_secret, description into vault_record from vault.decrypted_secrets where id=jwt_record.secret_id;
      current_set := jsonb_build_object(
        'description', to_jsonb(vault_record.description), 
        'id', to_jsonb(jwt_record.secret_id),
        'key', to_jsonb(encode(hmac(vault_record.decrypted_secret, user_api_key_secret, 'sha512'), 'hex'))
      );
      select into keys array_append(keys, current_set);
    end loop;
  end if;

  return keys;
end;
$$;

Revoke an API key

Deletes a JWT in Vault, and it's associated reference in auth.jwts via cascade delete. The passed-in secret_id should be the id returned from the load_api_keys function.

create or replace function revoke_api_key(id_of_user text, secret_id text)
returns void
language plpgsql
security definer
set search_path = public
as $$
begin
  if auth.uid() = id_of_user::uuid then
    delete from vault.secrets where id=secret_id::uuid;
  end if;
end;
$$;

Handle an API key

When an API request is made, simply pass it in the global header Authorization of the Supabase client. Do not include Bearer in the value.

Once you've created the function, include the following in any RLS policies that allow API key auth:

(select (auth.key_uid() = user_id))

The above might typically be used with other checks, e.g. for app-based Supabase users:

((select (auth.uid() = user_id)) OR (select (auth.key_uid() = user_id)))

see RLS Performance and Best Practices for an explanation of using select.

We name this function differently, and create it in the auth schema, so that it's hopefully as familiar as auth.uid().

• The api key is retrieved from the header.
• We hash the api key, with the project api key secret, and then search all JWT secret's name value for a match of the resulting project hash.
• If found, the user's id is returned; otherwise, it returns null. The returned value is compared against the right-hand side of your RLS policy check - e.g. auth.key_uid() = user_id

Returns uuid | null:

create or replace function auth.key_uid()
returns uuid
language plpgsql
security definer
set search_path = extensions
as $$
declare
  project_hash text;
  project_api_key_secret text;
  secret_uuid uuid;
  user_api_key text;
begin
  select current_setting('request.headers', true)::json->>'authorization' into user_api_key;
  select decrypted_secret into project_api_key_secret from vault.decrypted_secrets where name='project_api_key_secret';
  project_hash := encode(hmac(user_api_key, project_api_key_secret, 'sha512'), 'hex');
  select id into secret_uuid from vault.secrets where name=project_hash;

  if secret_uuid is not null then 
    return (select user_id from auth.jwts where secret_id=secret_uuid);
  else 
    return null;
  end if;
end;
$$;

Alternative to handling native API keys

This goes hand-in-hand with using your Supbase project's JWT secret. It is not applicable if you created your own JWT secret.

If you'd rather keep your RLS polices as simple as possible - or you have some other reason or requirement - then you can also exchange an API key for it's JWT. Then pass the JWT to Supabase. This requires an extra network request, but is compatible with a typical RLS policy of (auth.uid() = user_id). Note that you'll also need to use the service_role key when calling this function from your API server.

Returns a string:

create or replace function exchange_api_key_for_jwt(user_api_key text)
returns text
language plpgsql
as $$
declare
  jwt text;
  project_hash text;
  project_api_key_secret text;
begin
  select decrypted_secret into project_api_key_secret from vault.decrypted_secrets where name='project_api_key_secret';
  project_hash := encode(hmac(user_api_key, project_api_key_secret, 'sha512'), 'hex');
  select decrypted_secret into jwt from vault.decrypted_secrets where name=project_hash;
  
  return jwt;
end;
$$;

Implementation examples

All examples use javascript.

1. Create an API key

   const { error } = await supabase.rpc(
     'create_api_key', 
     { id_of_user, key_description }
   )

2. Load API keys

   const { data, error } = await supabase.rpc(
     'load_api_keys', 
     { id_of_user }
   )

3. Get an API key

   Typically used in conjunction with the "secure" api key load method. e.g. copying a key to the clipboard for the user.

   const { data, error } = await supabase.rpc(
     'get_api_key', 
     { id_of_user, secret_id }
   )

4. Revoke an API key

   const { error } = await supabase.rpc(
     'revoke_api_key', 
     { id_of_user, secret_id }
   )

5. Handle an API key

   Do this when you've received an API request.

   Native API key Method:

   This example assumes that an API user includes Bearer in their authorization request header. But remember to not include Bearer in the Supabase client's global authorization header.

   /* Somewhere in your API server */
   const user_api_key = request.headers.get('Authorization')?.split(' ')[1] || ''
   
   if (user_api_key) {
     const supabase = createClient(env.SUPABASE_URL, env.SUPABASE_ANON_KEY, {
       global: {
         headers: {
           Authorization: user_api_key
         }
       },
       auth: {
         persistSession: false,
         detectSessionInUrl: false,
         autoRefreshToken: false
       }
     })
   
     const { data, error } = await supabase.from('table').select()
     /* If error, return error */
   
     /* Otherwise, return data to API user */
   }

   Exchange JWT Method:

   This is essentially the same as the native method, but it first retrieves the JWT from Supabase. It also includes the Bearer string in the authorization header.

   /* Somewhere in your API server */
   const user_api_key = request.headers.get('Authorization')?.split(' ')[1] || ''
   
   if (user_api_key) {
     const supabaseServiceClient = createClient(
       env.SUPABASE_URL, 
       env.SERVICE_ROLE_KEY, {
       auth: {
         persistSession: false,
         detectSessionInUrl: false,
         autoRefreshToken: false
       }
     })
     
     const { data: jwt, error } = await supabaseServiceClient.rpc(
       'exchange_api_key_for_jwt', 
       { user_api_key }
     )
     /* If error, handle error */
   
     const supabase = createClient(
       env.SUPABASE_URL, 
       env.SUPABASE_ANON_KEY, {
       global: {
         headers: {
           Authorization: `Bearer ${jwt}`
         }
       },
       auth: {
         persistSession: false,
         detectSessionInUrl: false,
         autoRefreshToken: false
       }
     })
   
     const { data, error } = await supabase.from('table').select()
     /* If error, return error */
   
     /* Otherwise, return data to API user */
   } 

Great approach and write up! I hope Supabase supports this natively at some point.

Since create table auth.jwts is placed in the auth schema, it may make sense for organisational clarity to add the rest of the DB scripts to the auth schema as well.

Also, it may be worth consider using Supabase migration scripts as an alternative to "Pasting the DDL code into the SQL Editor."

Thanks for the feedback @pszjmb1

I hadn't thought of putting all the functions in auth. What are the advantages to that? I assume you'd need to call them with rpc differently as well?

I also did not think of migrations because I'm not familiar with how that works. I'll take a look.

re: What are the advantages of putting the db functions in the auth schema, there isn't a functional difference, but it just makes it easier when related items are grouped together. Also, by prefixing DB functions with auth, it's a helpful reminder to those maintaining the code (probably yourself months or yeas from now in the middle of the night) that these are security related so to take extra care.

Migrations are really nice, and the Supabase CLI has good support for them (see https://supabase.com/docs/reference/cli/supabase-migration and https://supabase.com/docs/reference/cli/supabase-db-push).

Excellent write-up. How about generating the API key when a user is created in Supabase?

@michael-sumner my apologies for the late reply. You can absolutely do that! This gist assumes a user can have multiple API keys. If a user can only ever have one api key, then you'd just work in some of this code to whatever function you're calling when a user is created.

For instance, from the above code you'd just create and store the API key somewhere in there.

This is a great and more secure way to do this than my (make a table of API keys and use service role method). It's certainly more complicated, but a more usable solution. Hopefully we get some kind of API key support from within Supabase someday.

@j4w8n This was an awesome guide! Thanks! -- Quesiton -- Did any of you guys run into any issue when running the "create JWT" part of this? Being that the JWT is in the auth schema as mentioned above as well, am I not supposed to see the JWT's created by the users? Ive added a "select"policy as described... But any which way I go to run this >>
let { data, error } = await supabase
.rpc('create_api_key', {
id_of_user,
key_description
})
if (error) console.error(error)
else console.log(data)

When I try and run that request through API or within my JS (ive tried both) i check the logs and get a 204, but NOTHING gets created in the JWT it seems..... anyone else have this issue??

This is an awesome writeup. One thing I did notice though was that if an admin rotates the JWT secret. They would also need to update the Vault JWT secret. This would need to be automated because humans forget. Tokens would be generated and unusable since they would be signed with incorrect secret (until admin updated it). Also would changing the JWT secret invalidate all the existing tokens?

This is an awesome writeup. One thing I did notice though was that if an admin rotates the JWT secret. They would also need to update the Vault JWT secret. This would need to be automated because humans forget. Tokens would be generated and unusable since they would be signed with incorrect secret (until admin updated it). Also would changing the JWT secret invalidate all the existing tokens?

@logicminds yeah, good observation there. I hadn't thought of someone rotating their supabase jwt secret. I need to update the gist, to explain things.

If someone were to rotate the secret, it would break their api key system if using the exchange key for jwt method.

A good alternative would be to create your own JWT secret for this step. You'd basically repeat the step above it, where you create the project api key secret, but change the Vault secret name to project_jwt_secret.

The only downside to that alternative is you can't use the exchange jwt method and auth.uid() as a check in your RLS policies. Not a big deal, but just a disclaimer.

That might be the better way to do it actually. So if you had to rotate your jwt secret for some reason, it wouldn't affect your api key system at all.

The native authorization method doesn't seem to work for me, e.g. this part:

/* Somewhere in your API server */
const user_api_key = request.headers.get('Authorization')?.split(' ')[1] || ''

if (user_api_key) {
  const supabase = createClient(env.SUPABASE_URL, env.SUPABASE_ANON_KEY, {
    global: {
      headers: {
        Authorization: user_api_key
      }
    },
    auth: {
      persistSession: false,
      detectSessionInUrl: false,
      autoRefreshToken: false
    }
  })

  const { data, error } = await supabase.from('table').select()
  /* If error, return error */

  /* Otherwise, return data to API user */
}

Not sure, but the return of get_api_key doesn't look to me like it should work. It's not a JWT, how would Supabase recognize it?

Key looks like this (retrieved from get_api_key ): 2693c48a8173ce172954fd505c5ad580ca1760f0a5f78bd2a6f38aa46c5db66efe908a67b84603027a9b585cf1b493be31255e7415292425c93d312313b6059e

What I just noticed when inspecting the JWT from vault.decrypted_secrets (because the exchange through exchange_api_key_for_jwt didn't get me authenticated either) is that the hashing algorithm is set to hs256 while you've been using hs512 everywhere. Couldn't get a valid signature yet, will dive deeper tomorrow (but maybe someone sees that and knows what I'm doing wrong :) ).

PPS.: load_api_keys should be maybe renamed to load_secret_ids or smth, because it's not actually getting any end user keys.

PPPS.: The line select decrypted_secret into user_api_key_secret from vault.decrypted_secrets where name=id_of_user; in load_api_keys (secure version) does nothing, can be removed no?

The native authorization method doesn't seem to work for me, e.g. this part:

/* Somewhere in your API server */
const user_api_key = request.headers.get('Authorization')?.split(' ')[1] || ''

if (user_api_key) {
  const supabase = createClient(env.SUPABASE_URL, env.SUPABASE_ANON_KEY, {
    global: {
      headers: {
        Authorization: user_api_key
      }
    },
    auth: {
      persistSession: false,
      detectSessionInUrl: false,
      autoRefreshToken: false
    }
  })

  const { data, error } = await supabase.from('table').select()
  /* If error, return error */

  /* Otherwise, return data to API user */
}

Not sure, but the return of get_api_key doesn't look to me like it should work. It's not a JWT, how does Supabase recognize it?

Key looks like this (retrieved from get_api_key ): 2693c48a8173ce172954fd505c5ad580ca1760f0a5f78bd2a6f38aa46c5db66efe908a67b84603027a9b585cf1b493be31255e7415292425c93d312313b6059e

That's the api key, not a jwt. So when using this method, you need to make sure your RLS policy includes a check for the api key. e.g. ( SELECT (auth.key_uid() = user_id) )

What I just noticed when inspecting the JWT from vault.decrypted_secrets (because the exchange through exchange_api_key_for_jwt didn't get me authenticated either) is that the hashing algorithm is set to hs256 while you've been using hs512 everywhere. Couldn't get a valid signature yet, will dive deeper tomorrow (but maybe someone sees that and knows what I'm doing wrong :) ).

Yep, this is likely due to my haste in updating the gist the other day. The one where you create your own project jwt secret instead of using the one Supabase provides. That sql does use sha512, so you'd want to change the algo drop-down, on jwt.io, to HS512. I've changed that section to sha256, which is enough for it's purpose.

PPS.: load_api_keys should be maybe renamed to load_secret_ids or smth, because it's not actually getting any end user keys.

PPPS.: The line select decrypted_secret into user_api_key_secret from vault.decrypted_secrets where name=id_of_user; in load_api_keys (secure version) does nothing, can be removed no?

Yeah, the "secure" method function could be renamed to something like load_api_key_info, to be more accurate. I kept it the same as the slightly less secure method, for simplicity.

Yes, thanks for catching the unneeded "select" statement. I'll get that removed.