MIME-Version: 1.0
References: 
 <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com>
 <CALj2ACXcmYsoJXTYtuGhkxNVXV5jtTNfL-vB+sQq-jE9__N5AA@mail.gmail.com>
 <CAA5RZ0surzz41exF5QwecuFU8NqZVRR5aDnC6MObeEcsXhfu4Q@mail.gmail.com>
 <CALj2ACX+SRgv2RO9Oo4Me-zzMMjHVg8rf-MqvMRbp9=1ioWbsg@mail.gmail.com>
 <CABV9wwM3nRitsgUxeCF0ywbAaLZV5jWC-9tj6WKxkdmQkHRcWg@mail.gmail.com>
 <CAA5RZ0vMp2B3UBUoqLVedy8G3u8_O8M11+Y5V7uZv3++CGYasg@mail.gmail.com>
In-Reply-To: 
 <CAA5RZ0vMp2B3UBUoqLVedy8G3u8_O8M11+Y5V7uZv3++CGYasg@mail.gmail.com>
From: Robert Treat <rob@xzilla.net>
Date: Mon, 30 Mar 2026 13:13:06 -0400
Message-ID: 
 <CABV9wwNBifXpOjxO9rGn1HHK=DG02qApVurWSHa+rDzPriK6pA@mail.gmail.com>
Subject: Re: Add pg_stat_autovacuum_priority
To: Sami Imseih <samimseih@gmail.com>
Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
 satyanarlapuram@gmail.com,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CABV9wwNBifXpOjxO9rGn1HHK%3DDG02qApVurWSHa%2BrDzPriK6pA%40mail.gmail.com>
Precedence: bulk

On Mon, Mar 30, 2026 at 11:17=E2=80=AFAM Sami Imseih <samimseih@gmail.com> =
wrote:
> > On Sun, Mar 29, 2026 at 10:09=E2=80=AFPM Bharath Rupireddy
> > <bharath.rupireddyforpostgres@gmail.com> wrote:
> > > On Sat, Mar 28, 2026 at 10:54=E2=80=AFAM Sami Imseih <samimseih@gmail=
.com> wrote:
> > > >
> > > > > 4. Is the view intended to be exposed to PUBLIC without any ACL r=
estrictions?
> > > >
> > > > > 2/ Do we need to revoke permissions on pg_stat_get_autovacuum_pri=
ority
> > > > > 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 w=
ant
> > > > > 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 AccessShareL=
ock,
> > > > 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 re=
lation,
> 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 permiss=
ions
> 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 fo=
r
> > > 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 lik=
ely 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_cla=
ss
> 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