On Fri, Dec 21, 2012 at 8:12 AM, ynux <y...@gmx.net> wrote: > Hi. > Your question was: > >>> We want to create a role used by a monitor to check for "<IDLE> in >>> Transaction" with the most restrictive permissions we can on a 8.4.13 >>> instance. >> >>> The user has been granted connect privilege to the database and some >>> limited permissions to user tabhles that need to be monitored. But >>> pg_stat_activity shows only "<insufficient privilege>" >> > > I had the same problem, wondered how nagios does it, and found this: > https://github.com/elecnix/nagios-postgresql/blob/master/pg_stat_activity.sql > > It works perfectly for me, on 8.4.12 though. > Make sure to run it in the database your monitoring user connects to, and do > not use template1. You may have to "create language plpgsql;" first. >
Be aware this will actually allow everyone to see all queries in pg_stat_activity, which might be a bit more than you want. I had an old project that dealt with this a little more fine grained, you might want to take a look at it: https://github.com/xzilla/secure_check_postgres/tree/master/sql It certainly needs updating for 9.2, but the concepts might still be useful. Robert Treat conjecture: xzilla.net consulting: omniti.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
