Re: Function to kill backend

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