On Mon, Mar 8, 2021, at 15:35, Joe Conway wrote: > While this is interesting and probably useful for troubleshooting, it does not > provide the complete picture if what you care about is something like "what > stuff can joel do in my database".
Good point, I agree. I think that's a different more complicated use-case though. Personally, I use these views to resolve errors like this: $ dropuser test dropuser: error: removal of role "test" failed: ERROR: role "test" cannot be dropped because some objects depend on it DETAIL: 1 object in database joel Hmmm. I wonder which 1 object that could be? $ psql # SELECT * FROM pg_ownerships WHERE rolname = 'test'; regclass | obj_desc | rolname ----------+----------+--------- pg_class | table t | test pg_type | type t | test pg_type | type t[] | test (3 rows) It could also be due to permissions, so normally I would check both pg_ownership and pg_permissions at the same time, since otherwise I could possibly get the same error again: $ dropuser test dropuser: error: removal of role "test" failed: ERROR: role "test" cannot be dropped because some objects depend on it DETAIL: 1 object in database joel # SELECT * FROM pg_permissions WHERE grantee = 'test'; regclass | obj_desc | grantor | grantee | privilege_type | is_grantable ----------+----------+---------+---------+----------------+-------------- pg_class | table t | joel | test | INSERT | f (1 row) Now, this situation is probably easiest to quickly resolve using REASSIGN OWNED BY ... TO ... but I think that command is scary, I would rather prefer to resolve it manually to not blindly cause problems. /Joel
