Update collector README to reference pg_monitor role for setup instructions

[jawnsy]

It looks like the pg_monitor predefined role is sufficient for pganalyze-collector, so granting access should be as simple as:

GRANT pg_monitor TO pganalyze;

This seems to be sufficient. The prometheus-postgres-exporter collects similar metrics and provides similar instructions, and states partway through:

Run following command if you use PostgreSQL versions >= 10

GRANT pg_monitor to postgres_exporter;

The documentation for predefined roles in PostgreSQL 14 states:

role	allowed access
pg_read_all_settings	Read all configuration variables, even those normally visible only to superusers.
pg_read_all_stats	Read all pg_stat_* views and use various statistics related extensions, even those normally visible only to superusers.
pg_stat_scan_tables	Execute monitoring functions that may take ACCESS SHARE locks on tables, potentially for a long time.
pg_monitor	Read/execute various monitoring views and functions. This role is a member of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables.

Thus, it seems pg_monitor may be sufficient for the collector's needs? I gave this a try and pganalyze-collector --dry-run worked fine, including showing queries belonging to other users in the system.

[lfittl]

@jawnsy Yep, that's correct - the pganalyze collector works just fine with pg_monitor for almost all statistics.

With "update documentation" in the original title I assume you are referencing the documentation in the collector README file?

We've actually referenced pg_monitor in the public documentation (as well as in-app) for quite a while (see e.g. here: https://pganalyze.com/docs/install/amazon_rds/02_create_monitoring_user), but haven't gotten around to clarifying this in the README directly (the idea was to move the helper functions to the pganalyze docs for easier explanation), and reference that from the collector.

I'll leave this issue open for us to work on and clarify that you were (probably) referencing the README - let me know in case that wasn't what you meant :)

[jawnsy]

Yup, I was referring to the README in the repository here, as it was the main thing I was looking at when setting up the collector. The Cloud SQL docs on the web site also seems to have the same issue, though - they mention the same schema/functions.

The documentation has a lot of nice provider-specific guides, which are really helpful for setting things up, but given the support matrix you have, I can understand that it might be a challenge to keep them all up-to-date, especially as pganalyze's capabilities evolve 😄

Cloud SQL seems to support PostgreSQL 9.6, 10, 11, 12, 13, and 14 - so with that wide range of versions, testing them all may be difficult, especially once you factor in AlloyDB.

[lfittl]

Ah, that's actually an important detail to know: The two helper functions mentioned (get_stat_replication and get_column_stats) are both recommended even when you have assigned pg_monitor to the pganalyze user.

The more important one of the two is get_column_stats, as that provides access to some of the statistics collected by ANALYZE, used for improving index recommendations. This is not available through pg_monitor, since its not a typical "monitoring" data point.