PostgreSQL(Supabase)：如何禁止全表查询，仅允许带WHERE条件的查询

方法一：修改RLS SELECT策略（简单但有局限性） #

直接调整现有RLS策略，禁止无过滤条件的全表查询，仅允许用户查询自己的行，或查询时指定id/name的过滤条件：

-- 删除原有SELECT策略（若存在）
DROP POLICY IF EXISTS "Allow select profile" ON profile;

-- 创建新的SELECT策略
CREATE POLICY "Allow select own profile or targeted by id/name" ON profile
FOR SELECT USING (
  -- 允许用户查询自己的行
  id = auth.uid()
  -- 非所有者用户必须在WHERE子句中指定id或name的过滤条件
  OR (
    current_query() ~* '\WHERE\s+'
    AND (current_query() ~* '\bid\s*=\s*' OR current_query() ~* '\bname\s*=\s*')
  )
);

注意：该方法依赖正则匹配查询语句，存在少量局限性——比如用户若构造WHERE id IS NOT NULL这类语句，也会匹配正则但实际是全表查询。如果你的场景中不存在恶意构造查询的情况，这个方法足够简单高效。

方法二：创建安全定义器函数（更可靠） #

通过PL/pgSQL函数强制客户端必须指定id或name才能查询他人行，同时允许用户查询自己的行。这种方法彻底避免了直接查询表的可能，安全性更高：

步骤1：创建查询函数 #

CREATE OR REPLACE FUNCTION get_profile(p_id uuid DEFAULT NULL, p_name text DEFAULT NULL)
RETURNS SETOF profile AS $$
BEGIN
  -- 未指定id/name时，返回自身行
  IF p_id IS NULL AND p_name IS NULL THEN
    RETURN QUERY SELECT * FROM profile WHERE id = auth.uid();
  -- 指定id时返回对应行
  ELSIF p_id IS NOT NULL THEN
    RETURN QUERY SELECT * FROM profile WHERE id = p_id;
  -- 指定name时返回对应行
  ELSIF p_name IS NOT NULL THEN
    RETURN QUERY SELECT * FROM profile WHERE name = p_name;
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 设置函数所有者为supabase_admin确保权限充足
ALTER FUNCTION get_profile(uuid, text) OWNER TO supabase_admin;

-- 授予认证用户调用函数的权限
GRANT EXECUTE ON FUNCTION get_profile(uuid, text) TO authenticated;

步骤2：限制表的直接查询权限 #

-- 撤销认证用户对profile表的直接SELECT权限
REVOKE SELECT ON profile FROM authenticated;

步骤3：客户端调用示例 #

用Supabase JS客户端直接调用函数：

// 查询自己的profile
const { data: myProfile, error } = await supabase.rpc('get_profile');

// 通过id查询他人profile
const { data: profileById, error } = await supabase.rpc('get_profile', { p_id: '目标用户uuid' });

// 通过name查询他人profile
const { data: profileByName, error } = await supabase.rpc('get_profile', { p_name: '目标用户名' });

这种方法完全杜绝了全表查询的可能，因为用户无法直接访问表，只能通过函数查询，而函数强制了查询条件，是更稳妥的解决方案。

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