Thread

Blocking access by remote users for a specific time period

Michael Nolan <htfoot@gmail.com> — 2014-12-14T04:13:32Z

I have several web apps that access our Postgresql database that I'd like
to lock out of the database for about an hour during a weekly maintenance
interval. (There are some internal users that do not get locked out,
because they're running the maintenance tasks.)

There are no time-of-day access limitation parameters in the pg_hba.conf
file, are there any simple ways to do this?
--
Mike Nolan

Re: Blocking access by remote users for a specific time period

Rob Sargent <robjsargent@gmail.com> — 2014-12-14T04:45:29Z

Turn the app off?  Seems that's one of the selling points of web apps. Throw up a "under maint" page. 

Sent from my iPhone

> On Dec 13, 2014, at 9:13 PM, Michael Nolan <htfoot@gmail.com> wrote:
> 
> I have several web apps that access our Postgresql database that I'd like to lock out of the database for about an hour during a weekly maintenance interval. (There are some internal users that do not get locked out, because they're running the maintenance tasks.)  
> 
> There are no time-of-day access limitation parameters in the pg_hba.conf file, are there any simple ways to do this?
> --
> Mike Nolan

Re: Blocking access by remote users for a specific time period

Adrian Klaver <adrian.klaver@aklaver.com> — 2014-12-14T05:10:18Z

On 12/13/2014 08:13 PM, Michael Nolan wrote:
> I have several web apps that access our Postgresql database that I'd
> like to lock out of the database for about an hour during a weekly
> maintenance interval. (There are some internal users that do not get
> locked out, because they're running the maintenance tasks.)
>
> There are no time-of-day access limitation parameters in the pg_hba.conf
> file, are there any simple ways to do this?

Use a cron job that at beginning of period swaps out the pg_hba.conf 
with one that denies access, reloads server and then at end of time 
period reverse procedure ?

> --
> Mike Nolan


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Blocking access by remote users for a specific time period

Michael Nolan <htfoot@gmail.com> — 2014-12-14T06:38:19Z

Yeah, a cron job to swap pg_hba.conf files is the best solution I've come
up with so far.  It's not one web app, it's closer to two dozen of them, on
multiple sites.
--
Mike Nolan

On Sat, Dec 13, 2014 at 11:10 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
>
> On 12/13/2014 08:13 PM, Michael Nolan wrote:
>
>> I have several web apps that access our Postgresql database that I'd
>> like to lock out of the database for about an hour during a weekly
>> maintenance interval. (There are some internal users that do not get
>> locked out, because they're running the maintenance tasks.)
>>
>> There are no time-of-day access limitation parameters in the pg_hba.conf
>> file, are there any simple ways to do this?
>>
>
> Use a cron job that at beginning of period swaps out the pg_hba.conf with
> one that denies access, reloads server and then at end of time period
> reverse procedure ?
>
>  --
>> Mike Nolan
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>

Re: Blocking access by remote users for a specific time period

Vick Khera <vivek@khera.org> — 2014-12-14T16:23:36Z

On Sun, Dec 14, 2014 at 1:38 AM, Michael Nolan <htfoot@gmail.com> wrote:
>
> Yeah, a cron job to swap pg_hba.conf files is the best solution I've come
> up with so far.  It's not one web app, it's closer to two dozen of them, on
> multiple sites.
>

Use time-based firewall rules to block them?

Re: Blocking access by remote users for a specific time period

Steve Atkins <steve@blighty.com> — 2014-12-14T18:01:55Z

On Dec 13, 2014, at 10:38 PM, Michael Nolan <htfoot@gmail.com> wrote:

> Yeah, a cron job to swap pg_hba.conf files is the best solution I've come up with so far.  It's not one web app, it's closer to two dozen of them, on multiple sites.   

If they use persistent connections you'll also have to kill existing connections after you've prevented new connections via pg_hba.conf

This ...

psql -q -c 'select application_name as "Client", procpid as "PID", pg_terminate_backend(procpid) as "Disconnected" from pg_stat_activity where procpid <> pg_backend_pid()' database_name

... will kill all connections to the server. You can use application_name, client_addr, datname or usename to be more selective about who lives and who dies.

Cheers,
  Steve

> --
> Mike Nolan
> 
> On Sat, Dec 13, 2014 at 11:10 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 12/13/2014 08:13 PM, Michael Nolan wrote:
> I have several web apps that access our Postgresql database that I'd
> like to lock out of the database for about an hour during a weekly
> maintenance interval. (There are some internal users that do not get
> locked out, because they're running the maintenance tasks.)
> 
> There are no time-of-day access limitation parameters in the pg_hba.conf
> file, are there any simple ways to do this?
> 
> Use a cron job that at beginning of period swaps out the pg_hba.conf with one that denies access, reloads server and then at end of time period reverse procedure ?
> 
> --
> Mike Nolan
> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com

Re: Blocking access by remote users for a specific time period

Scot Kreienkamp <scot.kreienkamp@la-z-boy.com> — 2014-12-19T21:33:49Z

Wouldn't it be a lot easier to set the database logins for those user accounts to zero allowed connections, then terminate their existing sessions, rather than mess with pg_hba.conf?

You could schedule a cron job to run a query to make the change to the user, then later the same to undo the change.

Scot Kreienkamp

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Steve Atkins
Sent: Sunday, December 14, 2014 1:02 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Blocking access by remote users for a specific time period

On Dec 13, 2014, at 10:38 PM, Michael Nolan <htfoot@gmail.com> wrote:

> Yeah, a cron job to swap pg_hba.conf files is the best solution I've come up with so far.  It's not one web app, it's closer to two dozen of them, on multiple sites.

If they use persistent connections you'll also have to kill existing connections after you've prevented new connections via pg_hba.conf

This ...

psql -q -c 'select application_name as "Client", procpid as "PID", pg_terminate_backend(procpid) as "Disconnected" from pg_stat_activity where procpid <> pg_backend_pid()' database_name

... will kill all connections to the server. You can use application_name, client_addr, datname or usename to be more selective about who lives and who dies.

Cheers,
  Steve

> --
> Mike Nolan
>
> On Sat, Dec 13, 2014 at 11:10 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 12/13/2014 08:13 PM, Michael Nolan wrote:
> I have several web apps that access our Postgresql database that I'd
> like to lock out of the database for about an hour during a weekly
> maintenance interval. (There are some internal users that do not get
> locked out, because they're running the maintenance tasks.)
>
> There are no time-of-day access limitation parameters in the
> pg_hba.conf file, are there any simple ways to do this?
>
> Use a cron job that at beginning of period swaps out the pg_hba.conf with one that denies access, reloads server and then at end of time period reverse procedure ?
>
> --
> Mike Nolan
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

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

This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, please note that you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.

Re: Blocking access by remote users for a specific time period

Joseph Kregloh <jkregloh@sproutloud.com> — 2014-12-20T08:42:34Z

The way I would do it is to setup pgBouncer and control the user access
from there. Not to mention the connection pooling gains you would get from
pgBouncer itself. The simples way is to setup all of the users to connect
through pgBouncer, then the admin users connect to the Postgresql port
directly. That way when the exclusion time comes all you have to do is turn
off pgBouncer and they wouldn't be able to login.


-Joseph

On Fri, Dec 19, 2014 at 4:33 PM, Scot Kreienkamp <
Scot.Kreienkamp@la-z-boy.com> wrote:

> Wouldn't it be a lot easier to set the database logins for those user
> accounts to zero allowed connections, then terminate their existing
> sessions, rather than mess with pg_hba.conf?
>
> You could schedule a cron job to run a query to make the change to the
> user, then later the same to undo the change.
>
> Scot Kreienkamp
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:
> pgsql-general-owner@postgresql.org] On Behalf Of Steve Atkins
> Sent: Sunday, December 14, 2014 1:02 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Blocking access by remote users for a specific time
> period
>
>
> On Dec 13, 2014, at 10:38 PM, Michael Nolan <htfoot@gmail.com> wrote:
>
> > Yeah, a cron job to swap pg_hba.conf files is the best solution I've
> come up with so far.  It's not one web app, it's closer to two dozen of
> them, on multiple sites.
>
> If they use persistent connections you'll also have to kill existing
> connections after you've prevented new connections via pg_hba.conf
>
> This ...
>
> psql -q -c 'select application_name as "Client", procpid as "PID",
> pg_terminate_backend(procpid) as "Disconnected" from pg_stat_activity where
> procpid <> pg_backend_pid()' database_name
>
> ... will kill all connections to the server. You can use application_name,
> client_addr, datname or usename to be more selective about who lives and
> who dies.
>
> Cheers,
>   Steve
>
> > --
> > Mike Nolan
> >
> > On Sat, Dec 13, 2014 at 11:10 PM, Adrian Klaver <
> adrian.klaver@aklaver.com> wrote:
> > On 12/13/2014 08:13 PM, Michael Nolan wrote:
> > I have several web apps that access our Postgresql database that I'd
> > like to lock out of the database for about an hour during a weekly
> > maintenance interval. (There are some internal users that do not get
> > locked out, because they're running the maintenance tasks.)
> >
> > There are no time-of-day access limitation parameters in the
> > pg_hba.conf file, are there any simple ways to do this?
> >
> > Use a cron job that at beginning of period swaps out the pg_hba.conf
> with one that denies access, reloads server and then at end of time period
> reverse procedure ?
> >
> > --
> > Mike Nolan
> >
> >
> > --
> > Adrian Klaver
> > adrian.klaver@aklaver.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
> This message is intended only for the individual or entity to which it is
> addressed. It may contain privileged, confidential information which is
> exempt from disclosure under applicable laws. If you are not the intended
> recipient, please note that you are strictly prohibited from disseminating
> or distributing this information (other than to the intended recipient) or
> copying this information. If you have received this communication in error,
> please notify us immediately by e-mail or by telephone at the above number.
> Thank you.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>