Обсуждение: Monitor without superuser?
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:
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
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