Thread: A fixed user id for the postgres user?
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
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
> 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
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
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
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