Re: stopping processes, preventing connections

On Wed, Mar 3, 2010 at 8:31 AM, Herouth Maoz <herouth(at)unicell(dot)co(dot)il> wrote:
>
>
> First, the easy part - regarding allowing/disallowing queries. Is it
> possible to GRANT or REVOKE access to tables based on the originating IP?
>

I'd suggest separating out access to your tables by roles, and then
restricting those roles to certain IP ranges in pg_hba.conf.

> Second, and the more complicated one - what do I do about rogue queries
> that are running when my process starts? Today we had a query that ran since
> yesterday. I called pg_cancel_backend() on it several times and waited for
> almost two hours - to no avail. Eventually I had to ask our sysadmin to
> shutdown PostgreSQL, which took some five minutes, but eventually worked. Is
> there a way to do the same thing to a single process without shutting down
> the whole server, and without causing any harm to the database or memory
> corruption? Something I can call from within SQL? I run the nightly script
> from a linux user which is not "postgres", so I'd prefer a way that doesn't
> require using "kill".
>
>
On 8.4, you can use pg_terminate_backend(), which sends a SIGTERM instead of
pg_cancel_backend's SIGINT. If you're not on 8.4, you can just do this
manually with a "kill -SIGTERM backend_pid". If that doesn't work either,
you might have to resort to a "kill -SIGKILL backend_pid". Killing a single
backend should be much better for you than restarting Postgres entirely.
These operations shouldn't result in database corruption.

You have to be database superuser to use pg_cancel_backend() or
pg_terminate_backend(), or have a shell login as the database user to use
"kill". No way around that for now.

Though next time you see a query which doesn't respond to
pg_cancel_backend(), try gathering information about the query and what the
backend is doing; either you're doing something unusual (e.g. an app is
restarting the query automatically after getting canceled) or perhaps you've
stumbled on a bug in Postgres.

Josh