Início / dba / Perguntas / 56096
Accepted
Neil McGuigan
Asked: 2014-01-04 14:07:29 +0800 CST

Como obter todas as funções das quais um usuário é membro (incluindo funções herdadas)?

  • 772

Digamos que eu tenha dois grupos de banco de dados Postgresql, "autores" e "editores", e dois usuários, "maxwell" e "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

Eu gostaria de escrever uma função performante que retornasse uma lista dos papéis (de preferência seus oid's) aos quais o maxwell pertence, algo assim:

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

Deve retornar os oids para maxwell, autores e editores (mas não ernest).

Mas não tenho certeza de como fazê-lo quando há herança.

postgresql users

6 respostas

  1. Best Answer

    Você pode consultar o catálogo do sistema com uma consulta recursiva , em particular 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

    O manual sobre o tipo de identificador de conversão para objeto regrole.

    BTW 1: INHERITé o comportamento padrão CREATE ROLEe não precisa ser explicado.

    BTW 2: dependências circulares não são possíveis. O Postgres não permite isso. Então não temos que verificar isso.

    • 44

  2. Esta é uma versão simplificada da resposta de Craig Ringer que um não superusuário pode usar diretamente:

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

    pg_rolesé essencialmente uma visão pg_authidacessível ao público, pois não revela senhas, ao contrário do pg_authid. A base oidé mesmo exportada para a vista. Quando não precisa de senhas, não faz sentido criar a função dedicada de propriedade do superusuário.

    • 25

  3. Versão curta:

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

    Aqui usamos uma versão de pg_has_roleque leva um nome de função como assunto e função oid para testar a associação , passando membero modo para que testamos associações herdadas.

    A vantagem de usar pg_has_roleé que ele usa os caches internos de informações de função do PostgreSQL para atender rapidamente às consultas de associação.

    Você pode querer envolver isso em uma SECURITY DEFINERfunção, pois pg_authidtem acesso restrito. Algo como:

    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...;

    Você pode usar pg_get_userbyid(oid)para obter o nome da função do oid sem a necessidade de consultar 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. Aqui está a minha opinião sobre isso. Funciona para um usuário específico ou para todos os usuários.

    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. Eu acredito que isso vai fazer isso

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

    Se você preferir obter os nomes das funções, substitua o primeiro oidpor rolname.

    • 1

  6. se você quiser saber todas as funções da sua função atualmente ativa:

    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

relate perguntas