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
