基于Supabase+PostgreSQL的用户表单访问权限高效查询方案咨询

现有数据表结构 #

users表 #

id | name
---------
1  | Ben
2  | Josh

groups表 #

id | name
-----------
1  | Group 1
2  | Group 2

group_users表（用户-组关联表） #

id | group_id | user_id
-----------------------
1  | 1        | 1

forms表 #

id | name
---------------
1  | Contact Us
2  | Questions

form_access表（表单权限分配表） #

id | form_id | user_id | group_id
----------------------------------
1  | 1       | 1       | null
2  | 2       | null    | 1

当前实现方案 #

表单访问权限可分配给单个用户或整个用户组。我使用Supabase搭配PostgreSQL，由于supabase-js客户端对这类查询支持有限，创建了以下视图来获取用户可访问的所有表单：

SELECT
  form_access.user_id,
  group_users.user_id as group_user_id,
  forms.*
FROM
  forms
  INNER JOIN form_access ON form_access.form_id = forms.id
  LEFT JOIN group_users ON group_users.group_id = form_access.group_id

之后通过查询该视图中user_id或group_user_id等于当前认证用户ID来获取结果。

需求 #

• 寻求更高效的实现方案或优化建议
• 实现数据的分页功能
• 验证指定form_id对应用户是否具备访问权限

优化方案与功能实现 #

1. 优化权限查询逻辑（替代视图的高效写法） #

原视图会产生大量冗余数据（比如同一个表单会因组内多个用户重复出现），可以直接用SQL查询替代视图，避免冗余同时利用索引提升性能：

SELECT DISTINCT forms.*
FROM forms
JOIN form_access ON forms.id = form_access.form_id
LEFT JOIN group_users ON form_access.group_id = group_users.group_id
WHERE 
  form_access.user_id = $1 -- 当前用户ID
  OR group_users.user_id = $1

索引优化建议

为加速查询，给以下字段创建索引：

• form_access(user_id, form_id)：加速单个用户的权限匹配
• form_access(group_id, form_id)：加速组权限的匹配
• group_users(group_id, user_id)：加速用户与组的关联查询

2. 分页功能实现 #

在Supabase中可以通过两种方式实现分页，键集分页更适合大数据量场景：

基础分页（LIMIT + OFFSET）

SELECT DISTINCT forms.*
FROM forms
JOIN form_access ON forms.id = form_access.form_id
LEFT JOIN group_users ON form_access.group_id = group_users.group_id
WHERE 
  form_access.user_id = $1 
  OR group_users.user_id = $1
ORDER BY forms.id
LIMIT 10 OFFSET 0 -- 每页10条，第一页

键集分页（高效无偏移）

SELECT DISTINCT forms.*
FROM forms
JOIN form_access ON forms.id = form_access.form_id
LEFT JOIN group_users ON form_access.group_id = group_users.group_id
WHERE 
  (form_access.user_id = $1 OR group_users.user_id = $1)
  AND forms.id > $2 -- 上一页最后一条的表单ID
ORDER BY forms.id
LIMIT 10

3. 验证指定表单的访问权限 #

可以通过简单查询返回布尔值判断权限，也可以封装为Supabase RPC函数方便前端调用：

直接查询验证

SELECT EXISTS (
  SELECT 1
  FROM form_access
  LEFT JOIN group_users ON form_access.group_id = group_users.group_id
  WHERE 
    form_access.form_id = $1 -- 要验证的表单ID
    AND (form_access.user_id = $2 OR group_users.user_id = $2) -- 当前用户ID
) AS has_access

封装为RPC函数

CREATE OR REPLACE FUNCTION check_form_access(p_form_id INT, p_user_id INT)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM form_access
    LEFT JOIN group_users ON form_access.group_id = group_users.group_id
    WHERE 
      form_access.form_id = p_form_id
      AND (form_access.user_id = p_user_id OR group_users.user_id = p_user_id)
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

前端调用示例：

const { data, error } = await supabase.rpc('check_form_access', {
  p_form_id: 1,
  p_user_id: currentUser.id
});

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