Postgres角色权限查询及新用户权限规则技术问询

1. 如何查询某个角色/用户的所有权限列表？ #

Postgres没有单一语句能返回全对象类型的权限，需针对不同对象类型分别查询，以下是常用场景的查询语句：

数据库级权限 #

查询目标角色在数据库上的权限：

SELECT grantee, privilege_type, is_grantable
FROM information_schema.database_privileges
WHERE grantee = 'public'; -- 替换为目标角色/用户名

表与视图权限 #

查询目标角色在所有表、视图上的权限：

SELECT table_schema, table_name, privilege_type, is_grantable
FROM information_schema.table_privileges
WHERE grantee = 'public';

序列权限 #

查询目标角色在序列上的权限：

SELECT sequence_schema, sequence_name, privilege_type, is_grantable
FROM information_schema.sequence_privileges
WHERE grantee = 'public';

函数与存储过程权限 #

查询目标角色在函数、存储过程上的权限：

SELECT specific_schema, specific_name, privilege_type, is_grantable
FROM information_schema.routine_privileges
WHERE grantee = 'public';

继承的权限 #

查看角色通过继承其他角色获得的权限：

SELECT 
    member.rolname AS target_role,
    role.rolname AS inherited_from,
    p.privilege_type
FROM pg_roles member
JOIN pg_auth_members am ON member.oid = am.member
JOIN pg_roles role ON am.roleid = role.oid
JOIN information_schema.database_privileges p ON role.rolname = p.grantee
WHERE member.rolname = '目标角色名';

2. 新创建的Postgres用户是否仅拥有从默认public角色继承的权限？ #

默认情况下，未指定特殊属性的普通新用户仅继承public角色的权限，无额外权限：

• Postgres所有用户默认都是public角色的成员，会自动继承该角色的权限，比如默认对新创建的表有SELECT权限、对数据库的CONNECT权限等。
• 若没有额外授予权限或指定特殊属性，新用户只能执行public角色允许的操作。

例外情况：

• 创建用户时指定SUPERUSER属性，该用户会获得Postgres的所有权限，不受public角色限制。
• 指定CREATEDB、CREATEROLE、REPLICATION等属性，用户会获得对应的创建数据库、创建角色、复制数据的权限，这些权限并非来自public角色。
• 管理员可在创建用户后直接授予额外权限，此时用户会拥有超出public继承范围的权限。

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