PostgreSQL角色权限互通问题：双向访问权限配置咨询

这问题我维护数据库时也碰到过——PostgreSQL的角色继承默认是单向的：成员角色（这里是foo）会自动继承组角色（main）的权限，但反过来组角色并不会自动获得成员角色创建的对象权限，这就是你现在遇到的核心卡点。下面分两种方案帮你实现双向权限一致的需求：

方案一：直接给main授权foo的对象权限（快速解决现有场景） #

1. 配置默认权限，让foo未来创建的对象自动对main开放 #

默认情况下，用户创建的表、序列、函数等对象只有自己拥有全部权限，所以我们需要修改foo的默认权限规则，让它新建的所有对象自动给main赋权：

-- 针对表的默认权限
ALTER DEFAULT PRIVILEGES FOR ROLE foo IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO main;

-- 针对序列（比如自增ID对应的序列）的默认权限
ALTER DEFAULT PRIVILEGES FOR ROLE foo IN SCHEMA public
GRANT ALL PRIVILEGES ON SEQUENCES TO main;

-- 针对函数的默认权限
ALTER DEFAULT PRIVILEGES FOR ROLE foo IN SCHEMA public
GRANT ALL PRIVILEGES ON FUNCTIONS TO main;

注意：如果你的对象不在public schema下，把上面的public替换成实际使用的schema名称。

2. 给foo已创建的现有对象补全权限 #

比如你提到的testfoo表，需要单独给main授权：

-- 给testfoo表授权
GRANT ALL PRIVILEGES ON TABLE testfoo TO main;

-- 如果表有自增字段，对应的序列也要授权
GRANT ALL PRIVILEGES ON SEQUENCE testfoo_id_seq TO main;

如果foo创建了很多表，可以用下面的SQL批量生成授权语句，执行生成的结果即可：

SELECT 'GRANT ALL PRIVILEGES ON TABLE ' || tablename || ' TO main;'
FROM pg_tables
WHERE schemaname = 'public' AND tableowner = 'foo';

3. 确认main的默认权限（确保foo仍能访问main的对象） #

因为foo已经是main的成员，只要main的默认权限是给自己（组）开放的，foo就能继承。如果之前没配置过，可以补充：

ALTER DEFAULT PRIVILEGES FOR ROLE main IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO main;

方案二：创建共同组角色（更灵活的长期方案） #

如果未来可能还有其他用户需要加入这个权限组，推荐创建一个共同的无登录角色，把main和foo都加入进去，统一管理权限：

1. 创建共同组角色 #

CREATE ROLE main_foo_group NOLOGIN; -- NOLOGIN表示这个角色不能直接登录，仅用于权限管理

2. 将main和foo加入该组 #

GRANT main_foo_group TO main;
GRANT main_foo_group TO foo;

3. 配置默认权限，让两个用户新建的对象自动对组开放 #

-- main创建的对象默认给组授权
ALTER DEFAULT PRIVILEGES FOR ROLE main IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO main_foo_group;
ALTER DEFAULT PRIVILEGES FOR ROLE main IN SCHEMA public
GRANT ALL PRIVILEGES ON SEQUENCES TO main_foo_group;
ALTER DEFAULT PRIVILEGES FOR ROLE main IN SCHEMA public
GRANT ALL PRIVILEGES ON FUNCTIONS TO main_foo_group;

-- foo创建的对象默认给组授权
ALTER DEFAULT PRIVILEGES FOR ROLE foo IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO main_foo_group;
ALTER DEFAULT PRIVILEGES FOR ROLE foo IN SCHEMA public
GRANT ALL PRIVILEGES ON SEQUENCES TO main_foo_group;
ALTER DEFAULT PRIVILEGES FOR ROLE foo IN SCHEMA public
GRANT ALL PRIVILEGES ON FUNCTIONS TO main_foo_group;

4. 给现有所有对象补全组权限 #

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO main_foo_group;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO main_foo_group;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO main_foo_group;

验证效果 #

完成配置后，切换到main用户执行：

SELECT * FROM testfoo;

如果能正常查询，说明权限生效；再切换到foo用户访问main创建的表，确认依然正常即可。

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