On Mon, Mar 8, 2021, at 02:09, David Fetter wrote: > +1 for both this and the ownerships view. > > Best, > David.
I'm glad you like it.
I've put some more effort into this patch, and developed a method to
mechanically verify its correctness.
Attached is a new patch with both pg_permissions and pg_ownerships in the same
patch,
based on HEAD (8a812e5106c5db50039336288d376a188844e2cc).
I've also added five catalogs to pg_ownerships that were discovered to be
missing in the previous version:
pg_catalog.pg_database
pg_catalog.pg_default_acl
pg_catalog.pg_largeobject_metadata
pg_catalog.pg_publication
pg_catalog.pg_subscription
Here is how I've verified correctness of complete coverage:
All catalogs with permissions have an aclitem[] column.
There are totally 13 such catalogs in HEAD:
SELECT COUNT(DISTINCT table_name) FROM information_schema.columns WHERE
table_schema = 'pg_catalog' AND udt_name = '_aclitem';
count
-------
13
(1 row)
Expect the same number of rows in the patch:
$ grep "(aclexplode(aa." 0001-pg_permissions-and-pg_ownerships.patch | wc -l
13
Using the new awesome pg_get_catalog_foreign_keys() function in v14,
we can now query what catalogs are referencing pg_authid.oid,
of which all named .*owner are known by convention to
indicate ownership. Let's see what other columns there are
referencing pg_authid.oid that could possibly also indicate ownership:
SELECT
regexp_replace(fkcols[1],'.*owner$','.*owner') AS fkcol,
COUNT(*)
FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_authid'::regclass
AND pkcols[1] = 'oid'
AND cardinality(fkcols) = 1
GROUP BY 1
ORDER BY 2 DESC;
fkcol | count
------------+-------
.*owner | 21
datdba | 1
defaclrole | 1
grantor | 1
member | 1
polroles | 1
roleid | 1
setrole | 1
umuser | 1
(9 rows)
If we exclude the .*owner and also look at fktable we see:
SELECT *
FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_authid'::regclass
AND pkcols[1] = 'oid'
AND cardinality(fkcols) = 1
AND fkcols[1] !~ '.*owner$'
fktable | fkcols | pktable | pkcols | is_array | is_opt
--------------------+--------------+-----------+--------+----------+--------
pg_database | {datdba} | pg_authid | {oid} | f | f
pg_db_role_setting | {setrole} | pg_authid | {oid} | f | t
pg_auth_members | {roleid} | pg_authid | {oid} | f | f
pg_auth_members | {member} | pg_authid | {oid} | f | f
pg_auth_members | {grantor} | pg_authid | {oid} | f | f
pg_user_mapping | {umuser} | pg_authid | {oid} | f | t
pg_policy | {polroles} | pg_authid | {oid} | t | t
pg_default_acl | {defaclrole} | pg_authid | {oid} | f | f
(8 rows)
By reading the documentation for these catalogs,
I've come to the conclusion these columns also indicate ownership:
pg_database.datdba
pg_default_acl.defaclrole
pg_policy.polroles
In total, we should expect 21+3=24 catalogs.
Let's see if this matches the patch:
$ grep "pg_authid.rolname" 0001-pg_permissions-and-pg_ownerships.patch | wc -l
24
All good.
I note it's not very often new catalogs are added,
so hopefully we can have a routine to update these views
when new catalogs with ownership- or permission columns are added.
However, should we ever get out of sync, we can use the method above to sort
things out.
/Joel
0001-pg_permissions-and-pg_ownerships.patch
Description: Binary data
