-- Owner-only moderation and revocable suspensions. Apply after 202611120001_access_approvals.sql. begin; create table private.hub_suspensions ( user_id uuid primary key references auth.users(id) on delete cascade, suspended_by uuid not null, reason text not null check (char_length(btrim(reason)) between 5 and 500), suspended_at timestamptz not null default now(), suspended_until timestamptz, lifted_at timestamptz ); alter table private.hub_suspensions enable row level security; revoke all on private.hub_suspensions from public, anon, authenticated; create table private.moderation_audit ( id bigint generated always as identity primary key, actor uuid not null, target uuid not null, action text not null check (action in ('suspend', 'unsuspend')), reason text, suspended_until timestamptz, changed_at timestamptz not null default now() ); alter table private.moderation_audit enable row level security; revoke all on private.moderation_audit from public, anon, authenticated; -- Preserve the existing RPC result shape. A suspension overrides approval -- without destroying the user's underlying approval status. create or replace function public.my_access() returns table(user_id uuid, status text, owner boolean) language sql stable security definer set search_path = '' as $$ select a.user_id, case when s.user_id is not null and s.lifted_at is null and (s.suspended_until is null or s.suspended_until > now()) then 'blocked'::text else a.status end, exists(select 1 from private.hub_owners o where o.user_id = auth.uid()) from public.hub_access a left join private.hub_suspensions s on s.user_id = a.user_id where a.user_id = auth.uid(); $$; create function public.admin_list_accounts( p_search text default '', p_filter text default 'all', p_offset integer default 0, p_page_size integer default 30 ) returns table( user_id uuid, email text, status text, is_owner boolean, is_suspended boolean, suspension_reason text, suspended_until timestamptz, created_at timestamptz, total_count bigint ) language plpgsql stable security definer set search_path = '' as $$ begin if auth.uid() is null or not exists ( select 1 from private.hub_owners o where o.user_id = auth.uid() ) then raise exception 'Owner access required' using errcode = '42501'; end if; if char_length(coalesce(p_search,'')) > 120 or p_filter not in ('all','pending','approved','blocked','suspended') or p_filter is null or p_offset is null or p_offset < 0 or p_page_size is null or p_page_size < 1 or p_page_size > 100 then raise exception 'Invalid account search'; end if; return query with people as ( select a.user_id, u.email::text as email, a.status, exists(select 1 from private.hub_owners o where o.user_id = a.user_id) as is_owner, (s.user_id is not null and s.lifted_at is null and (s.suspended_until is null or s.suspended_until > now())) as is_suspended, s.reason as suspension_reason, s.suspended_until, a.created_at from public.hub_access a join auth.users u on u.id = a.user_id left join private.hub_suspensions s on s.user_id = a.user_id where position(lower(btrim(coalesce(p_search,''))) in lower(coalesce(u.email,''))) > 0 ) select p.user_id, p.email, p.status, p.is_owner, p.is_suspended, case when p.is_suspended then p.suspension_reason else null::text end, case when p.is_suspended then p.suspended_until else null::timestamptz end, p.created_at, count(*) over()::bigint from people p where p_filter = 'all' or (p_filter = 'pending' and p.status = 'pending') or (p_filter = 'approved' and p.status = 'approved' and not p.is_suspended) or (p_filter = 'blocked' and p.status = 'blocked') or (p_filter = 'suspended' and p.is_suspended) order by p.created_at desc, p.user_id limit p_page_size offset p_offset; end $$; create function public.admin_suspend( p_target uuid, p_reason text, p_hours integer default null ) returns void language plpgsql security definer set search_path = '' as $$ declare target_status text; until_at timestamptz; begin if auth.uid() is null or not exists ( select 1 from private.hub_owners o where o.user_id = auth.uid() ) then raise exception 'Owner access required' using errcode = '42501'; end if; if p_target is null or exists ( select 1 from private.hub_owners o where o.user_id = p_target ) then raise exception 'Owner accounts cannot be suspended' using errcode = '42501'; end if; if p_reason is null or char_length(btrim(p_reason)) not between 5 and 500 or (p_hours is not null and p_hours not in (24, 168, 720)) then raise exception 'Provide a reason and a supported suspension duration'; end if; select a.status into target_status from public.hub_access a where a.user_id = p_target for update; if target_status is distinct from 'approved' then raise exception 'Only approved accounts can be suspended'; end if; until_at := case when p_hours is null then null else now() + make_interval(hours => p_hours) end; insert into private.hub_suspensions (user_id, suspended_by, reason, suspended_at, suspended_until, lifted_at) values (p_target, auth.uid(), btrim(p_reason), now(), until_at, null) on conflict (user_id) do update set suspended_by = excluded.suspended_by, reason = excluded.reason, suspended_at = excluded.suspended_at, suspended_until = excluded.suspended_until, lifted_at = null; insert into private.moderation_audit (actor, target, action, reason, suspended_until) values (auth.uid(), p_target, 'suspend', btrim(p_reason), until_at); end $$; create function public.admin_unsuspend(p_target uuid) returns void language plpgsql security definer set search_path = '' as $$ begin if auth.uid() is null or not exists ( select 1 from private.hub_owners o where o.user_id = auth.uid() ) then raise exception 'Owner access required' using errcode = '42501'; end if; if p_target is null or exists ( select 1 from private.hub_owners o where o.user_id = p_target ) then raise exception 'Owner accounts cannot be modified' using errcode = '42501'; end if; update private.hub_suspensions s set lifted_at = now() where s.user_id = p_target and s.lifted_at is null and (s.suspended_until is null or s.suspended_until > now()); if not found then raise exception 'No active suspension was found'; end if; insert into private.moderation_audit(actor, target, action) values(auth.uid(), p_target, 'unsuspend'); end $$; create function public.admin_moderation_history(p_limit integer default 50) returns table( event_id text, action text, target_user uuid, target_email text, reason text, suspended_until timestamptz, changed_at timestamptz ) language plpgsql stable security definer set search_path = '' as $$ begin if auth.uid() is null or not exists ( select 1 from private.hub_owners o where o.user_id = auth.uid() ) then raise exception 'Owner access required' using errcode = '42501'; end if; if p_limit is null or p_limit < 1 or p_limit > 200 then raise exception 'Invalid history limit'; end if; return query select events.event_id, events.action, events.target_user, events.target_email, events.reason, events.suspended_until, events.changed_at from ( select 'access-' || a.id::text as event_id, 'set-' || a.next_status as action, a.target as target_user, u.email::text as target_email, null::text as reason, null::timestamptz as suspended_until, a.changed_at from private.access_audit a left join auth.users u on u.id = a.target union all select 'moderation-' || m.id::text, m.action, m.target, u.email::text, m.reason, m.suspended_until, m.changed_at from private.moderation_audit m left join auth.users u on u.id = m.target ) events order by events.changed_at desc, events.event_id desc limit p_limit; end $$; -- SECURITY DEFINER functions are exposed only as authenticated RPCs and each -- checks membership in private.hub_owners itself. No service-role key in browsers. revoke all on function public.my_access() from public, anon; revoke all on function public.admin_list_accounts(text,text,integer,integer) from public, anon, authenticated; revoke all on function public.admin_suspend(uuid,text,integer) from public, anon, authenticated; revoke all on function public.admin_unsuspend(uuid) from public, anon, authenticated; revoke all on function public.admin_moderation_history(integer) from public, anon, authenticated; grant execute on function public.my_access() to authenticated; grant execute on function public.admin_list_accounts(text,text,integer,integer) to authenticated; grant execute on function public.admin_suspend(uuid,text,integer) to authenticated; grant execute on function public.admin_unsuspend(uuid) to authenticated; grant execute on function public.admin_moderation_history(integer) to authenticated; commit;