-- View all roles with attributes and parent role using psql command
/*
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
app | Create DB | {}
backups | Replication | {}
datadog | | {mon}
dba | Superuser, Create role, Create DB | {}
dev | | {}
devadmin | Create DB | {}
mon | | {}
mozdba | Superuser | {dba}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
psql_app | | {app}
repl | Cannot login, Replication | {}
slave | Replication | {repl}
*/
-- View all roles with attributes and parent role using query
/*
* equivalent query for /du command, found using psql -E
*/
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
/*
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
app | Create DB | {}
backups | Replication | {}
datadog | | {mon}
dba | Superuser, Create role, Create DB | {}
dev | | {}
devadmin | Create DB | {}
mon | | {}
mozdba | Superuser | {dba}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
psql_app | | {app}
repl | Cannot login, Replication | {}
slave | Replication | {repl}
*/
-- View all roles a user/role belongs to (recursively)
WITH RECURSIVE cte AS (
SELECT oid, rolname FROM pg_roles WHERE rolname = 'app_level3'
UNION ALL
SELECT a.roleid, pg_get_userbyid(a.roleid)
FROM cte
JOIN pg_auth_members a ON a.member = cte.oid
)
SELECT * FROM cte;
/*
oid | rolname
-------+------------
16505 | app_level3
16390 | psql_app
16385 | app
(3 rows)
*/
-- View the recursive hierarchy of all roles/users across all schemas
WITH RECURSIVE
cte1 as (
SELECT b.oid, b.rolname, m.roleid as parentid
FROM pg_catalog.pg_auth_members m
RIGHT OUTER JOIN pg_catalog.pg_roles b ON (m.member = b.oid)
WHERE b.rolname !~ '^pg_'
),
cte2 as (
SELECT oid, rolname, parentid, CAST(rolname AS varchar(100)) AS inheritance_map
FROM cte1
WHERE parentid IS NULL
UNION ALL
SELECT c1.oid, c1.rolname, c1.parentid,
CAST(c2.inheritance_map || '->' || c1.rolname AS varchar(100)) AS inheritance_map
FROM cte1 c1 INNER JOIN cte2 c2
ON (c1.parentid = c2.oid)
)
SELECT * FROM cte2;
/*
oid | rolname | parentid | inheritance_map
-------+------------+----------+---------------------------
16389 | repl | | repl
16386 | mon | | mon
10 | postgres | | postgres
16394 | backups | | backups
16388 | devadmin | | devadmin
16387 | dev | | dev
16384 | dba | | dba
16385 | app | | app
16393 | slave | 16389 | repl->slave
16392 | datadog | 16386 | mon->datadog
16391 | mozdba | 16384 | dba->mozdba
16390 | psql_app | 16385 | app->psql_app
16505 | app_level3 | 16390 | app->psql_app->app_level3
*/
-- View object ownership for all roles
SELECT relowner as userid,
pg_get_userbyid(relowner) as username,
CASE
WHEN relkind in ('r','t' ,'f','p') THEN 'table'
WHEN relkind in ('m', 'v') THEN 'view'
WHEN relkind='S' THEN 'sequence'
WHEN relkind='i' THEN 'index'
WHEN relkind='c' THEN 'composite_type'
END as type,
array_agg(relname) as owned_objs
FROM pg_class
WHERE relnamespace IN (
SELECT oid
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
)
GROUP BY 1,2,3
ORDER BY 2;
/*
userid | username | type | owned_objs
--------+------------+----------+--------------------------------------
16505 | app_level3 | index | {app_table_pkey,app_table1_pkey}
16505 | app_level3 | sequence | {app_table_id_seq,app_table1_id_seq}
16505 | app_level3 | table | {app_table,app_table1}
16384 | dba | table | {tabl3}
16384 | dba | index | {tabl3_pkey}
16384 | dba | sequence | {tabl3_id_seq}
(6 rows)
*/
/* References:
* https://www.postgresql.org/docs/10/catalog-pg-class.html
* http://bajis-postgres.blogspot.com/2014/01/handy-queries-of-postgresql-let-us.html
* https://dba.stackexchange.com/questions/56096/how-to-get-all-roles-that-a-user-is-a-member-of-including-inherited-roles
*/