Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

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

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 https://perconadev.atlassian.net/browse/PG-12#icft=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

 

Preview unavailable
Preview unavailable

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:

Preview unavailable

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".

 

How to test

None

How to document

None

Attachments

1
  • 02 Mar 2021, 12:22 PM

Smart Checklist

Activity

Show:

Roman Misyurin February 18, 2021 at 5:53 PM

We discussed it with today, short resume of our call is: Now on a frontend we don't have any information about database coupled with specific query so we need to add this information to existent endpoint or maybe create a new one.

Done

Details

Assignee

Reporter

Priority

Components

Labels

Needs QA

Yes

Fix versions

Story Points

Sprint

Affects versions

Smart Checklist

Created February 18, 2021 at 5:43 PM
Updated November 13, 2024 at 8:29 AM
Resolved March 8, 2021 at 3:34 PM
Configure

Flag notifications