aarbit
4c45b5afa7
Per organized-ideas.md §6: role assignment, account ban/unban/delete, and direct Admin/Super-Admin CRUD of public catalog entries outside the submission workflow. Backend: - list_users_for_admin(): Super-Admin-gated SECURITY DEFINER function joining profiles + auth.users (username, email, role, banned_until) — auth.users isn't exposed through PostgREST, so this is the only way to list accounts at all. - New Edge Function admin-user-action (ban/unban/delete), using @supabase/server's `auth: 'user'` mode to verify the caller's JWT, then Supabase Auth's Admin API for the actual mutation. This is deliberately an Edge Function rather than a Postgres function like everything else in this codebase: touching auth.users needs the Admin API, the stable documented interface, not a direct write to a schema Supabase manages internally. Self-action guard; verify_jwt = true at the gateway on top of the function's own JWT verification. - 5 new pgTAP tests (43/43 total) for list_users_for_admin (Super-Admin-only, even regular Admins get 42501). - CatalogRepository gains admin* methods (direct edit of a public port/cable/ device entry, plus adminUnpublish which flips is_public rather than deleting) — the update methods were already ownership-agnostic (RLS's is_admin() clause is what actually permits it), so these are thin aliases, not duplicated logic. Frontend: - authStore/AdminUserRepository: minimal role plumbing, shared UserRole type. - adminUserStore + AdminUsersModal: list/role-dropdown/ban/unban/delete, gated to Super Admin only via a new "Manage Users" TopBar button. - PortTypeManager/CableTypeManager/DevicePalette: built-in entries now show direct "Edit"/"Unpublish" for Admins (regular Admin included, per §6's capability table — not Super-Admin-exclusive) instead of "Suggest edit"; unpublish reuses the review-queue's impact-check RPC before confirming. - DeviceTemplateEditor gains an `adminMode` save path alongside its existing submissionMode/resubmitId ones. Verified: tsc -b and oxlint clean; supabase db reset + 43/43 pgTAP tests pass; confirmed both new privileged endpoints (the SQL function and the Edge Function) actually work through the real REST API via live curl calls — signup, email confirm, role promotion, ban/unban/delete round trips, self-action guard, non-super-admin rejection, and verify_jwt=true compatibility all exercised directly, not just asserted.
31 lines
1.3 KiB
PL/PgSQL
31 lines
1.3 KiB
PL/PgSQL
-- Super-Admin user management (organized-ideas.md §6): "CRUD user accounts"
|
|
-- starts with being able to see them at all. auth.users isn't exposed
|
|
-- through PostgREST (by design — Supabase doesn't expose the `auth` schema
|
|
-- to the API), so this read-only, Super-Admin-gated function is the only
|
|
-- way the app can list users alongside their profile/ban status. The
|
|
-- mutations themselves (ban/unban/delete) go through a new Edge Function
|
|
-- instead of a SQL function here — those specifically need Supabase Auth's
|
|
-- Admin API (auth.admin.updateUserById/deleteUser), the stable, documented
|
|
-- interface for touching auth.users, rather than writing to that schema's
|
|
-- tables directly (undocumented, not guaranteed stable across upgrades).
|
|
|
|
create or replace function public.list_users_for_admin()
|
|
returns table(id uuid, username text, email text, role text, banned_until timestamptz, created_at timestamptz)
|
|
language plpgsql
|
|
stable
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
begin
|
|
if not public.is_super_admin() then
|
|
raise exception 'insufficient_privilege' using errcode = '42501';
|
|
end if;
|
|
|
|
return query
|
|
select p.id, p.username, u.email::text, p.role, u.banned_until, p.created_at
|
|
from public.profiles p
|
|
join auth.users u on u.id = p.id
|
|
order by p.created_at desc;
|
|
end;
|
|
$$;
|