Re: pg_upgrade fails with non-standard ACL

On 2019/11/27 13:22, Michael Paquier wrote:
> On Wed, Nov 27, 2019 at 11:35:14AM +0900, Artur Zakirov wrote:
>> Other approach is similar to Anastasia's patch, which is scanning pg_proc,
>> pg_class, pg_attribute and others to get modified ACL's and compare it with
>> initial ACL from pg_init_privs. Next step is to find objects which names or
>> signatures were changed using pg_describe_object() and scanning pg_depend of
>> new cluster
> 
> Yeah, the actual take is if we want to make the frontend code more
> complicated with a large set of SQL queries to check that each object
> ACL is modified, which adds an additional maintenance cost in
> pg_upgrade.  Or if we want to keep the frontend simple and have more
> backend facility to ease cross-catalog lookups for ACLs.  Perhaps we
> could also live with just checking after the ACLs of functions in the
> short term and perhaps it covers most of the cases users would care
> about..  That's tricky to conclude about and I am not sure which path
> is better in the long-term, but at least it's worth discussing all
> possible candidates IMO so as we make sure to not miss anything.

I checked what objects changed their signatures between master and 9.6. 
I just ran pg_describe_object() for grantable object types, saved the 
output into a file and diffed the outputs. It seems that only functions 
and table columns changed their signatures. A list of functions is big 
and here the list of columns:

table pg_attrdef column adsrc
table pg_class column relhasoids
table pg_class column relhaspkey
table pg_constraint column consrc
table pg_proc column proisagg
table pg_proc column proiswindow
table pg_proc column protransform

As a result I think in pg_upgrade we could just check functions and 
columns signatures. It might simplify the patch. And if something 
changes in a future we could fix pg_upgrade too.

>> (there is a problem here though: there are no entries for
>> relations columns).
> 
> When it comes to column ACLs, pg_shdepend stores a dependency between
> the column's relation and the role.

Thank you for the hint. pg_shdepend could be used in a patch.

-- 
Artur