We've had some problem reports that the current practice of initdb
assigning to the postgres user the same usesysid as the user id of the
Unix user running initdb has caused some clashes.

(Imagine this scenario:  A few years ago you installed BluePants Linux
5.0, created a user for PostgreSQL, id 501, created a database.  Later you
created a few real users, which get uids 502, 503, etc.  Then you
pg_dumpall that database (which saves the sysid).  Now you install
BluePants Linux 7.0 on a new box, create a new user for PostgreSQL, which
turns out to be 502, because foolishly you created a user for TheirSQL
first.  So now you replay your pg_dumpall and you have two users with id
502.  Boom.)

One idea to resolve this, by getting rid of the usesysid column in favour
of the oid column has fallen by the wayside (for some valid reasons), so
the problem remains.  I think the simplest fix would be to assign a fixed
usesysid of 1.  There still is the possibility of changing that with an
initdb option, as there always has been.  (We could also ensure that
CREATE USER never assigns ids below, say, 10, so that if for who knows
what reason we decide to add more users into the bootstrap installation we
have some room.)

Comments?

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Sun, 19 Aug 2001 18:52:38 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
A fixed user id for the postgres user?
Peter Eisentraut <peter_e@gmx.net> writes:
> I think the simplest fix would be to assign a fixed usesysid of 1.

Slightly more flexible: make the ID number an initdb option, with a
default of 1.  This would let people do it the old way if they wanted.
Doesn't seem very critical though.

			regards, tom lane

Sun, 19 Aug 2001 13:50:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: A fixed user id for the postgres user? 
> One idea to resolve this, by getting rid of the usesysid column in favour
> of the oid column has fallen by the wayside (for some valid reasons), so
> the problem remains.  I think the simplest fix would be to assign a fixed
> usesysid of 1.  There still is the possibility of changing that with an
> initdb option, as there always has been.  (We could also ensure that
> CREATE USER never assigns ids below, say, 10, so that if for who knows
> what reason we decide to add more users into the bootstrap installation we
> have some room.)

Do we do any mapping from uid to usesysid in the code?  It is all by
user name right, so changing it will not affect authentication.

Remember local PEER/CRED authentication passes ownership by uid, not
name and people mostly use sameuser mapping.  Does this work?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 20 Aug 2001 11:36:45 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: A fixed user id for the postgres user?