81 lines
2.1 KiB
SQL
81 lines
2.1 KiB
SQL
-- allow senders to read their own messages
|
|
create policy "messages_select_sender"
|
|
on public.chat_messages
|
|
for select
|
|
using (sender_id = auth.uid());
|
|
|
|
-- allow other conversation members to read messages
|
|
create policy "messages_select_members"
|
|
on public.chat_messages
|
|
for select
|
|
using (
|
|
exists (
|
|
select 1
|
|
from public.chat_participants p
|
|
where p.conversation_id = chat_messages.conversation_id
|
|
and p.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
-- ===============================
|
|
-- Full setup for realtime listening via Supabase Realtime Inspector
|
|
|
|
-- Make sure we have uuid helpers for primary keys
|
|
create extension if not exists "pgcrypto";
|
|
|
|
create table if not exists public.ak_test (
|
|
id uuid primary key default gen_random_uuid(),
|
|
owner_id uuid default auth.uid(),
|
|
payload jsonb default '{}'::jsonb,
|
|
created_at timestamptz not null default timezone('utc', now())
|
|
);
|
|
|
|
comment on table public.ak_test is 'Scratch table for realtime policy tests';
|
|
|
|
-- Register the table with the realtime publication (idempotent)
|
|
do $$
|
|
begin
|
|
if not exists (
|
|
select 1
|
|
from pg_publication_tables
|
|
where pubname = 'supabase_realtime'
|
|
and schemaname = 'public'
|
|
and tablename = 'ak_test'
|
|
) then
|
|
alter publication supabase_realtime add table public.ak_test;
|
|
end if;
|
|
end $$;
|
|
|
|
-- ===============================
|
|
-- Reset helper: disable and recreate policies scoped to the `public` role
|
|
|
|
alter table public.ak_test disable row level security;
|
|
|
|
drop policy if exists "ak_test_insert_owner" on public.ak_test;
|
|
drop policy if exists "ak_test_select_all" on public.ak_test;
|
|
drop policy if exists "ak_test_select_owner" on public.ak_test;
|
|
|
|
alter table public.ak_test enable row level security;
|
|
|
|
create policy "ak_test_insert_owner"
|
|
on public.ak_test
|
|
for insert
|
|
to public
|
|
with check (owner_id = auth.uid());
|
|
|
|
create policy "ak_test_select_all"
|
|
on public.ak_test
|
|
for select
|
|
to public
|
|
using (true);
|
|
|
|
create policy "ak_test_select_owner"
|
|
on public.ak_test
|
|
for select
|
|
to public
|
|
using (owner_id = auth.uid());
|
|
|
|
-- To remove test data quickly
|
|
truncate table public.ak_test;
|
|
|