pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Document privileges required for vacuumdb --missing-stats-only. 2+ messages / 1 participants [nested] [flat]
* pgsql: Document privileges required for vacuumdb --missing-stats-only. @ 2025-08-26 19:49 Nathan Bossart <nathan@postgresql.org> 0 siblings, 0 replies; 2+ messages in thread From: Nathan Bossart @ 2025-08-26 19:49 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Document privileges required for vacuumdb --missing-stats-only. When vacuumdb's --missing-stats-only option is used, the catalog query for retrieving the list of relations to process must read pg_statistic and pg_statistic_ext_data. However, those catalogs can only be read by superusers by default, so --missing-stats-only is effectively superuser-only. This is unfortunate, but since the option is primarily intended for use by administrators after running pg_upgrade, let's just live with it for v18. This commit adds a note about the aforementioned privilege requirements to the documentation for --missing-stats-only. We first tried to improve matters by modifying the query to read the pg_stats and pg_stats_ext system views instead. While that is indeed more lenient from a privilege standpoint, it is also borderline incomprehensible. pg_stats shows rows for which the user has the SELECT privilege on the corresponding column, and pg_stats_ext shows rows for tables the user owns. Meanwhile, ANALYZE requires either MAINTAIN on the table or, for non-shared relations, ownership of the database. But even if the privilege discrepancies were tolerable, the performance impact was not. Ultimately, the modified query was substantially more expensive, so we abandoned the idea. For v19, perhaps we could introduce a simple, inexpensive way to discover which relations are missing statistics, such as a system function or view with similar privilege requirements to ANALYZE. Unfortunately, it is far too late for anything like that in v18. Reviewed-by: Yugo Nagata <nagata@sraoss.co.jp> Reviewed-by: Fujii Masao <masao.fujii@gmail.com> Discussion: https://postgr.es/m/CAHGQGwHh43suEfss1wvBsk7vqiou%3DUY0zcy8HGyE5hBp%2BHZ7SQ%40mail.gmail.com Backpatch-through: 18 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/984d7165dde7133dbdddc95e95ff56bbb177f628 Modified Files -------------- doc/src/sgml/ref/vacuumdb.sgml | 8 ++++++++ 1 file changed, 8 insertions(+) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Document privileges required for vacuumdb --missing-stats-only. @ 2025-08-26 19:49 Nathan Bossart <nathan@postgresql.org> 0 siblings, 0 replies; 2+ messages in thread From: Nathan Bossart @ 2025-08-26 19:49 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Document privileges required for vacuumdb --missing-stats-only. When vacuumdb's --missing-stats-only option is used, the catalog query for retrieving the list of relations to process must read pg_statistic and pg_statistic_ext_data. However, those catalogs can only be read by superusers by default, so --missing-stats-only is effectively superuser-only. This is unfortunate, but since the option is primarily intended for use by administrators after running pg_upgrade, let's just live with it for v18. This commit adds a note about the aforementioned privilege requirements to the documentation for --missing-stats-only. We first tried to improve matters by modifying the query to read the pg_stats and pg_stats_ext system views instead. While that is indeed more lenient from a privilege standpoint, it is also borderline incomprehensible. pg_stats shows rows for which the user has the SELECT privilege on the corresponding column, and pg_stats_ext shows rows for tables the user owns. Meanwhile, ANALYZE requires either MAINTAIN on the table or, for non-shared relations, ownership of the database. But even if the privilege discrepancies were tolerable, the performance impact was not. Ultimately, the modified query was substantially more expensive, so we abandoned the idea. For v19, perhaps we could introduce a simple, inexpensive way to discover which relations are missing statistics, such as a system function or view with similar privilege requirements to ANALYZE. Unfortunately, it is far too late for anything like that in v18. Reviewed-by: Yugo Nagata <nagata@sraoss.co.jp> Reviewed-by: Fujii Masao <masao.fujii@gmail.com> Discussion: https://postgr.es/m/CAHGQGwHh43suEfss1wvBsk7vqiou%3DUY0zcy8HGyE5hBp%2BHZ7SQ%40mail.gmail.com Backpatch-through: 18 Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/6b34e2157539c6fbdc62a78d7bb49409f222672b Modified Files -------------- doc/src/sgml/ref/vacuumdb.sgml | 8 ++++++++ 1 file changed, 8 insertions(+) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-08-26 19:49 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-08-26 19:49 pgsql: Document privileges required for vacuumdb --missing-stats-only. Nathan Bossart <nathan@postgresql.org> 2025-08-26 19:49 pgsql: Document privileges required for vacuumdb --missing-stats-only. Nathan Bossart <nathan@postgresql.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox