Re: [PATCH] pg_permissions

On Sat, Mar 06, 2021 at 08:03:17PM +0100, Joel Jacobson wrote:
> regclass | obj_desc | grantor | grantee |
privilege_type | is_grantable
>
--------------+-----------------------------+---------+---------+----------------+--------------

1. Is there a reason not to make 'grantor' and 'grantee' of type regrole?
In other words, s/rolname/oid::regrole/ throughout the view definition.
It looks the same visually, but should be easier to build on in a larger
query.

Hmm, ok, a grantee of 'public' can't be expressed as a regrole. This
seems an annoying little corner.[1] It can be represented by 0::regrole,
but that displays as '-'. Hmm again, you can even '-'::regrole and get 0.

2. Also to facilitate use in a larger query, how about columns for the
objid and objsubid, in addition to the human-friendly obj_desc?
And I'm not sure about using pg_attribute as the regclass for
attributes; it's nice to look at, but could also plant the wrong idea
that attributes have pg_attribute as their classid, when it's really
pg_class with an objsubid. Anyway, there's the human-friendly obj_desc
to tell you it's a column.

On 03/08/21 12:14, Joel Jacobson wrote:
> 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.

I could agree that the role membership and inherit/noinherit part is
a more complicated problem that could be solved by a larger query built
over this view (facilitated by giving grantor and grantee regrole type)
and some recursive-CTEness with the roles.

But I think it would be useful for this view to handle the part of the story
that involves acldefault() when the stored aclitem[] is null. I've long
wanted a view that actually shows you all of the permissions that apply
to something, even the ones you're supposed to Just Know, and indeed
I wrote such a thing for $work.

Then you could even query the view for an answer to the question "what
are all the permissions 'public' (or '-') can exercise here?"

On 03/06/21 19:08, Joel Jacobson wrote:
> SELECT * FROM ownerships WHERE rolname = 'joel' LIMIT 5;
> regclass | obj_desc | rolname
> ------------------+-----------------------------------+---------

Here again, I'd repeat the suggestions to present the owner as a regrole
(and in this case there is no need to deal with 'public'), and to include
the objid as well as the human-friendly obj_desc.

Regards,
-Chap