Re: Add pg_stat_autovacuum_priority

On Mon, Mar 30, 2026 at 11:17 AM Sami Imseih <samimseih(at)gmail(dot)com> wrote:
> > On Sun, Mar 29, 2026 at 10:09 PM Bharath Rupireddy
> > <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> > > On Sat, Mar 28, 2026 at 10:54 AM Sami Imseih <samimseih(at)gmail(dot)com> wrote:
> > > >
> > > > > 4. Is the view intended to be exposed to PUBLIC without any ACL restrictions?
> > > >
> > > > > 2/ Do we need to revoke permissions on pg_stat_get_autovacuum_priority
> > > > > for all and grant them to pg_monitor or similar? Especially since this
> > > > > function loops over all the relations in a database, we may not want
> > > > > everyone to be able to do this.
> > > >
> > > > I think you're correct there. While the data is not sensitive, it
> > > > should have more controlled usage. It's only taking an AccessShareLock,
> > > > but you would not want anyone to be able to run this since it's
> > > > doing real computation. I think requiring pg_read_all_stats
> > > > is a good idea. Will do.
> > >
> > > +1 for pg_read_all_stats.
> > >
> >
> > Is there a gap here where someone may have been granted MAINTAIN on a
> > relation but they do not have pg_read_all_stats?
>
> Yes, that is possible. MAINTAIN is a per-object privilege granted on a relation,
> whereas pg_read_all_stats is a global role membership. They operate at
> different levels.
>
> I don't think one needs to have MAINTAIN permissions on the table to see the
> autovacuum score. DBA Monitoring users are usually separate from the DBA
> operational users.
>
> I think pg_read_all_stats is the right permission here and it should
> be implemented
> similar to how pg_get_shmem_allocations is done where the default permissions
> are pg_read_all_stats. pg_monitor inherits pg_read_all_stats so any
> user with this
> privilege will be able to access this view. A DBA is free to also add
> privileges to
> to other users if they wish.
>
> This is unlike other pg_stat_* views that have tuple level permission
> checks ( i.e.
> pg_stat_activity), but in those cases the permissions are needed to
> hide sensitive data.
> This is not the case here.
>

I don't think we are in disagreement here, I was just thinking about
it the other way round; someone might have MAINTAIN privileges on a
table and want to see what the relevant "autovacuum score" is before
taking action. If the solution for that is to give those roles
pg_read_all_stats, I guess that's ok, but there was probably a reason
the permissions were limited in the first place. *shrug*

> > > IMHO, we can have pg_stat_get_relation_autovacuum_priority defined as
> > > a C function to give the autovacuum scoring as of the given moment for
> > > the given table. It's easy for one to write a function to get scoring
> > > for all the relations in a database. This keeps things simple yet
> > > useful.
> > >
> >
> > I don't have a strong opinion on the above, but I do suspect that the
> > most common way people will interact with this is by querying against
> > the view with a WHERE clause, so optimizing for that case seems
> > important.
>
> Yeah, after sleeping on it I actually think the most common case will likely be
> ORDER BY score DESC LIMIT ... because you usually want to see where your
> table priority is relative to everything else in the database.
> For the rare case where someone wants to look up an individual table, the caller
> can just use a WHERE clause. So, we should just always do the full pg_class
> scan. I don't see why we need to complicate the c-function more than this.
>

I think we are also in agreement here, although based on my
experience, filtering out things like system and toast tables will be
common, but I don't see that changing what you said above. On a
similar note, +1 to your changes in v2.

Robert Treat
https://xzilla.net