如果您想检查用户可以访问的角色,在PostgreSQL中没有简单的方法。在information_schema
中有关系enabled_roles
和applicable roles
,但这些只提供了current_user
的权限。那么我如何访问任何用户的相同信息呢?
如果您想检查用户可以访问的角色,在PostgreSQL中没有简单的方法。在information_schema
中有关系enabled_roles
和applicable roles
,但这些只提供了current_user
的权限。那么我如何访问任何用户的相同信息呢?
pg_roles
和 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;
这对我非常有帮助,因为我正在寻找这种类型的信息。将上述工作调整为包括一个级别以跟踪继承。
WITH RECURSIVE membership_tree(grpid, userid, lvl) AS (
-- Get all roles and list them as their own group as well
SELECT
pg_roles.oid
, pg_roles.oid
, 0
FROM
pg_roles
UNION ALL
-- Now add all group membership
SELECT
m_1.roleid
, t_1.userid
, lvl + 1
FROM
pg_auth_members m_1
INNER JOIN
membership_tree t_1
ON
m_1.member = t_1.grpid
)
SELECT DISTINCT
t.userid
, r.rolname AS usrname
, t.grpid
, m.rolname AS grpname
, t.lvl
FROM
membership_tree t
INNER JOIN
pg_roles r
ON
t.userid = r.oid
INNER JOIN
pg_roles m
ON
t.grpid = m.oid
ORDER BY
r.rolname
, t.lvl
, m.rolname;