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