Re: Terminating a rogue connection

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.

ChrisA