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

Tue, 21 Aug 2001 18:17:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: A fixed user id for the postgres user?
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


Wed, 22 Aug 2001 18:03:33 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: A fixed user id for the postgres user?
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


Sat, 8 Sep 2001 00:19:18 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: A fixed user id for the postgres user?