Re: pg_upgrade fails with non-standard ACL

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...

>> So I think that it would be better
>> for now to get to a point where we can warn about the function
>> signatures involved in a given database, without the generation of the
>> script with those REVOKE queries. Or would folks prefer keep that in
>> the first version? My take would be to handle both separately, and to
>> warn about everything so as there is no need to do pg_upgrade --check
>> more than once.
>
> I would prefer to warn about every function (so he can more quickly assess
> the situation) AND generate script. It is good to save some user time,
> because he is going to need that script anyway.

Not arguing against the fact that it is useful, but I'd think that it
is a two-step process, where we need to understand what logic needs to
be in the backend or some frontend:
1) Warn properly about the objects involved, where the object
description returned by pg_describe_object would be fine enough to
understand what's broken in a given database.
2) Generate a script which may be used by the end-user.
--
Michael