Hi guys,

A guy on the IRC channel managed to accidentally click the wrong thing 
in phpPgAdmin and managed to remove superuser privileges from his only 
superuser.

We thought and though but it seems that there is no way to recover from 
this situation except a re-init and reload.  And what user is he even 
going to dump as?

Is there anything he can do?

Also, shouldn't we prevent this situation from ever occurring?

Please reply to all as he isn't subscribed.

Chris


Mon, 24 May 2004 21:54:20 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Big problem
On Mon, May 24, 2004 at 09:54:20PM +0800, Christopher Kings-Lynne wrote:

> in phpPgAdmin and managed to remove superuser privileges from his only 
> superuser.
> 
> We thought and though but it seems that there is no way to recover from 
> this situation except a re-init and reload.  And what user is he even 
> going to dump as?

Hmm ... I'm not sure but maybe with a standalone backend it can be
recovered?

If not, I'd suggest compiling a hacked backend with the permission check
for the ALTER USER ripped out, use that to correct the problem, and then
erase it (and the patch).

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
One man's impedance mismatch is another man's layer of abstraction.
(Lincoln Yeoh)


Mon, 24 May 2004 10:10:28 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Big problem
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> A guy on the IRC channel managed to accidentally click the wrong thing 
> in phpPgAdmin and managed to remove superuser privileges from his only 
> superuser.

No sweat; we've seen this one before.

Stop postmaster and start a standalone backend.  Now you are a
superuser, and you can create a new superuser, or just go in and UPDATE
pg_shadow to make your original user super again.  Exit standalone
backend, restart postmaster, have a beer.

The REINDEX man page is worth reading if you've never used a standalone
backend before; it covers some of the gory details.

			regards, tom lane

Mon, 24 May 2004 10:12:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Big problem 
> No sweat; we've seen this one before.

Should this situation be prevented though?

Chris


Mon, 24 May 2004 22:19:20 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Big problem
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> No sweat; we've seen this one before.

> Should this situation be prevented though?

I think the cure would probably be worse than the disease.  To make any
serious attempt at preventing remove-the-last-superuser, we'd have to
make operations on pg_shadow grab exclusive lock.  For instance, you
couldn't allow two backends to DROP USER in parallel; they might be
dropping the last two superusers, but neither one would think it was
creating a problem.  And while we could theoretically make
CREATE/ALTER/DROP USER take such locks, I dunno how you make a straight
"DELETE FROM pg_shadow" do so.

The mistake has only come up two or three times that I can remember,
which doesn't elevate it to the category of stuff that I want to install
a lot of mechanism to prevent.  Especially not mechanism that would get
in the way of reasonable uses.  I think it's sufficient to have a
recovery procedure.

			regards, tom lane

Mon, 24 May 2004 10:34:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Big problem 
В Пнд, 24.05.2004, в 16:12, Tom Lane пишет:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > A guy on the IRC channel managed to accidentally click the wrong thing 
> > in phpPgAdmin and managed to remove superuser privileges from his only 
> > superuser.
> 
> No sweat; we've seen this one before.
> 
> Stop postmaster and start a standalone backend.  Now you are a
> superuser, and you can create a new superuser, or just go in and UPDATE
> pg_shadow to make your original user super again.  Exit standalone
> backend, restart postmaster, have a beer.

The question whether we should prevent this from happening stands; I
think we should.

-- 
Markus Bertheau <twanger@bluetwanger.de>


Mon, 24 May 2004 16:36:59 +0200
Markus Bertheau <twanger@bluetwanger.de>
Re: Big problem
> The mistake has only come up two or three times that I can remember,
> which doesn't elevate it to the category of stuff that I want to install
> a lot of mechanism to prevent.  Especially not mechanism that would get
> in the way of reasonable uses.  I think it's sufficient to have a
> recovery procedure.

Hmmm - I agree it's difficult, but somehow I think it's something we 
should do.  Just imagine if some major user of postgres did it - they'd 
be screaming blue murder...

We could always implement it without locks, thereby taking care of 
99.99999% of the times it might happen, with still the availability of a 
cure even if they manage to get through that...

Chris


Mon, 24 May 2004 22:41:44 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Big problem
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Hmmm - I agree it's difficult, but somehow I think it's something we 
> should do.  Just imagine if some major user of postgres did it - they'd 
> be screaming blue murder...

Shrug.  Superusers can *always* shoot themselves in the foot in Postgres.
Try "delete from pg_proc", for instance.  This sounds right up there
with the notion of preventing a Unix superuser from doing "rm -rf /".

			regards, tom lane

Mon, 24 May 2004 10:49:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Big problem 
On Mon, 24 May 2004, Tom Lane wrote:

> I think the cure would probably be worse than the disease.  To make any
> serious attempt at preventing remove-the-last-superuser, we'd have to
> make operations on pg_shadow grab exclusive lock.  For instance, you
> couldn't allow two backends to DROP USER in parallel; they might be
> dropping the last two superusers, but neither one would think it was
> creating a problem.  And while we could theoretically make
> CREATE/ALTER/DROP USER take such locks, I dunno how you make a straight
> "DELETE FROM pg_shadow" do so.

Isn't it just enough to prevent the user with userid 1 from losing the 
superuser status. If one want to allow it one could prevent it just when 
doing the ALTER USER stuff and allow it when editing pg_shadow directly. 
Or maybe have some guc variable that write locks the user with id 1.

Given that it was so "simple" to restore I'm not sure if it's worth it or 
not, but restricting just user 1 does not give any of the problems you 
wrote about.

-- 
/Dennis Bjï¿½rklund


Mon, 24 May 2004 16:55:51 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: Big problem 
> Isn't it just enough to prevent the user with userid 1 from losing the 
> superuser status. If one want to allow it one could prevent it just when 
> doing the ALTER USER stuff and allow it when editing pg_shadow directly. 
> Or maybe have some guc variable that write locks the user with id 1.

That gets my vote - can't take superuser off id 1...

> Given that it was so "simple" to restore I'm not sure if it's worth it or 
> not, but restricting just user 1 does not give any of the problems you 
> wrote about.

Well, sergio sure wasn't very happy...

And if I ever get around to my patch that separates out superuser and 
catalog modification privileges, superusers will no longer necessarily 
be able to 'delete from pg_proc';

Chris


Mon, 24 May 2004 23:03:02 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Big problem
On Mon, May 24, 2004 at 11:23:09AM -0700, Joe Conway wrote:
> Tom Lane wrote:
> >Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> >>Hmmm - I agree it's difficult, but somehow I think it's something we 
> >>should do.  Just imagine if some major user of postgres did it - they'd 
> >>be screaming blue murder...
> >
> >Shrug.  Superusers can *always* shoot themselves in the foot in Postgres.
> >Try "delete from pg_proc", for instance.  This sounds right up there
> >with the notion of preventing a Unix superuser from doing "rm -rf /".
> 
> FWIW, I've seen a unix superuser do a recursive chmod 777 on /, and I've 
> seen a Windows server admin recursively deny EVERYTHING from EVERYBODY 
> starting at c:\. In both cases, we found that's why we keep regular 
> backups ;-)

I've personally done rm -fr /, but this doesn't mean we couldn't do
better than imitate Unix permission scheme.  In fact, latest efforts are
trying to get rid of a all-powerful superuser by using more granular
"capabilities".

Maybe we don't need to exclusive-lock the entire ALTER USER operation;
perhaps a lock escalation method could be used.  OTOH I agree this
particular problem may not need a solution.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La grandeza es una experiencia transitoria.  Nunca es consistente.
Depende en gran parte de la imaginaciï¿½n humana creadora de mitos"
(Irulan)


Mon, 24 May 2004 14:22:41 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Big problem
Tom Lane wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>>Hmmm - I agree it's difficult, but somehow I think it's something we 
>>should do.  Just imagine if some major user of postgres did it - they'd 
>>be screaming blue murder...
> 
> Shrug.  Superusers can *always* shoot themselves in the foot in Postgres.
> Try "delete from pg_proc", for instance.  This sounds right up there
> with the notion of preventing a Unix superuser from doing "rm -rf /".

I have to agree.

FWIW, I've seen a unix superuser do a recursive chmod 777 on /, and I've 
seen a Windows server admin recursively deny EVERYTHING from EVERYBODY 
starting at c:\. In both cases, we found that's why we keep regular 
backups ;-)

Joe


Mon, 24 May 2004 11:23:09 -0700
Joe Conway <mail@joeconway.com>
Re: Big problem
Tom Lane wrote:

> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> 
>>Hmmm - I agree it's difficult, but somehow I think it's something we 
>>should do.  Just imagine if some major user of postgres did it - they'd 
>>be screaming blue murder...
> 
> 
> Shrug.  Superusers can *always* shoot themselves in the foot in Postgres.
> Try "delete from pg_proc", for instance.  This sounds right up there
> with the notion of preventing a Unix superuser from doing "rm -rf /".

Why not simply add a flag "undeleteable" applicable only to super user?
In this way is enough in the initdb fase create the postgres user as
undeleateable.

I think this is resonable.


Regards
Gaetano Mendola



Mon, 24 May 2004 20:37:11 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Big problem
On Mon, 2004-05-24 at 16:03, Christopher Kings-Lynne wrote:
> > Isn't it just enough to prevent the user with userid 1 from losing the 
> > superuser status. If one want to allow it one could prevent it just when 
> > doing the ALTER USER stuff and allow it when editing pg_shadow directly. 
> > Or maybe have some guc variable that write locks the user with id 1.
> 
> That gets my vote - can't take superuser off id 1...

Gets my vote too, postgres user can't take superuser off.

> 
> > Given that it was so "simple" to restore I'm not sure if it's worth it or 
> > not, but restricting just user 1 does not give any of the problems you 
> > wrote about.
> 
> Well, sergio sure wasn't very happy...
yes I wasn't but "Stop postmaster and start a standalone backend.  Now
you are asuperuser, and you can create a new superuser, or just go in
and UPDATE pg_shadow to make your original user super again.  Exit
standalone backend, restart postmaster, have a beer."
worked !

thanks ,

> 
> And if I ever get around to my patch that separates out superuser and 
> catalog modification privileges, superusers will no longer necessarily 
> be able to 'delete from pg_proc';
> 
> Chris
-- 
Sérgio M. B.


Mon, 24 May 2004 21:32:43 +0100
=?ISO-8859-1?Q?S=E9rgio?= Monteiro Basto <sergiomb@netcabo.pt>
Re: Big problem
Christopher Kings-Lynne wrote:

>> The mistake has only come up two or three times that I can remember,
>> which doesn't elevate it to the category of stuff that I want to install
>> a lot of mechanism to prevent.  Especially not mechanism that would get
>> in the way of reasonable uses.  I think it's sufficient to have a
>> recovery procedure.
>
>
> Hmmm - I agree it's difficult, but somehow I think it's something we 
> should do.  Just imagine if some major user of postgres did it - 
> they'd be screaming blue murder...

IMHO we (that is Christopher, me and others maintaining easy to (mis)use 
tools) should warn the users about what they're going to do.

Regards,
Andreas



Tue, 25 May 2004 01:15:48 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
Re: Big problem
> IMHO we (that is Christopher, me and others maintaining easy to (mis)use 
> tools) should warn the users about what they're going to do.

Yes, I'm going to have to modify phpPgAdmin methinks.

Chris


Tue, 25 May 2004 09:44:47 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Big problem