> 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

Sat, 3 Apr 2004 14:01:19 +0200
"Magnus Hagander" <mha@sollentuna.net>
Re: Function to kill backend 


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


Sat, 03 Apr 2004 11:08:30 -0500
Andrew Dunstan <andrew@dunslane.net>
Re: [HACKERS] Function to kill backend