From: Nathan Bossart <nathan@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Document privileges required for vacuumdb --missing-stats-only.
Date: Tue, 26 Aug 2025 19:49:53 +0000
Message-ID: <E1uqzg0-001nuX-2C@gemulon.postgresql.org> (raw)
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(+)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: nathan@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Document privileges required for vacuumdb --missing-stats-only.
In-Reply-To: <E1uqzg0-001nuX-2C@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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