List a postgresql role's owned objects

list_owned_objects_for_role.sql

-- From https://stackoverflow.com/questions/44835028/postgresql-get-effective-permissions-for-specified-roles-on-each-object-type

WITH 
databases AS (
  SELECT unnest('{db_name}'::text[]) AS dbname 
),
roles AS (
  SELECT unnest('{role_id}'::text[]) AS rname
),
permissions AS (
  SELECT 'DATABASE' AS ptype, unnest('{CREATE,CONNECT,TEMPORARY}'::text[])                                  AS pname
  UNION ALL
  SELECT 'SCHEMA'   AS ptype, unnest('{CREATE,USAGE}'::text[])                                              AS pname
  UNION ALL
  SELECT 'TABLE'    AS ptype, unnest('{SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER}'::text[])   AS pname
  UNION ALL
  SELECT 'TYPE'     AS ptype, unnest('{USAGE}'::text[])                                                     AS pname
  UNION ALL
  SELECT 'FUNCTION' AS ptype, unnest('{EXECUTE}'::text[])                                                   AS pname      
),
schemas AS (
  SELECT schema_name    AS sname
  FROM   information_schema.schemata
  WHERE  catalog_name IN (SELECT dbname FROM databases)                -- show schemas that exist in specified DB
    AND  schema_owner IN (SELECT rname FROM roles)                     -- show schemas that are owned by specified roles
    OR   schema_name IN ('public') -- always include these
    --OR   schema_name IN ('public', 'information_schema', 'pg_catalog')
),
tables AS (
  SELECT table_schema AS tschema, table_name AS tname
  FROM   information_schema.tables
  WHERE  table_catalog IN (SELECT dbname FROM databases)
    AND  table_schema IN (SELECT sname FROM schemas)
    AND  table_type IN ('BASE TABLE') -- , 'VIEW' 
),
types AS (
  SELECT nspname AS typeschema, typname AS typename, CASE typtype WHEN 'c' THEN 'composite' WHEN 'd' THEN 'domain' WHEN 'e' THEN 'enum' WHEN 'r' THEN 'range' ELSE 'other' END AS typekind 
  FROM pg_type INNER JOIN pg_namespace ON pg_type.typnamespace = pg_namespace.oid
  WHERE nspname IN (SELECT sname FROM schemas)
    AND typtype NOT IN ('b','p')                       -- exclude base and pseudo types
),
functions AS (
  SELECT nspname AS fnschema, proname AS fnname, pg_proc.oid AS fnoid, pg_get_function_arguments(pg_proc.oid) AS fnargs
  FROM   pg_proc INNER JOIN pg_namespace ON pg_proc.pronamespace = pg_namespace.oid
  WHERE  nspname IN (SELECT sname FROM schemas)
),
final AS (
SELECT
    permissions.ptype
  , databases.dbname                                                         AS obj_name
  , roles.rname
  , permissions.pname
  , has_database_privilege(roles.rname, databases.dbname, permissions.pname) AS has_permission
FROM 
  databases
  CROSS JOIN roles 
  CROSS JOIN permissions
WHERE 
  permissions.ptype = 'DATABASE'
UNION ALL -- ----------------------------------------------------------------------------------------------------------
SELECT
    permissions.ptype
  , schemas.sname                                                         AS obj_name
  , roles.rname
  , permissions.pname 
  , has_schema_privilege(roles.rname, schemas.sname, permissions.pname)   AS has_permission
FROM
  schemas
  CROSS JOIN roles
  CROSS JOIN permissions
WHERE 
  permissions.ptype = 'SCHEMA'
UNION ALL -- ----------------------------------------------------------------------------------------------------------
SELECT
    permissions.ptype
  , tables.tschema || '.' || tables.tname                                                        AS obj_name
  , roles.rname
  , permissions.pname 
  , has_table_privilege(roles.rname, (tables.tschema || '.' || tables.tname), permissions.pname) AS has_permission
FROM
  tables
  CROSS JOIN roles
  CROSS JOIN permissions
WHERE 
  permissions.ptype = 'TABLE'
UNION ALL -- ----------------------------------------------------------------------------------------------------------
SELECT
    permissions.ptype || ' - ' || types.typekind
  , types.typeschema || '.' || types.typename                                                       AS obj_name
  , roles.rname
  , permissions.pname 
  , has_type_privilege(roles.rname, (types.typeschema || '.' || types.typename), permissions.pname) AS has_permission
FROM
  types
  CROSS JOIN roles
  CROSS JOIN permissions
WHERE 
  permissions.ptype = 'TYPE'
UNION ALL -- ----------------------------------------------------------------------------------------------------------
SELECT
    permissions.ptype
  , functions.fnschema || '.' || functions.fnname || '(' || fnargs || ')'   AS obj_name
  , roles.rname
  , permissions.pname 
  , has_function_privilege(roles.rname, functions.fnoid, permissions.pname) AS has_permission
FROM
  functions
  CROSS JOIN roles
  CROSS JOIN permissions
WHERE 
  permissions.ptype = 'FUNCTION'                
)
-- ====================================================================================================================
SELECT
   rname                                                                  AS role_name  
 , ptype                                                                  AS object_type
 , obj_name                                                               AS object_name
 , string_agg(DISTINCT CASE WHEN     has_permission THEN pname END, ',')  AS granted_permissions
 , string_agg(DISTINCT CASE WHEN NOT has_permission THEN pname END, ',')  AS missing_premissions 
FROM 
  final
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3