PostgreSQL角色权限未被用户继承问题排查求助

常见问题点 #

• 角色继承属性异常：PostgreSQL角色默认带INHERIT属性，但如果创建角色时显式指定NOINHERIT，用户继承后无法直接使用角色权限。
• 模式权限未授予：即使给角色配置了表权限，若未授予模式的USAGE权限，用户连模式的访问权限都没有，更无法操作表。
• 默认权限未配置：先创建角色再建表的情况下，新表不会自动继承角色权限，需手动设置默认权限规则。
• 权限未覆盖全对象：仅给表授权，但忽略了序列、视图等关联对象，会导致依赖操作（如自增ID写入）失败。
• 用户未切换目标模式：连接数据库后默认使用public模式，若操作test_schema下的表，需先切换搜索路径。

分步解决命令 #

1. 检查并修正角色继承属性

-- 查看目标角色的继承属性
SELECT rolname, rolinherit FROM pg_roles WHERE rolname IN ('full_access', 'read_only', 'test_schema_full_access', 'test_schema_read_only');

-- 若存在NOINHERIT，修改为INHERIT
ALTER ROLE full_access INHERIT;
ALTER ROLE test_schema_full_access INHERIT;
ALTER ROLE read_only INHERIT;
ALTER ROLE test_schema_read_only INHERIT;

2. 补全模式与对象权限

-- 全局full_access角色授权（替换your_db_name为实际数据库名）
GRANT CONNECT ON DATABASE your_db_name TO full_access;
GRANT USAGE ON SCHEMA public, test_schema TO full_access;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public, test_schema TO full_access;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public, test_schema TO full_access;

-- 模式级test_schema_full_access角色授权
GRANT USAGE ON SCHEMA test_schema TO test_schema_full_access;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA test_schema TO test_schema_full_access;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA test_schema TO test_schema_full_access;

-- 只读角色同理，仅授予SELECT和USAGE权限
GRANT CONNECT ON DATABASE your_db_name TO read_only;
GRANT USAGE ON SCHEMA public, test_schema TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public, test_schema TO read_only;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public, test_schema TO read_only;

3. 配置默认权限（后续新建对象自动继承）

-- 全局角色默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public, test_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO full_access;
ALTER DEFAULT PRIVILEGES IN SCHEMA public, test_schema GRANT USAGE, SELECT ON SEQUENCES TO full_access;

ALTER DEFAULT PRIVILEGES IN SCHEMA public, test_schema GRANT SELECT ON TABLES TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA public, test_schema GRANT USAGE, SELECT ON SEQUENCES TO read_only;

-- 模式级角色默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA test_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO test_schema_full_access;
ALTER DEFAULT PRIVILEGES IN SCHEMA test_schema GRANT USAGE, SELECT ON SEQUENCES TO test_schema_full_access;

ALTER DEFAULT PRIVILEGES IN SCHEMA test_schema GRANT SELECT ON TABLES TO test_schema_read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA test_schema GRANT USAGE, SELECT ON SEQUENCES TO test_schema_read_only;

4. 确认用户角色授予与模式切换

-- 重新授予用户角色（确保权限生效）
GRANT test_schema_full_access TO ex;
GRANT test_schema_read_only TO sec;

-- 用户连接数据库后，切换到目标模式（或配置为用户默认搜索路径）
SET search_path TO test_schema, public;

5. 验证用户权限

-- 切换到ex用户后执行，检查指定表的权限
SELECT current_user, 
       has_table_privilege('test_schema.your_table', 'SELECT'), 
       has_table_privilege('test_schema.your_table', 'INSERT');

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