[ API ] QAN for Postgresql attempts to connect to a database with the same name as the username
General
Escalation
General
Escalation
Description
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 @Jiří Čtvrtka 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
Details
Assignee
Jiří Čtvrtka
Jiří ČtvrtkaReporter
Roman Misyurin
Roman Misyurin(Deactivated)Priority
Components
Needs QA
Yes
Fix versions
Story Points
3
Sprint
None
Smart Checklist
Open Smart Checklist
Smart Checklist

Open 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
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):
Open QAN in grafana
Pick some row from postresql.
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
Install PMM-server
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".