主页 / dba / 问题 / 56096
Accepted
Neil McGuigan
Asked: 2014-01-04 14:07:29 +0800 CST

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

  • 772

假设我有两个 Postgresql 数据库组，“authors”和“editors”，以及两个用户，“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、authors 和 editors（但不是 ernest）的 oid。

但是我不确定当有继承时该怎么做。

postgresql users

6 个回答

  1. Best Answer

    您可以使用递归查询来查询系统目录，特别是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 不允许这样做。所以我们不必检查。

    • 44

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

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

    pg_roles本质上是一种pg_authid公众可以访问的观点，因为它不会泄露密码，这与pg_authid. 基础oid甚至导出到视图中。当不需要密码时，创建专用的超级用户拥有的功能是没有意义的。

    • 25

  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($1, 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');
    • 21

  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'
    • 5

  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.

    • 1

  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;
    • 1

相关问题