postgres: import of previous dump fails

[Charles Curley]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
URL: <http://listman.redhat.com/archives/fedora-list/attachments/20031209/6de41819/attachment-0001.sig>