Re: pg_upgrade fails with non-standard ACL

On 2019/12/01 23:58, Grigory Smolkin wrote:
> On 11/29/19 11:07 AM, Artur Zakirov wrote:
>> New version of the patch differs from the previous:
>> - it doesn't generate script to revoke conflicting permissions (but
>> the patch can be fixed easily)
>> - generates file incompatible_objects_for_acl.txt to report which
>> objects changed their signatures
>> - uses pg_shdepend and pg_depend catalogs to get objects with custom
>> privileges
>> - uses pg_describe_object() to find objects with different signatures
>>
>> Currently relations, attributes, languages, functions and procedures
>> are scanned. According to the documentation foreign databases,
>> foreign-data wrappers, foreign servers, schemas and tablespaces also
>> support ACLs. But some of them doesn't have entries initialized during
>> initdb, others like schemas and tablespaces didn't change their names.
>> So the patch doesn't scan such objects.
>>
>> Grigory it would be great if you'll try the patch. I tested it but I
>> could miss something.
>
> I`ve tested the patch on upgrade from 9.5 to master and it works now,
> thank you.

Great!

> But I think that 'incompatible_objects_for_acl.txt' is not a very
> informative way of reporting to user the source of the problem.
> There is no mentions of rolenames that holds permissions, that prevents
> the upgrade, and even if they were, it is still up to user to conjure an
> script to revoke all those grants, which is not a very user-friendly.

I tried to find some pattern how pg_upgrade decides to log list of
problem objects or to generate SQL script file to execute by user.
Nowadays only "Checking for large objects" and "Checking for hash
indexes" generate SQL script files and log WARNING message.

> I think it should generate 'catalog_procedures.sql' script as in
> previous version with all REVOKE statements, that are required to
> execute for pg_upgrade to work.

I updated the patch. It generates "revoke_objects.sql" (similar to v3
patch) now and doesn't rely on --check option. It also logs still FATAL
message because it seems pg_upgrade should stop here since it fails
later if there are objects with changed identities.

The patch doesn't generate "incompatible_objects_for_acl.txt" now
because it would duplicate "revoke_objects.sql".

It now uses pg_identify_object() instead of pg_describe_object(), it is
easier to get column names with it.

--
Arthur