如何获得用户所属的所有角色（包括继承的角色）？

假设我有两个Postgresql数据库组，即“作者”和“编辑”，以及两个用户，“ maxwell”和“ ernest”。

create role authors;

create role editors;

create user maxwell;

create user ernest;

grant authors to editors; --editors can do what authors can do

grant editors to maxwell; --maxwell is an editor

grant authors to ernest; --ernest is an author

我想编写一个性能函数，该函数返回maxwell所属角色的列表（最好是其oid），例如：

create or replace function get_all_roles() returns oid[] ...

它应该返回maxwell的oid，作者和编辑（但不是最认真的人）。

但是我不确定在继承时如何去做。

#1 楼

您可以使用递归查询来查询系统目录，尤其是pg_auth_members：

WITH RECURSIVE cte AS (
   SELECT oid FROM pg_roles WHERE rolname = 'maxwell'

   UNION ALL
   SELECT m.roleid
   FROM   cte
   JOIN   pg_auth_members m ON m.member = cte.oid
   )
SELECT oid, oid::regrole::text AS rolename FROM cte;  -- oid & name

有关强制转换为对象标识符类型regrole的手册。

BTW 1：INHERIT是CREATE ROLE的默认行为，不必说明。

BTW 2：不可能存在循环依赖性。 Postgres不允许这样做。因此我们不必检查。

如何查看角色名称而不是ID？

– david.perez
20-2-19在11:04

@ david.perez：我在上面添加了角色名称和一些说明。

–欧文·布兰德斯特（Erwin Brandstetter）
20-2-21在19:20

#2 楼

这是Craig Ringer答案的简化版本，非超级用户可以直接使用：

 SELECT oid, rolname FROM pg_roles WHERE
   pg_has_role( 'maxwell', oid, 'member');

pg_roles本质上是pg_authid的视图，公众可以访问，因为它不是显示密码，与pg_authid相反。基本的oid甚至被导出到视图中。当不需要密码时，没有必要创建专用的超级用户所有功能。

#3 楼

简短版本：

SELECT a.oid 
FROM pg_authid a 
WHERE pg_has_role('maxwell', a.oid, 'member');

这里我们使用的pg_has_role版本以角色名称作为主题和角色oid来测试成员资格，并通过member模式进行测试

使用pg_has_role的优点是它使用PostgreSQL的角色信息的内部缓存来快速满足成员资格查询。

您可能希望将其包装在SECURITY DEFINER函数中，因为pg_authid具有受限的访问权限。像这样的东西：

CREATE OR REPLACE FUNCTION user_role_memberships(text)
RETURNS SETOF oid
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$
SELECT a.oid 
FROM pg_authid a 
WHERE pg_has_role(, a.oid, 'member');
$$;

REVOKE EXECUTE ON FUNCTION user_role_memberships(text) FROM public;

GRANT EXECUTE ON FUNCTION user_role_memberships(text) TO ...whoever...;

您可以使用pg_get_userbyid(oid)从oid中获取角色名称，而无需查询pg_authid：

SELECT a.oid AS member_oid, pg_get_userbyid(oid) AS member_name
FROM pg_authid a 
WHERE pg_has_role('maxwell', a.oid, 'member');

无论如何+1，因为pg_has_role（）可能比我的递归查询快一点，即使这并不重要。但是，最后一件事：它返回了超级管理员的所有角色，这可能是也可能不是受欢迎的副作用。那就是结果与我的查询不同的地方。

–欧文·布兰德斯特（Erwin Brandstetter）
2014年1月4日在1:31

#4 楼

这是我的看法。
它适用于一个特定用户或所有用户。

select a.oid as user_role_id
, a.rolname as user_role_name
, b.roleid as other_role_id
, c.rolname as other_role_name
from pg_roles a
inner join pg_auth_members b on a.oid=b.member
inner join pg_roles c on b.roleid=c.oid 
where a.rolname = 'user_1'

如果您解释了与先前答案的区别和改进之处，将会大大改善。您可以将其他信息直接编辑到答案中。

–迈克尔·格林（Michael Green）
19年2月24日在10:07

#5 楼

我相信这可以做到。

SELECT 
    oid 
FROM 
    pg_roles 
WHERE 
    oid IN (SELECT 
                roleid 
            FROM 
                pg_auth_members 
            WHERE 
                member=(SELECT oid FROM pg_roles WHERE rolname='maxwell'));

如果您希望获得角色名称，请用oid替换第一个rolname。

#6 楼

如果您想知道当前活动角色的所有角色：

CREATE OR REPLACE VIEW public.my_roles
AS WITH RECURSIVE cte AS (
         SELECT pg_roles.oid,
            pg_roles.rolname
           FROM pg_roles
          WHERE pg_roles.rolname = CURRENT_USER
        UNION ALL
         SELECT m.roleid,
            pgr.rolname
           FROM cte cte_1
             JOIN pg_auth_members m ON m.member = cte_1.oid
             JOIN pg_roles pgr ON pgr.oid = m.roleid
        )
 SELECT array_agg(cte.rolname) AS my_roles
   FROM cte;

编程黑洞网