Re: A fixed user id for the postgres user?

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