On Mon, 2002-04-22 at 03:17, Andreas Goesele wrote:
...
> 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.
The right way is any way that works! There should be no security
implication if all the databases have ended up owned by the correct
user. You will probably have ended up with some system tables owned by
"user" rather than by postgres, but I don't think that will be a
problem...(probably!)
> Any comments? What would have been the right way?
What the postinst script does is to rewrite pg_hba.conf to allow all
access from localhost while it is doing the reinstallation of the
databases. (It reduces the security risk by changing the listening port
at the same time.)
The "right way" would have been to set "local all trust" in pg_hba.conf
while you were doing the reinstallation. That would have saved you a
bit of time.
I am interested to know how the original problem arose. Did you at some
stage manage to insert a duplicate sysid into the user list? Or did you
specify sysid 1 for "user" at the time when postgresql installation used
to use the postgres unix uid by default?
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"If my people, which are called by my name, shall
humble themselves, and pray, and seek my face, and
turn from their wicked ways; then will I hear from
heaven, and will forgive their sin, and will heal
their land." II Chronicles 7:14
Attachment:
signature.asc
Description: This is a digitally signed message part