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

2023-06-22 Thread Nathan Bossart
On Thu, Jun 22, 2023 at 08:43:01AM -0700, Nathan Bossart wrote: > I plan to commit these patches later today. Committed. I've also marked the related open item for v16 as resolved. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com

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

2023-06-22 Thread Nathan Bossart
On Thu, Jun 22, 2023 at 04:11:08PM +0900, Michael Paquier wrote: > Sounds good to me. Thanks. I plan to commit these patches later today. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com

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

2023-06-22 Thread Michael Paquier
On Wed, Jun 21, 2023 at 08:06:06PM -0700, Nathan Bossart wrote: > On Thu, Jun 22, 2023 at 10:46:41AM +0900, Michael Paquier wrote: >> - /* >> -* We already checked that the user has privileges to CLUSTER the >> -* partitioned table when we locked it earlier, so there's no

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

2023-06-21 Thread Nathan Bossart
On Thu, Jun 22, 2023 at 10:46:41AM +0900, Michael Paquier wrote: > On Wed, Jun 21, 2023 at 10:16:24AM -0700, Nathan Bossart wrote: >>> I think that there is a testing gap with the coverage of CLUSTER. >>> "Ownership of partitions is checked" is a test that looks for the case >>> where

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

2023-06-21 Thread Michael Paquier
On Wed, Jun 21, 2023 at 10:16:24AM -0700, Nathan Bossart wrote: >> I think that there is a testing gap with the coverage of CLUSTER. >> "Ownership of partitions is checked" is a test that looks for the case >> where regress_ptnowner owns the partitioned table and one of its >> partitions,

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

2023-06-21 Thread Michael Paquier
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

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

2023-06-21 Thread Nathan Bossart
On Tue, Jun 20, 2023 at 09:15:18PM -0700, Nathan Bossart wrote: > Perhaps we should add something like > > Note that while REINDEX on a partitioned index or table requires > MAINTAIN on the partitioned table, such commands skip the privilege > checks when processing the

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

2023-06-21 Thread Jeff Davis
On Tue, 2023-06-20 at 15:52 -0700, Nathan Bossart wrote: > At the moment, I think I'm inclined to call this "existing behavior" > since > we didn't check privileges for each partition in this case even > before > MAINTAIN was introduced.  IIUC we still process the individual > partitions > in v15

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

2023-06-21 Thread Jeff Davis
On Wed, 2023-06-21 at 07:53 +0900, Michael Paquier wrote: > I am not sure to understand this last sentence.  REINDEX on a > partitioned table builds a list of the indexes to work on in the > first > transaction processing the command in ReindexPartitions(), and there > is no need to process

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

2023-06-20 Thread Nathan Bossart
On Wed, Jun 21, 2023 at 10:21:04AM +0900, Michael Paquier wrote: > Looking at 0001.. Thanks for taking a look. > -step s2_auth { SET ROLE regress_cluster_part; } > +step s2_auth { SET ROLE regress_cluster_part; SET > client_min_messages = ERROR; } > > Is this change

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

2023-06-20 Thread Michael Paquier
On Tue, Jun 20, 2023 at 03:52:57PM -0700, Nathan Bossart wrote: > However, I do agree that it feels inconsistent. Besides the options you > proposed, we might also consider making REINDEX work a bit more like VACUUM > and ANALYZE and emit a WARNING for any relations that the user is not >

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

2023-06-20 Thread Michael Paquier
On Tue, Jun 20, 2023 at 11:43:05AM -0700, Jeff Davis wrote: > The only behavior I'm worried about is REINDEX. I'm not sure what we > should do about it, or if we even want to do something about it. If we > want REINDEX to fail in this case, we should be sure to check > permissions on everything

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

2023-06-20 Thread Nathan Bossart
I've attached rebased versions of the remaining two patches. On Tue, Jun 20, 2023 at 11:43:05AM -0700, Jeff Davis wrote: > * REINDEX TABLE applies to all indexes in all partitions, which seems > a bit inconsistent. > > The only behavior I'm worried about is REINDEX. I'm not sure what we >

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

2023-06-20 Thread Nathan Bossart
On Tue, Jun 20, 2023 at 11:49:36AM -0700, Jeff Davis wrote: > On Tue, 2023-06-20 at 10:56 -0700, Nathan Bossart wrote: >> On Tue, Jun 20, 2023 at 10:49:27AM -0700, Nathan Bossart wrote: >> > Patch incoming... >> >> Attached. > > Looks good to me. Thanks, committed. -- Nathan Bossart Amazon

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

2023-06-20 Thread Jeff Davis
On Tue, 2023-06-20 at 10:56 -0700, Nathan Bossart wrote: > On Tue, Jun 20, 2023 at 10:49:27AM -0700, Nathan Bossart wrote: > > Patch incoming... > > Attached. Looks good to me. Regards, Jeff Davis

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

2023-06-20 Thread Jeff Davis
On Mon, 2023-06-19 at 14:55 -0700, Nathan Bossart wrote: > I'm hoping to commit 0002 and 0003 by the end of the week so > that these fixes are available in 16beta2. A few observations for the case where a user does have the MAINTAIN privilege on a partitioned table but not the partitions: *

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

2023-06-20 Thread Nathan Bossart
On Tue, Jun 20, 2023 at 10:49:27AM -0700, Nathan Bossart wrote: > Patch incoming... Attached. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com >From 8842b58808372175202960c0a7067b5f00eee122 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Wed, 14 Jun 2023 11:08:03 -0700

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

2023-06-20 Thread Nathan Bossart
On Tue, Jun 20, 2023 at 10:40:32AM -0700, Nathan Bossart wrote: > On Tue, Jun 20, 2023 at 10:04:37AM -0700, Jeff Davis wrote: >> I think v4-0001 broke the handling of toast tables? It looks like you >> removed the check for !skip_privs but need to add it to the flags in >>

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

2023-06-20 Thread Nathan Bossart
On Tue, Jun 20, 2023 at 09:16:59AM -0700, Jeff Davis wrote: > On Tue, 2023-06-20 at 14:26 +0900, Michael Paquier wrote: >> TBH, I have a mixed feeling about this line of reasoning because >> MAINTAIN is much broader and less specific than TRUNCATE, for >> instance, being spawned across so much

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

2023-06-20 Thread Nathan Bossart
On Tue, Jun 20, 2023 at 10:04:37AM -0700, Jeff Davis wrote: > I think v4-0001 broke the handling of toast tables? It looks like you > removed the check for !skip_privs but need to add it to the flags in > vacuum_is_permitted_for_relation(). Good catch. I'm not sure why some of the calls to

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

2023-06-20 Thread Jeff Davis
On Mon, 2023-06-19 at 14:55 -0700, Nathan Bossart wrote: > In v4 of the patch set, I moved the skip_privs flag refactoring to > 0001.  I > intend to commit this tomorrow unless there is additional feedback. I think v4-0001 broke the handling of toast tables? It looks like you removed the check

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

2023-06-20 Thread Jeff Davis
On Tue, 2023-06-20 at 14:26 +0900, Michael Paquier wrote: > TBH, I have a mixed feeling about this line of reasoning because > MAINTAIN is much broader and less specific than TRUNCATE, for > instance, being spawned across so much more operations. ... > Some users may find that surprising as they

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

2023-06-19 Thread Michael Paquier
On Mon, Jun 19, 2023 at 02:55:34PM -0700, Nathan Bossart wrote: > In v4 of the patch set, I moved the skip_privs flag refactoring to 0001. I > intend to commit this tomorrow unless there is additional feedback. Fine by me. 0001 looks OK seen from here. > These object_ownercheck() calls were

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

2023-06-19 Thread Nathan Bossart
On Thu, Jun 15, 2023 at 10:20:25PM -0700, Nathan Bossart wrote: > I noticed that it was possible to make the documentation changes in 0001 > easier to read. Apologies for the noise. Yet more noise... In v4 of the patch set, I moved the skip_privs flag refactoring to 0001. I intend to commit

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

2023-06-15 Thread Nathan Bossart
On Thu, Jun 15, 2023 at 04:57:00PM -0700, Nathan Bossart wrote: > Here's an attempt at adjusting the documentation as I proposed yesterday. > I think this is a good opportunity to simplify the privilege-related > sections for these maintenance commands. I noticed that it was possible to make the

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

2023-06-15 Thread Nathan Bossart
On Wed, Jun 14, 2023 at 09:10:44PM -0700, Nathan Bossart wrote: > IMO > we should update the docs and leave out the ownership checks since MAINTAIN > is now a grantable privilege like any other. WDYT? Here's an attempt at adjusting the documentation as I proposed yesterday. I think this is a

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

2023-06-14 Thread Nathan Bossart
On Thu, Jun 15, 2023 at 09:46:33AM +0900, Michael Paquier wrote: > The result after 0001 is applied is that a couple of > object_ownercheck() calls that existed before ff9618e are removed from > some ACL checks in the REINDEX, CLUSTER and VACUUM paths. Is that OK > for shared relations and

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

2023-06-14 Thread Michael Paquier
On Wed, Jun 14, 2023 at 11:17:11AM -0700, Nathan Bossart wrote: > On Tue, Jun 13, 2023 at 04:54:42PM -0700, Nathan Bossart wrote: > > On Wed, Jun 14, 2023 at 08:16:15AM +0900, Michael Paquier wrote: > >> So, yes, agreed about the removal of has_partition_ancestor_privs(). > >> I am adding an open

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

2023-06-14 Thread Jeff Davis
On Tue, 2023-06-13 at 14:12 -0700, Nathan Bossart wrote: > I've been reviewing ff9618e lately, and I'm wondering whether it has > the > same problem that 19de0ab solved.  Specifically, ff9618e introduces > has_partition_ancestor_privs(), which is used to check whether a user > has > MAINTAIN on

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

2023-06-14 Thread Nathan Bossart
On Tue, Jun 13, 2023 at 04:54:42PM -0700, Nathan Bossart wrote: > On Wed, Jun 14, 2023 at 08:16:15AM +0900, Michael Paquier wrote: >> So, yes, agreed about the removal of has_partition_ancestor_privs(). >> I am adding an open item assigned to you and Jeff. > > Thanks. I suspect there's more

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

2023-06-13 Thread Nathan Bossart
On Wed, Jun 14, 2023 at 08:16:15AM +0900, Michael Paquier wrote: > While on it, this buzzes me: > static bool > -vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) > +vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool > skip_privs) > > VacuumParams has been

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

2023-06-13 Thread Michael Paquier
On Tue, Jun 13, 2023 at 02:12:46PM -0700, Nathan Bossart wrote: > I've been reviewing ff9618e lately, and I'm wondering whether it has the > same problem that 19de0ab solved. Specifically, ff9618e introduces > has_partition_ancestor_privs(), which is used to check whether a user has > MAINTAIN on

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

2023-06-13 Thread Nathan Bossart
I've been reviewing ff9618e lately, and I'm wondering whether it has the same problem that 19de0ab solved. Specifically, ff9618e introduces has_partition_ancestor_privs(), which is used to check whether a user has MAINTAIN on any partition ancestors. This involves syscache lookups, and presently

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

2023-01-13 Thread Nathan Bossart
On Fri, Jan 13, 2023 at 02:56:26PM -0800, Nathan Bossart wrote: > Okay. Here is a new patch set. And here is a rebased patch set (c44f633 changed the same LOCK TABLE docs). -- Nathan Bossart Amazon Web Services: https://aws.amazon.com >From 48be435025ba10235c821cbd298c43763cbc5a56 Mon Sep 17

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

2023-01-13 Thread Nathan Bossart
On Fri, Jan 13, 2023 at 01:30:28PM -0800, Jeff Davis wrote: > If we care about that use case, let's do it right and have forms of > VACUUM/CLUSTER/REINDEX that check permissions on the main table, skip > the work on the main table, and descend directly to the toast tables. > That doesn't seem

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

2023-01-13 Thread Jeff Davis
On Fri, 2023-01-13 at 12:33 -0800, Nathan Bossart wrote: > That would fix the problem in the original complaint, but it wouldn't > allow > for vacuuming toast tables directly if you only have MAINTAIN > privileges on > the main relation.  If you can vacuum the toast table indirectly via > the >

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

2023-01-13 Thread Nathan Bossart
On Fri, Jan 13, 2023 at 11:56:03AM -0800, Jeff Davis wrote: > I'm hesitant to add an index to pg_class just for the privilege checks > on toast tables, and I don't think we need to. I bet this index will be useful for more than just these privilege checks (e.g., autovacuum currently creates a

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

2023-01-13 Thread Jeff Davis
On Mon, 2023-01-09 at 14:51 -0800, Nathan Bossart wrote: > On Tue, Jan 03, 2023 at 03:45:49PM -0800, Nathan Bossart wrote: > > I'd like to get this fixed, but I have yet to hear thoughts on the > > suggested approach.  I'll proceed with adjusting the tests and > > documentation shortly unless

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

2023-01-09 Thread Nathan Bossart
On Tue, Jan 03, 2023 at 03:45:49PM -0800, Nathan Bossart wrote: > I'd like to get this fixed, but I have yet to hear thoughts on the > suggested approach. I'll proceed with adjusting the tests and > documentation shortly unless someone objects. As promised, here is a new version of the patch

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

2023-01-03 Thread Nathan Bossart
On Sun, Dec 18, 2022 at 03:30:18PM -0800, Nathan Bossart wrote: > Here is a new version of the patch. Besides some cleanup, I added an index > on reltoastrelid for the toast-to-main-relation lookup. Before I bother > adjusting the tests and documentation, I'm curious to hear thoughts on >

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

2022-12-19 Thread Jeff Davis
On Sun, 2022-12-18 at 17:38 -0600, Justin Pryzby wrote: > On Sat, Dec 17, 2022 at 04:39:29AM -0800, Ted Yu wrote: > > +   List   *ancestors = get_partition_ancestors(relid); > > +   Oid root = InvalidOid; > > > > nit: it would be better if the variable `root` can

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

2022-12-18 Thread Nathan Bossart
On Sun, Dec 18, 2022 at 04:25:15PM -0800, Ted Yu wrote: > + * Note: Because this function assumes that the realtion whose OID is > passed as > > Typo: realtion -> relation Thanks, fixed. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com >From

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

2022-12-18 Thread Ted Yu
On Sun, Dec 18, 2022 at 3:30 PM Nathan Bossart wrote: > Here is a new version of the patch. Besides some cleanup, I added an index > on reltoastrelid for the toast-to-main-relation lookup. Before I bother > adjusting the tests and documentation, I'm curious to hear thoughts on > whether this

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

2022-12-18 Thread Justin Pryzby
On Sat, Dec 17, 2022 at 04:39:29AM -0800, Ted Yu wrote: > + List *ancestors = get_partition_ancestors(relid); > + Oid root = InvalidOid; > > nit: it would be better if the variable `root` can be aligned with variable > `ancestors`. It is aligned, but only

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

2022-12-18 Thread Nathan Bossart
Here is a new version of the patch. Besides some cleanup, I added an index on reltoastrelid for the toast-to-main-relation lookup. Before I bother adjusting the tests and documentation, I'm curious to hear thoughts on whether this seems like a viable approach. On Sat, Dec 17, 2022 at 04:39:29AM

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

2022-12-17 Thread Ted Yu
On Fri, Dec 16, 2022 at 10:04 PM Nathan Bossart wrote: > On Thu, Dec 15, 2022 at 10:10:43AM -0800, Jeff Davis wrote: > > The proposal to skip privilege checks for partitions would be > > consistent with INSERT, SELECT, REINDEX that flow through to the > > underlying partitions regardless of

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

2022-12-16 Thread Nathan Bossart
On Thu, Dec 15, 2022 at 10:10:43AM -0800, Jeff Davis wrote: > The proposal to skip privilege checks for partitions would be > consistent with INSERT, SELECT, REINDEX that flow through to the > underlying partitions regardless of permissions/ownership (and even > RLS). It would be very minor

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

2022-12-15 Thread Nathan Bossart
On Thu, Dec 15, 2022 at 09:13:54PM -0800, Nathan Bossart wrote: > I do wonder whether this is something we really need to be concerned about. > In the worst case, you might be able to VACUUM a table with a concurrent > owner change. I suppose we might want to avoid running the index functions as

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

2022-12-15 Thread Nathan Bossart
On Thu, Dec 15, 2022 at 08:35:53PM -0800, Jeff Davis wrote: > But why make CLUSTER more like VACUUM? Shouldn't we make > VACUUM/CLUSTER/ANALYZE more like INSERT/SELECT/REINDEX? Hm. Since VACUUM may happen across multiple transactions, it is careful to re-check the privileges for each relation.

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

2022-12-15 Thread Jeff Davis
On Thu, 2022-12-15 at 17:19 -0800, Nathan Bossart wrote: > The attached patch adds WARNING messages, but we're still far from > consistency with VACUUM/ANALYZE. But why make CLUSTER more like VACUUM? Shouldn't we make VACUUM/CLUSTER/ANALYZE more like INSERT/SELECT/REINDEX? As far as I can tell,

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

2022-12-15 Thread Nathan Bossart
On Thu, Dec 15, 2022 at 01:48:13PM -0600, Justin Pryzby wrote: > vacuum initially calls vacuum_is_permitted_for_relation() only for the > partitioned table, and *later* locks the partition and then checks its > permissions, which is when the message is output. > > Since v15, cluster calls

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

2022-12-15 Thread Justin Pryzby
On Thu, Dec 15, 2022 at 10:10:43AM -0800, Jeff Davis wrote: > On Thu, 2022-12-15 at 12:31 +0300, Pavel Luzanov wrote: > > I think the approach that Nathan implemented [1] for TOAST tables > > in the latest version can be used for partitioned tables as well. > > Skipping the privilege check for

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

2022-12-15 Thread Nathan Bossart
On Thu, Dec 15, 2022 at 10:42:15AM -0800, Jeff Davis wrote: > Right now, targetting the toast table directly requires the USAGE > privilege on the toast schema, and you have to look up the name first, > right? As it is, that's not a great UI. > > How about if we add a VACUUM option like

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

2022-12-15 Thread Jeff Davis
On Wed, 2022-12-14 at 16:27 -0800, Nathan Bossart wrote: > I don't know if this is good enough.  It seems like ideally you > should be > able to VACUUM a TOAST table directly if you have MAINTAIN on its > main > relation. Right now, targetting the toast table directly requires the USAGE privilege

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

2022-12-15 Thread Jeff Davis
On Thu, 2022-12-15 at 12:31 +0300, Pavel Luzanov wrote: > I think the approach that Nathan implemented [1] for TOAST tables > in the latest version can be used for partitioned tables as well. > Skipping the privilege check for partitions while working with > a partitioned table. In that case we

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

2022-12-15 Thread Pavel Luzanov
On 15.12.2022 03:27, Nathan Bossart wrote: Another option I'm looking at is skipping the privilege checks when VACUUM recurses to a TOAST table. This won't allow you to VACUUM the TOAST table directly, but it would at least address the originally-reported issue This approach can be

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

2022-12-15 Thread Pavel Luzanov
On 15.12.2022 03:18, Jeff Davis wrote: Right, that's what I had in mind: a user is only granted operations on the partitioned table, not the partitions. It's all clear now. There's definitely a problem with this patch and partitioning, because REINDEX affects the partitions, CLUSTER is a

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

2022-12-14 Thread Nathan Bossart
On Thu, Dec 15, 2022 at 09:12:26AM +0900, Michael Paquier wrote: > On Wed, Dec 14, 2022 at 03:29:39PM -0800, Nathan Bossart wrote: >> On Wed, Dec 14, 2022 at 11:05:13AM -0800, Jeff Davis wrote: >>> On Wed, 2022-12-14 at 10:16 -0800, Nathan Bossart wrote: Okay.  Should all the privileges

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

2022-12-14 Thread Jeff Davis
On Wed, 2022-12-14 at 16:11 -0600, Justin Pryzby wrote: > Yeah, but: > > regression=> insert into p1 values (1); > ERROR:  permission denied for table p1 > regression=> select * from p1; > ERROR:  permission denied for table p1 Right, that's what I had in mind: a user is only granted operations

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

2022-12-14 Thread Michael Paquier
On Wed, Dec 14, 2022 at 03:29:39PM -0800, Nathan Bossart wrote: > On Wed, Dec 14, 2022 at 11:05:13AM -0800, Jeff Davis wrote: >> On Wed, 2022-12-14 at 10:16 -0800, Nathan Bossart wrote: >>> Okay.  Should all the privileges governed by MAINTAIN apply to a >>> relation's >>> TOAST table as well? >>

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

2022-12-14 Thread Nathan Bossart
On Wed, Dec 14, 2022 at 11:05:13AM -0800, Jeff Davis wrote: > On Wed, 2022-12-14 at 10:16 -0800, Nathan Bossart wrote: >> Okay.  Should all the privileges governed by MAINTAIN apply to a >> relation's >> TOAST table as well? > > Yes, I agree. This might be tricky, because AFAICT you have to scan

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

2022-12-14 Thread Isaac Morland
On Wed, 14 Dec 2022 at 15:57, Jeff Davis wrote: > On Wed, 2022-12-14 at 15:32 -0500, Isaac Morland wrote: > > > Is there a firm decision on the issue of changing the cluster index > > of a table? Re-clustering a table on the same index is clearly > > something that should be granted by MAINTAIN

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

2022-12-14 Thread Justin Pryzby
On Thu, Dec 15, 2022 at 01:02:39AM +0300, Pavel Luzanov wrote: > On 14.12.2022 22:46, Jeff Davis wrote: > > The behavior is that MAINTAIN > > privileges on the partitioned table does not imply MAINTAIN privileges > > on the partitions. I believe that's fine and it's consistent with other > >

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

2022-12-14 Thread Pavel Luzanov
On 14.12.2022 22:46, Jeff Davis wrote: The behavior is that MAINTAIN privileges on the partitioned table does not imply MAINTAIN privileges on the partitions. I believe that's fine and it's consistent with other privileges on partitioned tables, such as SELECT and INSERT. Sorry, I may have

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

2022-12-14 Thread Jeff Davis
On Wed, 2022-12-14 at 15:32 -0500, Isaac Morland wrote: > Is there a firm decision on the issue of changing the cluster index > of a table? Re-clustering a table on the same index is clearly > something that should be granted by MAINTAIN as I imagine it, but > changing the cluster index, strictly

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

2022-12-14 Thread Isaac Morland
On Wed, 14 Dec 2022 at 14:47, Jeff Davis wrote: Furthermore, MAINTAIN privileges on the partitioned table do not grant > the ability to create new partitions. There's a comment in tablecmds.c > alluding to a possible "UNDER" privilege: > > /* >* We should have an UNDER permission flag for

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

2022-12-14 Thread Jeff Davis
On Wed, 2022-12-14 at 12:07 +0300, Pavel Luzanov wrote: > After a fresh install, including the patch for \dpS [1], > I found that granting MAINTAIN privilege does not allow the TOAST > table > to be vacuumed. I wanted to also mention partitioning. The behavior is that MAINTAIN privileges on the

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

2022-12-14 Thread Jeff Davis
On Wed, 2022-12-14 at 10:16 -0800, Nathan Bossart wrote: > Okay.  Should all the privileges governed by MAINTAIN apply to a > relation's > TOAST table as well? Yes, I agree. -- Jeff Davis PostgreSQL Contributor Team - AWS

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

2022-12-14 Thread Nathan Bossart
On Wed, Dec 14, 2022 at 07:05:34PM +0100, Alvaro Herrera wrote: > On 2022-Dec-14, Nathan Bossart wrote: >> On Wed, Dec 14, 2022 at 12:07:13PM +0300, Pavel Luzanov wrote: >> > I found that granting MAINTAIN privilege does not allow the TOAST table to >> > be vacuumed. >> >> Hm. My first thought

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

2022-12-14 Thread Alvaro Herrera
On 2022-Dec-14, Nathan Bossart wrote: > On Wed, Dec 14, 2022 at 12:07:13PM +0300, Pavel Luzanov wrote: > > I found that granting MAINTAIN privilege does not allow the TOAST table to > > be vacuumed. > > Hm. My first thought is that this is the appropriate behavior. WDYT? It seems wrong to me.

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

2022-12-14 Thread Nathan Bossart
On Wed, Dec 14, 2022 at 12:07:13PM +0300, Pavel Luzanov wrote: > I found that granting MAINTAIN privilege does not allow the TOAST table to > be vacuumed. Hm. My first thought is that this is the appropriate behavior. WDYT? > So, the patch for \dpS works as expected and can be committed.

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

2022-12-14 Thread Pavel Luzanov
After a fresh install, including the patch for \dpS [1], I found that granting MAINTAIN privilege does not allow the TOAST table to be vacuumed. postgres@postgres(16.0)=# GRANT MAINTAIN ON pg_type TO alice; GRANT postgres@postgres(16.0)=# \c - alice You are now connected to database "postgres"

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

2022-12-13 Thread Nathan Bossart
On Tue, Dec 13, 2022 at 07:05:10PM -0800, Jeff Davis wrote: > Committed. > > The only significant change is that it also allows LOCK TABLE if you > have the MAINTAIN privilege. Thanks! > I noticed a couple issues unrelated to your patch, and started separate > patch threads[1][2]. Will take a

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

2022-12-13 Thread Jeff Davis
On Mon, 2022-12-12 at 13:01 -0800, Nathan Bossart wrote: > On Mon, Dec 12, 2022 at 12:04:27PM -0800, Nathan Bossart wrote: > > Patch attached.  I ended up reverting some parts of the > > VACUUM/ANALYZE > > patch that were no longer needed (i.e., if the user doesn't have > > permission > > to

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

2022-12-12 Thread Nathan Bossart
On Mon, Dec 12, 2022 at 12:04:27PM -0800, Nathan Bossart wrote: > Patch attached. I ended up reverting some parts of the VACUUM/ANALYZE > patch that were no longer needed (i.e., if the user doesn't have permission > to VACUUM, we don't need to separately check whether the user has > permission to

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

2022-12-12 Thread Nathan Bossart
On Sat, Dec 10, 2022 at 12:41:09PM -0800, Nathan Bossart wrote: > On Sat, Dec 10, 2022 at 12:07:12PM -0800, Jeff Davis wrote: >> 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? > > Given

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

2022-12-10 Thread Nathan Bossart
On Sat, Dec 10, 2022 at 12:07:12PM -0800, Jeff Davis wrote: > 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? Given the feedback in the other thread [0], I was planning to rewrite this patch

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

2022-12-10 Thread Jeff Davis
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

allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

2022-12-08 Thread Nathan Bossart
Hi hackers, This is meant as a continuation of the work to make VACUUM and ANALYZE grantable privileges [0]. As noted there, the primary motivation for this is to continue chipping away at things that require special privileges or even superuser. I've attached two patches. 0001 makes it