Hi hackers, Here is an attempt to revive this patch from 2021-2022, that has been ready now for a while, thanks to pg_get_acl() function that was committed in 4564f1c and d898665.
I've renamed the $subject of the email thread, to match the commitfest entry: https://commitfest.postgresql.org/50/5033/ --- Add pg_ownerships and pg_privileges system views. These new views provide a more accessible and user-friendly way to retrieve information about object ownerships and privileges. The view pg_ownerships provides access to information about object ownerships. The view pg_privileges provides access to information about explicitly granted privileges on database objects. The special grantee value "-" means the privilege is granted to PUBLIC. Example usage: CREATE ROLE alice; CREATE ROLE bob; CREATE ROLE carol; CREATE TABLE alice_table (); ALTER TABLE alice_table OWNER TO alice; REVOKE ALL ON alice_table FROM alice; GRANT SELECT ON alice_table TO bob; CREATE TABLE bob_table (); ALTER TABLE bob_table OWNER TO bob; REVOKE ALL ON bob_table FROM bob; GRANT SELECT, UPDATE ON bob_table TO carol; SELECT * FROM pg_ownerships ORDER BY owner; classid | objid | objsubid | type | schema | name | identity | owner ----------+-------+----------+-------+--------+-------------+--------------------+------- pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob (2 rows) SELECT * FROM pg_privileges ORDER BY grantee; classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable ----------+-------+----------+-------+--------+-------------+--------------------+---------+---------+----------------+-------------- pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice | bob | SELECT | f pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | SELECT | f pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | UPDATE | f (3 rows) --- Recap: During the work on this, the need for a pg_get_acl() function was identified. Thanks to feedback from Peter Eisentraut, the view "pg_permissions" was renamed to "pg_privileges", since "permissions" is not an SQL term. David Fetter: > +1 for both this and the ownerships view. Joe Conway: > 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. Chapman Flack expressed interest in reviewing the patch, but at that time the pg_get_acl() had not yet been committed and the view not been renamed. Michael Paquier alerted me CF bot had been red, and the patch was rebased. /Joel
v2-0001-Add-pg_ownerships-and-pg_privileges-system-views.patch
Description: Binary data
