Thread: pg_dump: bug?

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

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

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

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