基于订阅类型的Supabase策略实现：Todo应用待办事项插入权限控制方案

嘿，我来帮你搞定这个Supabase策略的问题！针对你的Todo应用需求，我们可以通过Row Level Security（RLS）策略和数据库触发器来实现身份验证+数量限制的双重校验，一步步来：

第一步：开启Row Level Security（RLS） #

首先得确保你的account和todo表都开启了RLS，这是Supabase权限控制的基础。你可以在Supabase控制台的Table Editor里找到对应表开启RLS，或者直接执行以下SQL：

ALTER TABLE public.account ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.todo ENABLE ROW LEVEL SECURITY;

第二步：自动创建用户账户记录 #

为了避免用户登录后没有对应的account记录（导致后续策略失效），我们可以给Supabase的auth.users表加一个触发器，用户注册成功后自动在account表生成默认记录：

CREATE OR REPLACE FUNCTION create_user_account()
RETURNS TRIGGER AS $$
BEGIN
  -- 默认给新用户分配普通订阅类型，待办计数初始为0
  INSERT INTO public.account (id, subscriptionType, toDoCount)
  VALUES (NEW.id, 'users', 0)
  ON CONFLICT (id) DO NOTHING; -- 避免重复创建
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_create_user_account
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION create_user_account();

第三步：设置Account表的访问策略 #

确保用户只能查看和修改自己的账户信息，避免越权：

CREATE POLICY "Users can access their own account" ON public.account
FOR SELECT TO authenticated
USING (auth.uid() = id);

第四步：核心Todo插入策略（关键！） #

这一步实现你要求的两个条件：用户已认证、未超出待办数量限制。直接给todo表创建INSERT策略：

CREATE POLICY "Authenticated users can insert todos within limit" ON public.todo
FOR INSERT TO authenticated
USING (
  -- 确保待办事项属于当前登录用户
  auth.uid() = userId
)
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.account
    WHERE account.id = auth.uid()
    AND (
      -- 专业用户无数量限制
      account.subscriptionType = 'pro'
      -- 普通用户：已创建的待办数小于20
      OR (
        account.subscriptionType = 'users'
        AND (SELECT COUNT(*) FROM public.todo WHERE todo.userId = auth.uid()) < 20
      )
    )
  )
);

这里的逻辑很清晰：

• 只允许已认证用户插入待办
• 插入的待办必须属于当前用户（防止替他人创建）
• 根据订阅类型判断：专业用户直接放行，普通用户则检查已有的待办数量是否小于20

第五步：同步Account表的toDoCount字段 #

因为你的account表有toDoCount字段，为了让它和实际待办数量保持一致（比如前端要显示剩余可创建数量），我们可以加两个触发器，分别在插入和删除待办时更新计数：

插入待办时递增计数

CREATE OR REPLACE FUNCTION increment_todo_count()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE public.account
  SET toDoCount = toDoCount + 1
  WHERE id = NEW.userId;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_increment_todo_count
AFTER INSERT ON public.todo
FOR EACH ROW
EXECUTE FUNCTION increment_todo_count();

删除待办时递减计数

CREATE OR REPLACE FUNCTION decrement_todo_count()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE public.account
  SET toDoCount = toDoCount - 1
  WHERE id = OLD.userId;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_decrement_todo_count
AFTER DELETE ON public.todo
FOR EACH ROW
EXECUTE FUNCTION decrement_todo_count();

最后验证一下 #

你可以在Supabase控制台的SQL Editor里执行这些语句，然后测试：

• 用普通用户登录，创建20条待办后，第21条应该插入失败
• 切换到专业用户，无论创建多少条都能成功
• 插入/删除待办后，account表的toDoCount应该同步更新

内容的提问来源于stack exchange，提问作者crushen