Re: pg_upgrade fails with non-standard ACL

Thank you for reviews!

On 2019/11/21 17:53, Michael Paquier wrote:
> On Fri, Nov 15, 2019 at 11:30:02AM +0300, Grigory Smolkin wrote:
>> On 11/9/19 5:26 AM, Michael Paquier wrote:
>>> Another question I have: do we need to care more about other extra
>>> ACLs applied to other object types?  For example a subset of columns
>>> on a table with a column being renamed could be an issue.  Procedure
>>> renamed in core are not that common still we did it.
>>
>> I think that all objects must be supported.
> 
> The unfortunate part about the current approach is that it is not
> really scalable from the point of view of the client.  What the patch
> does is to compare the initdb-time ACLs and the ones stored in
> pg_proc.  In order to support all object types we would need to have
> more client-side logic to join properly with all the catalogs holding
> the ACLs of the objects to be compared.  I am wondering if it would be
> more simple to invent a backend function which uses an input similar
> to pg_describe_object() with (classid, objid and objsubid) that
> returns the ACL of the corresponding object after looking at the
> catalog defined by classid.  This would simplify the client part to
> just scan pg_init_privs...

I've started to implement new backend function similar to 
pg_describe_object() and tried to make new version of the patch. But I'm 
wondering now if it is possible to backpatch  new functions to older 
Postgres releases? pg_upgrade will require a presence of this function 
on an older source cluster.

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 (there is a 
problem here though: there are no entries for relations columns).

-- 
Artur