On Sat, 23 Aug 2025 09:36:07 -0500
Nathan Bossart <nathandboss...@gmail.com> wrote:
> On Sat, Aug 23, 2025 at 05:32:30AM -0400, Corey Huinker wrote:
> > On Fri, Aug 22, 2025 at 11:20 PM Fujii Masao <masao.fu...@gmail.com> wrote:
> >> On Sat, Aug 23, 2025 at 12:00 PM Nathan Bossart <nathandboss...@gmail.com>
> >> wrote:
> >>> Hm. Maybe we should just document that the option requires SELECT
> >>> privileges on pg_statistic and pg_statistic_ext_data (which are restricted
> >>> to superusers by default). I suspect we have relatively limited
> >>> opportunities for tuning the query, and I'd like to avoid invasive changes
> >>> to v18 at this point.
> >>
> >> Yeah, adding a note about the permissions required for
> >> --missing-stats-only,
> >> leaving the query unchanged in v18, and revisiting the issue in v19 seems
> >> reasonable given the limited time before the v18 release.
> >
> > Rather than resorting to the redundant where-clause trick that we did in
> > pg_dump.
>
> Here's a patch for the documentation update.
The documentation fix looks good to me. However, it’s not very user-friendly
that,
when the user lacks the required privileges, an error from the internal query is
raised. Instead, how about checking whether the user has the necessary
privileges
and printing an appropriate message if any privilege is missing?
I've added the 0002 patch to address this; the 0001 is the same as the previous.
The check is performed per database, so it may be a bit slower when an enormous
number of databases are processed, but the overhead should be relatively small
compared
with the other queries executed together.
Regards,
Yugo Nagata
--
Yugo Nagata <nag...@sraoss.co.jp>
>From 7e86930ede6ccc9294c14c748adba068e6e88ecf Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nag...@sraoss.co.jp>
Date: Mon, 25 Aug 2025 06:18:04 +0900
Subject: [PATCH v5 2/2] vacuumdb: check SELECT privilege on pg_statitis or
pg_statistic_ext_data
---
src/bin/scripts/vacuumdb.c | 24 ++++++++++++++++++++++++
1 file changed, 24 insertions(+)
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index fd236087e90..2087b4131a2 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -801,6 +801,30 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
SimpleStringList *found_objs = palloc0(sizeof(SimpleStringList));
bool objects_listed = false;
+ if (vacopts->missing_stats_only)
+ {
+ PQExpBufferData aclcheck;
+
+ initPQExpBuffer(&aclcheck);
+ appendPQExpBufferStr(&aclcheck,
+ "SELECT has_table_privilege('pg_catalog.pg_statistic', 'select'), "
+ "has_table_privilege('pg_catalog.pg_statistic_ext_data', 'select')");
+ res = executeQuery(conn, aclcheck.data, echo);
+ termPQExpBuffer(&aclcheck);
+ if (strcmp(PQgetvalue(res, 0, 0), "t") != 0)
+ {
+ PQfinish(conn);
+ pg_fatal("--missing-stats-only requires SELECT privileges on pg_statistic");
+ }
+ else if (strcmp(PQgetvalue(res, 0, 1), "t") != 0)
+ {
+ PQfinish(conn);
+ pg_fatal("--missing-stats-only requires SELECT privileges on pg_statistic_ext_data");
+ }
+
+ termPQExpBuffer(&aclcheck);
+ }
+
initPQExpBuffer(&catalog_query);
for (cell = objects ? objects->head : NULL; cell; cell = cell->next)
{
--
2.43.0
>From 3ce399cb775e7f3a6f0b0a46959f9a3bc67ad7f2 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nat...@postgresql.org>
Date: Sat, 23 Aug 2025 09:33:53 -0500
Subject: [PATCH v5 1/2] doc: Note privileges required for vacuumdb
--missing-stats-only.
---
doc/src/sgml/ref/vacuumdb.sgml | 8 ++++++++
1 file changed, 8 insertions(+)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 53147480515..84c76d7350c 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -292,6 +292,14 @@ PostgreSQL documentation
This option can only be used in conjunction with
<option>--analyze-only</option> or <option>--analyze-in-stages</option>.
</para>
+ <para>
+ Note that <option>--missing-stats-only</option> requires
+ <literal>SELECT</literal> privileges on
+ <link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
+ and
+ <link linkend="catalog-pg-statistic-ext-data"><structname>pg_statistic_ext_data</structname></link>,
+ which are restricted to superusers by default.
+ </para>
</listitem>
</varlistentry>
--
2.43.0
