byGalax
54 lines
2.3 KiB
SQL
54 lines
2.3 KiB
SQL
-- Per-user X25519 identity replacing the per-device key model.
|
|
-- The private key is sealed with a PIN-derived Argon2id KEK; the server
|
|
-- never sees plaintext. Lockout counters protect the 6-digit PIN against
|
|
-- online brute force by gating ciphertext delivery (see try_unlock_user_key).
|
|
|
|
create table if not exists public.user_keys (
|
|
user_id uuid primary key references auth.users(id) on delete cascade,
|
|
public_key bytea not null,
|
|
sealed_private_key bytea not null,
|
|
salt bytea not null,
|
|
kdf_params jsonb not null,
|
|
recovery_sealed_private_key bytea null,
|
|
recovery_salt bytea null,
|
|
failed_attempts int not null default 0,
|
|
locked_until timestamptz null,
|
|
failed_recovery_attempts int not null default 0,
|
|
recovery_locked_until timestamptz null,
|
|
key_version int not null default 1,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
alter table public.user_keys enable row level security;
|
|
|
|
drop policy if exists user_keys_self_rw on public.user_keys;
|
|
create policy user_keys_self_rw
|
|
on public.user_keys
|
|
for all
|
|
to authenticated
|
|
using (user_id = auth.uid())
|
|
with check (user_id = auth.uid());
|
|
|
|
-- Public-key view: any authenticated user may read peer public keys to wrap
|
|
-- conv-keys for them. Only the columns granted below are exposed.
|
|
create or replace view public.user_public_keys
|
|
with (security_invoker = true) as
|
|
select user_id, public_key, key_version
|
|
from public.user_keys;
|
|
|
|
grant select on public.user_public_keys to authenticated;
|
|
|
|
-- Column-level grant lets the view see those columns without RLS rejecting
|
|
-- non-owners. The self_rw policy still grants full row access to the owner.
|
|
grant select (user_id, public_key, key_version) on public.user_keys to authenticated;
|
|
|
|
drop policy if exists user_keys_select_public_columns on public.user_keys;
|
|
create policy user_keys_select_public_columns
|
|
on public.user_keys
|
|
for select
|
|
to authenticated
|
|
using (true);
|
|
|
|
alter publication supabase_realtime add table public.user_keys;
|