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

On Dec 13, 2014, at 10:38 PM, Michael Nolan <htfoot(at)gmail(dot)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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com