From: | Mark Morgan Lloyd <markMLl(dot)pgsql-general(at)telemetry(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)PostgreSQL(dot)org |
Subject: | Re: Terminating a rogue connection |
Date: | 2012-07-27 15:17:47 |
Message-ID: | juuber$373$1@pye-srv-01.telemetry.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Chris Angelico wrote:
> On Fri, Jul 27, 2012 at 7:09 PM, Mark Morgan Lloyd
> <markMLl(dot)pgsql-general(at)telemetry(dot)co(dot)uk> wrote:
>> Chris Angelico wrote:
>>> On Fri, Jul 27, 2012 at 6:27 PM, Mark Morgan Lloyd
>>> <markMLl(dot)pgsql-general(at)telemetry(dot)co(dot)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]
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Morgan Lloyd | 2012-07-27 15:22:50 | Adding users connection via SSL |
Previous Message | Igor Neyman | 2012-07-27 14:47:52 | Re: information_schema.referential_constraints broken? |