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