blob: 515deb478bee22fbc9f01c095918f97429a3364f (
plain) (
blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
create table public.api_keys (
id uuid primary key default gen_random_uuid(),
user_id uuid not null references public.users(id) on delete cascade,
name text not null,
key_prefix text not null,
key_hash text not null unique,
last_used_at timestamptz,
expires_at timestamptz,
revoked_at timestamptz,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create index api_keys_user_id_idx on public.api_keys(user_id);
create index api_keys_key_hash_idx on public.api_keys(key_hash);
create trigger api_keys_updated_at
before update on public.api_keys
for each row execute function public.handle_updated_at();
alter table public.api_keys enable row level security;
create policy "Users can view own API keys"
on public.api_keys for select
using (auth.uid() = user_id);
create policy "Users can create own API keys"
on public.api_keys for insert
with check (auth.uid() = user_id);
create policy "Users can update own API keys"
on public.api_keys for update
using (auth.uid() = user_id);
create policy "Users can delete own API keys"
on public.api_keys for delete
using (auth.uid() = user_id);
create or replace function public.validate_api_key(api_key_hash text)
returns table (user_id uuid, api_key_id uuid)
language plpgsql
security definer
as $$
begin
return query
select ak.user_id, ak.id as api_key_id
from public.api_keys ak
where ak.key_hash = api_key_hash
and ak.revoked_at is null
and (ak.expires_at is null or ak.expires_at > now());
end;
$$;
create or replace function public.update_api_key_last_used(p_api_key_id uuid)
returns void
language plpgsql
security definer
as $$
begin
update public.api_keys
set last_used_at = now()
where id = p_api_key_id;
end;
$$;
|