pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMonitor without superuser?
2+ messages / 2 participants
[nested] [flat]
* Monitor without superuser?
@ 2012-02-17 15:37 Jan Nielsen <jan.sture.nielsen@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jan Nielsen @ 2012-02-17 15:37 UTC (permalink / raw)
To: pgsql-admin
I would like to create a role "dba_monitor" for a portion of my PG 9.1
user-base to be able to monitor PG but without granting superuser. And I
ran across this:
http://blog.kimiensoftware.com/2011/05/querying-pg_stat_activity-and-insufficient-privilege-291
Specifically, I'd like to enable the dba_monitor users to use PGAdmin III
Server Status tools which requires superuser and I would like this role to
be able write queries like:
SELECT *
FROM pg_locks
LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid =
pg_class.relnamespace),
pg_stat_activity
WHERE
NOT pg_locks.pid=pg_backend_pid()
AND pg_locks.pid=pg_stat_activity.procpid;
and be able to read all the contents, including the "current_query" which,
it appears, requires superuser.
Is this achievable? Any way around this?
Cheers,
Jan
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Monitor without superuser?
@ 2012-02-28 16:44 Greg Smith <greg@2ndQuadrant.com>
parent: Jan Nielsen <jan.sture.nielsen@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Smith @ 2012-02-28 16:44 UTC (permalink / raw)
To: pgsql-admin; jan.sture.nielsen@gmail.com
On 02/17/2012 10:37 AM, Jan Nielsen wrote:
> I would like to create a role "dba_monitor" for a portion of my PG 9.1
> user-base to be able to monitor PG but without granting superuser.
You want to create a security definer function for this:
http://www.postgresql.org/docs/9.1/static/sql-createfunction.html
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-02-28 16:44 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-17 15:37 Monitor without superuser? Jan Nielsen <jan.sture.nielsen@gmail.com>
2012-02-28 16:44 ` Greg Smith <greg@2ndQuadrant.com>
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