Suggest using pg_monitor role for PostgreSQL >= 10

[ilmari]

As of PostgreSQL 10, there are built-in roles that give access to extra statistics without needing to be superuser.

The documentation should suggest using creating an unprivileged user and granting the pg_monitor role to it.

[wrouesnel]

If someone could test what works for this, I'd be happy to update.

[keithf4]

The built in roles eliminate the need for having to make special SECURITY DEFINER functions to see all row data in views such as pg_stat_activity. We do this for the role we make for postgres_exporter and it makes a huge difference in simplicity for setup.

You can see the difference in what is required in our PG9.6 setup file for our monitoring platform

https://github.com/CrunchyData/pgmonitor/blob/master/exporter/postgres/setup_pg96.sql

vs our PG10+ setup file

https://github.com/CrunchyData/pgmonitor/blob/master/exporter/postgres/setup_pg10.sql

We're able to eliminate a bunch of functions that are no longer necessary.

[crimean-celica]

If someone could test what works for this, I'd be happy to update.

It works fine with 12.6
Seems, it would be nice to add that information into the readme

[ilmari]

Assuming postgres_exporter runs as the OS user prometheus, either of the following will work.

In shell, as an OS user that has superuser or createrole access (e.g. postgres on most OSes):

createuser --role pg_monitor prometheus

or in psql, as a a similarly privileged user:

create user prometheus in role pg_monitor;