Re: Terminating a rogue connection

Chris Angelico wrote:
> On Fri, Jul 27, 2012 at 7:09 PM, Mark Morgan Lloyd
> <markMLl.pgsql-general@telemetry.co.uk> wrote:
>> Chris Angelico wrote:
>>> On Fri, Jul 27, 2012 at 6:27 PM, Mark Morgan Lloyd
>>> <markMLl.pgsql-general@telemetry.co.uk> wrote:
>>>> Assuming a *nix server: if a monitoring program determines that an
>>>> established connection appears to be trying to so something
>>>> inappropriate,
>>>> what's the best way of terminating that session rapidly?
>>>
>>> select pg_terminate_backend(procpid) from pg_stat_activity where .....
>>>
>>> The main difficulty is recognizing which PID to terminate, though.
>>
>> Exactly :-)
>>
>> I'd add that this is a hypothetical situation at present, I'm just trying to
>> plan ahead.
>
> Something I've been developing at work lately combines this with
> editing pg_hba.conf to ensure that a kicked connection cannot
> reconnect. Services register themselves with a particular user name,
> then SET USER to switch to the one actual user who owns tables and
> stuff, so my overlording monitor can kick off any service based on IP
> and usename (note the spelling - it's not "username" in the table).
> Rewrite pg_hba.conf, SIGHUP, then pg_terminate_backend in a searched
> SELECT as seen above.
>
> This may be overkill for what you're doing, though. It's part of our
> "prevent split-brain problems" technique.

One problem there is that if somebody is doing something that causes a
significant CPU or memory overcommit, it might be some while before
SIGHUP etc. works. I'm currently eyeballing the Linux capabilities
stuff, it looks as though if a monitor has CAP_NET_ADMIN that it will be
able to temporarily add a firewall rule that blocks the rogue client's
traffic.

I'm hoping to be able to avoid "on the fly" editing of configuration
files, there's too much could go wrong. Which I suppose leads into
another question...

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]