Chris Angelico wrote:
On Fri, Jul 27, 2012 at 7:09 PM, Mark Morgan Lloyd
<markMLl.pgsql-general@xxxxxxxxxxxxxxx> wrote:
Chris Angelico wrote:
On Fri, Jul 27, 2012 at 6:27 PM, Mark Morgan Lloyd
<markMLl.pgsql-general@xxxxxxxxxxxxxxx> 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]
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
