Re: [PATCH] pg_permissions

On 3/6/21 2:03 PM, Joel Jacobson wrote:
> ...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.

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

The reasons for this include default grants to PUBLIC and role membership, and
even that is convoluted by INHERIT/NOINHERIT role attributes.

I won't try to describe all the implications here, but a while back I wrote a
fairly comprehensive blog[1] about it.

FWIW in the blog I reference an extension that I wrote to facilitate object and
role privilege inspection[2]. I have toyed with the idea of morphing that into a
feature I can submit for pg15, but I don't want to spend effort on the morphing
unless there is both sufficient interest and lack of conceptual objections to
the feature. I'd love to hear from both sides of that scale.

Joe

[1]
http://blog.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-1
[2] https://github.com/CrunchyData/crunchy_check_access

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development