Permission denied error on Postgres

[ashishdchauhan]

We setup powa user in postgres for all dbs with below privileges.

ANALYSE; GRANT SELECT ON ALL TABLES IN SCHEMA public TO powa; ANALYSE; GRANT SELECT ON pg_statistic TO powa; ANALYSE; GRANT SELECT ON pg_statistic TO powa; ANALYSE; GRANT SELECT ON pg_statistic TO powa;

Still getting permission denied errors while trying to access reports from Web. What else permission should i assign to powa user on db?

[rjuju]

The user you're using on the UI should only need to have GRANT SELECT on pg_statistic and all the tables used in the application. Maybe you have some tables which are not in the public schema?

Can you show the error reported by powa-web?

[ashishdchauhan]

Issue been fixed! As you suggested, few tables were not in public, i made necessary changes and all good now! Thanks for you help!

[Bregor]

Sorry if this is a wrong issue:

powa=# grant SELECT on pg_statistic to powa_web;
GRANT

In powa-web logfile:

ProgrammingError: (psycopg2.ProgrammingError) permission denied for relation pg_statistic
 [SQL: "\n    SELECT json_object_agg(attrelid || '.'|| attnum, value)\n    FROM (\n    SELECT attrelid, attnum, json_build_object(\n        'relname', relname,\n        'attname', attname,\n        'n
 spname', nspname,\n        'n_distinct', COALESCE(stadistinct, 0),\n        'null_frac', stanullfrac,\n        'most_common_values', CASE\n            WHEN s.stakind1 = 1 THEN s.stavalues1\n            WH
 EN s.stakind2 = 1 THEN s.stavalues2\n            WHEN s.stakind3 = 1 THEN s.stavalues3\n            WHEN s.stakind4 = 1 THEN s.stavalues4\n            WHEN s.stakind5 = 1 THEN s.stavalues5\n            EL
 SE NULL::anyarray\n        END,\n        'table_liverows', pg_stat_get_live_tuples(c.oid)\n    ) as value\n    FROM pg_attribute a\n    INNER JOIN pg_class c on c.oid = a.attrelid\n    INNER JOIN pg_names
 pace n ON n.oid = c.relnamespace\n    LEFT JOIN pg_statistic s ON s.starelid = c.oid\n                       AND s.staattnum = a.attnum\n    WHERE (attrelid, attnum) IN %(att_list)s\n    ) detail\n"] [par
 ameters: {'att_list': ((u'696804940', 1),)}]

[rjuju]

Hello,

it looks like you did the GRANT on the powa database. You need to do this grant on every user database you access through the UI.

[Bregor]

@rjuju should I grant both ON ALL TABLES IN SCHEMA public and ON pg_statistic for every DB, or only to pg_statistic?

[rjuju]

Both, and also to tables not in schema public if any.

[Bregor]

@rjuju thank you! Works like a charm.