postgres: import of previous dump fails

[Lamar Owen]

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.
-- 
Lamar Owen
RPM Maintainer, PostgreSQL Global Development Group
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu