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

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

      
    Raw
  

              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:

JWTs, and the secrets used to hash them, are stored in your encrypted Vault.
API keys are not stored in Supabase. Rather, we store a hash of the API key.
The protected auth schema is used to store info in the database.
Only references to JWTs are stored in the database.
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), 'sha512'), '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.


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;
$$;


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.


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;
$$;


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
    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 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.

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.


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


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


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 }
)


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


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 */
}