Re: postgres 7.1 -> 7.2 ... sigh

[Andreas Goesele <Goesele@hfph.mwn.de>]

Jason Chambers <jason.chambers@ntlworld.com> writes:

> > CREATE USER "user" WITH SYSID 1 CREATEDB CREATEUSER;
> > ERROR:  CREATE USER: sysid 1 is already assigned
>                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^
> 
> Sysid 1 is probably assigned to the user postgres created by the
> install. To get your data reloaded into the database try editing the
> dumped file so that 'user' is now number two or something.

Thanks! I followed you suggestion. And then:

$ psql -e template1 < db.out
You are now connected to database template1.
DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');
DELETE 0
CREATE USER "user" WITH SYSID 2 CREATEDB CREATEUSER;
CREATE USER
DELETE FROM pg_group;
DELETE 0
You are now connected to database template1 as user postgres.
CREATE DATABASE "accounts" WITH TEMPLATE = template0 ENCODING = 'LATIN1';
ERROR:  CREATE DATABASE: database "accounts" already exists
You are now connected to database accounts as user postgres.
\connect: FATAL 1:  IDENT authentication failed for user "user"

What I then did was to delete the lines 

DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');

CREATE USER "user" WITH SYSID 2 CREATEDB CREATEUSER;

DELETE FROM pg_group;

from db.out, delete every line with just "\connect postgres", remove
for every "\connect ... postgres" the "postgres" and finally run

psql -e template1 < db.out

again as user.

This gave me my databases back, but I'm sure that wasn't the right way
to do it, and maybe there are some security problems involved.

Any comments? What would have been the right way?

Andreas Goesele


-- 
To UNSUBSCRIBE, email to debian-user-request@lists.debian.org 
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org