PostgreSQL DB account role and privilege

PostgreSQL
1

Hi, to monitor PostgreSQL DB instance, does the PostgreSQL DB account used by PMM client to login to the DB really need to have “superuser” role? Can it have non-root DB privileges - if so, what are they?

Thanks in advance!

1 Like
2

Hi @thy17,

Percona recommends that a PostgreSQL user be configured for SUPERUSER level access, in order to gather the maximum amount of data.

1 Like
3

Hi @Vadim_Yalovets,
Thanks for the reply.

From security standpoint, if superuser role cannot be granted, is there a recommended set of postgresql tables / views that can provide the required query performance data?

Thank you.

1 Like
4

Hi @Vadim_Yalovets and all,

I found additional information regarding the possibility to grant non superuser role to the PMM Client’s PostgreSQL DB account, but would like to seek thoughts and advice on the legitimacy / validity of the info found.

  1. PMM Client’s PostgreSQL DB accounts: In the case when a PostgreSQL DB is the target to be monitored, it is recommended in the PMM documentation for the PMM client (specifically, pmm-agent) to connect to the PostgreSQL DB using a DB account with “SUPERUSER” role, so that the maximum amount of metrics information can be obtained. This is aligned with what @Vadim_Yalovets has shared above.

  2. However, referencing the github page for PMM PostgreSQL exporter (i.e. see section “Running as non-superuser” on GitHub - percona/postgres_exporter: A PostgresSQL metric exporter for Prometheus), there is another guidance that a DB account with non-SUPERUSER role (i.e. either pg_monitor, or pg_read_all_stats built-in PostgreSQL roles) can be created and granted to read metrics from the “pg_stat*” views.

Thus, may I know if the GitHub guidance can be followed to grant just the required built-in roles and privileges to the connecting PostgreSQL DB account, instead of “SUPERUSER”? Based on PostgreSQL doc, there are 4 predefined roles that can collect metrics: namely “pg_monitor”, “pg_read_all_stats”, “pg_read_all_settings”, “pg_stat_scan_tables” (ref: see attached screenshot extracted from PostgreSQL: Documentation: 15: 22.5. Predefined Roles):
Predefined PG Roles

Thus, is it feasible to grant the predefined roles, instead of SUPERUSER role, to the PostgreSQL DB account to collect DB metrics by PMM agent? This will follow the least-privilege security principle and reduce the threat of SUPERUSER credentials leakage if the PMM Server’s PostgreSQL DB is compromised, since these credentials are not encrypted at rest currently.

Does anyone have any thoughts on this? Thank you!

1 Like