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