Using pg_dump  and pg_dumpall I ran into the following problems and had 
addressed it earlier to this list with no response.

Using PostgreSQL 7.2.x

1. create a user with createdb privilege.
2. create a database as that user (allowing that user full reign over 
that particular db)
3. drop the createdb from the user.
4. pg_dumpall the databases to a single file
5. either use pg_restore or psql < infile to restore the databases and 
it fails.

The problem is that the user does not have createdb privilege and 
therefore the system cannot create a database with that particular user 
as the owner.

Is it possible to address this before the 7.3 release or is this concern 
no longer a problem in the 7.3 branch?




Mon, 19 Aug 2002 14:40:06 -0500
Thomas Swan <tswan@idigx.com>
pg_dump, pg_dumpall and createdb privilege
Thomas Swan <tswan@idigx.com> writes:
> 1. create a user with createdb privilege.
> 2. create a database as that user (allowing that user full reign over
> that particular db)
> 3. drop the createdb from the user.
> 4. pg_dumpall the databases to a single file
> 5. either use pg_restore or psql < infile to restore the databases and
> it fails.

This is a known problem, I remember raising it on hackers several
months ago.

> Is it possible to address this before the 7.3 release or is this
> concern no longer a problem in the 7.3 branch?

It's fixed in CVS. Databases are now created using CREATE DATABASE xxx
WITH OWNER yyy: this technique can also be used by the DBA in the
first place, avoiding the need to manually add and then remove
CREATEDB privs from the new user account.

Cheers,

Neil

-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC


19 Aug 2002 15:46:36 -0400
Neil Conway <neilc@samurai.com>
Re: pg_dump, pg_dumpall and createdb privilege






Neil Conway wrote:

Thomas Swan <tswan@idigx.com> writes:
  

1. create a user with createdb privilege.
2. create a database as that user (allowing that user full reign over
that particular db)
3. drop the createdb from the user.
4. pg_dumpall the databases to a single file
5. either use pg_restore or psql < infile to restore the databases and
it fails.
    


This is a known problem, I remember raising it on hackers several
months ago.

If there was a 7.2.2 release it would be wonderful for this to be knocked
out, too.



  

Is it possible to address this before the 7.3 release or is this
concern no longer a problem in the 7.3 branch?
    


It's fixed in CVS. Databases are now created using CREATE DATABASE xxx
WITH OWNER yyy: this technique can also be used by the DBA in the
first place, avoiding the need to manually add and then remove
CREATEDB privs from the new user account.

This is a wonderful thing for those of us running postgresql in a hosting
environment.




Mon, 19 Aug 2002 15:06:19 -0500
Thomas Swan <tswan@idigx.com>
Re: pg_dump, pg_dumpall and createdb privilege