Thread

1. pg_dump: bug?

   Neil Conway <nconway@klamath.dyndns.org> — 2002-02-02T04:40:55Z

   Hi,
   
   The problem can be reproduced by following these steps.
   
   1) initdb a new DB cluster
   
   2) connect to template1 as postgres
   
   3) CREATE USER foo WITH CREATEDB;
   
   4) \c template1 foo
   
   5) CREATE DATABASE foo;
   
   6) \c template1 postgres
   
   7) ALTER USER foo NOCREATEDB;
   
   8) (quit psql); pg_dumpall
   
   The dump that is produced will attempt to re-create the database like
   so:
   
       (1) create a user 'foo' with 'nocreatedb', since that's what the
   latest data in pg_shadow says to do
   
       (2) database 'foo' that was created by user 'foo': so the next step
   is to connect as 'foo' and create the database
   
   Obviously, the 2nd step fails. This wasn't too annoying for me (as I was
   just doing development), but for, say, a corporate DBA migrating a
   couple hundred GB of data in a production environment, it could be a
   _real_ annoyance.
   
   Now, is this a bug?
   
   Perhaps pg_dump could check the current user permissions and see if such
   a contradictory situation will arise? IMHO, it is better to detect such
   a condition during the dump and bailout than to create a dump we _know_
   won't restore properly. This still seems like a kludge...
   
   Maybe we could not allow "ALTER USER foo NOCREATEDB" if there is an
   entry in pg_database where 'datdba' = the user's sysID. Or at the least,
   emit a warning...
   
   Anyway, I just ran into this so I figured I'd toss it out for some
   comments. This is running RC2, BTW.
   
   Cheers,
   
   Neil
   
   -- 
   Neil Conway <neilconway@rogers.com>
   PGP Key ID: DB3C29FC
   
   
   

2. Re: pg_dump: bug?

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-02-02T05:29:34Z

   Neil Conway <nconway@klamath.dyndns.org> writes:
   > Now, is this a bug?
   
   Good question.  I don't think this is the only example of a
   non-self-consistent situation that could arise after a series of
   ALTER commands; I'm not sure that we can or should try to solve
   every one.
   
   However, it does seem that a superuser should be able to create
   databases on behalf of users who can't themselves do so.   So
   I'd say that we need a "CREATE DATABASE foo WITH OWNER bar" option.
   Then pg_dumpall should emit such critters rather than the
   circumlocution it uses now.
   
   			regards, tom lane
   
   

3. Re: pg_dump: bug?

   Don Baccus <dhogaza@pacifier.com> — 2002-02-02T06:14:50Z

   Tom Lane wrote:
   
   > Neil Conway <nconway@klamath.dyndns.org> writes:
   > 
   >>Now, is this a bug?
   >>
   > 
   > Good question.  I don't think this is the only example of a
   > non-self-consistent situation that could arise after a series of
   > ALTER commands; I'm not sure that we can or should try to solve
   > every one.
   
   
   Ummm...at some point in time, PG will need to be able to dump and 
   recreate a database no matter what the history.
   
   No matter whether or not "non-self-consistent situations" occur.  PG 
   needs to be able to snapshot and restore current state, whether or not 
   it is a horror.
   
   Or else you might as well state that, like MySQL, the only thing to do 
   is to knock down the database, tar files, and hope no one is interested 
   in 24x7 uptime.
   
   When my clients ask about Oracle vs. PG I like to say "PG".  They still 
   mostly say "Oracle" and I oblige.
   
   -- 
   Don Baccus
   Portland, OR
   http://donb.photo.net, http://birdnotes.net, http://openacs.org
   
   
   

4. Re: pg_dump: bug?

   Gavin Sherry <swm@linuxworld.com.au> — 2002-02-02T11:27:00Z

   On Sat, 2 Feb 2002, Tom Lane wrote:
   
   > However, it does seem that a superuser should be able to create
   > databases on behalf of users who can't themselves do so.   So
   > I'd say that we need a "CREATE DATABASE foo WITH OWNER bar" option.
   
   I have submitted a patch to enable this. From memory Bruce put it against
   7.3.
   
   Gavin