Thread

Function to kill backend

Magnus Hagander <mha@sollentuna.net> — 2004-04-02T21:52:16Z

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.

The advantage over using the kill command from a shell account is, well,
you don't need shell access to the db server. On win32, that's going to
be more common than on Unix - plus, if you want to signal a specific
backend, you need a special tool (can't do from tas kmanager/service
manager etc - service manager can only do the postmaster, and task
manager can only do kill -9).

I also think a function like this could be good to have for e.g.
pgadmin, to implement some more "management functionality". For example,
in MSSQL I can go into a view called "current activity", pick a "bad
user", right-click and cancel query or terminate session. To do this
remote, a funciton like this is required. pg_stat_activity can be used
to get a list of sessions and their pids. The function should probably
be complemented with a pg_get_postmasterpid or something along that way,
to be able to send signals to th epostmaster itself.

So, would such a function be accepted into the backend code? And if so,
any preferences on where you want it put?


//Magnus

Re: Function to kill backend

Rod Taylor <rbt@rbt.ca> — 2004-04-02T22:02:25Z

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

Re: Function to kill backend

Andrew Dunstan <andrew@dunslane.net> — 2004-04-02T22:16:26Z

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

Re: Function to kill backend

Tom Lane <tgl@sss.pgh.pa.us> — 2004-04-02T22:28:01Z

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

Re: Function to kill backend

Andrew Dunstan <andrew@dunslane.net> — 2004-04-03T00:19:14Z


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

Re: Function to kill backend

Tom Lane <tgl@sss.pgh.pa.us> — 2004-04-03T01:06:34Z

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

Re: Function to kill backend

Bruce Momjian <pgman@candle.pha.pa.us> — 2004-04-03T02:26:52Z

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

Re: Function to kill backend

Tom Lane <tgl@sss.pgh.pa.us> — 2004-04-03T03:55:21Z

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

Re: Function to kill backend

Bruce Momjian <pgman@candle.pha.pa.us> — 2004-04-03T04:11:12Z

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

Re: Function to kill backend

Neil Conway <neilc@samurai.com> — 2004-04-04T03:22:06Z

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

Re: Function to kill backend

Andrew Dunstan <andrew@dunslane.net> — 2004-04-04T07:51:59Z

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

Re: Function to kill backend

Jan Wieck <janwieck@yahoo.com> — 2004-04-06T14:13:39Z

Bruce Momjian wrote:
> 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.

Even as superuser, they still need to get a lock to drop the table. So 
killing other backends will ...

This is so pointless. If an attacker manages to become superuser in the 
compromised database, what good are restrictions against killing 
backends? I agree that it should be restricted to backends, with an 
identification based on Xid and SIGINT. But that's it.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #