On Mon, Dec 08, 2003 at 04:02:44PM -0500, Lamar Owen wrote: > On Monday 08 December 2003 01:54 pm, Charles Curley wrote: > > 7.2.3. When I try to import the old dump as user postgres, I see the > > following: > > > CREATE USER "root" WITH SYSID 0 NOCREATEDB NOCREATEUSER; > [Sorry it took me so long to reply to this; I had to do some research on it > (while working, too)....] > > This is no longer supported, which is what the error message is telling you. > Trying this line at a psql prompt in 7.4, I find the same behavior. The only > reason you didn't see this prior to 7.3 was that the use of CREATE USER > versus a COPY of the SYSIDs was done for 7.3, but the problem existed long > before that. > > > I didn't see anything useful at http://www.postgresql.org. > > This change happened a long time ago. Specifically, file user.c in > src/backend/commands was changed on September 8, 2001 with this change. > (Reference the web CVS > URL:http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/commands/user.c?rev=1.83&content-type=text/x-cvsweb-markup > to see the code right after the change: to see the diff between that and the > previous version see > http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/commands/user.c.diff?r1=1.82&r2=1.83 > and for the complete history see > http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/commands/user.c > ) > > There can be no database user ID less than 1; SYSID 1 is the user initdb > creates and is by default the database superuser (the PostgreSQL SYSID's do > not have to match the system UID for ident authentication to work; the names > must match: case in point, the default UID for the user postgres is 26, but > the SYSID for postgres is 1). > > The last release that the use of SYSID of 0 was supported would have been > 7.1.x, and even then it wasn't supported. Try it out yourself on your 7.2.3 > database and see if the raw CREATE USER command you posted will execute (you > can see the error even if the SYSID of 0 already exists, since this test is > earlier in the codepath than the duplicate test). It imported into 7.2 > because the 7.1 pg_dump didn't create CREATE USER commands, it used COPY for > this functionality, which bypasses this test. > > The only clue in the Release Notes for 7.2 that this happened is a line about > the SuperUser ID being fixed at 1; the fact that a SYSID of 0 was unavailable > is not mentioned. Right. Someone on another list told me that there does not have to be any correlation between the ID of a postgres user and a user on the host computer. Workaround was to edit the dump file to give "root" a SYSID of 2. It seems to be working but I haven't fully exercised the system. -- Charles Curley /"\ ASCII Ribbon Campaign Looking for fine software \ / Respect for open standards and/or writing? X No HTML/RTF in email http://www.charlescurley.com / \ No M$ Word docs in email Key fingerprint = CE5C 6645 A45A 64E4 94C0 809C FFF6 4C48 4ECD DFDB
Attachment:
pgpxLMNYqiDS1.pgp
Description: PGP signature
