predefined role(s) for VACUUM and ANALYZE

From: Nathan Bossart <nathandbossart(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: predefined role(s) for VACUUM and ANALYZE
Date: 2022-07-22 20:37:35
Message-ID: 20220722203735.GB3996698@nathanxps13
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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].



Nathan Bossart
Amazon Web Services:

Attachment Content-Type Size
pg_vacuum_analyze.patch text/x-diff 8.1 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2022-07-22 20:40:43 Re: make -C libpq check fails obscurely if tap tests are disabled
Previous Message Jacob Champion 2022-07-22 20:19:54 Re: Proposal: add a debug message about using geqo