0

I have a postgres user created with the pg_read_all_stats default role permissions which states the user should be able to read as if superuser. This works fine for any of the pg_stat_* tables.

However I need some data from the pg_stats table. These queries do not show rows for my applications in the public schema.

The documentation does not indicate how this table should behave when interacting with my applications public schema. Am I completely out of luck for querying this table with pg_read_all_stats permissions?

1 Answer 1

1

Looking at the definition of pg_stats:

SELECT ...
FROM pg_statistic s
  JOIN pg_class c ON c.oid = s.starelid
  JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum
  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT a.attisdropped AND has_column_privilege(c.oid, a.attnum, 'select'::text)
  AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));

you can see that you need to have SELECT rights on the column in question to see the statistics.

The underlying table pg_statistic can only be examined by the bootstrap superuser.

One way to work around this is to create a function owned by a superuser with SECURITY DEFINER that selects from pg_stats and returns the results.

Make sure to

  • REVOKE EXECUTE on this function FROM PUBLIC.

  • ALTER FUNCTION ... SET search_path = pg_catalog, pg_temp;

4
  • Unfortunately it still does not work, as the default role pg_read_all_stats explicitly only returns on tables that it already has access to. Since it can't access public by design, it can't do it.
    – eignhpants
    Nov 19, 2019 at 20:30
  • GRANT SELECT ON pg_statistic TO normaluser; and then the user can select from pg_statistic. Nov 19, 2019 at 22:20
  • normaluser will still be unable to read from the pg_statistic of schemas it does not have access to. A secondary step of GRANT on the schemaname is required which we don't want in our case.
    – eignhpants
    Nov 21, 2019 at 15:25
  • I have added another idea. Nov 21, 2019 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.