Postgres and seeing other schemas/roles/databases

[kwolfe19]

So I’ve got PMM set up and I can see certain things, but I can’t (in QAN) see everything. I can see the pmm_user and postgres database for example, but I cannot see the database where all of my schemas (and tables are). I also can not see any of the other users/roles. I am guessing this is a missing role/permission thing from pmm_user. I can see queries that are being run by pmm_user through QAN. Any idea on what I need to do to be able to see the details about the other databases and schemas?

I am running Postgres 9.6 hosted in GCP, and it is DaaS.

Thank you!

[Agustin_G]

Hi,

Can you send the exact steps you used to install and configure this PMM client with QAN? Please send steps used for OS and PostgreSQL commands. For your reference, let me send one documentation link and one blogpost that can help you with the steps needed, in case you missed any:

https://www.percona.com/doc/percona-monitoring-and-management/2.x/manage/conf-postgres.html

https://www.percona.com/blog/2019/10/29/monitoring-postgresql-databases-using-percona-monitoring-management

In particular, does the user have SUPERUSER level access? (https://www.percona.com/doc/percona-monitoring-and-management/2.x/manage/conf-postgres.html#prerequisites)

[kwolfe19]

Since we’re a managed Google Cloud instance we can’t give SUPERUSER to pmm_user. Instead we’ve followed (mostly) this one: https://www.percona.com/blog/2020/09/22/using-security-definer-to-monitor-postgresql-9-6-or-earlier-using-percona-monitoring-and-management/

We had to do a few things differently though because (I think) PMM now logs in to the postgres database. So I enabled the pg_stat_statements in the postgres database and created the functions there as well. When I followed the directions in a separate pmm database the QAN agent would not start.

On permissions for the pmm_user, that user was created through psql so it does not have all of the administrator privileges that a user created through google’s CLI would have. I can recreate the user if needed though.

[kwolfe19]

Any ideas on this? I have posted the steps.

[jobin.augustine]

Hi kwolfe19The QAN picks of information from pg_stat_statements using the same user which we used for adding the monitoring configuration.

For example, if I use a statement like:

sudo pmm-admin add postgresql --username=pmm --password=pmm  --environment=versions --query-source=pgstatmonitor

The user “pmm” should be existing in database and should have access to pg_stat_statements. So please connect to command line tool (psql) as the same user and make sure that you are able to see the same information in pg_stat_statements. it is appearing while directly querying the pg_stat_statements view, It should be appearing in PMM as well. You may adjust the duration of the monitoring to a small value (for example, 5 mintues) for example to make sure that you have the same information from both pg_stat_statements and QAN.