Data collected and exposed in PMM Query Analytics

PostgreSQL
1

The PMM Query Analytics feature allows the user to identify and drill in on the SQL queries that have performance issues.

  1. For these SQL statements that are collected by PMM Query Analytics, are the parameter values stored?

E.g. For a SQL statement captured: [select * from tableA where creditCard=‘1234-5678-1234’;]

Is the creditcard value “1234-5678-1234” also captured by PMM’s internal database, and then also displayed on the PMM/Grafana Dashboard?

My objective is to understand, from security standpoint, whether sensitive database data included in SQL statements will be exposed in PMM database and dashboard.

Specifically, for PostgreSQL DB.

Thank you!

1 Like
2

Hi, if you dont want expose data it should be enough disable examples. Then we storing only fingerprint. You can do it by --disable-queryexamples in pmm-admin CLI, or by UI during adding service. Also please check you have latest version of PMM. Let me know if this solved your problem. In next versions of PMM there will be also available run explain without examples.

1 Like
3

Ideally, also, you would encrypt such sensitive information before transmitting to the backend database and store it in encrypted form. This way, even PMM would not be able to see the PCI/PII data.

1 Like
4

Hi @Jiri_Ctvrtka and @matthewb , thanks for your pointers!

I would like to seek your help on a few follow-up queries below regarding Sensitive Data Protection using “disable –queryexamples”:

1.1) I saw that we can “SET pg_stat_monitor.pgsm_normalized_query = true” to avoid disclosing the actual values of parameters in the WHERE clause. Also, we can set “disable --queryexamples” to prevent display of actual parameter values of each query on the QAN dashboard.
To confirm, do these controls apply to all query statements including SELECT, INSERT, UPDATE, DELETE?
And are these query statements data stored in the ClickHouse DB or VictoriaMetrics DB?

1.2) Do the above two configurations (i.e. SET pg_stat_monitor.pgsm_normalized_query = true and disable –queryexamples) apply only if “pg_stat_monitor” extension is installed?

1.3) If only the pg_stat_statements views are used for PostgreSQL DB metrics collection, then do the above two configurations stated in point 1.2 still apply?

1.4 If only the pg_stat_statements views are used for monitoring PostgreSQL DBs, then will PMM still provide additional value-add compared with the traditional Prometheus-Grafana setup?

Thank you!

1 Like
5

@thy17 Hi, about your questions:
1.1) Yes, it also applies on SELECT, INSERT, UPDATE and DELETE. Its stored in ClickHouse DB used by PMM Server.
1.2) pg_stat_monitor.pgsm_normalized_query is applied only for pg_stat_monitor. Disable examples is same case, because only pg_stat_monitor supports examples at the moment. Pg_stat_statements doesnt has this option.
1.3) Yes it still apply, but it doesnt has any effect.
1.4) Yes, all our extra values (which we have for pg_stat_statements) will be still provided.

Related to this, since next version you can run “EXPLAIN” without “EXAMPLES” for MySQL. PostgreSQL support will be implemented in next versions too. Maybe it will be useful for you.

Thank you.

1 Like