Re: [PATCH] pg_permissions

On Sat, Mar 06, 2021 at 08:03:17PM +0100, Joel Jacobson wrote:
> Hi,
>
> It's easy to answer the question...
>
> - What permissions are there on this specific object?
>
> ...but to answer the question...
>
> - What permissions are there for a specific role in the database?
>
> you need to manually query all relevant pg_catalog or information_schema.*_privileges views,
> which is a O(n) mental effort, while the first question is mentally O(1).
>
> I think this can be improved by providing humans a single pg_permissions system view
> which can be queried to answer the second question. This should save a lot of keyboard punches.
>
> Demo:
>
> SELECT * FROM pg_permissions WHERE 'joel' IN (grantor,grantee);
> regclass | obj_desc | grantor | grantee | privilege_type | is_grantable
> --------------+-----------------------------+---------+---------+----------------+--------------
> pg_namespace | schema foo | joel | joel | USAGE | f
> pg_namespace | schema foo | joel | joel | CREATE | f
> pg_class | table foo.bar | joel | joel | INSERT | f
> pg_class | table foo.bar | joel | joel | SELECT | f
> pg_class | table foo.bar | joel | joel | UPDATE | f
> pg_class | table foo.bar | joel | joel | DELETE | f
> pg_class | table foo.bar | joel | joel | TRUNCATE | f
> pg_class | table foo.bar | joel | joel | REFERENCES | f
> pg_class | table foo.bar | joel | joel | TRIGGER | f
> pg_attribute | column baz of table foo.bar | joel | joel | SELECT | f
> pg_attribute | column baz of table foo.bar | joel | joel | UPDATE | f
> (11 rows)
>
> All catalogs with _aclitem columns have been included in the view.
>
> I think a similar one for ownerships would be nice too.
> But I'll let you digest this one first to see if the concept is fruitful.

+1 for both this and the ownerships view.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate