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

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