Rolling my own API key auth in Postgres
Introduction
Recently, I had to implement API key authentication in Supabase. I didn’t feel like reading someone else’s documentation, paying for yet another “plug-and-play” service, or surrendering control to a black box. So naturally, I did the only reasonable thing: I rolled my own authentication stack in my favorite Postgres wrapper, Supabase.
Now, why reinvent the wheel? Fair question. My answer is twofold: over-engineering is my love language; and second, I had a hard requirement to integrate this with an existing Envoy ext_authz service, and rolling my own was the simplest way to do it.
How Not to Store API Keys
I started out by storing plaintext UUIDs in Supabase and using them for authn/z (yikes). Alarm bells should immediately be ringing in your head for at least two good reasons:
All the eggs in one key: Using a single API key for every action is lazy, insecure, and pretty bad UX. It forces users to treat every request with the same level of paranoia, whether they’re subscribing to a data feed or wiring funds to a bank account1
Intercept attacks: Anyone testing with production keys on a public network is one Wireshark session away from starring in their own postmortem.
Public and Secret Keys
After a bit of research, I decided to use Digital Signatures, in particular Hash Message Authentication Code (HMAC). HMAC uses two keys: a Public Key and a Secret Key. Think of it like this:
Public Key: like a username, safe to log, visible to anyone, used to identify the user.
Secret Key: like a password, never log it, never send/store it in plaintext, used to prove the user is who they say they are.
Instead of sending the secret key over the wire, we generate a signature using a hashing algorithm (like SHA256) that combines the secret key with a timestamp, a nonce, and optionally other parameters (endpoint, body, etc.). This signature, along with the timestamp and nonce in plaintext, is sent with the request. Each piece here has a purpose:
- hashing the secret key keeps it… well, secret.
- hashing the secret key ensures that the key is not being sent in plaintext
- the timestamp ensures the signature only works for a limited window, preventing replay/intercept attacks (the expiration time is a bit generous to account for clock skew)
- the other optional additional params tie the signature to a specific request so intercepting it doesn’t let attackers do anything else.
- the nonce is used to make the signature single use. The same nonce cannot be used until expiration.
In short: Public key = who you are, Secret key = prove it, nonce & timestamp = no funny business. This StackExchange post is also useful to understand HMAC (if my explanation wasn’t clear enough).
UUIDs and My Pet Peeves About Them
Now for the most important decision — what should the API keys look like? Previously, they were plain UUIDs, 36 characters long (32 chars + 4 dashes):
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Using this for my auth stack would have made me rage-quit.
- I cannot quickly copy this key by double-clicking or using
viwin Vim2. - It’s visually impossible to differentiate between a public and private API key at a glance.
Thankfully, there is already a solution for this (and I do not have to roll this on my own) - TypeID. This addresses my pet peeves while providing on-par (if not better) performance and a better UX/DX
sec_2x4y6z8a0b1c2d3e4f5g6h7j8k
pub_2x4y6z8a0b1c2d3e4f5g6h7j8k
└──┘ └────────────────────────┘
type uuid suffix (base32)
I added type ID support to Supabase by running the queries provided in the official typeid-sql GitHub repository.
Storing public keys
Public keys are stored in a public.keys table linked to the users table via TypeID functions. Row-level security ensures each user can only access their own keys, with all other operations managed by the backend.
create table if not exists public.keys (
id bigserial primary key,
public_key text not null default typeid_generate_text('pub'),
user_id uuid not null references auth.users (id) on delete cascade,
created_at timestamptz not null default timezone('utc', now()),
revoked_at timestamptz,
name text,
constraint keys_public_public_key_unique unique (public_key),
constraint keys_public_public_key_check check (typeid_check_text(public_key, 'pub'))
);
alter table public.keys enable row level security;
create policy "Users can view their own keys"
on public.keys
for select
using (auth.uid() = user_id);
create policy "Users can create their own keys"
on public.keys
for select
using (auth.uid() = user_id);
create index if not exists apikeys_idx on public.keys (public_key);
Storing secret keys
Secret keys are basically passwords, so they deserve to live somewhere more private than public. I store them in a dedicated private schema as encrypted bytes.
create schema if not exists private;
revoke all on schema private from public;
revoke all on schema private from anon;
revoke all on schema private from authenticated;
grant usage on schema private to postgres;
grant usage on schema private to service_role;
create table if not exists private.keys (
public_key text primary key
references public.keys (public_key) on delete cascade,
secret_enc bytea not null
);
alter table private.keys enable row level security;
create policy "no direct access"
on private.keys
for all
using (false) with check (false);
I decided to store the encryption key is stored in Supabase Vault with the name HMAC_MASTER.
Creating API keys
To ensure public and secret keys are handled correctly, I use a Postgres function that generates both keys, stores the public key in the public.keys table, encrypts the secret key in the private.keys table, and returns a JSON object containing the raw secret key.
create extension if not exists pgsodium;
create extension if not exists pgsodium;
create or replace function public.create_keys(p_name text)
returns json
language plpgsql
security definer
set search_path = public, pg_catalog, extensions, pgsodium, private
as $$
declare
v_current_user_id uuid;
v_pub text := typeid_generate_text('qfex_pub');
v_sec text := typeid_generate_text('qfex_secret');
v_master text;
v_id bigint;
v_public_key text;
v_secret_key text;
v_created_at timestamptz;
v_result_json json;
begin
-- Require authentication
if auth.uid() is null then
raise exception 'unauthenticated' using errcode = '28000';
end if;
v_current_user_id := auth.uid();
-- Require API key name
if p_name is null or btrim(p_name) = '' then
raise exception 'public_key_name_required' using errcode = '22023';
end if;
-- Get HMAC master key from Vault
select decrypted_secret
into v_master
from vault.decrypted_secrets
where name = 'HMAC_MASTER'
limit 1;
if v_master is null then
raise exception 'HMAC master key not configured in Vault';
end if;
-- Insert public key
insert into public.keys (public_key, user_id, name)
values (v_pub, v_current_user_id, p_name)
returning keys.id, keys.public_key, keys.created_at
into v_id, v_public_key, v_created_at;
-- Insert encrypted private key
insert into private.keys (public_key, secret_enc)
values (
v_pub,
extensions.pgp_sym_encrypt_bytea(convert_to(v_sec, 'utf8'), v_master)
);
v_secret_key := v_sec;
-- Return generated API key JSON
v_result_json := json_build_object(
'id', v_id,
'name', p_name,
'public_key', v_public_key,
'secret_key', v_secret_key,
'created_at', v_created_at
);
return v_result_json;
end;
$$;
Verifying API keys
To prevent replay attacks, we maintain a nonce log that tracks used nonces. Each nonce can only be used once, and entries are cleaned up every 15 minutes to avoid unnecessary table growth.
create extension if not exists pg_cron;
create table if not exists public.nonce_log (
public_key text not null,
nonce text not null,
seen_at timestamptz not null default timezone('utc', now()),
primary key (public_key, nonce)
);
select cron.schedule(
'nonce_log_cleanup',
'*/5 * * * *',
$$
delete from public.nonce_log
where seen_at < timezone('utc', now()) - interval '15 minutes';
$$
);
The user sends the following signature
Signature = Hex(Nonce:Timestamp)
sample python script generating the signature
import hmac
import secrets
pub = "pub_xxx"
sec = "sec_yyy"
nonce = secrets.token_hex(nonce_bytes)
ts = int(time.time())
msg = f"{nonce}:{ts}".encode()
sig = hmac.new(sec.encode(), msg, hashlib.sha256).hexdigest()
After this, the following trigger can be used for verifying the signature
create or replace function public.verify_signature(
p_public_key text,
p_nonce text,
p_timestamp bigint,
p_sig_hex text,
p_skew_sec integer default 300
) returns boolean
language plpgsql
security definer
set search_path = public, pg_catalog, extensions, private
as $$
declare
v_master text;
v_secret text;
v_calc_hex text;
v_now_sec bigint := extract(epoch from timezone('utc', now()));
v_payload bytea;
begin
-- input validation
if p_public_key is null or p_nonce is null or p_timestamp is null or p_sig_hex is null then
raise exception 'missing required parameters'
using errcode = '22023',
hint = 'public_key, nonce, timestamp, and sig_hex are required.';
end if;
-- timestamp check
if abs(v_now_sec - p_timestamp) > coalesce(p_skew_sec, 300) then
raise exception 'timestamp outside allowed window', p_skew_sec
using errcode = '22023',
hint = 'Client clock may be out of sync. Please retry with correct UTC time.';
end if;
-- nonce check
begin
insert into public.nonce_log (public_key, nonce)
values (p_public_key, p_nonce);
exception when unique_violation then
raise exception 'nonce has already been used for this key'
using errcode = '23505',
hint = 'Each nonce must be unique per key to prevent replay attacks.';
end;
select decrypted_secret
into v_master
from vault.decrypted_secrets
where name = 'HMAC_MASTER'
limit 1;
if v_master is null then
raise exception 'HMAC master key not configured in Vault'
using errcode = 'P0001';
end if;
-- decrypted secret key
select convert_from(
extensions.pgp_sym_decrypt_bytea(pp.secret_enc, v_master),
'utf8'
)
into v_secret
from public.keys ap
join private.keys pp
on pp.public_key = ap.public_key
where ap.public_key = p_public_key
and ap.revoked_at is null
limit 1;
if v_secret is null then
raise exception 'invalid or revoked API key'
using errcode = 'P0002',
hint = 'The provided public_key may be invalid or revoked.';
end if;
-- validate signature
v_payload := convert_to(p_nonce || ':' || p_timestamp, 'utf8');
v_calc_hex := encode(
extensions.hmac(v_payload, convert_to(v_secret, 'utf8'), 'sha256'),
'hex'
);
if v_calc_hex <> lower(p_sig_hex) then
raise exception 'invalid HMAC signature'
using errcode = '22023',
hint = 'Ensure you are signing exactly "<nonce>:<timestamp>" using HMAC-SHA256.';
end if;
return true;
end;
$$;
Reflection
Building my own API key authentication was surprisingly fun.
Would do it again.