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.

• nick: dev=# create extension pg_permissions; ERROR: could not open extension control file "/usr/pgsql-12/share/extension/pg_permissions.control": No such file or directory
• Laurenz Albe: Well, you need to install the software first. It is not a core extension.

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 ;