Re: Big problem

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

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)

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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

> No sweat; we've seen this one before.

Should this situation be prevented though?

Chris

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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

В Пнд, 24.05.2004, в 16:12, Tom Lane пишет:
> Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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(at)bluetwanger(dot)de>

> 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

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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

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

> 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

On Mon, May 24, 2004 at 11:23:09AM -0700, Joe Conway wrote:
> Tom Lane wrote:
> >Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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)

Tom Lane wrote:
> Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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

Tom Lane wrote:

> Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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

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.

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

> 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