Security breach when using PGSM and pgsm_enable_query_plan enabled

[dutow]

When using PGSM V2.0.0 and pg_stat_monitor.pgsm_enable_query_plan is enabled. We can see the following output:

postgres=# SELECT Sum(calls)                                AS calls,
postgres-#        query :: VARCHAR(100)                     AS QUERY,
postgres-#        query_plan :: VARCHAR(500)                AS query_plan,
postgres-#        (SELECT Round(Sum(shared_blks_hit) :: DECIMAL, 2)
postgres(#         FROM   pg_stat_monitor)                  AS total_shared_blks_hit,
postgres-#        Round(Sum(shared_blks_hit) :: DECIMAL, 2) AS shared_blks_hit,
postgres-#        Round(( ( Sum(shared_blks_hit) ) * 100 ) / (SELECT Sum(shared_blks_hit)
postgres(#                                                    FROM   pg_stat_monitor) ::
postgres(#              DECIMAL,
postgres(#        2)                                        AS "shared_blks_hit [%]"
postgres-# FROM   pg_stat_monitor
postgres-# GROUP  BY query,
postgres-#           query_plan
postgres-# ORDER  BY "shared_blks_hit [%]" DESC
postgres-# LIMIT  10;
 calls  |                                                query                                                 |                         query_plan                         | total_shared_blks_hit | shared_blks_hit | shared_blks_hit [%]
--------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------+-----------------------+-----------------+---------------------
 543586 | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2                                  |                                                            |           14709314.00 |      6105878.00 |               41.51
 543586 | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2                                  |                                                            |           14709314.00 |      2765381.00 |               18.80
 543586 | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2                                   |                                                            |           14709314.00 |      2737817.00 |               18.61
      1 | vacuum analyze pgbench_accounts                                                                      |                                                            |           14709314.00 |       821952.00 |                5.59
 543585 | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP) |                                                            |           14709314.00 |       562217.00 |                3.82
   5000 | insert into pgbench_tellers(tid,bid,tbalance) values ($1,$2,$3)                                      |                                                            |           14709314.00 |         5051.00 |                0.03
      1 | alter table pgbench_accounts add primary key (aid)                                                   |                                                            |           14709314.00 |         2220.00 |                0.02
      1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1                                                 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+|           14709314.00 |            6.00 |                0.00
        |                                                                                                      |   Index Cond: (aid = 1389835)                              |                       |                 |
      1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1                                                 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+|           14709314.00 |            6.00 |                0.00
        |                                                                                                      |   Index Cond: (aid = 10119771)                             |                       |                 |
      1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1                                                 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+|           14709314.00 |            6.00 |                0.00
        |                                                                                                      |   Index Cond: (aid = 30365540)                             |                       |                 |
(10 rows)

Since I am using pg_stat_monitor.pgsm_normalized_query enabled, the queries do not expose the bind variable values. E.g.:

SELECT abalance FROM pgbench_accounts WHERE aid = $1

This helps to hide potentially sensitive data (E.g.: credit card numbers or whatever else), but we also have the chance to set {{pg_stat_monitor.pgsm_normalized_query }}to disabled. And it helps to troubleshoot problems with data cardinality.

When checking the query plan, we ALWAYS expose the execution plan and the bind variable values we used.

Having a flag similar to pgsm_normalized_query for the plans would be great. This would allow us to expose or hide information depending on the case.

I still think that getting the chance to expose data or not is the way to go. It provides a strong power when troubleshooting unstable execution plans.

Best Regards,

https://jira.percona.com/browse/PG-630