Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qC1Ra-0007iI-J5 for pgsql-hackers@arkaria.postgresql.org; Wed, 21 Jun 2023 17:16:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qC1RZ-0007t3-Eq for pgsql-hackers@arkaria.postgresql.org; Wed, 21 Jun 2023 17:16:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qC1RZ-0007pk-2F for pgsql-hackers@lists.postgresql.org; Wed, 21 Jun 2023 17:16:33 +0000 Received: from mail-pg1-x530.google.com ([2607:f8b0:4864:20::530]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qC1RV-003rKq-BX for pgsql-hackers@postgresql.org; Wed, 21 Jun 2023 17:16:32 +0000 Received: by mail-pg1-x530.google.com with SMTP id 41be03b00d2f7-54f85f8b961so4642734a12.3 for ; Wed, 21 Jun 2023 10:16:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1687367787; x=1689959787; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=/Mn/5Jadk5hvWnBqKjrxEwhkmUDc80mFyhLwYNgW7IU=; b=K4uyA+UOxbSVrQZYq8HeTn6img+Bh3DTcbisOSNhtCv/rmEQDdPe4LijMFP91R0VV2 kGbsP0W0QGCmOafdr+xyXEe6H4RNx9a5SlvbVDSTdrHDJUCiBs51/Ro/oeXiYo5LU6m+ 0VC9d3H6cuFEySzXPEDguCoTeEibN5zA4BA02PI4uSDP/Iqz4XaJl4b+LDjZjjLGHAZI SlCnv5XQAyy+rMa2IE7+0+yIPt810rzJxbdW6/dJPGfyAz51OarpcvhRe00VdF/A9a+q UiEHHoXUKOGCULJiDjTxkML/Nlms2lhNKAHzoFgp2krWCz5Tyt86wBx6HlTCWWCUcyGx Vuwg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1687367787; x=1689959787; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=/Mn/5Jadk5hvWnBqKjrxEwhkmUDc80mFyhLwYNgW7IU=; b=f1F9aWJNSGQxvi1lTr+MLvZcezOvl2NJYLIFR76/Z387S4iO+NjqRdKIwErOYDLDco V0u54DV84f1ECk1cr4HqEpHMwqTBJspwlci5S3NJ6xUYOEHzGy/bB/e0kyH0YmdS4Ego 2N3GYcAMGzcCnl3SYLOYT3Cg/nfqJQ4gBzECLXdOjrsZYCCkvg41nfqy4aohE0qJUGlA Tz/WIQ/BO7wmwHlj9Rr8VF/xZvYELFTtPzqx4RTEd+voeoT0iLPKvC0IqnBJo3+HrB83 53ls7M/6LJj82b0DfND3+1el+2zcS58JiPHzldKkWSu6BSbWg7FT/GCvgd5kIe21ox5Q FF4A== X-Gm-Message-State: AC+VfDzTJcsgtNftBXQGocPwzNScb3wOhFC+vqUwouQgZ/18OxWjIpDY 4Nn+sKJl1SmzkkARFsNfx8s= X-Google-Smtp-Source: ACHHUZ4p4WCCoequgCVRb29wUM4LUqpZf0o2VD2K39bINYJqL0EXkANITQVZS9e1h8lLiHfvs/xjPw== X-Received: by 2002:a17:90b:11d2:b0:25e:aedf:e82b with SMTP id gv18-20020a17090b11d200b0025eaedfe82bmr15344721pjb.15.1687367787033; Wed, 21 Jun 2023 10:16:27 -0700 (PDT) Received: from nathanxps13 ([50.47.162.83]) by smtp.gmail.com with ESMTPSA id p12-20020a17090a348c00b0025e857eb895sm9832871pjb.49.2023.06.21.10.16.26 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 21 Jun 2023 10:16:26 -0700 (PDT) Date: Wed, 21 Jun 2023 10:16:24 -0700 From: Nathan Bossart To: Michael Paquier Cc: Jeff Davis , Ted Yu , Pavel Luzanov , Justin Pryzby , pgsql-hackers@postgresql.org Subject: Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX Message-ID: <20230621171624.GA881253@nathanxps13> References: <20230614181711.GA488295@nathanxps13> <20230615041044.GA736001@nathanxps13> <20230615235700.GA877311@nathanxps13> <20230616052025.GA1026700@nathanxps13> <20230619215534.GA442477@nathanxps13> <20230620225257.GA771663@nathanxps13> <20230621041518.GA774124@nathanxps13> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="FL5UXtIhxfXey3p5" Content-Disposition: inline In-Reply-To: <20230621041518.GA774124@nathanxps13> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --FL5UXtIhxfXey3p5 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 individual partitions. > > Thoughts? I'm trying to keep the privilege documentation for maintenance > commands as simple as possible, so I'm hoping to avoid adding too much text > dedicated to these special cases. Here is a new patch set that includes this new sentence. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com --FL5UXtIhxfXey3p5 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v8-0001-partial-revert-of-ff9618e82a.patch" From 6a18f0ab9706eac0295c2e1e53dc2433df10e66a Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Mon, 19 Jun 2023 13:57:57 -0700 Subject: [PATCH v8 1/2] partial revert of ff9618e82a --- doc/src/sgml/ref/analyze.sgml | 5 +-- doc/src/sgml/ref/cluster.sgml | 5 +-- doc/src/sgml/ref/lock.sgml | 5 +-- doc/src/sgml/ref/reindex.sgml | 6 +--- doc/src/sgml/ref/vacuum.sgml | 5 +-- src/backend/commands/cluster.c | 10 ++---- src/backend/commands/indexcmds.c | 27 +++++++-------- src/backend/commands/lockcmds.c | 8 ----- src/backend/commands/tablecmds.c | 34 +++---------------- src/backend/commands/vacuum.c | 8 +---- src/include/commands/tablecmds.h | 1 - .../expected/cluster-conflict-partition.out | 14 ++++---- .../specs/cluster-conflict-partition.spec | 7 ++-- src/test/regress/expected/cluster.out | 3 +- src/test/regress/expected/create_index.out | 4 +-- src/test/regress/expected/privileges.out | 8 ++--- src/test/regress/expected/vacuum.out | 18 ++++++++++ 17 files changed, 62 insertions(+), 106 deletions(-) diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index 20c6f9939f..30a893230e 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -190,10 +190,7 @@ ANALYZE [ VERBOSE ] [ table_and_columnsANALYZE can only be performed by superusers and roles - with privileges of pg_maintain.) If a role has - permission to ANALYZE a partitioned table, it is also - permitted to ANALYZE each of its partitions, regardless - of whether the role has the aforementioned privileges on the partition. + with privileges of pg_maintain.) ANALYZE will skip over any tables that the calling user does not have permission to analyze. diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml index 29f0f1fd90..f0dd7faed5 100644 --- a/doc/src/sgml/ref/cluster.sgml +++ b/doc/src/sgml/ref/cluster.sgml @@ -137,10 +137,7 @@ CLUSTER [VERBOSE] on the table or be the table's owner, a superuser, or a role with privileges of the pg_maintain - role. If a role has permission to CLUSTER a partitioned - table, it is also permitted to CLUSTER each of its - partitions, regardless of whether the role has the aforementioned - privileges on the partition. CLUSTER will skip over any + role. CLUSTER will skip over any tables that the calling user does not have permission to cluster. diff --git a/doc/src/sgml/ref/lock.sgml b/doc/src/sgml/ref/lock.sgml index 5b3b2b793a..8524182211 100644 --- a/doc/src/sgml/ref/lock.sgml +++ b/doc/src/sgml/ref/lock.sgml @@ -177,10 +177,7 @@ LOCK [ TABLE ] [ ONLY ] name [ * ] MODE (or a less-conflicting mode as described in ) is permitted. If a user has SELECT privileges on the table, ACCESS SHARE - MODE is permitted. If a role has permission to lock a - partitioned table, it is also permitted to lock each of its partitions, - regardless of whether the role has the aforementioned privileges on the - partition. + MODE is permitted. diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 71455dfdc7..23f8c7630b 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -306,11 +306,7 @@ REINDEX [ ( option [, ...] ) ] { DA indexes on shared catalogs will be skipped unless the user owns the catalog (which typically won't be the case), has privileges of the pg_maintain role, or has the MAINTAIN - privilege on the catalog. If a role has permission to - REINDEX a partitioned table, it is also permitted to - REINDEX each of its partitions, regardless of whether the - role has the aforementioned privileges on the partition. Of course, - superusers can always reindex anything. + privilege on the catalog. Of course, superusers can always reindex anything. diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 57bc4c23ec..445325e14c 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -452,10 +452,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ relname))); /* Check permissions */ - if (pg_class_aclcheck(relId, GetUserId(), ACL_MAINTAIN) != ACLCHECK_OK && - !has_partition_ancestor_privs(relId, GetUserId(), ACL_MAINTAIN)) - aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_TABLE, - relation->relname); -} - -/* - * If relid is a partition, returns whether userid has any of the privileges - * specified in acl on any of its ancestors. Otherwise, returns false. - */ -bool -has_partition_ancestor_privs(Oid relid, Oid userid, AclMode acl) -{ - List *ancestors; - ListCell *lc; - - if (!get_rel_relispartition(relid)) - return false; - - ancestors = get_partition_ancestors(relid); - foreach(lc, ancestors) - { - Oid ancestor = lfirst_oid(lc); - - if (OidIsValid(ancestor) && - pg_class_aclcheck(ancestor, userid, acl) == ACLCHECK_OK) - return true; - } - - return false; + aclresult = pg_class_aclcheck(relId, GetUserId(), ACL_MAINTAIN); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_TABLE, relation->relname); } /* diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index bb79de4da6..e7a0450cc4 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -41,7 +41,6 @@ #include "catalog/pg_namespace.h" #include "commands/cluster.h" #include "commands/defrem.h" -#include "commands/tablecmds.h" #include "commands/vacuum.h" #include "miscadmin.h" #include "nodes/makefuncs.h" @@ -721,17 +720,12 @@ vacuum_is_permitted_for_relation(Oid relid, Form_pg_class reltuple, /*---------- * A role has privileges to vacuum or analyze the relation if any of the * following are true: - * - the role is a superuser - * - the role owns the relation * - the role owns the current database and the relation is not shared * - the role has been granted the MAINTAIN privilege on the relation - * - the role has privileges to vacuum/analyze any of the relation's - * partition ancestors *---------- */ if ((object_ownercheck(DatabaseRelationId, MyDatabaseId, GetUserId()) && !reltuple->relisshared) || - pg_class_aclcheck(relid, GetUserId(), ACL_MAINTAIN) == ACLCHECK_OK || - has_partition_ancestor_privs(relid, GetUserId(), ACL_MAINTAIN)) + pg_class_aclcheck(relid, GetUserId(), ACL_MAINTAIN) == ACLCHECK_OK) return true; relname = NameStr(reltuple->relname); diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h index 17b9404937..250d89ff88 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -99,7 +99,6 @@ extern void AtEOSubXact_on_commit_actions(bool isCommit, extern void RangeVarCallbackMaintainsTable(const RangeVar *relation, Oid relId, Oid oldRelId, void *arg); -extern bool has_partition_ancestor_privs(Oid relid, Oid userid, AclMode acl); extern void RangeVarCallbackOwnsRelation(const RangeVar *relation, Oid relId, Oid oldRelId, void *arg); diff --git a/src/test/isolation/expected/cluster-conflict-partition.out b/src/test/isolation/expected/cluster-conflict-partition.out index 8d21276996..7be9e56ef1 100644 --- a/src/test/isolation/expected/cluster-conflict-partition.out +++ b/src/test/isolation/expected/cluster-conflict-partition.out @@ -3,7 +3,7 @@ Parsed test spec with 2 sessions starting permutation: s1_begin s1_lock_parent s2_auth s2_cluster s1_commit s2_reset step s1_begin: BEGIN; step s1_lock_parent: LOCK cluster_part_tab IN SHARE UPDATE EXCLUSIVE MODE; -step s2_auth: SET ROLE regress_cluster_part; +step s2_auth: SET ROLE regress_cluster_part; SET client_min_messages = ERROR; step s2_cluster: CLUSTER cluster_part_tab USING cluster_part_ind; step s1_commit: COMMIT; step s2_cluster: <... completed> @@ -11,7 +11,7 @@ step s2_reset: RESET ROLE; starting permutation: s1_begin s2_auth s1_lock_parent s2_cluster s1_commit s2_reset step s1_begin: BEGIN; -step s2_auth: SET ROLE regress_cluster_part; +step s2_auth: SET ROLE regress_cluster_part; SET client_min_messages = ERROR; step s1_lock_parent: LOCK cluster_part_tab IN SHARE UPDATE EXCLUSIVE MODE; step s2_cluster: CLUSTER cluster_part_tab USING cluster_part_ind; step s1_commit: COMMIT; @@ -21,17 +21,15 @@ step s2_reset: RESET ROLE; starting permutation: s1_begin s1_lock_child s2_auth s2_cluster s1_commit s2_reset step s1_begin: BEGIN; step s1_lock_child: LOCK cluster_part_tab1 IN SHARE UPDATE EXCLUSIVE MODE; -step s2_auth: SET ROLE regress_cluster_part; -step s2_cluster: CLUSTER cluster_part_tab USING cluster_part_ind; +step s2_auth: SET ROLE regress_cluster_part; SET client_min_messages = ERROR; +step s2_cluster: CLUSTER cluster_part_tab USING cluster_part_ind; step s1_commit: COMMIT; -step s2_cluster: <... completed> step s2_reset: RESET ROLE; starting permutation: s1_begin s2_auth s1_lock_child s2_cluster s1_commit s2_reset step s1_begin: BEGIN; -step s2_auth: SET ROLE regress_cluster_part; +step s2_auth: SET ROLE regress_cluster_part; SET client_min_messages = ERROR; step s1_lock_child: LOCK cluster_part_tab1 IN SHARE UPDATE EXCLUSIVE MODE; -step s2_cluster: CLUSTER cluster_part_tab USING cluster_part_ind; +step s2_cluster: CLUSTER cluster_part_tab USING cluster_part_ind; step s1_commit: COMMIT; -step s2_cluster: <... completed> step s2_reset: RESET ROLE; diff --git a/src/test/isolation/specs/cluster-conflict-partition.spec b/src/test/isolation/specs/cluster-conflict-partition.spec index ae38cb4ee3..4d38a7f49a 100644 --- a/src/test/isolation/specs/cluster-conflict-partition.spec +++ b/src/test/isolation/specs/cluster-conflict-partition.spec @@ -23,12 +23,15 @@ step s1_lock_child { LOCK cluster_part_tab1 IN SHARE UPDATE EXCLUSIVE MODE; step s1_commit { COMMIT; } session s2 -step s2_auth { SET ROLE regress_cluster_part; } +step s2_auth { SET ROLE regress_cluster_part; SET client_min_messages = ERROR; } step s2_cluster { CLUSTER cluster_part_tab USING cluster_part_ind; } step s2_reset { RESET ROLE; } -# CLUSTER waits if locked, passes for all cases. +# CLUSTER on the parent waits if locked, passes for all cases. permutation s1_begin s1_lock_parent s2_auth s2_cluster s1_commit s2_reset permutation s1_begin s2_auth s1_lock_parent s2_cluster s1_commit s2_reset + +# When taking a lock on a partition leaf, CLUSTER on the parent skips +# the leaf, passes for all cases. permutation s1_begin s1_lock_child s2_auth s2_cluster s1_commit s2_reset permutation s1_begin s2_auth s1_lock_child s2_cluster s1_commit s2_reset diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out index 2eec483eaa..27a5dff5d4 100644 --- a/src/test/regress/expected/cluster.out +++ b/src/test/regress/expected/cluster.out @@ -508,6 +508,7 @@ CREATE TEMP TABLE ptnowner_oldnodes AS JOIN pg_class AS c ON c.oid=tree.relid; SET SESSION AUTHORIZATION regress_ptnowner; CLUSTER ptnowner USING ptnowner_i_idx; +WARNING: permission denied to cluster "ptnowner2", skipping it RESET SESSION AUTHORIZATION; SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C"; @@ -515,7 +516,7 @@ SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a -----------+---------- ptnowner | t ptnowner1 | f - ptnowner2 | f + ptnowner2 | t (3 rows) DROP TABLE ptnowner; diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index acfd9d1f4f..1473bc3175 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2831,9 +2831,9 @@ RESET ROLE; GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser; SET SESSION ROLE regress_reindexuser; REINDEX TABLE pg_toast.pg_toast_1260; -ERROR: must be owner of table pg_toast_1260 +ERROR: permission denied for table pg_toast_1260 REINDEX INDEX pg_toast.pg_toast_1260_index; -ERROR: must be owner of index pg_toast_1260_index +ERROR: permission denied for index pg_toast_1260_index -- Clean up RESET ROLE; REVOKE USAGE ON SCHEMA pg_toast FROM regress_reindexuser; diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 3cf4ac8c9e..3e4dfcc2ec 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -2928,13 +2928,13 @@ WARNING: permission denied to analyze "maintain_test", skipping it VACUUM (ANALYZE) maintain_test; WARNING: permission denied to vacuum "maintain_test", skipping it CLUSTER maintain_test USING maintain_test_a_idx; -ERROR: must be owner of table maintain_test +ERROR: permission denied for table maintain_test REFRESH MATERIALIZED VIEW refresh_test; -ERROR: must be owner of table refresh_test +ERROR: permission denied for table refresh_test REINDEX TABLE maintain_test; -ERROR: must be owner of table maintain_test +ERROR: permission denied for table maintain_test REINDEX INDEX maintain_test_a_idx; -ERROR: must be owner of index maintain_test_a_idx +ERROR: permission denied for index maintain_test_a_idx REINDEX SCHEMA reindex_test; ERROR: must be owner of schema reindex_test RESET ROLE; diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index 41e020cf20..4def90b805 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -442,14 +442,20 @@ ALTER TABLE vacowned_parted OWNER TO regress_vacuum; ALTER TABLE vacowned_part1 OWNER TO regress_vacuum; SET ROLE regress_vacuum; VACUUM vacowned_parted; +WARNING: permission denied to vacuum "vacowned_part2", skipping it VACUUM vacowned_part1; VACUUM vacowned_part2; +WARNING: permission denied to vacuum "vacowned_part2", skipping it ANALYZE vacowned_parted; +WARNING: permission denied to analyze "vacowned_part2", skipping it ANALYZE vacowned_part1; ANALYZE vacowned_part2; +WARNING: permission denied to analyze "vacowned_part2", skipping it VACUUM (ANALYZE) vacowned_parted; +WARNING: permission denied to vacuum "vacowned_part2", skipping it VACUUM (ANALYZE) vacowned_part1; VACUUM (ANALYZE) vacowned_part2; +WARNING: permission denied to vacuum "vacowned_part2", skipping it RESET ROLE; -- Only one partition owned by other user. ALTER TABLE vacowned_parted OWNER TO CURRENT_USER; @@ -478,14 +484,26 @@ ALTER TABLE vacowned_parted OWNER TO regress_vacuum; ALTER TABLE vacowned_part1 OWNER TO CURRENT_USER; SET ROLE regress_vacuum; VACUUM vacowned_parted; +WARNING: permission denied to vacuum "vacowned_part1", skipping it +WARNING: permission denied to vacuum "vacowned_part2", skipping it VACUUM vacowned_part1; +WARNING: permission denied to vacuum "vacowned_part1", skipping it VACUUM vacowned_part2; +WARNING: permission denied to vacuum "vacowned_part2", skipping it ANALYZE vacowned_parted; +WARNING: permission denied to analyze "vacowned_part1", skipping it +WARNING: permission denied to analyze "vacowned_part2", skipping it ANALYZE vacowned_part1; +WARNING: permission denied to analyze "vacowned_part1", skipping it ANALYZE vacowned_part2; +WARNING: permission denied to analyze "vacowned_part2", skipping it VACUUM (ANALYZE) vacowned_parted; +WARNING: permission denied to vacuum "vacowned_part1", skipping it +WARNING: permission denied to vacuum "vacowned_part2", skipping it VACUUM (ANALYZE) vacowned_part1; +WARNING: permission denied to vacuum "vacowned_part1", skipping it VACUUM (ANALYZE) vacowned_part2; +WARNING: permission denied to vacuum "vacowned_part2", skipping it RESET ROLE; DROP TABLE vacowned; DROP TABLE vacowned_parted; -- 2.25.1 --FL5UXtIhxfXey3p5 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v8-0002-simplify-privilege-related-documentation-for-main.patch" From 71361221853f7f8e731ccd84fc888fad56acbdbc Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Mon, 19 Jun 2023 14:07:26 -0700 Subject: [PATCH v8 2/2] simplify privilege-related documentation for maintenance commands --- doc/src/sgml/ref/analyze.sgml | 8 +----- doc/src/sgml/ref/cluster.sgml | 6 +---- doc/src/sgml/ref/lock.sgml | 6 ++--- .../sgml/ref/refresh_materialized_view.sgml | 6 ++--- doc/src/sgml/ref/reindex.sgml | 26 +++++++++---------- doc/src/sgml/ref/vacuum.sgml | 8 +----- doc/src/sgml/user-manag.sgml | 3 ++- 7 files changed, 22 insertions(+), 41 deletions(-) diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index 30a893230e..954491b5df 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -183,14 +183,8 @@ ANALYZE [ VERBOSE ] [ table_and_columns To analyze a table, one must ordinarily have the MAINTAIN - privilege on the table or be the table's owner, a superuser, or a role with - privileges of the - pg_maintain - role. However, database owners are allowed to + privilege on the table. However, database owners are allowed to analyze all tables in their databases, except shared catalogs. - (The restriction for shared catalogs means that a true database-wide - ANALYZE can only be performed by superusers and roles - with privileges of pg_maintain.) ANALYZE will skip over any tables that the calling user does not have permission to analyze. diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml index f0dd7faed5..06f3d269e6 100644 --- a/doc/src/sgml/ref/cluster.sgml +++ b/doc/src/sgml/ref/cluster.sgml @@ -134,11 +134,7 @@ CLUSTER [VERBOSE] To cluster a table, one must have the MAINTAIN privilege - on the table or be the table's owner, a superuser, or a role with - privileges of the - pg_maintain - role. CLUSTER will skip over any - tables that the calling user does not have permission to cluster. + on the table. diff --git a/doc/src/sgml/ref/lock.sgml b/doc/src/sgml/ref/lock.sgml index 8524182211..070855da18 100644 --- a/doc/src/sgml/ref/lock.sgml +++ b/doc/src/sgml/ref/lock.sgml @@ -166,10 +166,8 @@ LOCK [ TABLE ] [ ONLY ] name [ * ] To lock a table, the user must have the right privilege for the specified - lockmode, or be the table's - owner, a superuser, or a role with privileges of the pg_maintain - role. If the user has MAINTAIN, + lockmode. + If the user has MAINTAIN, UPDATE, DELETE, or TRUNCATE privileges on the table, any lockmode is permitted. If the user has diff --git a/doc/src/sgml/ref/refresh_materialized_view.sgml b/doc/src/sgml/ref/refresh_materialized_view.sgml index 4d79b6ae7f..19737668cd 100644 --- a/doc/src/sgml/ref/refresh_materialized_view.sgml +++ b/doc/src/sgml/ref/refresh_materialized_view.sgml @@ -31,10 +31,8 @@ REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name REFRESH MATERIALIZED VIEW completely replaces the - contents of a materialized view. To execute this command you must be the - owner of the materialized view, have privileges of the - pg_maintain - role, or have the MAINTAIN + contents of a materialized view. To execute this command you must have the + MAINTAIN privilege on the materialized view. The old contents are discarded. If WITH DATA is specified (or defaults) the backing query is executed to provide the new data, and the materialized view is left in a diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 23f8c7630b..bef3486843 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -292,21 +292,21 @@ REINDEX [ ( option [, ...] ) ] { DA - Reindexing a single index or table requires being the owner of that - index or table, having privileges of the - pg_maintain - role, or having the MAINTAIN privilege on the - table. Reindexing a schema or database requires being the + Reindexing a single index or table requires + having the MAINTAIN privilege on the + table. Note that while REINDEX on a partitioned index or + table requires having the MAINTAIN privilege on the + partitioned table, such commands skip the privilege checks when processing + the individual partitions. Reindexing a schema or database requires being the owner of that schema or database or having privileges of the - pg_maintain role. Note specifically that it's thus + pg_maintain + role. Note specifically that it's thus possible for non-superusers to rebuild indexes of tables owned by - other users. However, as a special exception, when - REINDEX DATABASE, REINDEX SCHEMA - or REINDEX SYSTEM is issued by a non-superuser, - indexes on shared catalogs will be skipped unless the user owns the - catalog (which typically won't be the case), has privileges of the - pg_maintain role, or has the MAINTAIN - privilege on the catalog. Of course, superusers can always reindex anything. + other users. However, as a special exception, + REINDEX DATABASE, REINDEX SCHEMA, + and REINDEX SYSTEM will skip indexes on shared catalogs + unless the user has the MAINTAIN privilege on the + catalog. diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 445325e14c..c42bbea9e2 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -445,14 +445,8 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ pg_maintain - role. However, database owners are allowed to + privilege on the table. However, database owners are allowed to vacuum all tables in their databases, except shared catalogs. - (The restriction for shared catalogs means that a true database-wide - VACUUM can only be performed by superusers and roles - with privileges of pg_maintain.) VACUUM will skip over any tables that the calling user does not have permission to vacuum. diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index b6c37ccef2..e1540dd481 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -692,7 +692,8 @@ DROP ROLE doomed_role; REFRESH MATERIALIZED VIEW, REINDEX, and LOCK TABLE on all - relations. + relations, as if having MAINTAIN rights on those + objects, even without having it explicitly. pg_use_reserved_connections -- 2.25.1 --FL5UXtIhxfXey3p5--