PostgreSQL 15.2：授予editor角色权限后adminuser仍无法访问表

此前已有类似问题，但我的情况在PostgreSQL 15.2上似乎较为特殊，可能忽略了某些配置。我以appowner用户创建数据库，尝试通过创建editor角色为adminuser用户授予查询权限。当以adminuser执行查询时，出现如下错误：

select * from roles;
ERROR:  permission denied for table roles

请问需要补充哪些权限配置，才能让被授予角色的用户拥有查询权限？

复现步骤 #

1. 启动PostgreSQL容器 #

docker run \
    --name myPostgresDb \
    -p 5455:5432 \
    -e POSTGRES_USER=mysuperuser \
    -e POSTGRES_PASSWORD=postgres \
    -e POSTGRES_DB=postgresDB \
    -d \
    postgres

2. 以超级用户（mysuperuser）执行命令 #

CREATE USER appowner WITH PASSWORD 'changeme' CREATEDB;

3. 以appowner用户连接并操作 #

create database testdb;
\c testdb
CREATE TABLE testtable(
   role_id serial PRIMARY KEY,
   role_name VARCHAR (255) UNIQUE NOT NULL
);
INSERT INTO testtable (role_id, role_name) values (1, 1);

4. 以超级用户执行以下命令 #

\c testdb
CREATE USER adminuser WITH PASSWORD 'changeme' CREATEDB;
create role editor;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO editor;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO editor;
GRANT USAGE ON SCHEMA public TO editor;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO editor;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO editor;
GRANT ALL PRIVILEGES ON TABLE testtable TO editor;

grant adminuser to editor;

5. 以adminuser用户（editor角色成员）执行查询 #

testdb=> select * from testtable;
ERROR:  permission denied for table testtable

问题原因及解决方法 #

1. 角色授予方向错误 #

你执行的grant adminuser to editor;是把adminuser角色赋予editor，而非让adminuser继承editor的权限。正确的命令应该是：

GRANT editor TO adminuser;

这条命令会将editor的所有权限赋予adminuser，是权限继承的正确写法。

2. 默认权限的作用范围（补充说明） #

ALTER DEFAULT PRIVILEGES默认仅对当前执行用户创建的对象生效。你用超级用户执行了这条命令，但testtable是appowner创建的，所以该默认权限对已存在的testtable无效。如果需要让后续appowner创建的表自动应用该权限，需指定用户：

ALTER DEFAULT PRIVILEGES FOR USER appowner IN SCHEMA public GRANT SELECT ON TABLES TO editor;

不过你已经直接给editor授予了testtable的全权限，所以这不是当前报错的直接原因，但后续创建新表时需要注意。

验证修复 #

执行正确的角色授予命令后，切换到adminuser重新查询即可正常获取数据：

testdb=> select * from testtable;
 role_id | role_name
---------+-----------
       1 | 1
(1 row)

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