Re: [PATCH] pg_permissions

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