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 [EMAIL PROTECTED] 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
signature.asc
Description: This is a digitally signed message part
