PostgreSQL普通用户无法访问pgcrypto加密函数问题求助

问题场景 #

我有一个已安装pgcrypto扩展的PostgreSQL数据库：

postgres=# CREATE EXTENSION IF NOT EXISTS pgcrypto;
NOTICE:  extension "pgcrypto" already exists, skipping
CREATE EXTENSION

使用管理员用户登录时，可正常使用该扩展：

postgres=# select public.gen_salt('bf', 8);
       gen_salt            
-------------------------------
 $2_SOME_SALT_Qu
(1 row)

查看扩展信息，确认其位于public schema中：

postgres=# \dx
                  List of installed extensions
   Name   | Version |   Schema   |         Description          
----------+---------+------------+------------------------------
 pgcrypto | 1.3     | public     | cryptographic functions

但普通数据库用户调用加密函数时失败：

user=> select gen_salt('bf', 8);
ERROR:  function gen_salt(unknown, integer) does not exist
LINE 1: select gen_salt('bf', 8);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

即使指定schema调用public.gen_salt也会出现同样错误。

解决方法 #

1. 确保普通用户拥有public schema的使用权限 #

以管理员身份登录数据库，执行以下命令，授予所有用户对public schema的使用权限：

GRANT USAGE ON SCHEMA public TO PUBLIC;

注：这里的PUBLIC是PostgreSQL内置角色，代表所有数据库用户。

2. 授予pgcrypto函数的执行权限 #

pgcrypto扩展的函数默认仅对安装该扩展的用户（通常是超级用户）开放执行权限，需要手动将权限授予所有用户：

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO PUBLIC;

如果后续在public schema中新增pgcrypto相关函数，可设置默认权限，让新函数自动开放给所有用户：

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO PUBLIC;

3. 验证配置 #

切换到普通用户，再次执行测试命令：

select public.gen_salt('bf', 8);

此时应能正常返回盐值，报错问题解决。

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