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@hagander.net> wrote:
> > On Mon, Feb 28, 2011 at 06:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Robert Treat <rob@xzilla.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@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +