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
