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
