Re: predefined role(s) for VACUUM and ANALYZE

From: Nathan Bossart <nathandbossart(at)gmail(dot)com>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>, Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: predefined role(s) for VACUUM and ANALYZE
Date: 2022-09-05 18:56:30
Message-ID: 20220905185630.GA1961927@nathanxps13
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Here is a first attempt at allowing users to grant VACUUM or ANALYZE
per-relation. Overall, this seems pretty straightforward. I needed to
adjust the permissions logic for VACUUM/ANALYZE a bit, which causes some
extra WARNING messages for VACUUM (ANALYZE) in some cases, but this didn't
seem particularly worrisome. It may be desirable to allow granting ANALYZE
on specific columns or to allow granting VACUUM/ANALYZE at the schema or
database level, but that is left as a future exercise.

On Tue, Aug 23, 2022 at 07:46:47PM -0400, Stephen Frost wrote:
> I've long felt that we should redefine the way the ACLs work to have a
> distinct set of bits for each object type. We don't need to support a
> CONNECT bit on a table, yet we do today and we expend quite a few bits
> in that way. Having that handled on a per-object-type basis instead
> would allow us to get quite a bit more mileage out of the existing 32bit
> field before having to introduce more complicated storage methods like
> using a bit to tell us to go look up more ACLs somewhere else.

There are 2 bits remaining at the moment, so I didn't redesign the ACL
system in the attached patch. However, I did some research on a couple
options. Using a distinct set of bits for each catalog table should free
up a handful of bits, which should indeed kick the can down the road a
little. Another easy option is to simply make AclMode a uint64, which
would immediately free up another 16 privilege bits. I was able to get
this approach building and passing tests in a few minutes, but there might
be performance/space concerns.

Nathan Bossart
Amazon Web Services:

Attachment Content-Type Size
v3-0001-Allow-granting-VACUUM-and-ANALYZE-privileges-on-r.patch text/x-diff 39.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Zhihong Yu 2022-09-05 19:40:46 Re: Table AM modifications to accept column projection lists
Previous Message Peter Geoghegan 2022-09-05 18:22:36 Re: Backpatching nbtree VACUUM (page deletion) hardening