Re: pg_dumpall perms errata...

Would it be possible to change the restore order in pg_dump and
pg_dumpall that way the below situation will work for users that
aren't allowed to create databases? -sc

> When I create user accounts for postgresql, I setup users so that
> they can't create databases or users. No biggie. I also create
> databases that are the same as the user's username and set the
> datdba to the user's userID. This system works out great until
> doing a pg_dumpall and psql -f foo.sql restore.
>
>
> CREATE USER "user1" WITH SYSID 834 PASSWORD 'ugabooga' NOCREATEDB NOCREATEUSER;
>
> [snip]
>
> \connect template1 user1
> CREATE DATABASE "user1" WITH TEMPLATE = template0 ENCODING = 'SQL_ASCII';
> \connect user1 user1
>
>
>
> Would it be possible to create the database from the original
> connecting user, then update pg_database with the appropriate sysid?
> Something like the following:
>
> CREATE USER "user1" WITH SYSID 834 PASSWORD 'ugabooga' NOCREATEDB NOCREATEUSER;
>
> [snip]
>
> CREATE DATABASE "user1" WITH TEMPLATE = template0 ENCODING = 'SQL_ASCII';
> UPDATE pg_database SET datdba = 834 WHERE datname = "user1";
>
> \connect user1 user1
> /* Continue restoring data.... */

--
Sean Chittenden