Terminating a rogue connection

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.
There's a good lot of information available in pg_stat_activity;
logins, application names, and connection IP addresses are handy here.
But ultimately, it's just pg_terminate_backend.

ChrisA

Hi all,

in elderly versions, where pg_terminate_backend is missing, you'd
issue a kill -15 <pid> from the command line.

Bèrto

On 27 July 2012 09:33, Chris Angelico <rosuav@gmail.com> 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.
There's a good lot of information available in pg_stat_activity;
logins, application names, and connection IP addresses are handy here.
But ultimately, it's just pg_terminate_backend.

ChrisA

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

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.

There's a good lot of information available in pg_stat_activity;
logins, application names, and connection IP addresses are handy here.
But ultimately, it's just pg_terminate_backend.

ChrisA

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

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

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.

ChrisA

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]