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