On Wed, Mar 3, 2010 at 8:31 AM, Herouth Maoz <hero...@unicell.co.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
