PostgreSQL 15：通过角色组配置用户只读权限失效问题咨询

核心原因 #

PostgreSQL中所有用户默认都是PUBLIC角色的成员，权限采用累加规则——用户会继承所有所属角色（包括PUBLIC）的权限。你遇到的问题根源在于：

1. my_user 除了属于 readonly_role，还默认继承了PUBLIC角色的权限
2. 在PostgreSQL 15中，public模式默认给PUBLIC角色授予了CONNECT和TEMP权限；若你的数据库是从旧版本升级而来，public模式可能还保留了旧默认权限（比如CREATE），这会让my_user拥有在public模式下创建对象的权限
3. CREATE USER 语句默认会给用户添加LOGIN权限，而你的readonly_role默认不带LOGIN属性（CREATE ROLE默认无此权限），这虽不属于"全部权限"范畴，但也是需要注意的细节

修正步骤 #

要实现真正的只读用户，需完成以下操作：

1. 撤销PUBLIC角色在public模式的多余权限（推荐） #

若不需要所有用户都能在public模式创建对象，先撤销PUBLIC的CREATE权限：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

2. 配置对未来表的默认只读权限 #

你当前的GRANT SELECT ON ALL TABLES仅对已存在的表生效，需设置默认权限让未来创建的表也能被readonly_role读取：

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role;

3. 移除my_user的不必要权限 #

若my_user已被赋予额外权限（比如CREATE），直接撤销：

REVOKE CREATE ON SCHEMA public FROM my_user;

4. 规范创建只读用户（可选） #

确保readonly_role本身无法登录，创建用户时明确继承只读角色：

-- 确保readonly_role无登录权限（默认已满足）
ALTER ROLE readonly_role NOLOGIN;

-- 创建用户并加入只读角色
CREATE USER my_user IN ROLE readonly_role;

权限验证 #

可通过以下语句检查my_user的权限：

-- 检查用户对表的权限
SELECT grantee, privilege_type FROM information_schema.table_privileges WHERE grantee = 'MY_USER';
-- 检查用户对模式的权限
SELECT grantee, privilege_type FROM information_schema.schema_privileges WHERE grantee = 'MY_USER';

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