Thread: A fixed user id for the postgres user?

A fixed user id for the postgres user?

From
Peter Eisentraut
Date:
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



Re: A fixed user id for the postgres user?

From
Tom Lane
Date:
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


Re: A fixed user id for the postgres user?

From
Bruce Momjian
Date:
> 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,
Pennsylvania19026
 


Re: A fixed user id for the postgres user?

From
Tom Lane
Date:
Peter Eisentraut wrote:
> 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.
> ...
> I think the simplest fix would be to assign a fixed usesysid of 1.

I was initially lukewarm about this idea, but I've just thought of a
reason to like it ;-).

I've been thinking a little bit about how one might recover from Really
Stupid Mistakes, like deleting one's only superuser pg_shadow entry.
(Let's see ... you can't make another one ... and you can't easily run
pg_dump without a superuser identity ... is your database a lost cause?)

I think that the only way to get around this kind of thing in extremis
is to shut down the postmaster and run a standalone backend, in which
you can do a CREATE USER or whatever other surgery you need to perform.
Accordingly, a standalone backend should not do any permission-checking;
the fact that you are able to start a backend with access to the
database files should be good enough evidence that you are the
superuser.

However there's still a problem, if you've made this particular variety
of Really Stupid Mistake: the standalone backend won't fire up.

$ postgres template1
DEBUG:  database system was shut down at 2001-08-21 17:56:07 EDT
DEBUG:  checkpoint record is at (0, 39113800)
DEBUG:  redo record is at (0, 39113800); undo record is at (0, 0); shutdown TRUE

DEBUG:  next transaction id: 8595; next oid: 262492
DEBUG:  database system is ready
FATAL 1:  user "postgres" does not exist
DEBUG:  shutting down
DEBUG:  database system is shut down

What I'm thinking is that if we hard-wired usesysid = 1 for the
superuser, it'd be possible to arrange for standalone backends to fire
up with that sysid and superuserness assumed, and not consult pg_shadow
at all.  Then you'd have a platform in which you could do CREATE USER.

Thoughts?

Next mind-bending problem: recover from DROP TABLE pg_class ;-)
        regards, tom lane


Re: A fixed user id for the postgres user?

From
Peter Eisentraut
Date:
Tom Lane writes:

> What I'm thinking is that if we hard-wired usesysid = 1 for the
> superuser, it'd be possible to arrange for standalone backends to fire
> up with that sysid and superuserness assumed, and not consult pg_shadow
> at all.  Then you'd have a platform in which you could do CREATE USER.

I had always figured that you could use bki to recover from these things,
but a quick attempt shows that you can't.

You proposal makes sense from a Unix admin point of view (booting into
single user mode without password).  Since we have a check against root
access and against too liberal PGDATA permissions, I think this would be
safe.  Possibly we need to guard against setgid problems as well.

> Next mind-bending problem: recover from DROP TABLE pg_class ;-)

Definitely BKI land.  But that usecatupd field does make some sense,
apparently.

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



Re: A fixed user id for the postgres user?

From
Peter Eisentraut
Date:
Tom Lane writes:

> I've been thinking a little bit about how one might recover from Really
> Stupid Mistakes, like deleting one's only superuser pg_shadow entry.

> What I'm thinking is that if we hard-wired usesysid = 1 for the
> superuser, it'd be possible to arrange for standalone backends to fire
> up with that sysid and superuserness assumed, and not consult pg_shadow
> at all.  Then you'd have a platform in which you could do CREATE USER.

FYI:  I'm working on this now.  It seems to work out nicely; at least I
was able to recover from DELETE FROM pg_shadow; without a problem.

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