Horton

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

Jeff Davis <pgsql@j-davis.com>

View thread View on postgr.es
From: Jeff Davis <pgsql@j-davis.com>
To: Nathan Bossart <nathandbossart@gmail.com>, pgsql-hackers@postgresql.org
Date: 2022-12-10T20:07:12Z
Lists: pgsql-hackers 
On Thu, 2022-12-08 at 10:37 -0800, Nathan Bossart wrote:
> 0001 makes it possible to
> grant CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX.  0002 adds
> predefined roles that allow performing these commands on all
> relations.

Regarding the pg_refresh_all_matview predefined role, I don't think
it's a good idea. Refreshing a materialized view doesn't seem like an
administrative action to me.

First, it's unbounded in time, so the admin would need to be careful to
have a timeout. Second, the freshness of a materialized view seems very
specific to the application, rather than something that an admin would
have a blanket policy about. Thirdly, there's not a lot of information
the admin could use to make decisions about when to refresh (as opposed
to VACUUM/CLUSTER/REINDEX, where the stats are helpful).

But I'm fine with having a grantable privilege to refresh a
materialized view.

It seems like the discussion on VACUUM/CLUSTER/REINDEX privileges is
happening in the other thread. What would you like to accomplish in
this thread?


-- 
Jeff Davis
PostgreSQL Contributor Team - AWS

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.