Horton

Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

Michael Paquier <michael@paquier.xyz>

View thread View on postgr.es
From: Michael Paquier <michael@paquier.xyz>
To: Jeff Davis <pgsql@j-davis.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>, Ted Yu <yuzhihong@gmail.com>, Pavel Luzanov <p.luzanov@postgrespro.ru>, Justin Pryzby <pryzby@telsasoft.com>, pgsql-hackers@postgresql.org
Date: 2023-06-22T00:50:54Z
Lists: pgsql-hackers 
On Wed, Jun 21, 2023 at 09:26:09AM -0700, Jeff Davis wrote:
> What I meant is that if you do:
> 
>   CREATE TABLE p(i INT, j INT) PARTITION BY RANGE (i);
>   CREATE TABLE p0 PARTITION OF p FOR VALUES FROM (00) TO (10);
>   CREATE TABLE p1 PARTITION OF p FOR VALUES FROM (10) TO (20);
>   CREATE INDEX p_idx ON p (i);
>   CREATE INDEX special_idx ON p0 (j);
>   GRANT MAINTAIN ON p TO foo;
>   \c - foo
>   REINDEX TABLE p;
> 
> That would reindex p0_i_idx and p1_i_idx, but skip special_idx. That
> might be too confusing, but feels a bit more consistent permissions-
> wise.

FWIW, the current behavior to reindex special_idx in this case feels
more natural to me, as the user requests a REINDEX at table-level, not
at index-level.  This would mean to me that all the indexes of all the
partitions should be rebuilt on, not just the partitioned indexes that
are defined in the partitioned table requested for rebuild.
--
Michael

Commits

  1. Improve privilege documentation for maintenance commands.

  2. Fix cache lookup hazards introduced by ff9618e82a.

  3. Move bool parameter for vacuum_rel() to option bits.

  4. Fix MAINTAIN privileges for toast tables and partitions.

  5. Add grantable MAINTAIN privilege and pg_maintain role.