110 lines
3.2 KiB
PL/PgSQL
110 lines
3.2 KiB
PL/PgSQL
-- 1) Test-Daten-Wipe: alle conversation/message Daten weg, User+Devices+
|
|
-- Friendships+Invites bleiben erhalten so dass bestehende Logins weiter
|
|
-- funktionieren. Cascade räumt mitlaufende rows (envelopes, attachments,
|
|
-- reactions, reads, conversation_keys, conversation_members).
|
|
|
|
truncate
|
|
public.message_reactions,
|
|
public.message_reads,
|
|
public.message_attachments,
|
|
public.messages,
|
|
public.conversation_keys,
|
|
public.conversation_members,
|
|
public.conversations
|
|
restart identity cascade;
|
|
|
|
-- 2) RPC: share conv-key bundles to multiple recipients atomically.
|
|
-- Runs as SECURITY DEFINER so RLS doesn't reject individual rows. The
|
|
-- function itself enforces the same invariants as the policy. This avoids
|
|
-- the per-row 403 console spam we got with direct inserts.
|
|
|
|
create or replace function public.share_conv_keys(
|
|
p_conv_id uuid,
|
|
p_sender_device_id uuid,
|
|
p_key_version int,
|
|
p_bundles jsonb
|
|
) returns int
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
declare
|
|
caller uuid := auth.uid();
|
|
bundle jsonb;
|
|
recipient_user_id uuid;
|
|
inserted int := 0;
|
|
recipient_did uuid;
|
|
enc_key_hex text;
|
|
nonce_hex text;
|
|
begin
|
|
if caller is null then
|
|
raise exception 'not authenticated';
|
|
end if;
|
|
|
|
-- Caller must be an accepted member of the conversation.
|
|
if not exists (
|
|
select 1
|
|
from public.conversation_members
|
|
where conversation_id = p_conv_id
|
|
and user_id = caller
|
|
and accepted = true
|
|
) then
|
|
raise exception 'caller is not an accepted member of %', p_conv_id;
|
|
end if;
|
|
|
|
-- Sender device must belong to the caller.
|
|
if not exists (
|
|
select 1
|
|
from public.devices
|
|
where id = p_sender_device_id
|
|
and user_id = caller
|
|
) then
|
|
raise exception 'sender_device % not owned by caller', p_sender_device_id;
|
|
end if;
|
|
|
|
-- Iterate bundles and only insert ones whose recipient is a current
|
|
-- accepted member. Silently skip otherwise (no error → no 403 noise).
|
|
for bundle in
|
|
select * from jsonb_array_elements(p_bundles)
|
|
loop
|
|
recipient_did := (bundle->>'recipient_device_id')::uuid;
|
|
enc_key_hex := bundle->>'encrypted_key';
|
|
nonce_hex := bundle->>'nonce';
|
|
|
|
select user_id
|
|
into recipient_user_id
|
|
from public.devices
|
|
where id = recipient_did;
|
|
if recipient_user_id is null then continue; end if;
|
|
|
|
if not exists (
|
|
select 1
|
|
from public.conversation_members
|
|
where conversation_id = p_conv_id
|
|
and user_id = recipient_user_id
|
|
and accepted = true
|
|
) then continue; end if;
|
|
|
|
insert into public.conversation_keys
|
|
(conversation_id, recipient_device_id, key_version,
|
|
sender_device_id, encrypted_key, nonce)
|
|
values
|
|
(p_conv_id, recipient_did, p_key_version,
|
|
p_sender_device_id,
|
|
decode(enc_key_hex, 'hex'),
|
|
decode(nonce_hex, 'hex'))
|
|
on conflict (conversation_id, recipient_device_id, key_version)
|
|
do nothing;
|
|
|
|
if found then
|
|
inserted := inserted + 1;
|
|
end if;
|
|
end loop;
|
|
|
|
return inserted;
|
|
end;
|
|
$$;
|
|
|
|
revoke execute on function public.share_conv_keys(uuid, uuid, int, jsonb) from public, anon;
|
|
grant execute on function public.share_conv_keys(uuid, uuid, int, jsonb) to authenticated;
|