Re: Function to kill backend

> 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(at)candle(dot)pha(dot)pa(dot)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