PostgreSQL中授权角色权限后用户查表仍提示permission denied求助

你执行的操作步骤如下：

1. 创建只读角色read_role：

CREATE ROLE read_role WITH
NOLOGIN
NOSUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION;

2. 为角色赋予intgr模式的表查询权限：

ALTER DEFAULT PRIVILEGES IN SCHEMA intgr GRANT SELECT ON TABLES TO read_role;
GRANT SELECT ON ALL TABLES IN SCHEMA intgr TO read_role;

3. 创建登录用户user并将其加入read_role：

CREATE ROLE user WITH
LOGIN
NOSUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION
ENCRYPTED PASSWORD '';

GRANT read_role TO user WITH ADMIN OPTION;

使用user登录查询intgr模式下的表时提示permission denied，问题大概率出在以下几个方面：

• 缺少模式的访问权限
  PostgreSQL要求用户必须拥有模式的USAGE权限，才能访问该模式下的任何对象。你只赋予了表的SELECT权限，但未授予模式的访问权限，执行以下命令补全：

  GRANT USAGE ON SCHEMA intgr TO read_role;
  

• 默认权限的应用范围限制
  ALTER DEFAULT PRIVILEGES仅对执行该命令之后创建的表生效，已存在的表靠GRANT SELECT ON ALL TABLES覆盖，但如果表是其他用户创建的，默认权限不会自动应用。若要覆盖其他所有者的默认权限，需要指定所有者角色：

  ALTER DEFAULT PRIVILEGES FOR ROLE 表所有者角色名 IN SCHEMA intgr GRANT SELECT ON TABLES TO read_role;
  

• 权限验证
  登录user后可以执行以下命令验证权限状态：

  • 确认当前会话用户：SELECT current_user, session_user;
  • 检查角色继承关系：SELECT * FROM pg_auth_members WHERE roleid = (SELECT oid FROM pg_roles WHERE rolname = 'read_role') AND member = (SELECT oid FROM pg_roles WHERE rolname = 'user');
  • 验证具体表的权限：SELECT has_table_privilege('user', 'intgr.你的表名', 'select');

• 空密码的潜在问题
  虽然空密码不是权限拒绝的直接原因，但部分PostgreSQL配置可能限制空密码登录，建议设置有效密码避免其他登录异常。

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