pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedallow a user to see current_query in pg_stat_activity in 8.4
5+ messages / 4 participants
[nested] [flat]
* allow a user to see current_query in pg_stat_activity in 8.4
@ 2012-12-14 02:59 Greg Williamson <gwilliamson39@yahoo.com>
2012-12-14 03:16 ` Re: allow a user to see current_query in pg_stat_activity in 8.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Greg Williamson @ 2012-12-14 02:59 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: allow a user to see current_query in pg_stat_activity in 8.4
2012-12-14 02:59 allow a user to see current_query in pg_stat_activity in 8.4 Greg Williamson <gwilliamson39@yahoo.com>
@ 2012-12-14 03:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-12-14 03:34 ` Re: allow a user to see current_query in pg_stat_activity in 8.4 Greg Williamson <gwilliamson39@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2012-12-14 03:16 UTC (permalink / raw)
To: Greg Williamson <gwilliamson39@yahoo.com>; +Cc: pgsql-admin
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 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>"
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: allow a user to see current_query in pg_stat_activity in 8.4
2012-12-14 02:59 allow a user to see current_query in pg_stat_activity in 8.4 Greg Williamson <gwilliamson39@yahoo.com>
2012-12-14 03:16 ` Re: allow a user to see current_query in pg_stat_activity in 8.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-12-14 03:34 ` Greg Williamson <gwilliamson39@yahoo.com>
2012-12-21 13:12 ` Re: allow a user to see current_query in pg_stat_activity in 8.4 ynux <ynux@gmx.net>
0 siblings, 1 reply; 5+ messages in thread
From: Greg Williamson @ 2012-12-14 03:34 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
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 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>"
>
>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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: allow a user to see current_query in pg_stat_activity in 8.4
2012-12-14 02:59 allow a user to see current_query in pg_stat_activity in 8.4 Greg Williamson <gwilliamson39@yahoo.com>
2012-12-14 03:16 ` Re: allow a user to see current_query in pg_stat_activity in 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2012-12-14 03:34 ` Re: allow a user to see current_query in pg_stat_activity in 8.4 Greg Williamson <gwilliamson39@yahoo.com>
@ 2012-12-21 13:12 ` ynux <ynux@gmx.net>
2012-12-26 17:27 ` Re: allow a user to see current_query in pg_stat_activity in 8.4 Robert Treat <rob@xzilla.net>
0 siblings, 1 reply; 5+ messages in thread
From: ynux @ 2012-12-21 13:12 UTC (permalink / raw)
To: pgsql-admin
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...
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: allow a user to see current_query in pg_stat_activity in 8.4
2012-12-14 02:59 allow a user to see current_query in pg_stat_activity in 8.4 Greg Williamson <gwilliamson39@yahoo.com>
2012-12-14 03:16 ` Re: allow a user to see current_query in pg_stat_activity in 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2012-12-14 03:34 ` Re: allow a user to see current_query in pg_stat_activity in 8.4 Greg Williamson <gwilliamson39@yahoo.com>
2012-12-21 13:12 ` Re: allow a user to see current_query in pg_stat_activity in 8.4 ynux <ynux@gmx.net>
@ 2012-12-26 17:27 ` Robert Treat <rob@xzilla.net>
0 siblings, 0 replies; 5+ messages in thread
From: Robert Treat @ 2012-12-26 17:27 UTC (permalink / raw)
To: ynux <ynux@gmx.net>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-12-26 17:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-12-14 02:59 allow a user to see current_query in pg_stat_activity in 8.4 Greg Williamson <gwilliamson39@yahoo.com>
2012-12-14 03:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-12-14 03:34 ` Greg Williamson <gwilliamson39@yahoo.com>
2012-12-21 13:12 ` ynux <ynux@gmx.net>
2012-12-26 17:27 ` Robert Treat <rob@xzilla.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox