Thread: pg_dumpall perms errata...

Quick FWIW.  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

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