如何在Java创建PostgreSQL用户时撤销其函数执行权限

问题核心原因 #

你当前的权限撤销操作存在两个关键问题：

1. 语法错误：PostgreSQL中用户名无需加单引号，'readonlyuser'会被识别为字符串而非角色，导致权限撤销命令未生效。
2. 权限覆盖不全：REVOKE EXECUTE ON FUNCTIONS是SQL标准语法，但PostgreSQL需要指定具体模式才能覆盖所有函数；另外public角色的默认权限会被用户继承，部分系统函数的执行权限默认开放给public，需要针对性处理。

正确的权限处理步骤 #

1. 修正用户创建SQL（移除用户名的单引号）

String sql = "CREATE USER readonlyuser WITH ENCRYPTED PASSWORD 'pass';";
Statement stmt = connection.createStatement();
stmt.execute(sql);

2. 撤销指定模式下的函数执行权限

以默认的public模式为例，先撤销用户及public角色的权限：

// 撤销readonlyuser对public模式所有函数的执行权限
stmt.execute("REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM readonlyuser;");
// 撤销public角色的函数执行权限，防止用户继承
stmt.execute("REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM public;");

若存在其他自定义模式，替换public为对应模式名称重复执行即可。

3. 针对性处理系统函数权限

系统函数（如pg_sleep）通常存放在pg_catalog模式下，需指定完整函数签名（PostgreSQL按签名区分同名函数）：

// 撤销pg_sleep（接受integer参数版本）的执行权限
stmt.execute("REVOKE EXECUTE ON FUNCTION pg_catalog.pg_sleep(integer) FROM readonlyuser;");
stmt.execute("REVOKE EXECUTE ON FUNCTION pg_catalog.pg_sleep(integer) FROM public;");

4. 阻止新函数自动开放权限

设置默认权限，避免后续创建的函数自动授予public角色执行权限：

stmt.execute("ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM public;");

验证权限是否生效 #

执行以下SQL查询目标函数的权限状态，确认是否还存在readonlyuser或public的执行权限标记（X）：

SELECT proname, proacl 
FROM pg_proc 
WHERE proname = 'pg_sleep' AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog');

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