> On Sun, Mar 29, 2026 at 10:09 PM Bharath Rupireddy > <[email protected]> wrote: > > On Sat, Mar 28, 2026 at 10:54 AM Sami Imseih <[email protected]> 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. > > 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. Attached v2 implements it as above. A few other things in v2: 1/ I set autovacuum_enabled = OFF in the tests. This will make sure the test is both stable and will also test that the score is returned even in the case where autovacuum is disabled. 2/ Moved pg_stat_autovacuum_priority to the end of the docs in "Monitoring Database Activity". 3/ Also added a mention of the extremely high score values when failsafe is triggered [1] [1] https://www.postgresql.org/message-id/abGP87A3JPIXDG2I%40nathan -- Sami
v2-0002-Add-pg_stat_autovacuum_priority-view.patch
Description: Binary data
v2-0001-Add-force_scores-option-to-relation_needs_vacanal.patch
Description: Binary data
