PostgreSQL角色权限问题：同角色用户无法查询对方创建的表

操作步骤 #

PS C:\> psql -h ora05lw -U dba -d kuku
psql (16.0, server 13.3)

kuku=# create role r1 with login;
CREATE ROLE
kuku=# create user u1 with password 'aaaaaa';
CREATE ROLE
kuku=# create user u2 with password 'aaaaaa';
CREATE ROLE
kuku=# grant r1 to u1;
GRANT ROLE
kuku=# grant r1 to u2;
GRANT ROLE
kuku=# create schema s1;
CREATE SCHEMA
kuku=# grant all privileges on schema s1 to r1;
GRANT
kuku=# ALTER DEFAULT PRIVILEGES IN SCHEMA s1 GRANT SELECT ON TABLES TO r1;
ALTER DEFAULT PRIVILEGES
kuku=# \q
PS C:\> psql -h ora05lw -U u1 -d kuku
psql (16.0, server 13.3)

kuku=> create table s1.kuku (one integer);
CREATE TABLE
kuku=> \q
PS C:\> psql -h ora05lw -U u2 -d kuku
psql (16.0, server 13.3)

kuku=> select * from "s1"."kuku";
ERROR:  permission denied for table kuku
kuku=> \q

原因解析 #

你执行的ALTER DEFAULT PRIVILEGES IN SCHEMA s1 GRANT SELECT ON TABLES TO r1;仅对**执行该命令的用户（此处为dba）**后续在S1中创建的表生效，不会作用于其他用户（比如U1）创建的表。

U1作为独立用户在S1中创建表时，该表的默认权限仅开放给U1自身，R1角色并未获得该表的SELECT权限，因此继承R1的U2自然无法查询这张表。

解决方法 #

若要让U1在S1中创建的表自动赋予R1 SELECT权限，需要针对U1角色设置默认权限：

ALTER DEFAULT PRIVILEGES FOR ROLE u1 IN SCHEMA s1 GRANT SELECT ON TABLES TO r1;

或者切换到U1用户执行ALTER DEFAULT PRIVILEGES IN SCHEMA s1 GRANT SELECT ON TABLES TO r1;，这样U1后续创建的表都会自动给R1开放SELECT权限，U2就能正常查询了。

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