Re: pg_upgrade fails with non-standard ACL

On Tue, Aug 13, 2019 at 07:04:42PM +0300, Anastasia Lubennikova wrote:
> > In an ideal world, it seems like we'd make a judgement call and arrange
> > to pull the privileges across when we can do so without granting the
> > user privileges beyond those that were intended, and otherwise we'd
> > suppress the GRANT to avoid getting an error.  I'm not sure what a good
> > way is to go about either figuring out a way to pull the privileges
> > across, or to suppress the GRANTs when we need to (or always), would be
> > though.  Neither seems easy to solve in a clean way.
> > 
> > Certainly open to suggestions.
> Based on our initial bug report, I would vote against suppressing the
> GRANTS,
> because it leads to an unexpected failure in case a user has a special role
> for
> use in a third-party utility such as a backup tool, which already took care
> of
> internal API changes.
> 
> Still I agree with your arguments about possibility of providing more grants
> than expected. Ideally, we do not change behaviour of existing functions
> that
> much, but in real-world it may happen.
> 
> Maybe, as a compromise, we can reset grants to default for all changed
> functions
> and also generate a script that will allow a user to preserve privileges of
> the
> old cluster by analogy with analyze_new_cluster script.
> What do you think?

I agree pg_upgrade should work without user correction as much as
possible.  However, as you can see, it can fail when user objects
reference system table objects that have changed between major releases.

As much as it would be nice if the release notes covered all that, and
we updated pg_upgrade to somehow handle them, it just isn't realistic. 
As we can see here, the problems often take years to show up, and even
then there were probably many other people who had the problem who never
reported it to us.

I think a realistic approach is to come up with a list of all the user
behaviors that can cause pg_upgrade to break (by reviewing previous
pg_upgrade bug reports), and then add code to pg_upgrade to detect them
and either fix them or report them in --check mode.

In summary, I am saying that the odds that patch authors, committers,
release note writers, and pg_upgrade maintainers are going to form a
consistent work flow that catches all these changes is unrealistic ---
our best bet is to create something in the pg_upgrade code to detects
this.  pg_upgrade already connects to the old and new cluster, so
technically it can perform system table modification checks itself.

The only positive is that when pg_upgrade does fail, at least we have a
system that clearly points to the cause, but unfortunately usually at
run-time, not at --check time.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +