Better error reporting in widgets (was: "Powa-Web - An Error occurred while loading this widget")

[gowtham500]

Hello team,

Versions - Remote server

powa-4.1.2
PostgreSQL 12

Versions - Central Repository

powa-4.1.2
powa-collector powa-collector.py version 1.1.1
PostgreSQL 13

Version powa-web

powa-web-4.1.0

Error

Observation

I just checked that backend query for this operation by enabling - log_min_duration_statement:0 and it executes below query for the that particular query id. I tried it on the DB with the same UI user, and I got output back. But in web I see above error. Can you please advise.

SELECT powa_statements.srvid, qualid, powa_statements.queryid, query, powa_statements.dbid, to_json(quals) AS quals, sum(execution_count) AS execution_count, sum(occurences) AS occurences, sum(nbfiltered) / sum(occurences) AS avg_filter, CASE WHEN (sum(execution_count) = 0) THEN 0 ELSE (sum(nbfiltered) / CAST(sum(execution_count) AS NUMERIC)) * 100 END AS filter_ratio
        FROM
            (
            SELECT srvid, qualid, queryid, dbid, userid, (unnested.records).*
            FROM (
                SELECT pqnh.srvid, pqnh.qualid, pqnh.queryid, pqnh.dbid, pqnh.userid,
                  pqnh.coalesce_range, unnest(records) AS records
                FROM powa_qualstats_quals_history pqnh
                WHERE coalesce_range  && tstzrange('2021-02-03 05:49:09-0500', '2021-02-10 05:49:09-0500', '[]')
                AND pqnh.srvid = '5'
            ) AS unnested
            WHERE  (records).ts <@ tstzrange('2021-02-03 05:49:09-0500', '2021-02-10 05:49:09-0500', '[]')
            UNION ALL
            SELECT pqnc.srvid, qualid, queryid, dbid, userid, pqnc.ts, pqnc.occurences,
              pqnc.execution_count, pqnc.nbfiltered,
              pqnc.mean_err_estimate_ratio, pqnc.mean_err_estimate_num
            FROM powa_qualstats_quals_history_current pqnc
            WHERE pqnc.ts <@ tstzrange('2021-02-03 05:49:09-0500', '2021-02-10 05:49:09-0500', '[]')
            AND pqnc.srvid = '5'
            ) h
            JOIN powa_qualstats_quals pqnh USING (srvid, queryid, qualid)
             JOIN powa_statements ON powa_statements.queryid = pqnh.queryid AND powa_statements.srvid = pqnh.srvid
        WHERE powa_statements.queryid = '8427650663988862301' GROUP BY powa_statements.srvid, qualid, powa_statements.queryid, powa_statements.dbid, powa_statements.query, quals;

Output of above query when executed as the same web ui user.

-[ RECORD 1 ]---+--------------------------------------------------------------------
srvid           | 5
qualid          | 2547130657
queryid         | 8427650663988862301
query           | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
dbid            | 24492
quals           | [{"relid":"24502","attnum":1,"opno":"96","eval_type":"i"}]
execution_count | 113302
occurences      | 32873
avg_filter      | 0.000000000000000000000000
filter_ratio    | 0.000000000000000000000000

[gowtham500]

Hello,

I was able to resolve this issue as the user used by the collector - powa_user (in my case) didnt have access to pg_statistic.

[rjuju]

Hello @gowtham500

I reopened this issue to see if I can improve the UI by adding a better error message in that case. Ideally hinting for granting adequate privileges on pg_statistics, but if that's not easily doable at least showing the error message so it's easier to track down the root issue.

[gowtham500]

Thanks for adding this as part of enhancement.

I also noticed that the user used by the collector - ex: powa_user requires SELECT permission on the target table for it to run EXPLAIN - so that we get output from predicates section up above and also to get output from "OPTIMIZE DATABASE". Is this understanding correct ?

If so, is there any alternative to run give permission to run EXPLAIN without granting access to read the table?

[banlex73]

I don't think it's possible to get EXPLAIN without access to the table.. I understand your security concern ... Also in the same boat cannot implement it in PRD environment пт, 12 лют. 2021 о 21:03 Gowtham Raj Elangovan <notifications@github.com> пише:

…

Thanks for adding this as part of enhancement. I also noticed that the user used by the collector - ex: powa_user requires SELECT permission on the target table for it to run EXPLAIN - so that we get output from predicates section up above and also to get output from "OPTIMIZE DATABASE". Is this understanding correct ? If so, is there any alternative to run give permission to run EXPLAIN without granting access to read the table? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#128 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHWEYEAZGVPC4YV7JH3RMDS6YBZLANCNFSM4XMX2BRA> .