pg_monitor is not enough for non-superuser

[Just-MP]

What did you do? I've created PostgreSQL user for monitoring as described in the readme

Run following command if you use PostgreSQL versions >= 10

GRANT pg_monitor to postgres_exporter;

I've installed postgres_exporter and I've run it. Connected to prometheus without problems. Created postgres-exporter dashboard in the grafana without problems.

What did you expect to see? All metrics work as expected.

What did you see instead? Under which circumstances? In the grafana most of metrics worked succesfully However during of monitoring of our load testing there was always 1 connection active in Grafana monitoring in the "Connections by state (stacked)" chart. After checking pg_stat_activity under postgres superuser I've found that there are 480+ connections really, sometimes idle, sometimes active during the test. Environment

• System information:

  Linux 5.10.0-27-amd64 x86_64

• postgres_exporter version:

exporter have been run from the "latest" docker container:

ExecStart=/usr/bin/docker run \ --net=host \ -e DATA_SOURCE_NAME="postgresql://postgres_metrics_exporter:my_pwd@127.0.0.1:my_pg_port/postgres?sslmode=disable" \ --name=postgres-exporter-container \ quay.io/prometheuscommunity/postgres-exporter:latest

• PostgreSQL version:

  15.6

Diving down the issue I've found that pg_monitor user rights are not enough to find any connection state from pg_stat_activity:

 psql -p <myport> -h 127.0.0.1 -U postgres_metrics_exporter postgres
psql (15.6 (Debian 15.6-1.pgdg110+2))

postgres=> select count(1),datname, application_name, state from  pg_stat_activity group by datname, application_name, state;
 count |  datname   | application_name  | state
-------+------------+-------------------+--------
   400 | cargo      | cargo-core        |
     1 |            | patroni_cpp_node3 |
     1 | postgres   | Patroni restapi   |
    20 | dictionary | common-dict       |
     1 | postgres   | psql              | active
     1 |            | patroni_cpp_node2 |
    20 | telegram   | telegram-adapter  |
    20 | cargo      | cargo-dict        |
    10 | etdvs      | etdvs             |
     1 | postgres   | Patroni heartbeat |
     5 |            |                   |
     1 | postgres   |                   | idle
(12 rows)

(note the NULL value in the state column of the output in all the rows except the two) but when granted pg_read_all_stats to the user, query to the pg_stat_activity worked as expected:

<my_linux_user>@pg-ha-node1-db:~$ psql -p <myport> -h 127.0.0.1 -U postgres postgres
psql (15.6 (Debian 15.6-1.pgdg110+2))

postgres=# grant pg_read_all_stats to postgres_metrics_exporter;
GRANT ROLE
postgres=# \q
<my_linux_user>@pg-ha-node1-db:~$ psql -p <myport> -h 127.0.0.1 -U postgres_metrics_exporter postgres
psql (15.6 (Debian 15.6-1.pgdg110+2))

postgres=> select count(1),datname, application_name, state from  pg_stat_activity group by datname, application_name, state;
 count |  datname   | application_name  | state
-------+------------+-------------------+--------
    20 | dictionary | common-dict       | idle
   400 | cargo      | cargo-core        | idle
    20 | telegram   | telegram-adapter  | idle
     1 | postgres   | psql              | active
     1 | postgres   | Patroni restapi   | idle
    10 | etdvs      | etdvs             | idle
     1 |            | patroni_cpp_node2 | active
    20 | cargo      | cargo-dict        | idle
     5 |            |                   |
     1 | postgres   |                   | idle
     1 | postgres   | Patroni heartbeat | idle
     1 |            | patroni_cpp_node3 | active
(12 rows)

postgres=> \q

So, the citation from the manual

To be able to collect metrics from pg_stat* views as non-superuser in PostgreSQL server versions >= 10 you can grant the pg_monitor or pg_read_all_stats [built-in roles](https://www.postgresql.org/docs/current/predefined-roles.html) to the user

is wrong. I need both pg_monitor AND pg_read_all_stats roles to get states of the connections from grafana by postgres_exporter.

As well as I have need grants

grant pg_monitor, pg_read_all_stats to prometheus_metrics_exporter;

for metrics to work, not the

Run following command if you use PostgreSQL versions >= 10

GRANT pg_monitor to postgres_exporter;

citation from your docs. It will not get neither provide to prometheus info about connection states. Please fix the docs.

[Just-MP]

Here the grafana charts before and after the debugging and granting

[Just-MP]

I will fix here key link for the solution: https://dba.stackexchange.com/a/259142 pg_read_all_stats are mandatory. Even though there is a words in PG docs https://www.postgresql.org/docs/current/predefined-roles.html that pg_monitor is a member "This role is a member of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables", somehow it is not. user with pg_monitor cannot show states of the connections without a pg_read_all_stats.

[jsabater]

Hey, @Just-MP !

I just went through the same problem and ended up here. I am using two database versions, 13.16 and 15.8 and this is what I think is the minimum set of commands to execute to allow the prometheus scrapping user access to the stats.

As postgres user, psql --dbname=postgres and:

CREATE USER prometheus WITH NOSUPERUSER NOCREATEDB NOCREATEROLE;
GRANT pg_monitor to prometheus;
GRANT pg_read_all_stats to prometheus;
CREATE SCHEMA prometheus AUTHORIZATION prometheus;
ALTER USER prometheus SET SEARCH_PATH TO prometheus,pg_catalog;

I am unsure whether the last two commands are actually necessary. Would you be so kind as to confirm this, in case you know?

Thanks.