PostgreSQL 14.17共享环境下如何限制角色仅查看指定授权数据库？

问题根源 #

你已撤销include_db2对pg_catalog.pg_database的权限，但默认public角色仍拥有该表的SELECT权限，include_db2（或其成员角色include_user2）会继承public的权限，因此仍能看到所有数据库。同时，\du显示所有用户是因为public角色对pg_catalog.pg_authid（存储角色信息的系统表）有默认访问权限。

解决方案步骤 #

1. 撤销public对系统表的默认权限 #

收回public角色对数据库列表和角色列表的访问权限，避免普通角色继承这些权限：

-- 撤销public查看所有数据库的权限
REVOKE SELECT ON pg_catalog.pg_database FROM public;

-- 撤销public查看所有角色的权限（可选，若需要隐藏其他用户）
REVOKE SELECT ON pg_catalog.pg_authid FROM public;

2. 给需要全局访问的角色重新授权 #

为不影响现有服务（如phpPgAdmin、超级用户），给相关角色重新授予系统表的访问权限：

-- 给超级用户postgres保留全权限
GRANT SELECT ON pg_catalog.pg_database TO postgres;
GRANT SELECT ON pg_catalog.pg_authid TO postgres;

-- 给phpPgAdmin使用的角色（假设为phpadmin）授予权限，确保其正常运行
-- GRANT SELECT ON pg_catalog.pg_database TO phpadmin;
-- GRANT SELECT ON pg_catalog.pg_authid TO phpadmin;

3. 给目标登录角色授予指定数据库的权限 #

由于include_db2设置了No inheritance且Cannot login，实际登录的是其成员角色include_user2，需要直接给include_user2授予目标数据库的CONNECT权限，这样它才能看到并访问这些数据库：

-- 授予include_user2访问include_db2数据库的权限
GRANT CONNECT ON DATABASE include_db2 TO include_user2;

-- 若需要让它访问include_rem数据库，同样授予权限
-- GRANT CONNECT ON DATABASE include_rem TO include_user2;

4. 验证效果 #

使用include_user2登录后执行以下命令：

-- 查看数据库列表，应仅显示已授权的数据库
\l

-- 查看角色列表，应仅显示自身及有权限查看的角色（若执行了步骤1的撤销pg_authid权限）
\du

关键说明 #

• PostgreSQL中，用户只能看到自己拥有CONNECT权限的数据库，当public的pg_database查询权限被撤销后，系统会自动过滤无权限的数据库。
• 角色的No inheritance属性意味着成员角色不会继承父角色的权限，因此需要直接给登录用的角色（include_user2）授予权限，而非仅给include_db2授权。
• 若需要保留对template1的访问（默认模板库），可单独给目标角色授予CONNECT权限：GRANT CONNECT ON DATABASE template1 TO include_user2;

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