Supabase用户管理 #

// 获取当前登录用户
const { data: { user }, error } = await supabase.auth.getUser()

if (user) {
  console.log('User ID:', user.id)
  console.log('Email:', user.email)
  console.log('Phone:', user.phone)
  console.log('Metadata:', user.user_metadata)
  console.log('Created:', user.created_at)
  console.log('Last Sign In:', user.last_sign_in_at)
}

// 获取当前会话
const { data: { session }, error } = await supabase.auth.getSession()

if (session) {
  console.log('Access Token:', session.access_token)
  console.log('Refresh Token:', session.refresh_token)
  console.log('Expires At:', new Date(session.expires_at! * 1000))
  console.log('User:', session.user)
}

// 监听认证状态变化
const { data: { subscription } } = supabase.auth.onAuthStateChange(
  (event, session) => {
    console.log('Event:', event)
    console.log('Session:', session)
    
    switch (event) {
      case 'SIGNED_IN':
        // 用户登录
        break
      case 'SIGNED_OUT':
        // 用户登出
        break
      case 'TOKEN_REFRESHED':
        // Token刷新
        break
      case 'USER_UPDATED':
        // 用户信息更新
        break
    }
  }
)

// 清理监听
subscription.unsubscribe()

-- 创建用户资料表
CREATE TABLE public.profiles (
    id UUID REFERENCES auth.users(id) PRIMARY KEY,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    username TEXT UNIQUE,
    full_name TEXT,
    avatar_url TEXT,
    website TEXT,
    bio TEXT,
    
    CONSTRAINT username_length CHECK (char_length(username) >= 3)
);

-- 启用RLS
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- 创建策略
CREATE POLICY "Public profiles are viewable by everyone"
    ON profiles FOR SELECT USING (true);

CREATE POLICY "Users can insert own profile"
    ON profiles FOR INSERT WITH CHECK (auth.uid() = id);

CREATE POLICY "Users can update own profile"
    ON profiles FOR UPDATE USING (auth.uid() = id);

-- 创建触发器函数
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.profiles (id, full_name, avatar_url)
    VALUES (
        NEW.id,
        NEW.raw_user_meta_data->>'full_name',
        NEW.raw_user_meta_data->>'avatar_url'
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 创建触发器
CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW
    EXECUTE FUNCTION public.handle_new_user();

// 更新auth用户元数据
const { data, error } = await supabase.auth.updateUser({
  data: {
    full_name: 'John Doe',
    avatar_url: 'https://example.com/avatar.jpg',
  },
})

// 更新profiles表
const { error } = await supabase
  .from('profiles')
  .update({
    full_name: 'John Doe',
    bio: 'Software Developer',
  })
  .eq('id', user.id)

import { useState, useEffect } from 'react'
import { supabase } from '../lib/supabase'

interface Profile {
  id: string
  username: string
  full_name: string
  avatar_url: string
  bio: string
}

export function ProfileEditor() {
  const [profile, setProfile] = useState<Profile | null>(null)
  const [loading, setLoading] = useState(true)

  useEffect(() => {
    fetchProfile()
  }, [])

  async function fetchProfile() {
    const { data: { user } } = await supabase.auth.getUser()
    
    if (user) {
      const { data } = await supabase
        .from('profiles')
        .select('*')
        .eq('id', user.id)
        .single()
      
      setProfile(data)
    }
    setLoading(false)
  }

  async function updateProfile(updates: Partial<Profile>) {
    const { data: { user } } = await supabase.auth.getUser()
    
    if (user) {
      const { error } = await supabase
        .from('profiles')
        .update(updates)
        .eq('id', user.id)
      
      if (!error) {
        setProfile(prev => prev ? { ...prev, ...updates } : null)
      }
    }
  }

  if (loading) return <div>Loading...</div>
  if (!profile) return <div>No profile</div>

  return (
    <form onSubmit={(e) => {
      e.preventDefault()
      updateProfile(profile)
    }}>
      <input
        value={profile.username}
        onChange={(e) => setProfile({ ...profile, username: e.target.value })}
        placeholder="Username"
      />
      <input
        value={profile.full_name}
        onChange={(e) => setProfile({ ...profile, full_name: e.target.value })}
        placeholder="Full Name"
      />
      <textarea
        value={profile.bio}
        onChange={(e) => setProfile({ ...profile, bio: e.target.value })}
        placeholder="Bio"
      />
      <button type="submit">Save</button>
    </form>
  )
}

// 获取用户所有会话
const { data, error } = await supabase.auth.getSessions()

data?.sessions.forEach(session => {
  console.log('Session ID:', session.user.id)
  console.log('Created:', session.user.created_at)
})

// 刷新当前会话
const { data, error } = await supabase.auth.refreshSession()

// 或只刷新token
const { data, error } = await supabase.auth.refreshAccessToken()

// 登出当前设备
await supabase.auth.signOut()

// 登出其他设备
await supabase.auth.signOut({ scope: 'others' })

// 登出所有设备
await supabase.auth.signOut({ scope: 'global' })

-- 在profiles表添加角色字段
ALTER TABLE profiles ADD COLUMN role TEXT DEFAULT 'user';

-- 添加角色约束
ALTER TABLE profiles ADD CONSTRAINT valid_role 
    CHECK (role IN ('user', 'admin', 'moderator'));

-- 创建函数添加自定义JWT声明
CREATE OR REPLACE FUNCTION auth.jwt()
RETURNS JSONB AS $$
DECLARE
    claims JSONB;
BEGIN
    SELECT jsonb_build_object(
        'role', role,
        'plan', subscription_plan
    )
    INTO claims
    FROM profiles
    WHERE id = auth.uid();
    
    RETURN COALESCE(claims, '{}'::jsonb);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 管理员可以访问所有数据
CREATE POLICY "Admins have full access"
    ON posts FOR ALL
    USING (
        EXISTS (
            SELECT 1 FROM profiles
            WHERE id = auth.uid() AND role = 'admin'
        )
    );

-- 普通用户只能访问自己的数据
CREATE POLICY "Users can manage own posts"
    ON posts FOR ALL
    USING (author_id = auth.uid());

// 检查用户角色
async function checkUserRole(userId: string): Promise<string> {
  const { data } = await supabase
    .from('profiles')
    .select('role')
    .eq('id', userId)
    .single()
  
  return data?.role || 'user'
}

// 或从JWT获取
const { data: { user } } = await supabase.auth.getUser()
const role = user?.user_metadata?.role

// 服务端搜索用户
import { createClient } from '@supabase/supabase-js'

const supabaseAdmin = createClient(url, serviceRoleKey, {
  auth: { autoRefreshToken: false, persistSession: false },
})

// 列出用户
const { data, error } = await supabaseAdmin.auth.admin.listUsers({
  page: 1,
  perPage: 50,
})

// 搜索特定邮箱
const users = data.users.filter(u => 
  u.email?.includes('example.com')
)

-- 创建搜索函数
CREATE OR REPLACE FUNCTION search_users(search_query TEXT)
RETURNS SETOF profiles AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM profiles
    WHERE 
        username ILIKE '%' || search_query || '%' OR
        full_name ILIKE '%' || search_query || '%'
    LIMIT 20;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

// 调用搜索函数
const { data, error } = await supabase.rpc('search_users', {
  search_query: 'john'
})

// 用户删除自己的账号
async function deleteAccount() {
  const { data: { user } } = await supabase.auth.getUser()
  
  if (user) {
    // 1. 删除用户数据
    await supabase.from('profiles').delete().eq('id', user.id)
    await supabase.from('posts').delete().eq('author_id', user.id)
    
    // 2. 删除认证用户（需要服务端）
    // 这需要在服务端使用service_role key
  }
}

// 服务端删除用户
const { error } = await supabaseAdmin.auth.admin.deleteUser(userId)

-- 设置外键级联删除
ALTER TABLE posts
DROP CONSTRAINT posts_author_id_fkey,
ADD CONSTRAINT posts_author_id_fkey
    FOREIGN KEY (author_id) REFERENCES auth.users(id) ON DELETE CASCADE;

// 批量创建用户
async function importUsers(users: Array<{ email: string; name: string }>) {
  const results = []
  
  for (const user of users) {
    const { data, error } = await supabaseAdmin.auth.admin.createUser({
      email: user.email,
      email_confirm: true,
      user_metadata: {
        full_name: user.name,
      },
    })
    
    results.push({ email: user.email, success: !error, error })
  }
  
  return results
}

// 导出用户数据
async function exportUserData(userId: string) {
  const [profile, posts, comments] = await Promise.all([
    supabase.from('profiles').select('*').eq('id', userId).single(),
    supabase.from('posts').select('*').eq('author_id', userId),
    supabase.from('comments').select('*').eq('user_id', userId),
  ])
  
  return {
    profile: profile.data,
    posts: posts.data,
    comments: comments.data,
  }
}

// 敏感操作前验证密码
async function verifyPassword(email: string, password: string): Promise<boolean> {
  const { error } = await supabase.auth.signInWithPassword({
    email,
    password,
  })
  
  return !error
}

// 删除账号前验证
async function deleteAccountWithVerification(
  email: string, 
  password: string
) {
  const verified = await verifyPassword(email, password)
  
  if (!verified) {
    throw new Error('Invalid password')
  }
  
  // 执行删除...
}

-- 创建操作日志表
CREATE TABLE user_activity_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id),
    action TEXT NOT NULL,
    details JSONB,
    ip_address TEXT,
    user_agent TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 记录登录
CREATE OR REPLACE FUNCTION log_user_login()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_activity_logs (user_id, action)
    VALUES (NEW.id, 'login');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;