# Implement user API keys with Supabase This is pretty much my first crack at this. I'm sure things could be improved or done differently. ## 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 :white_check_mark: Recommend a custom JWT secret, to sign JWTs. :white_large_square: 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 or via your API. Implementing these are outside the scope of this gist. ## JWT structure This is an example decoded JWT. Header: ```js { "alg": "HS256", "typ": "JWT" } ``` Payload: ```js { "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](#alternative-to-secure-method) 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. ```sql 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. ```sql 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](#alternative-to-handling-native-api-keys). Paste the below code into your SQL Editor on your Supabase dashboard. ```sql 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](#alternative-to-handling-native-api-keys) - 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. ```sql 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. ```sql 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) ```sql 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. ```sql 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. ```sql 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`. ```sql 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_id`s 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: ```ts { "description": string; "id": string; } ``` ```sql 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`: ```sql 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_id`s 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: ```ts { "description": string; "id": string; "key": string; } ``` ```sql 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. ```sql 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](https://github.com/orgs/supabase/discussions/14576) 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`: ```sql 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`: ```sql 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 ```js const { error } = await supabase.rpc( 'create_api_key', { id_of_user, key_description } ) ``` 2. Load API keys ```js 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. ```js const { data, error } = await supabase.rpc( 'get_api_key', { id_of_user, secret_id } ) ``` 4. Revoke an API key ```js 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. ```js /* 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. ```js /* 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 */ } ```