Thread: allow a user to see current_query in pg_stat_activity in 8.4
This seems like a question whose answer must be right in front of me, but I am not seeing it.
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've trolled the archives and postgres docs with no success.
Could some kind soul point me in the right direction ?
Thanks!
Greg Williamson
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've trolled the archives and postgres docs with no success.
Could some kind soul point me in the right direction ?
Thanks!
Greg Williamson
Greg Williamson <gwilliamson39@yahoo.com> writes: > We want to create a role used by a monitor to check for "<IDLE> in Transaction" with the most restrictive permissions wecan 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 bemonitored. But pg_stat_activity shows only "<insufficient privilege>" Yup. You can only see the query of another session if (1) you are a superuser, or (2) you are the same user the other session is logged in as. Table permissions have nothing to do with this. regards, tom lane
Tom -- >Greg Williamson <gwilliamson39@yahoo.com> writes: >> We want to create a role used by a monitor to check for "<IDLE> in Transaction" with the most restrictive permissionswe 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 tobe monitored. But pg_stat_activity shows only "<insufficient privilege>" > >Yup. You can only see the query of another session if (1) you are a >superuser, or (2) you are the same user the other session is logged in >as. Table permissions have nothing to do with this. Thanks for the swi8ft answer even if wasn't the one I was hoping for. ;-} Another solution suggests itself then ... thanks! Greg
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. Regards, Ynux -- View this message in context: http://postgresql.1045698.n5.nabble.com/allow-a-user-to-see-current-query-in-pg-stat-activity-in-8-4-tp5736517p5737499.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
On Fri, Dec 21, 2012 at 8:12 AM, ynux <ynux@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