Re: [PATCH] pg_permissions

On Tue, Mar 9, 2021, at 04:01, Chapman Flack wrote:
> 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?

I considered it, but this view is tailored for human-use,
to be used by experienced as well as beginner users.

> 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.

If using regrole, the users would need to know they would need to cast it to text, to search for values, e.g.:

SELECT * FROM pg_permissions WHERE grantee = 'foobar';
ERROR: invalid input syntax for type oid: "foobar"
LINE 1: SELECT * FROM pg_permissions WHERE grantee = 'foobar';

SELECT * FROM pg_permissions WHERE grantee LIKE 'foo%';
ERROR: operator does not exist: regrole ~~ unknown
LINE 1: SELECT * FROM pg_permissions WHERE grantee LIKE 'foo%';

> 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?

I think it's good to prevent users from abusing this view,
by not including oids and other columns needed for proper
integration in larger queries/systems.

Otherwise there is a risk users will be sloppy and just join pg_permissions,
when they really should be joining some specific catalog.

Also, lots of extra columns not needed by humans just makes the view less readable,
since you would more often need to \x when the width of the output does't fit.

Personally, I'm on a 15" MacBook Pro and I usually have two 117x24 terminals next to each other,
in which both pg_permissions and pg_ownerships output usually fits fine.

> 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.

While pg_class is the "origin class", I think we convey more meaningful information,
by using the regclass for the table which stores the aclitem[] column,
in your example, pg_attribute. This makes it more obvious to the user the permission
is on some column, rather than on the table. In the case where you try to drop a user
and don't understand why you can't, and then look in pg_permissions what could be the
reason, it's more helpful to show pg_attribute than pg_class, since you hopefully then
understand you should revoke permissions for some column, and not the table.

You get this information in obj_desc as well, but I think regclass complements it nicely.

And it's also more precise, the permission *is* really on pg_attribute,
it just happens to be that pg_attribute has a multi-key primary key,
where one of the keys is referencing pg_class.oid.

> 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?"

Seems useful, but maybe that's a different view/function?
Could it be integrated into these views without increasing complexity?

/Joel