Postgres pg_read_all_stats default role cannot read from my schema

Question

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?

Answer 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;

• eignhpants: 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.
• Laurenz Albe: GRANT SELECT ON pg_statistic TO normaluser; and then the user can select from pg_statistic.
• eignhpants: 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.
• Laurenz Albe: I have added another idea.