Получение всех прав объекта для определенной роли

Ответ 1

Нет такого представления из коробки, но данные, необходимые для его создания, находятся в системных каталогах:

http://www.postgresql.org/docs/current/static/catalogs.html

Например, в relacl поле pg_class:

select oid::regclass, relacl from pg_class;

В других каталогах есть похожие поля, а именно typacl в pg_type и proacl в pg_proc.

Предположительно вы захотите использовать еще два каталога, а именно pg_authid чтобы узнать, какие роли имеют привилегии суперпользователя, и pg_auth_members чтобы узнать, кто имеет какую роль.

(pg_default_acl используется только при создании объекта, поэтому это не полезно).

Есть несколько внутренних функций, связанных с aclitem, которые могут пригодиться при создании представления. Вы можете перечислить их в psql следующим образом:

\df+ *acl*

В частности, aclexplode(). Следующий пример, надеюсь, будет достаточным для начала работы:

select oid::regclass,
       (aclexplode(relacl)).grantor,
       (aclexplode(relacl)).grantee,
       (aclexplode(relacl)).privilege_type,
       (aclexplode(relacl)).is_grantable
from pg_class
where relacl is not null;

Его можно оптимизировать, сначала расширив строки acl, например:

select oid::regclass,
       aclitem.grantee
from (select oid, aclexplode(relacl) as aclitem from pg_class) sub

Это приведет вас прямо к желаемому результату.

Насколько мне известно, это так же хорошо, как и при использовании встроенных инструментов. (Естественно, вы могли бы написать свой собственный набор операторов в C, если вы хотите попытаться оптимизировать это дальше.)

Что касается ваших дополнительных вопросов, я боюсь, на них может ответить только горстка людей в мире, а также основных разработчиков. Они чаще встречаются в списке хакеров, чем они здесь.

Ответ 2

Наверное, это не лучший/эффективный способ, но мне это очень помогает! Я нуждался в нем, имея проблемы с ролями и ошибкой.

ERROR:  role ROLE_NAME cannot be dropped because some objects depend on it

Вы можете использовать его как

SELECT * FROM upg_roles_privs WHERE grantee = 'testuser'

Код ниже. Я не включаю объекты "системы" (из pg_catalog и information_schema), вы можете взять условия из запроса, если хотите их перечислить.

CREATE VIEW upg_roles_privs AS

    /* Databases */
    select type, objname, r1.rolname grantor, r2.rolname grantee, privilege_type
    from
    (select 
      'database'::text as type, datname as objname, datistemplate, datallowconn, 
      (aclexplode(datacl)).grantor as grantorI, 
      (aclexplode(datacl)).grantee as granteeI,
      (aclexplode(datacl)).privilege_type,
      (aclexplode(datacl)).is_grantable
    from pg_database) as db
    join pg_roles r1 on db.grantorI = r1.oid
    join pg_roles r2 on db.granteeI = r2.oid
    where r2.rolname not in ('postgres')

    union all

    /* Schemas / Namespaces */
    select type, objname, r1.rolname grantor, r2.rolname grantee, privilege_type from 
    (select
      'schema'::text as type, nspname as objname, 
      (aclexplode(nspacl)).grantor as grantorI, 
      (aclexplode(nspacl)).grantee as granteeI,
      (aclexplode(nspacl)).privilege_type,
      (aclexplode(nspacl)).is_grantable
    from pg_catalog.pg_namespace) as ns
    join pg_roles r1 on ns.grantorI = r1.oid
    join pg_roles r2 on ns.granteeI = r2.oid
    where r2.rolname not in ('postgres')

    union all

    /* Tabelas */
    select 'tables'::text as type, table_name||' ('||table_schema||')' as objname, grantor, grantee, privilege_type  
    from information_schema.role_table_grants 
    where grantee not in ('postgres')
    and table_schema not in ('information_schema', 'pg_catalog')
    and grantor <> grantee

    union all

    /* Colunas (TODO: se o revoke on table from x retirar acesso das colunas, nao precisa desse bloco) */
    select 
      'columns'::text as type, column_name||' ('||table_name||')' as objname,
      grantor, grantee, privilege_type
    from information_schema.role_column_grants
    where 
    table_schema not in ('information_schema', 'pg_catalog')
    and grantor <> grantee

    union all

    /* Funcoes / Procedures */
    select 'routine'::text as type, routine_name as objname, grantor, grantee, privilege_type
    from information_schema.role_routine_grants
    where grantor <> grantee
    and routine_schema not in ('information_schema', 'pg_catalog')

    --union all information_schema.role_udt_grants

    union all

    /* Outros objetos */
    select 'object'::text as type, object_name||'( '||object_type||')' as objname, grantor, grantee, privilege_type
    from information_schema.role_usage_grants
    where object_type <> 'COLLATION' and object_type <> 'DOMAIN'