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;