Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-admin-owner+M40528=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1TnqoC-0007q3-O7
	for pgsql-admin@arkaria.postgresql.org; Wed, 26 Dec 2012 13:14:08 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-admin-owner+M40528=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1TnqoC-00045C-7c
	for pgsql-admin@arkaria.postgresql.org; Wed, 26 Dec 2012 13:14:08 +0000
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ynux@gmx.net>) id 1Tm2OX-0000Z2-KA
	for pgsql-admin@postgresql.org; Fri, 21 Dec 2012 13:12:09 +0000
Received: from sam.nabble.com ([216.139.236.26])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ynux@gmx.net>) id 1Tm2OV-00075c-TC
	for pgsql-admin@postgresql.org; Fri, 21 Dec 2012 13:12:09 +0000
Received: from [192.168.236.26] (helo=sam.nabble.com)
	by sam.nabble.com with esmtp (Exim 4.72)
	(envelope-from <ynux@gmx.net>) id 1Tm2OU-00077j-Qh
	for pgsql-admin@postgresql.org; Fri, 21 Dec 2012 05:12:06 -0800
Date: Fri, 21 Dec 2012 05:12:06 -0800 (PST)
From: ynux <ynux@gmx.net>
To: pgsql-admin@postgresql.org
Message-ID: <1356095526821-5737499.post@n5.nabble.com>
In-Reply-To: <1355456074.36965.YahooMailNeo@web125901.mail.ne1.yahoo.com>
References: <1355453967.41645.YahooMailNeo@web125905.mail.ne1.yahoo.com>
	<18307.1355454996@sss.pgh.pa.us>
	<1355456074.36965.YahooMailNeo@web125901.mail.ne1.yahoo.com>
Subject: Re: allow a user to see current_query in pg_stat_activity in 8.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -0.8 (/)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin