Re: vacuumdb --missing-stats-only and permission issue

On Thu, Aug 21, 2025 at 12:52:17PM -0400, Corey Huinker wrote:
> Here's the query changes, no regression test just yet.

I think there's a problem with the privilege checks for pg_stats (and
friends) versus ANALYZE.  pg_stats checks for SELECT privileges on the
column, while ANALYZE checks for MAINTAIN privileges.  If a role lacks
SELECT on the columns, it will always try to analyze the table.  If a role
lacks MAINTAIN on the table, it will fail if it tries to analyze the table.

    create role myrole login;
    create table no_stats_select as select generate_series(1, 10);
    create table no_stats_maintain as select generate_series(1, 10);
    create table stats_select as select generate_series(1, 10);
    create table stats_maintain as select generate_series(1, 10);
    grant maintain on no_stats_maintain to myrole;
    grant maintain on stats_maintain to myrole;
    grant select on no_stats_select to myrole;
    grant select on stats_select to myrole;
    analyze stats_select, stats_maintain;

    $ vacuumdb ... --missing-stats-only -Z -t stats_select
    vacuumdb: vacuuming database "postgres"

    $ vacuumdb ... --missing-stats-only -Z -t stats_maintain
    vacuumdb: vacuuming database "postgres"
    INFO:  analyzing "public.stats_maintain"
    ...

    $ vacuumdb ... --missing-stats-only -Z -t no_stats_select
    vacuumdb: vacuuming database "postgres"
    WARNING:  permission denied to analyze "no_stats_select", skipping it

    $ vacuumdb ... --missing-stats-only -Z -t no_stats_maintain
    vacuumdb: vacuuming database "postgres"
    INFO:  analyzing "public.no_stats_maintain"
    ...

Perhaps we should also filter for only columns for which the user has
SELECT and MAINTAIN.  Another option is just to look for SELECT and let the
attempt to ANALYZE it fail (since it just produces a WARNING).  Whatever we
choose should probably be noted in the vacuumdb docs.

-- 
nathan