Uploaded image for project: 'Percona Monitoring and Management'
  1. Percona Monitoring and Management
  2. PMM-7555

[ API ] QAN for Postgresql attempts to connect to a database with the same name as the username

    XMLWordPrintable

    Details

      Description

      Suggested implementation:
      1. Modify API in pmm repo for qan endpoint.
      2. Add property database in endpoint GetReport in qan-api2.
      3. Get right database name for row.
      4. Send it to frontend.

       

      How to test (without FE part):

      1. Open QAN in grafana
      2. Pick some row from postresql.
      3. See in debugger endpoint /GetReport where rows contains property datababase. See screenshot:

       

       Impact on the user:

      • User is unable to see Table and indexes data/information for PG monitoring

      Steps to reproduce:

      Verified with PMM 2.13.0 and PG-12

      1. Install PMM-server 
      2. configure PG  and create a user as described in the document 

      https://www.percona.com/doc/percona-monitoring-and-management/2.x/setting-up/client/postgresql.html

       

      Add Postgresql to pmm monitoring:  (Username is pmm_user  )

      # pmm-admin add postgresql pgsql --username=pmm_user --password=Admin123 127.0.0.1:5432
      PostgreSQL Service added.
      Service ID : /service_id/a783e239-70c1-4780-941b-3d5c6e0d0373
      Service name: pgsql

       

      PG commands:

       

      postgres=# CREATE DATABASE dbmonitoring;
      CREATE DATABASE
      postgres-# \l
       List of databases
       Name | Owner | Encoding | Collate | Ctype | Access privileges 
      --------------+----------+----------+---------+-------+-----------------------
       dbmonitoring | postgres | UTF8 | en_IN | en_IN | 
       postgres | postgres | UTF8 | en_IN | en_IN | 
       template0 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
       | | | | | postgres=CTc/postgres
       template1 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
       | | | | | postgres=CTc/postgres
      (4 rows)
      postgres-# \c dbmonitoring
      psql (13.1 (Ubuntu 13.1-1.pgdg18.04+1), server 12.5 (Ubuntu 2:12.5-2.bionic))
      You are now connected to database "dbmonitoring" as user "postgres".
      
      dbmonitoring=# CREATE TABLE links (
      id SERIAL PRIMARY KEY,
      url VARCHAR(255) NOT NULL,
      name VARCHAR(255) NOT NULL,
      description VARCHAR (255),
       last_update DATE
      );
      CREATE TABLE
      dbmonitoring=# INSERT INTO links (url, name)
      dbmonitoring-# VALUES('https://www.postgresqltutorial.com','PostgreSQL Tutorial');
      INSERT 0 1
      dbmonitoring=#
      

       

       

       

      Actual result:
      PMM QAN: 

      PG Queries (INSERT) has the following in Tables and Examples:

      pq: database "pmm_user" does not exist

       

      Expected Result:

      • PMM uses the correct database and information about Tables and indexes exists

      Workaround:
      N/A

      Details

      Original report:

      When using QAN with postgres, click on "tables" tab shows the same error:

      Maybe it should connect to the default "postgres" or the public schema instead of attempting 

       

      Suggested implementation:
      1. Currently we get table info from example, but it doesn't have database info, so it this field could be added it could be use on a frontend side in  request "v1/management/Actions/StartPostgreSQLShowCreateTable".

       

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              jiri.ctvrtka Jiří Čtvrtka
              Reporter:
              roman.misyurin Roman Misyurin
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 2 hours
                  2h

                    Smart Checklist