是否可限制PostgreSQL security definer函数仅用于RLS场景？

解决方案 #

方法1：将函数移出public schema（最推荐，从根源禁止RPC调用）

Supabase的RPC接口默认只会暴露public schema下的函数给外部调用，你只要把这类仅供RLS使用的函数创建到非public的自定义schema（比如专门建一个internal schema），外部就完全无法通过RPC调用，而数据库内部的RLS策略依然可以正常调用该函数，无任何性能影响。
操作步骤：

1. 先创建私有schema：

CREATE SCHEMA IF NOT EXISTS internal;
-- 确保RLS执行时有权限访问该schema下的函数
GRANT USAGE ON SCHEMA internal TO authenticated, anon;

2. 把函数创建到internal schema下：

CREATE OR REPLACE FUNCTION internal.get_bases_editable_or_viewable_for_user(user_id uuid, allow_edit bool)
returns setof bigint as $$
  select base_id
  from access_controls
  where access_controls.user_id = $1 AND ($2 AND access_controls.access_level = 'editor') OR access_controls.access_level = 'viewer';
$$ stable language sql security definer;
-- 收回公开执行权限，仅允许内部授权角色调用
REVOKE ALL ON FUNCTION internal.get_bases_editable_or_viewable_for_user FROM public;
GRANT EXECUTE ON FUNCTION internal.get_bases_editable_or_viewable_for_user TO authenticated, anon;

3. RLS策略调用时带上schema名即可：

CREATE policy "Users can read bases they are editors or viewers of"
on public.bases
for select using ( bases.id in (internal.get_bases_editable_or_viewable_for_user(auth.uid(), true)) );

方法2：函数内部增加权限校验

如果必须把函数放在public schema，可在函数逻辑开头加权限校验，两种校验规则可选：

• 限制普通用户只能查询自身信息，仅管理员可查询任意用户：

CREATE OR REPLACE FUNCTION get_bases_editable_or_viewable_for_user(user_id uuid, allow_edit bool)
returns setof bigint as $$
BEGIN
  -- 非管理员用户只能查询自己的权限
  IF NOT pg_has_role(current_user, 'supabase_admin', 'member') AND user_id != auth.uid() THEN
    RAISE EXCEPTION 'Permission denied: you can only query your own permissions';
  END IF;
  RETURN QUERY
  select base_id
  from access_controls
  where access_controls.user_id = $1 AND ($2 AND access_controls.access_level = 'editor') OR access_controls.access_level = 'viewer';
END;
$$ stable language plpgsql security definer;

• 完全禁止外部RPC调用，仅允许RLS内部调用：通过判断是否为外部RPC请求实现，Supabase的外部RPC请求会自动设置request.method为POST，内部RLS执行无该参数：

CREATE OR REPLACE FUNCTION get_bases_editable_or_viewable_for_user(user_id uuid, allow_edit bool)
returns setof bigint as $$
BEGIN
  -- 拦截外部RPC调用
  IF current_setting('request.method', true) = 'POST' THEN
    RAISE EXCEPTION 'This function cannot be called via RPC';
  END IF;
  RETURN QUERY
  select base_id
  from access_controls
  where access_controls.user_id = $1 AND ($2 AND access_controls.access_level = 'editor') OR access_controls.access_level = 'viewer';
END;
$$ stable language plpgsql security definer;

方法3：直接收回函数的公开执行权限

你也可以直接撤销public角色对该函数的执行权限，仅保留给数据库内部RLS执行所需的系统角色：

REVOKE EXECUTE ON FUNCTION get_bases_editable_or_viewable_for_user FROM public, anon, authenticated;
-- 仅给RLS权限校验角色授权
GRANT EXECUTE ON FUNCTION get_bases_editable_or_viewable_for_user TO pg_check_permissions;

该方法效果和方法1类似，更适合个别函数的临时权限管控。

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