PostgreSQL中实现PUBLIC与PRIVATE FUNCTIONS、限制访问的惯用方法是什么？

核心思路是通过PostgreSQL原生的角色权限体系+函数安全属性实现隔离，不需要依赖托管平台的额外特性，完全兼容标准PG 13+版本。

第一步：角色分层配置 #

首先创建3个逻辑隔离的角色，避免权限溢出：

• 函数所有者角色（比如命名为func_owner）：所有public/private函数的创建者，拥有所有函数的所有权，不要对外暴露该角色的凭据。
• 应用访问角色（比如命名为app_user）：客户端/应用连接数据库时唯一使用的角色，仅开放最低必要权限。
• 私有资源访问角色（比如命名为private_func_executor）：仅拥有所有私有函数的执行权限，不对外暴露。

第二步：私有函数权限限制 #

所有私有函数统一放到单独的schema下管理（比如private schema，不要用默认的public schema），然后做如下权限配置：

1. 撤销所有公共角色对private schema的访问权限：

REVOKE ALL ON SCHEMA private FROM PUBLIC;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA private FROM PUBLIC;

2. 仅给func_owner和private_func_executor开放私有函数的执行权限：

GRANT USAGE ON SCHEMA private TO func_owner, private_func_executor;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA private TO func_owner, private_func_executor;
-- 配置默认权限，后续新增的私有函数自动继承权限
ALTER DEFAULT PRIVILEGES FOR ROLE func_owner IN SCHEMA private GRANT EXECUTE ON FUNCTIONS TO private_func_executor;

第三步：公开函数配置 #

3个需要对外暴露的公开函数放到public schema下，配置为安全定义器（SECURITY DEFINER），指定运行时角色为private_func_executor，这样公开函数运行时可以调用私有函数，而应用角色本身没有私有函数的访问权限：

1. 创建公开函数时添加安全属性：

CREATE OR REPLACE FUNCTION public.your_public_func()
RETURNS xxx AS $$
-- 函数逻辑，内部可以直接调用private schema下的私有函数
$$ LANGUAGE plpgsql
SECURITY DEFINER -- 核心配置：运行时使用指定角色的权限
SET ROLE = private_func_executor; -- 限定运行时角色，避免权限溢出

注意：配置SECURITY DEFINER的函数需要严格校验入参，避免存在SQL注入漏洞，导致权限溢出。

2. 仅给app_user开放3个公开函数的执行权限，同时撤销public角色对公开函数的默认执行权限：

REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;
-- 单独给应用角色开放指定3个函数的权限
GRANT USAGE ON SCHEMA public TO app_user;
GRANT EXECUTE ON FUNCTION public.func1(), public.func2(), public.func3() TO app_user;

第四步：验证配置 #

用app_user角色连接数据库测试：

• 执行3个公开函数可以正常运行
• 直接调用private schema下的函数会提示权限不足
• 执行\df查看函数列表时，仅能看到3个公开函数的元信息，看不到私有函数

Supabase环境适配说明 #

如果是Supabase环境，默认的anon（匿名用户）和authenticated（登录用户）角色对应上面的app_user，直接把权限授予给这两个角色即可，不需要额外创建应用角色。

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