Обсуждение: Terminating a rogue connection
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? -- 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 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]