Re: [PATCH] pg_permissions

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