Discussion:
[ADMIN] Monitor without superuser?
(too old to reply)
Jan Nielsen
2012-02-17 15:37:22 UTC
Permalink
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
Greg Smith
2012-02-28 16:44:04 UTC
Permalink
Post by Jan Nielsen
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 ***@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Loading...