Have pg_monitor access to repmgr metadata for monitoring/diagnostic purposes

[martinmarques]

We've found that a standard repmgr setup does not provide access to the repmgr metadata for the pg_monitor role. This would be useful for monitoring and diagnostic tools

[martinmarques]

As an example:

 repmgr=# select * from repmgr.nodes ;
 node_id | upstream_node_id | active | node_name |  type   | location | priority |                                           conninfo                                           | repluser |   slot_name   |        con
fig_file
---------+------------------+--------+-----------+---------+----------+----------+----------------------------------------------------------------------------------------------+----------+---------------+-----------
-----------------
       1 |                  | t      | zonal     | primary | default  |      100 | host=zonal port=5432 dbname=repmgr user=repmgr  application_name=zonal connect_timeout=2     | repmgr   | repmgr_slot_1 | /etc/repmg
r/13/repmgr.conf
       2 |                1 | t      | kinetic   | standby | default  |      100 | host=kinetic port=5432 dbname=repmgr user=repmgr  application_name=kinetic connect_timeout=2 | repmgr   | repmgr_slot_2 | /etc/repmg
r/13/repmgr.conf
       4 |                2 | t      | kernel    | standby | default  |      100 | host=kernel port=5432 dbname=repmgr user=repmgr  application_name=kernel connect_timeout=2   | repmgr   | repmgr_slot_4 | /etc/repmg
r/13/repmgr.conf
(3 rows)

repmgr=# set role pg_monitor;
SET
repmgr=> select * from repmgr.nodes ;
ERROR:  permission denied for schema repmgr
LINE 1: select * from repmgr.nodes ;
                      ^

[martinmarques]

One option would be to grant usage on the schema and select over the tables to the pg_monitor role. Now if we consider that the conninfo shouldn't be visible we can add a view the redacts it. From a diagnostics point of view, I would prefer to see that field as well