Thread: Remote monitoring of Postgres w/minimal grants
I'm setting up remote monitoring of postgres, but running into an uncomfortable situation with permissions. Basically it seems hard to set up a secure "read only" role, yet also allow proper monitoring. A brief writeup of that is here: http://help.logicmonitor.com/installation-getting-started/notes-for-monitoring-specific-types-of-hosts/databases/postgresql/postgresql-credentials/ In order to get accurate server busy stats and max query time, the LogicMonitor user needs to be a superuser "alter role logicmonitor superuser;". Without the SuperUser privilege, all servers will appear busy, and maximum query time will always be 0. Is there a way to grant the type of permission needed to view stats, without superuser?
On Wed, Mar 10, 2010 at 12:26 AM, Bryce Nesbitt <bryce2@obviously.com> wrote:
Seems like you could get around most of these cases by making a function or set returning function to return the data and making it "security definer" and then grant your monitoring user access to that.
Tony
I'm setting up remote monitoring of postgres, but running into an uncomfortable situation with permissions.
Basically it seems hard to set up a secure "read only" role, yet also allow proper monitoring.
A brief writeup of that is here:
http://help.logicmonitor.com/installation-getting-started/notes-for-monitoring-specific-types-of-hosts/databases/postgresql/postgresql-credentials/
In order to get accurate server busy stats and max query time, the LogicMonitor user needs to be a superuser "alter role logicmonitor superuser;". Without the SuperUser privilege, all servers will appear busy, and maximum query time will always be 0.
Is there a way to grant the type of permission needed to view stats, without superuser?
Seems like you could get around most of these cases by making a function or set returning function to return the data and making it "security definer" and then grant your monitoring user access to that.
Tony