如何枚举PostgreSQL中所有用户的所有已启用角色？ [英] How to enumerate all enabled roles for all users in PostgreSQL?

问题描述

如果要检查用户有权访问的角色，PostgreSQL中没有简单的方法。在 information_schema 中，存在关系 enabled_roles 和适用角色，但是这些仅提供 current_user 的特权。那么，如何为任何用户访问相同的信息？

If you want to check the roles that a user has access to, there is no easy way in PostgreSQL. In the information_schema there are relations enabled_roles and applicable roles but these only provide the privileges of the current_user. So how can I access the same information for any user?

推荐答案

诀窍是使系统目录关系 pg_roles 和 pg_auth_members 的递归查询：

The trick is to make a recursive query over the system catalog relations pg_roles and pg_auth_members:

WITH RECURSIVE membership_tree(grpid, userid) AS (
    -- Get all roles and list them as their own group as well
    SELECT pg_roles.oid, pg_roles.oid
    FROM pg_roles
  UNION ALL
    -- Now add all group membership
    SELECT m_1.roleid, t_1.userid
    FROM pg_auth_members m_1, membership_tree t_1
    WHERE m_1.member = t_1.grpid
)
SELECT DISTINCT t.userid, r.rolname AS usrname, t.grpid, m.rolname AS grpname
FROM membership_tree t, pg_roles r, pg_roles m
WHERE t.grpid = m.oid AND t.userid = r.oid
ORDER BY r.rolname, m.rolname;

这提供了系统中具有所有继承角色成员身份的所有用户的视图。将其包装起来，以便始终可以使用该实用程序。

This gives a view of all users in the system with all inherited role memberships. Wrap this in a view to have this utility always handy.

干杯，
Patrick

Cheers, Patrick

这篇关于如何枚举PostgreSQL中所有用户的所有已启用角色？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！