Function to kill backend

On Fri, 2004-04-02 at 16:52, Magnus Hagander wrote:

Hi!

When debugging on win32, I've created myself a little function that I
feel should be added to the "backend proper". While it adds a lot of
vlaue on win32, I think it adds quite a bit of value on non-win32
platforms as well...

The function is pg_kill_backend(<pid>,<signal>). superuser-only, of
course. Which simply sends a signal to the specified backend -
querycancel, terminate, etc.

Nice.. My new favourite command is going to be:

SELECT pg_kill_backend(procpid, 'TERM') FROM pg_stat_activity WHERE
current_query LIKE '<IDLE>%';

--
Rod Taylor <rbt [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc

Magnus Hagander wrote:

Hi!

When debugging on win32, I've created myself a little function that I
feel should be added to the "backend proper". While it adds a lot of
vlaue on win32, I think it adds quite a bit of value on non-win32
platforms as well...

The function is pg_kill_backend(<pid>,<signal>). superuser-only, of
course. Which simply sends a signal to the specified backend -
querycancel, terminate, etc.

If' we're going to have this shouldn't it be a proper command? And maybe
an internal shutdown command to go with it?

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

Magnus Hagander wrote:

The function is pg_kill_backend(<pid>,<signal>). superuser-only, of
course. Which simply sends a signal to the specified backend -
querycancel, terminate, etc.

If' we're going to have this shouldn't it be a proper command? And maybe
an internal shutdown command to go with it?

I don't like the idea at all, but if we were to have something
it would definitely need to be a lot more constrained than
send-any-signal-to-any-postgres-process ... even for a superuser,
that's a mighty fat-gauge foot-gun.

regards, tom lane

Hi!

When debugging on win32, I've created myself a little function that I
feel should be added to the "backend proper". While it adds a lot of
vlaue on win32, I think it adds quite a bit of value on non-win32
platforms as well...

The function is pg_kill_backend(<pid>,<signal>). superuser-only, of
course. Which simply sends a signal to the specified backend -
querycancel, terminate, etc.

If' we're going to have this shouldn't it be a proper command?
And maybe
an internal shutdown command to go with it?

I guess it could be. I guess:

1) I don't know how to do one of those ;-) Which is why I didn't even
think it.

2) Won't that clutter up the namespace more, by introducing more
keywords that you can't use for other things?

3) Will it still be possible to do the kind of things Rod mentioned,
e.g.
SELECT pg_kill_backend(procpid, 'TERM') FROM pg_stat_activity WHERE
current_query LIKE '<IDLE>%';
?

//Magnus

-----Original Message-----
From: Magnus Hagander [mailto:mha@sollentuna.net]
Sent: Friday, April 02, 2004 2:34 PM
To: Andrew Dunstan; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Function to kill backend

Hi!

When debugging on win32, I've created myself a little

function that I

feel should be added to the "backend proper". While it adds

a lot of

vlaue on win32, I think it adds quite a bit of value on non-win32
platforms as well...

The function is pg_kill_backend(<pid>,<signal>). superuser-only, of
course. Which simply sends a signal to the specified backend -
querycancel, terminate, etc.

If' we're going to have this shouldn't it be a proper command?
And maybe
an internal shutdown command to go with it?

I guess it could be. I guess:

1) I don't know how to do one of those ;-) Which is why I
didn't even think it.

2) Won't that clutter up the namespace more, by introducing
more keywords that you can't use for other things?

3) Will it still be possible to do the kind of things Rod
mentioned, e.g. SELECT pg_kill_backend(procpid, 'TERM') FROM
pg_stat_activity WHERE current_query LIKE '<IDLE>%'; ?

Sybase had something like that:

Syb_kill <pid> to kill a dangling process.

And the undocumented:

Syb_terminate <pid>
to absolutely, positively kill it (Syb_kill only worked sometimes).

In general, I think this approach is a bit worrisome. It reminds one of
the famous tagline:

"Tip: Don't kill -9 the postmaster."

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

Magnus Hagander wrote:

The function is pg_kill_backend(<pid>,<signal>). superuser-only, of
course. Which simply sends a signal to the specified backend -
querycancel, terminate, etc.

If' we're going to have this shouldn't it be a proper command? And maybe
an internal shutdown command to go with it?

I don't like the idea at all, but if we were to have something
it would definitely need to be a lot more constrained than
send-any-signal-to-any-postgres-process ... even for a superuser,
that's a mighty fat-gauge foot-gun.

What sort of constraints do you have in mind?

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

Tom Lane wrote:

it would definitely need to be a lot more constrained than
send-any-signal-to-any-postgres-process ... even for a superuser,
that's a mighty fat-gauge foot-gun.

What sort of constraints do you have in mind?

I'd limit it to SIGINT (query cancel) and SIGTERM (fast shutdown),
and I'm not even real sure about SIGTERM. That facility is designed to
work in the case of shutting down all backends together --- I'm not sure
I want to promise that it behaves pleasantly to SIGTERM one backend and
leave the rest going. Nor do I see a real good use-case for it.

Also, no killing processes that aren't regular backends (eg, the
bgwriter, the stats processes, and most especially the postmaster).

Another point is that killing by PID is not necessarily what you want to
do --- kill by transaction ID might be a better API, especially for
query-cancel cases.

regards, tom lane

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

Tom Lane wrote:

it would definitely need to be a lot more constrained than
send-any-signal-to-any-postgres-process ... even for a superuser,
that's a mighty fat-gauge foot-gun.

What sort of constraints do you have in mind?

I'd limit it to SIGINT (query cancel) and SIGTERM (fast shutdown),
and I'm not even real sure about SIGTERM. That facility is designed to
work in the case of shutting down all backends together --- I'm not sure
I want to promise that it behaves pleasantly to SIGTERM one backend and
leave the rest going. Nor do I see a real good use-case for it.

Also, no killing processes that aren't regular backends (eg, the
bgwriter, the stats processes, and most especially the postmaster).

Another point is that killing by PID is not necessarily what you want to
do --- kill by transaction ID might be a better API, especially for
query-cancel cases.

Seems like useful functionality. Right now, how does an administrator
kill another backend from psql? They can't.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

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?"

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.

Having to log into the database server locally to execute such
operations doesn't seem that bad to me.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

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?"

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.

Having to log into the database server locally to execute such
operations doesn't seem that bad to me.

If they can read/write your data (as superuser), killing backends is the
least worry.

I can see it as useful as part of pg_stat_activity output.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane wrote:

it would definitely need to be a lot more constrained than
send-any-signal-to-any-postgres-process ... even for a superuser,
that's a mighty fat-gauge foot-gun.

What sort of constraints do you have in mind?

I'd limit it to SIGINT (query cancel) and SIGTERM (fast shutdown),

Actually, that is a restriction that's already there - just didn't get
into those details. Since the functino as I wrote it so far just takes
signal name as a string (can't rely on signal numbers being identical
across platforms, right?), and then comparing it with a fixed set of
signals.

and I'm not even real sure about SIGTERM. That facility is designed to
work in the case of shutting down all backends together ---
I'm not sure
I want to promise that it behaves pleasantly to SIGTERM one backend and
leave the rest going. Nor do I see a real good use-case for it.

Really? Then what is the recommended way of shutting down a backend that
you are not connected to, as an administrator? Even if you are logged in
with shell access?

I may have been doing things wrong for a long time, because I have
certainly killed backends with TERM many times without problems. If
that's not safe, there really ought to be a tip on the mailinglists to
complement the "don't kill -9 the postmaster" with "and don't ever kill
the backends, period"? I'm sure I'm not the only one who has done
that...

Also, no killing processes that aren't regular backends (eg, the
bgwriter, the stats processes, and most especially the postmaster).

That sounds like a reasonable limitation to add. Either by specifically
excluding these processes, or by limiting it to only work on the
backends currently listed in pg_stat_activity.

Another point is that killing by PID is not necessarily what
you want to
do --- kill by transaction ID might be a better API, especially for
query-cancel cases.

Well, in my scenarios, killing by PID is what I need. But I guess
transaction IDs might be added to the pg_stat_activity, which would give
me the same functionality (I usually check that one first to see what a
backend does, before I do anything) - and then some, because the
transaction id carries other information as well.
Question on that - how will it handle an idle backend (that has not
explicitly opened a transaction, and is not executing a command in an
implicit transaction)?

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.
Having to log into the database server locally to execute such
operations doesn't seem that bad to me.

It does to me. I prefer being able to admin the server without having to
do a separate login. I also much prefer being able to delegate the
capability to terminate a backend, interrupt a long-running query, etc
to someone who does not have to have shell access on the server. I guess
it depends on the environment.

Bruce Momjian <pgman@candle.pha.pa.us> writes:

If they can read/write your data (as superuser), killing backends is

the

least worry.

That's pretty much the assumption I was working under.

//Magnus

Magnus Hagander wrote:

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.
Having to log into the database server locally to execute such
operations doesn't seem that bad to me.

It does to me. I prefer being able to admin the server without having to
do a separate login. I also much prefer being able to delegate the
capability to terminate a backend, interrupt a long-running query, etc
to someone who does not have to have shell access on the server. I guess
it depends on the environment.

Bruce Momjian <pgman@candle.pha.pa.us> writes:

If they can read/write your data (as superuser), killing backends is

the

least worry.

That's pretty much the assumption I was working under.

Perhaps for the paranoid we could invent a setting which turns the
facility off. Personally, I don't usually allow a superuser *any* access
except from the local host - maybe that would be an answer.

cheers

andrew

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

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

On 2-Apr-04, at 5:16 PM, Andrew Dunstan wrote:

If' we're going to have this shouldn't it be a proper command?

Why? What benefit would this offer over implementing this feature as a
function?

-Neil

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

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

Neil Conway said:

On 2-Apr-04, at 5:16 PM, Andrew Dunstan wrote:

If' we're going to have this shouldn't it be a proper command?

Why? What benefit would this offer over implementing this feature as a
function?

psql help

cheers

andrew

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.

Consider a scenario like:
User A starts transaction.
User A issues a LOCK TABLE (or does something to lock it)
User A goes on vacation without commit/rollback

User A might well be Program A instead, of course. Caught in a tight
loop, waiting for user input, or whatever.

In this case, SIGINT (query cancel) will not help, because all locks
held by the transaction will still be held.

If there was a way to "force rollback" a connection, that could be done.
Buf AFAIK there are none? And would those be safer/better than
terminating the backend?

//Magnus