PostgreSQL非超级用户可用扩展创建及权限配置问题咨询

• 首先pgcrypto属于PostgreSQL内置的高安全等级扩展，官方要求必须由超级用户才能执行创建操作，该限制无法绕过，所以不能直接用应用用户创建。
• 你用超级用户创建后应用用户无法访问，通常是两个原因：扩展被安装到了应用用户的search_path不包含的schema，或者应用用户没有扩展所在schema的使用、函数执行权限。
• 之前uuid-ossp可以正常创建使用，是因为多数PostgreSQL发行版（含云托管版本）默认开放了普通用户创建该扩展的权限，且默认安装到公开schema。

1. 首先确认pgcrypto扩展当前所在的schema

SELECT extname, nspname 
FROM pg_extension 
JOIN pg_namespace ON pg_extension.extnamespace = pg_namespace.oid 
WHERE extname = 'pgcrypto';

2. 建议统一将扩展迁移到public schema，方便所有用户访问

ALTER EXTENSION pgcrypto SET SCHEMA public;

3. 给应用用户mydb开放对应权限

-- 授予public schema访问权限
GRANT USAGE ON SCHEMA public TO "mydb";
-- 授予public schema下所有函数的执行权限，包含pgcrypto的加密相关函数
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO "mydb";
-- 可选：配置默认权限，后续新增到public schema的函数自动开放执行权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO "mydb";

4. 可选：如果应用用户的搜索路径未包含public，永久设置搜索路径

ALTER ROLE "mydb" SET search_path TO public;

如果你希望和业务schema做隔离，可以单独创建extensions专属schema存放所有公共扩展，操作逻辑和上面一致，只需要将上述步骤中的public替换为你新建的extensions schema即可。

内容的提问来源于stack exchange，提问作者Mr. Developerdude