Hi hackers,
The previous attempt to add a predefined role for VACUUM and ANALYZE [0]
resulted in the new pg_checkpoint role in v15. I'd like to try again to
add a new role (or multiple new roles) for VACUUM and ANALYZE.
The primary motivation for this is to continue chipping away at things that
require special privileges or even superuser. VACUUM and ANALYZE typically
require table ownership, database ownership, or superuser. And only
superusers can VACUUM/ANALYZE shared catalogs. A predefined role for these
operations would allow delegating such tasks (e.g., a nightly VACUUM
scheduled with pg_cron) to a role with fewer privileges.
The attached patch adds a pg_vacuum_analyze role that allows VACUUM and
ANALYZE commands on all relations. I started by trying to introduce
separate pg_vacuum and pg_analyze roles, but that quickly became
complicated because the VACUUM and ANALYZE code is intertwined. To
initiate the discussion, here's the simplest thing I could think of.
An alternate approach might be to allow using GRANT to manage these
privileges, as suggested in the previous thread [1].
Thoughts?
[0] https://postgr.es/m/67a1d667e8ec228b5e07f232184c80348c5d93f4.camel%40j-davis.com
[1] https://postgr.es/m/20211104224636.5qg6cfyjkw52rh4d@alap3.anarazel.de
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com