Thread

1. Re: Function to kill backend

   Josh Berkus <josh@agliodbs.com> — 2004-04-04T01:09:35Z

   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
   
   
   

2. Re: Function to kill backend

   Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2004-04-04T02:56:39Z

   > 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
   
   

3. Re: Function to kill backend

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-04-04T04:38:44Z

   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
   
   

4. Re: Function to kill backend

   Mike Mascari <mascarm@mascari.com> — 2004-04-04T06:04:32Z

   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