Re: Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE

bruce wrote:
> Robert Haas wrote:
> > On Thu, Jan 6, 2011 at 1:59 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > > Well, if everyone who logs in gets the same username, you can easily
> > > conclude that trying to dump/restore the database will cause problems if
> > > you have objects in there that are not owned by that user.
> >
> > I can't, and neither could Florian. I'm not sure why this is so
> > obvious to you and Tom. Unless I've made some catastrophic *manual*
> > change to the system catalogs, like nuking pg_proc, I expect dump and
> > restore to just work. pg_dump's job is to emit a series of commands
> > that will work. Every time I run across a case where it doesn't, I'm
> > violently annoyed, because it's happened to me as a user and it feels
> > like a bug every time. Florian is probably made of a bit sterner
> > stuff than the typical user, but a typical user doesn't go "Oh, gee,
> > dump and restore didn't work, I guess that setting I installed in
> > there six years ago must actually be something that the developers
> > never intended for me to do." First they cuss, and then they blame us
> > for not being able to dump the database that we let them create, and
> > then if they're really ticked they go use some other product. When
> > someone actually takes the time to troubleshoot what broke and let us
> > know, the only correct response from our end is to say "thanks, we'll
> > work on making that less confusing", not "well that was a stupid thing
> > to do".
>
> Well, we usually tell people to restore as super-user, particularly
> pg_dumpall, but in this case, it is impossible. Certainly pg_upgrade
> requires it, which is the root of the problem.

We could modify pg_dump to emit RESET AUTHORIZATION in --binary-upgrade
mode. I am unclear if that might cause some other problems though.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> We could modify pg_dump to emit RESET AUTHORIZATION in --binary-upgrade
> mode. I am unclear if that might cause some other problems though.

I finally figured out what was really bugging me about that proposal:
it's a one-shot hack for fixing one problem that could arise from
non-default ALTER DATABASE/ALTER ROLE settings. Who's to say there
are not other such issues, either now or in the future?

It occurs to me that a more principled way to deal with this class of
problems would be to delay restoring ALTER DATABASE/ALTER ROLE
settings until after everything else is done. Not sure what the
implementation consequences of that would be. Ideally we'd make
pg_dumpall output work that way in general, not just for pg_upgrade.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > We could modify pg_dump to emit RESET AUTHORIZATION in --binary-upgrade
> > mode. I am unclear if that might cause some other problems though.
>
> I finally figured out what was really bugging me about that proposal:
> it's a one-shot hack for fixing one problem that could arise from
> non-default ALTER DATABASE/ALTER ROLE settings. Who's to say there
> are not other such issues, either now or in the future?
>
> It occurs to me that a more principled way to deal with this class of
> problems would be to delay restoring ALTER DATABASE/ALTER ROLE
> settings until after everything else is done. Not sure what the
> implementation consequences of that would be. Ideally we'd make
> pg_dumpall output work that way in general, not just for pg_upgrade.

Yep, it feels like a one-off that no one else will ever hit, and there
are certainly other ALTER DATABASE SET commands that could also obstruct
a restore.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Thu, Jan 6, 2011 at 10:08 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Tom Lane wrote:
>> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>> > We could modify pg_dump to emit RESET AUTHORIZATION in --binary-upgrade
>> > mode.  I am unclear if that might cause some other problems though.
>>
>> I finally figured out what was really bugging me about that proposal:
>> it's a one-shot hack for fixing one problem that could arise from
>> non-default ALTER DATABASE/ALTER ROLE settings.  Who's to say there
>> are not other such issues, either now or in the future?
>>
>> It occurs to me that a more principled way to deal with this class of
>> problems would be to delay restoring ALTER DATABASE/ALTER ROLE
>> settings until after everything else is done.  Not sure what the
>> implementation consequences of that would be.  Ideally we'd make
>> pg_dumpall output work that way in general, not just for pg_upgrade.
>
> Yep, it feels like a one-off that no one else will ever hit, and there
> are certainly other ALTER DATABASE SET commands that could also obstruct
> a restore.
>

Did anything ever come of this discussion? On one of the databases I
was upgrading, I ran into a similar problem with roles that are set as
roles. The problem seems to stem from pg_dumpall dumping roles in
alphabetical order:

CREATE ROLE asha;
ALTER ROLE asha SET role TO 'omniti';
.. sometime later ...
CREATE ROLE omniti;

It generates an error because the ALTER ROLE fails with the role not
existing, which causes pg_upgrade to bail out (it's in the on error
stop part).

ISTM this fails in general, so not blaming pg_upgrade; I think there
should probably be a fix in pg_dumpall to create all roles first
before running the alters, but there might be some other options.

Thoughts?

Robert Treat
play: xzilla.net
work: omniti.com
hiring: l42.org/Lg

Robert Treat <rob(at)xzilla(dot)net> writes:
> Did anything ever come of this discussion?

I think it's a TODO --- nothing done about it as yet, AFAIR.

> On one of the databases I
> was upgrading, I ran into a similar problem with roles that are set as
> roles. The problem seems to stem from pg_dumpall dumping roles in
> alphabetical order:

> CREATE ROLE asha;
> ALTER ROLE asha SET role TO 'omniti';
> .. sometime later ...
> CREATE ROLE omniti;

That seems like a pretty bizarre thing to do. Why would you want such a
setting?

regards, tom lane

On Mon, Feb 28, 2011 at 06:21, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Treat <rob(at)xzilla(dot)net> writes:
>> Did anything ever come of this discussion?
>
> I think it's a TODO --- nothing done about it as yet, AFAIR.
>
>> On one of the databases I
>> was upgrading, I ran into a similar problem with roles that are set as
>> roles. The problem seems to stem from pg_dumpall dumping roles in
>> alphabetical order:
>
>> CREATE ROLE asha;
>> ALTER ROLE asha SET role TO 'omniti';
>> .. sometime later ...
>> CREATE ROLE omniti;
>
> That seems like a pretty bizarre thing to do.  Why would you want such a
> setting?

I'm sure there are several. I've seen (and done) this more than once
to ensure that the owner of newly created object is the "shared role"
and not the individual, for example.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Mon, Feb 28, 2011 at 3:42 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Mon, Feb 28, 2011 at 06:21, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Treat <rob(at)xzilla(dot)net> writes:
>>> Did anything ever come of this discussion?
>>
>> I think it's a TODO --- nothing done about it as yet, AFAIR.
>>
>>> On one of the databases I
>>> was upgrading, I ran into a similar problem with roles that are set as
>>> roles. The problem seems to stem from pg_dumpall dumping roles in
>>> alphabetical order:
>>
>>> CREATE ROLE asha;
>>> ALTER ROLE asha SET role TO 'omniti';
>>> .. sometime later ...
>>> CREATE ROLE omniti;
>>
>> That seems like a pretty bizarre thing to do.  Why would you want such a
>> setting?
>
> I'm sure there are several. I've seen (and done) this more than once
> to ensure that the owner of newly created object is the "shared role"
> and not the individual, for example.
>

Yeah, there are actually several of the roles that get set to the
"omniti" role, like the "robert" role, which doesn't have any issue
because it comes alphabetically after omniti. This also helps folks
get around several permission related issues (simplified management,
uniform permissions across users, simplified dependencies, etc..), but
object ownership is a key part of it.

Robert Treat
play: xzilla.net
work: omniti.com
hiring: l42.org/Lg

Robert Treat wrote:
> On Mon, Feb 28, 2011 at 3:42 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> > On Mon, Feb 28, 2011 at 06:21, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> Robert Treat <rob(at)xzilla(dot)net> writes:
> >>> Did anything ever come of this discussion?
> >>
> >> I think it's a TODO --- nothing done about it as yet, AFAIR.
> >>
> >>> On one of the databases I
> >>> was upgrading, I ran into a similar problem with roles that are set as
> >>> roles. The problem seems to stem from pg_dumpall dumping roles in
> >>> alphabetical order:
> >>
> >>> CREATE ROLE asha;
> >>> ALTER ROLE asha SET role TO 'omniti';
> >>> .. sometime later ...
> >>> CREATE ROLE omniti;
> >>
> >> That seems like a pretty bizarre thing to do. ?Why would you want such a
> >> setting?
> >
> > I'm sure there are several. I've seen (and done) this more than once
> > to ensure that the owner of newly created object is the "shared role"
> > and not the individual, for example.
> >
>
> Yeah, there are actually several of the roles that get set to the
> "omniti" role, like the "robert" role, which doesn't have any issue
> because it comes alphabetically after omniti. This also helps folks
> get around several permission related issues (simplified management,
> uniform permissions across users, simplified dependencies, etc..), but
> object ownership is a key part of it.

Added to TODO:

Allow pg_dumpall to output restorable ALTER USER/DATABASE SET settings

* http://archives.postgresql.org/pgsql-hackers/2010-12/msg00916.php
* http://archives.postgresql.org/pgsql-hackers/2011-01/msg00394.php
* http://archives.postgresql.org/pgsql-hackers/2011-02/msg02359.php

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +