Re: allow a user to see current_query in pg_stat_activity in 8.4

On Fri, Dec 21, 2012 at 8:12 AM, ynux <ynux(at)gmx(dot)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