Обсуждение: allow a user to see current_query in pg_stat_activity in 8.4

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