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
