Tom,

> > Seems like useful functionality.  Right now, how does an administrator
> > kill another backend from psql?  They can't.
> 
> The question to ask is "should they be able to?"

And the answer is, "Yes".

This is a commonly requested feature by DBA's migrating from SQL Server and 
Oracle.    In those databases, there is a GUI to monitor database requests, 
and potentially kill them to resolve deadlocks or runaway queries (though, in 
the case of SQL server, it does not work).  Right now, it is very difficult 
for any of our GUI projects to construct such an interface due to the 
necessity of root shell access.

> I think any such facility is inherently a security risk, since it means
> that a remote attacker who's managed to break into your superuser
> account can randomly zap other backends.  Now admittedly there's plenty
> of other mischief he can do with superuser privs, but that doesn't mean
> we should hand him a pre-loaded, pre-sighted cannon.

And requiring DBAs to use root shell access whenever they want to stop a 
runaway query is somehow a good security approach?     If nothing else, it 
exposes lots of DBAs to the temptation to use SIGKILL instead off SIGINT or 
SIGTERM, making the database shut down.   And I, personally, worry about the 
number of root shells I have to use, becuase every once in a while I forget 
and leave one open at the end of the day.

Killing backends with runaway queries is a routine administrative task.   It 
should be possible to accomplish it remotely, using tools provided by 
PostgreSQL instead of the command shell, because then it is possible for us 
to limit what those tools can do.

Further, if an intruder has superuser access, having them kill random backends 
is the last thing I'm worried about.  "DROP DATABASE" ranks a lot higher.   
In fact, it would be nice if they started killing random backends because 
then I'd know something was wrong.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Sat, 3 Apr 2004 17:09:35 -0800
Josh Berkus <josh@agliodbs.com>
Re: Function to kill backend 
> This is a commonly requested feature by DBA's migrating from SQL Server and 
> Oracle.    In those databases, there is a GUI to monitor database requests, 
> and potentially kill them to resolve deadlocks or runaway queries (though, in 
> the case of SQL server, it does not work).  Right now, it is very difficult 
> for any of our GUI projects to construct such an interface due to the 
> necessity of root shell access.

Yes, MySQL can do it too.

http://www.mysql.com/doc/en/KILL.html

I would love to have a KILL <pid> command in postgres.  I don't know how 
you would restrict it to only being able to kill postgres backends though.

Chris

Sun, 04 Apr 2004 10:56:39 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Function to kill backend
Josh Berkus <josh@agliodbs.com> writes:
> Killing backends with runaway queries is a routine administrative
> task.

Cancelling runaway queries is a routine task.  I'm less convinced that a
remote kill (ie SIGTERM) facility is such a great idea.

			regards, tom lane

Sat, 03 Apr 2004 23:38:44 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Function to kill backend 
Tom Lane wrote:

> Josh Berkus <josh@agliodbs.com> writes:
> 
>>Killing backends with runaway queries is a routine administrative
>>task.
> 
> 
> Cancelling runaway queries is a routine task.  I'm less convinced that a
> remote kill (ie SIGTERM) facility is such a great idea.

Of course, cancelling runaway queries on Oracle is only a necessity 
if the DBA hasn't made use of resource limits - PROFILEs. ;-)

Mike Mascari




Sun, 04 Apr 2004 01:04:32 -0500
Mike Mascari <mascarm@mascari.com>
Re: Function to kill backend