16. supabase-admin-set-profile-status.sql

원본 파일: 'C:\Repository\loafacto-hub\docs\web-ui\16. supabase-admin-set-profile-status.sql'
sql
-- =============================================================================
-- 관리자용: 사용자 계정 상태 변경 RPC (active / warning / banned / withdrawn)
-- 15. supabase-withdraw-account.sql(profiles.status, withdrawn_at) 적용 후 실행하세요.
-- status에 warning, banned를 쓰려면 아래 "프로필 status 확장"을 먼저 실행하세요.
-- 호출자: super_admin 또는 operator. 본인 계정은 변경 불가.
-- =============================================================================

-- 프로필 status 확장: active, withdrawn 외에 warning, banned 허용
-- (제약 이름이 다르면 수동으로 확인: SELECT conname FROM pg_constraint WHERE conrelid = 'public.profiles'::regclass AND contype = 'c'; 후 DROP CONSTRAINT 해당이름)
DO $$
DECLARE
  conname text;
BEGIN
  SELECT c.conname INTO conname
  FROM pg_constraint c
  JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
  WHERE c.conrelid = 'public.profiles'::regclass AND c.contype = 'c' AND a.attname = 'status'
  LIMIT 1;
  IF conname IS NOT NULL THEN
    EXECUTE format('ALTER TABLE public.profiles DROP CONSTRAINT %I', conname);
  END IF;
END $$;
ALTER TABLE public.profiles ADD CONSTRAINT profiles_status_check
  CHECK (status IN ('active', 'warning', 'banned', 'withdrawn'));

COMMENT ON COLUMN public.profiles.status IS '계정 상태: active(활성) | warning(경고) | banned(정지) | withdrawn(탈퇴)';

CREATE OR REPLACE FUNCTION public.set_user_profile_status(p_target_user_id uuid, p_status text)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_caller_role text;
BEGIN
  IF p_status NOT IN ('active', 'warning', 'banned', 'withdrawn') THEN
    RETURN jsonb_build_object('ok', false, 'error', 'invalid_status');
  END IF;

  -- 본인 계정은 상태 변경 불가
  IF p_target_user_id = auth.uid() THEN
    RETURN jsonb_build_object('ok', false, 'error', 'cannot_change_own_status');
  END IF;

  SELECT role INTO v_caller_role
  FROM public.user_roles
  WHERE user_id = auth.uid()
  LIMIT 1;

  IF v_caller_role IS NULL OR v_caller_role NOT IN ('super_admin', 'operator') THEN
    RETURN jsonb_build_object('ok', false, 'error', 'unauthorized');
  END IF;

  INSERT INTO public.profiles (user_id, status, withdrawn_at, updated_at)
  VALUES (
    p_target_user_id,
    p_status,
    CASE WHEN p_status = 'withdrawn' THEN now() ELSE NULL END,
    now()
  )
  ON CONFLICT (user_id) DO UPDATE SET
    status = EXCLUDED.status,
    withdrawn_at = EXCLUDED.withdrawn_at,
    updated_at = EXCLUDED.updated_at;

  RETURN jsonb_build_object('ok', true);
END;
$$;

COMMENT ON FUNCTION public.set_user_profile_status(uuid, text) IS '관리자(super_admin/operator)만 호출. 대상 사용자 계정 상태를 active(활성)/warning(경고)/banned(정지)/withdrawn(탈퇴)로 변경. 본인 계정은 변경 불가.';

GRANT EXECUTE ON FUNCTION public.set_user_profile_status(uuid, text) TO authenticated;
16. supabase-admin-set-profile-status.sql ​

16. supabase-admin-set-profile-status.sql
