e856de79b8
Some checks failed
supabase-ci / validate (push) Has been cancelled
(cherry picked from commit bebd0e6312370620dc1e9df97f7e80076a7654be)
109 lines
3.5 KiB
PL/PgSQL
109 lines
3.5 KiB
PL/PgSQL
-- 77-storage-taxonomy-rls.sql
|
|
-- RLS for taxonomy storage buckets. cc.examboards intentionally gets no
|
|
-- direct object-read policy: exam-board PDF bytes are exposed only through
|
|
-- API-issued, time-limited signed URLs.
|
|
|
|
create or replace function public.is_platform_admin()
|
|
returns boolean
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
select exists (
|
|
select 1
|
|
from public.admin_profiles ap
|
|
where ap.id = auth.uid()
|
|
and coalesce(ap.is_super_admin, false)
|
|
)
|
|
$$;
|
|
|
|
grant execute on function public.is_platform_admin() to authenticated;
|
|
|
|
-- Use the first path segment of cc.institutes objects as the institute id.
|
|
-- Expected layout: cc.institutes/<institute_uuid>/...
|
|
create or replace function public.storage_object_first_path_segment_uuid(p_name text)
|
|
returns uuid
|
|
language plpgsql
|
|
immutable
|
|
as $$
|
|
declare
|
|
v_segment text;
|
|
begin
|
|
v_segment := split_part(coalesce(p_name, ''), '/', 1);
|
|
if v_segment = '' then
|
|
return null;
|
|
end if;
|
|
begin
|
|
return v_segment::uuid;
|
|
exception when invalid_text_representation then
|
|
return null;
|
|
end;
|
|
end;
|
|
$$;
|
|
|
|
grant execute on function public.storage_object_first_path_segment_uuid(text) to authenticated;
|
|
|
|
-- Exam-board catalogue rows are authenticated metadata. PDF bytes remain
|
|
-- protected separately in cc.examboards storage and require signed URLs.
|
|
alter table public.eb_specifications enable row level security;
|
|
alter table public.eb_exams enable row level security;
|
|
|
|
drop policy if exists "eb_specifications read by authenticated users" on public.eb_specifications;
|
|
drop policy if exists "eb_exams read by authenticated users" on public.eb_exams;
|
|
|
|
create policy "eb_specifications read by authenticated users"
|
|
on public.eb_specifications
|
|
for select
|
|
to authenticated
|
|
using (true);
|
|
|
|
create policy "eb_exams read by authenticated users"
|
|
on public.eb_exams
|
|
for select
|
|
to authenticated
|
|
using (true);
|
|
|
|
alter table storage.objects enable row level security;
|
|
|
|
-- Replace only the policies owned by this migration. The absence of any
|
|
-- cc.examboards SELECT policy is the deny-by-default posture for raw reads.
|
|
drop policy if exists "cc.admin objects read by platform admins" on storage.objects;
|
|
drop policy if exists "cc.institutes objects read by institute members" on storage.objects;
|
|
drop policy if exists "cc.public objects read by authenticated users" on storage.objects;
|
|
|
|
create policy "cc.admin objects read by platform admins"
|
|
on storage.objects
|
|
for select
|
|
to authenticated
|
|
using (
|
|
bucket_id = 'cc.admin'
|
|
and public.is_platform_admin()
|
|
);
|
|
|
|
create policy "cc.institutes objects read by institute members"
|
|
on storage.objects
|
|
for select
|
|
to authenticated
|
|
using (
|
|
bucket_id = 'cc.institutes'
|
|
and public.storage_object_first_path_segment_uuid(name) in (
|
|
select public.user_institute_ids()
|
|
)
|
|
);
|
|
|
|
create policy "cc.public objects read by authenticated users"
|
|
on storage.objects
|
|
for select
|
|
to authenticated
|
|
using (bucket_id = 'cc.public');
|
|
|
|
comment on policy "cc.admin objects read by platform admins" on storage.objects
|
|
is 'cc.admin raw reads are limited to platform admins.';
|
|
comment on policy "cc.institutes objects read by institute members" on storage.objects
|
|
is 'cc.institutes raw reads are scoped by leading path segment institute uuid.';
|
|
comment on policy "cc.public objects read by authenticated users" on storage.objects
|
|
is 'cc.public raw reads are available to any authenticated user.';
|
|
comment on table storage.objects
|
|
is 'Classroom Copilot taxonomy buckets use RLS; cc.examboards raw reads stay denied and are served through API signed URLs.';
|