Preventing access temporarily.

On Thu, Jan 26, 2012 at 3:05 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote:

PG V9.0.1 on Linux

I want to temporarily prevent users from connecting to a DB, let the
existing connections finish, <do some work>, re-enable connections.

What's the best way to do that?

Edit pg_hba.conf to reject all connections and reload. current
connections will stay connected, new ones will be refused. use
pg_stat_activity to monitor connections til they're all gone / idle.

I found something else on the web.

update pg_database set datallowconn = false where datname = 'foo';
update pg_database set datallowconn = true where datname = 'foo';

Seems to have worked OK.

Thanks for the pg_hab.conf suggestion. I'll add that to my notes.

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Thursday, January 26, 2012 5:39 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Preventing access temporarily.

On Thu, Jan 26, 2012 at 3:05 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote:

PG V9.0.1 on Linux

I want to temporarily prevent users from connecting to a DB, let the
existing connections finish, <do some work>, re-enable connections.

What's the best way to do that?

Edit pg_hba.conf to reject all connections and reload. current
connections will stay connected, new ones will be refused. use
pg_stat_activity to monitor connections til they're all gone / idle.

On Thu, Jan 26, 2012 at 3:55 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote:

I found something else on the web.

update pg_database set datallowconn = false where datname = 'foo';
update pg_database set datallowconn = true where datname = 'foo';

Seems to have worked OK.

Thanks for the pg_hab.conf suggestion.  I'll add that to my notes.

Yeah either of those will work. pg_hba.conf is nice for more complex
setups and you can have several pg_hba.conf.whatever files laying
about, link the right one and reload. So it's pretty easy to script
and back out for complex stuff.

On Thu, Jan 26, 2012 at 3:58 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Thu, Jan 26, 2012 at 3:55 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote:

I found something else on the web.

update pg_database set datallowconn = false where datname = 'foo';
update pg_database set datallowconn = true where datname = 'foo';

Seems to have worked OK.

Thanks for the pg_hab.conf suggestion.  I'll add that to my notes.

Yeah either of those will work.  pg_hba.conf is nice for more complex
setups and you can have several pg_hba.conf.whatever files laying
about, link the right one and reload.  So it's pretty easy to script
and back out for complex stuff.

Oh and also you can revoke connect by user which allows for finer
grained control of connections as well.