Postgres view role permissions

Question

I am trying to work out how database roles work. Here is my user case...

create role user1 login password 'user1';

create schema authorization user1;

CREATE ROLE DEV_ROLE;

grant connect, temporary on database test to DEV_ROLE;
GRANT ALL ON SCHEMA manager TO DEV_ROLE;

Now how do i view the all the grants assigned to 'DEV_ROLE'? This is possible in Oracle but trying to work out the same here.

Do appreciate your reply. Ta

Answer 1

There is no way to do that in PostgreSQL; you have to look at all ACLs on all objects.

Perhaps an extension like pg_permissions can be useful.

One other crude method I can think of:

BEGIN;
DROP ROLE dev_role;
ROLLBACK;

The DROP ROLE will give you a list of permissions it can find that are granted to the role.

Answer 2

This is a minor variation of a query that I've been using for listing database object grants.

WITH relkinds AS (
    SELECT *
        FROM (
            VALUES
                ( 'c', 'type' ),
                ( 'f', 'foreign table' ),
                ( 'i', 'index' ),
                ( 'm', 'materialized view' ),
                ( 'p', 'partitioned table' ),
                ( 'r', 'table' ),
                ( 's', 'special' ),
                ( 't', 'TOAST table' ),
                ( 'v', 'view' ),
                ( 'I', 'partitioned index' ),
                ( 'S', 'sequence' )
            ) AS t ( relkind, label )
),
prokinds AS (
    SELECT *
        FROM (
            VALUES
                ( 'a', 'aggregate' ),
                ( 'f', 'function' ),
                ( 'p', 'procedure' ),
                ( 'w', 'window' )
            ) AS t ( prokind, label ) 
),
typtypes AS (
    SELECT *
        FROM (
            VALUES
                ( 'b', 'base type' ),
                ( 'c', 'composite type' ),
                ( 'd', 'domain' ),
                ( 'e', 'enum type' ),
                ( 't', 'pseudo-type' ),
                ( 'r', 'range type' ),
                ( 'm', 'multirange' )
            ) AS t ( typtype, label )
),
rol AS (
    SELECT oid,
            rolname::text AS role_name
        FROM pg_catalog.pg_roles
    UNION
    SELECT 0::oid AS oid,
            'public'::text
),
schemas AS ( -- Schemas
    SELECT n.oid AS schema_oid,
            n.nspname::text AS schema_name,
            n.nspowner AS owner_oid,
            'schema'::text AS object_type,
            coalesce ( n.nspacl, acldefault ( 'n'::"char", n.nspowner ) ) AS acl
        FROM pg_catalog.pg_namespace n
),
classes AS ( -- Tables, views, etc.
    SELECT schemas.schema_oid,
            schemas.schema_name AS object_schema,
            c.oid,
            c.relname::text AS object_name,
            c.relowner AS owner_oid,
            coalesce ( ct.label, c.relkind::text ) AS object_type,
            CASE
                WHEN c.relkind = 'S' THEN coalesce ( c.relacl, acldefault ( 's'::"char", c.relowner ) )
                ELSE coalesce ( c.relacl, acldefault ( 'r'::"char", c.relowner ) )
                END AS acl
        FROM pg_catalog.pg_class c
        LEFT JOIN relkinds ct
            ON ( ct.relkind = c.relkind::text )
        JOIN schemas
            ON ( schemas.schema_oid = c.relnamespace )
        WHERE c.relkind IN ( 'r', 'v', 'm', 'S', 'f', 'p' )
),
cols AS ( -- Columns
    SELECT c.object_schema,
            null::integer AS oid,
            c.object_name || '.' || a.attname::text AS object_name,
            'column' AS object_type,
            c.owner_oid,
            coalesce ( a.attacl, acldefault ( 'c'::"char", c.owner_oid ) ) AS acl
        FROM pg_catalog.pg_attribute a
        JOIN classes c
            ON ( a.attrelid = c.oid )
        WHERE a.attnum > 0
            AND NOT a.attisdropped
),
procs AS ( -- Procedures and functions
    SELECT schemas.schema_oid,
            schemas.schema_name AS object_schema,
            p.oid,
            p.proname::text AS object_name,
            p.proowner AS owner_oid,
            coalesce ( pt.label, 'function' ) AS object_type,
            pg_catalog.pg_get_function_arguments ( p.oid ) AS calling_arguments,
            coalesce ( p.proacl, acldefault ( 'f'::"char", p.proowner ) ) AS acl
        FROM pg_catalog.pg_proc p
        JOIN schemas
            ON ( schemas.schema_oid = p.pronamespace )
        LEFT JOIN prokinds pt
            ON ( pt.prokind = p.prokind::text )
),
udts AS ( -- User defined types
    SELECT schemas.schema_oid,
            schemas.schema_name AS object_schema,
            t.oid,
            t.typname::text AS object_name,
            t.typowner AS owner_oid,
            coalesce ( typtypes.label, t.typtype::text ) AS object_type,
            coalesce ( t.typacl, acldefault ( 'T'::"char", t.typowner ) ) AS acl
        FROM pg_catalog.pg_type t
        JOIN schemas
            ON ( schemas.schema_oid = t.typnamespace )
        LEFT JOIN typtypes
            ON ( typtypes.typtype = t.typtype::text )
        WHERE ( t.typrelid = 0
                OR ( SELECT c.relkind = 'c'
                        FROM pg_catalog.pg_class c
                        WHERE c.oid = t.typrelid ) )
            AND NOT EXISTS (
                SELECT 1
                    FROM pg_catalog.pg_type el
                    WHERE el.oid = t.typelem
                        AND el.typarray = t.oid )
),
fdws AS ( -- Foreign data wrappers
    SELECT null::oid AS schema_oid,
            null::text AS object_schema,
            p.oid,
            p.fdwname::text AS object_name,
            p.fdwowner AS owner_oid,
            'foreign data wrapper' AS object_type,
            coalesce ( p.fdwacl, acldefault ( 'F'::"char", p.fdwowner ) ) AS acl
        FROM pg_catalog.pg_foreign_data_wrapper p
),
fsrvs AS ( -- Foreign servers
    SELECT null::oid AS schema_oid,
            null::text AS object_schema,
            p.oid,
            p.srvname::text AS object_name,
            p.srvowner AS owner_oid,
            'foreign server' AS object_type,
            coalesce ( p.srvacl, acldefault ( 'S'::"char", p.srvowner ) ) AS acl
        FROM pg_catalog.pg_foreign_server p
),
all_objects AS (
    SELECT schema_name AS object_schema,
            object_type,
            schema_name AS object_name,
            null::text AS calling_arguments,
            owner_oid,
            acl
        FROM schemas
    UNION
    SELECT object_schema,
            object_type,
            object_name,
            null::text AS calling_arguments,
            owner_oid,
            acl
        FROM classes
    UNION
    SELECT object_schema,
            object_type,
            object_name,
            null::text AS calling_arguments,
            owner_oid,
            acl
        FROM cols
    UNION
    SELECT object_schema,
            object_type,
            object_name,
            calling_arguments,
            owner_oid,
            acl
        FROM procs
    UNION
    SELECT object_schema,
            object_type,
            object_name,
            null::text AS calling_arguments,
            owner_oid,
            acl
        FROM udts
    UNION
    SELECT object_schema,
            object_type,
            object_name,
            null::text AS calling_arguments,
            owner_oid,
            acl
        FROM fdws
    UNION
    SELECT object_schema,
            object_type,
            object_name,
            null::text AS calling_arguments,
            owner_oid,
            acl
        FROM fsrvs
),
acl_base AS (
    SELECT object_schema,
            object_type,
            object_name,
            calling_arguments,
            owner_oid,
            ( aclexplode ( acl ) ).grantor AS grantor_oid,
            ( aclexplode ( acl ) ).grantee AS grantee_oid,
            ( aclexplode ( acl ) ).privilege_type AS privilege_type,
            ( aclexplode ( acl ) ).is_grantable AS is_grantable
        FROM all_objects
)
SELECT acl_base.object_schema,
        acl_base.object_type,
        acl_base.object_name,
        regexp_replace (
            regexp_replace ( acl_base.calling_arguments, ' DEFAULT [^,]+', '', 'g' ),
                 '(IN|OUT|INOUT) ', '', 'g' ) AS calling_signature,
        owner.role_name AS object_owner,
        grantor.role_name AS grantor,
        grantee.role_name AS grantee,
        acl_base.privilege_type,
        acl_base.is_grantable
    FROM acl_base
    JOIN rol owner
        ON ( owner.oid = acl_base.owner_oid )
    JOIN rol grantor
        ON ( grantor.oid = acl_base.grantor_oid )
    JOIN rol grantee
        ON ( grantee.oid = acl_base.grantee_oid )
    WHERE acl_base.grantor_oid <> acl_base.grantee_oid ;