| -- 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 |
| */ |