From: Nathan Bossart <nathandbossart@gmail.com>
To: Ted Yu <yuzhihong@gmail.com>
Cc: Jeff Davis <pgsql@j-davis.com>
Cc: Pavel Luzanov <p.luzanov@postgrespro.ru>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX
Date: Mon, 9 Jan 2023 14:51:57 -0800
Message-ID: <20230109225157.GA1288965@nathanxps13> (raw)
In-Reply-To: <20230103234549.GA289060@nathanxps13>
References: <f746293762ae262e321f89153d653173ba759636.camel@j-davis.com>
<8f7172da-2b58-3bd0-97ae-5126e2a7970c@postgrespro.ru>
<20221214221140.GA1153@telsasoft.com>
<cf56dc1a36e5c15e19c4be634038029096d96dab.camel@j-davis.com>
<b41c0577-8e16-c0fc-0d98-f17155047354@postgrespro.ru>
<295e86c7aeafb8e2623f8eccdc846855bf2c7e0c.camel@j-davis.com>
<20221217060408.GA1256247@nathanxps13>
<CALte62zW+s+V3fDUxP2ZYk2f=DZsyZO-Aa_bbiA39=wrz=UHuQ@mail.gmail.com>
<20221218233018.GA1476904@nathanxps13>
<20230103234549.GA289060@nathanxps13>
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 with adjusted tests and
documentation.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.comAttachments:
[text/x-diff] v4-0001-fix-MAINTAIN-privs.patch (25.6K, ../20230109225157.GA1288965@nathanxps13/2-v4-0001-fix-MAINTAIN-privs.patch)
download | inline diff:
From e46033659ec3fd2bfeac7d816364b72eccb55410 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandbossart@gmail.com>
Date: Mon, 9 Jan 2023 14:29:24 -0800
Subject: [PATCH v4 1/1] fix MAINTAIN privs
---
doc/src/sgml/ref/analyze.sgml | 5 ++-
doc/src/sgml/ref/cluster.sgml | 20 +++++++--
doc/src/sgml/ref/lock.sgml | 5 ++-
doc/src/sgml/ref/reindex.sgml | 9 +++-
doc/src/sgml/ref/vacuum.sgml | 8 +++-
src/backend/catalog/partition.c | 22 ++++++++++
src/backend/catalog/toasting.c | 32 +++++++++++++++
src/backend/commands/cluster.c | 35 +++++++++++-----
src/backend/commands/indexcmds.c | 10 ++---
src/backend/commands/lockcmds.c | 9 ++++
src/backend/commands/tablecmds.c | 41 ++++++++++++++++++-
src/backend/commands/vacuum.c | 7 ++--
src/include/catalog/partition.h | 1 +
src/include/catalog/pg_class.h | 1 +
src/include/catalog/toasting.h | 1 +
src/include/commands/tablecmds.h | 1 +
.../expected/cluster-conflict-partition.out | 6 ++-
.../specs/cluster-conflict-partition.spec | 5 +--
src/test/regress/expected/cluster.out | 4 +-
src/test/regress/expected/vacuum.out | 18 --------
src/test/regress/sql/cluster.sql | 2 +
21 files changed, 189 insertions(+), 53 deletions(-)
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgmlindex a26834da4f..2f94e89cb0 100644--- a/doc/src/sgml/ref/analyze.sgml+++ b/doc/src/sgml/ref/analyze.sgml@@ -156,7 +156,10 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
analyze all tables in their databases, except shared catalogs.
(The restriction for shared catalogs means that a true database-wide
<command>ANALYZE</command> can only be performed by superusers and roles
- with privileges of <literal>pg_maintain</literal>.)+ with privileges of <literal>pg_maintain</literal>.) If a role has+ permission to <command>ANALYZE</command> a partitioned table, it is also+ permitted to <command>ANALYZE</command> each of its partitions, regardless+ of whether the role has the aforementioned privileges on the partition.
<command>ANALYZE</command> will skip over any tables that the calling user
does not have permission to analyze.
</para>
diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgmlindex 145101e6a5..5616293eef 100644--- a/doc/src/sgml/ref/cluster.sgml+++ b/doc/src/sgml/ref/cluster.sgml@@ -69,10 +69,7 @@ CLUSTER [VERBOSE]
<para>
<command>CLUSTER</command> without any parameter reclusters all the
previously-clustered tables in the current database that the calling user
- owns or has the <literal>MAINTAIN</literal> privilege for, or all such tables- if called by a superuser or a role with privileges of the- <link linkend="predefined-roles-table"><literal>pg_maintain</literal></link>- role. This form of <command>CLUSTER</command> cannot be+ has privileges for. This form of <command>CLUSTER</command> cannot be
executed inside a transaction block.
</para>
@@ -134,6 +131,21 @@ CLUSTER [VERBOSE]
<refsect1>
<title>Notes</title>
+ <para>+ To cluster a table, one must have the <literal>MAINTAIN</literal> privilege+ on the table or be the table's owner, a superuser, or a role with+ privileges of the+ <link linkend="predefined-roles-table"><literal>pg_maintain</literal></link>+ role. If a role has permission to <command>CLUSTER</command> a partitioned+ table, it is also permitted to <command>CLUSTER</command> each of its+ partitions, regardless of whether the role has the aforementioned+ privileges on the partition. Similarly, if a role has permission to+ <command>CLUSTER</command> the main relation of a <acronym>TOAST</acronym>+ table, it is also permitted to <command>CLUSTER</command> its+ <acronym>TOAST</acronym> table. <command>CLUSTER</command> will skip over+ any tables that the calling user does not have permission to cluster.+ </para>+
<para>
In cases where you are accessing single rows randomly
within a table, the actual order of the data in the
diff --git a/doc/src/sgml/ref/lock.sgml b/doc/src/sgml/ref/lock.sgmlindex d9c5bf9a1d..21a9f88c70 100644--- a/doc/src/sgml/ref/lock.sgml+++ b/doc/src/sgml/ref/lock.sgml@@ -176,7 +176,10 @@ LOCK [ TABLE ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ]
or <literal>TRUNCATE</literal> privileges on the target table. All other
forms of <command>LOCK</command> are allowed with
table-level <literal>UPDATE</literal>, <literal>DELETE</literal>,
- or <literal>TRUNCATE</literal> privileges.+ or <literal>TRUNCATE</literal> privileges. 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.
</para>
<para>
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgmlindex 192513f34e..aa95c8743e 100644--- a/doc/src/sgml/ref/reindex.sgml+++ b/doc/src/sgml/ref/reindex.sgml@@ -306,7 +306,14 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { 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
<literal>pg_maintain</literal> role, or has the <literal>MAINTAIN</literal>
- privilege on the catalog. Of course, superusers can always reindex anything.+ privilege on the catalog. If a role has permission to+ <command>REINDEX</command> a partitioned table, it is also permitted to+ <command>REINDEX</command> each of its partitions, regardless of whether the+ role has the aforementioned privileges on the partition. Similarly, if a+ role has permission to <command>REINDEX</command> the main relation of a+ <acronym>TOAST</acronym> table, it is also permitted to+ <command>REINDEX</command> its <acronym>TOAST</acronym> table. Of course,+ superusers can always reindex anything.
</para>
<para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgmlindex 8fa8421847..2e48757882 100644--- a/doc/src/sgml/ref/vacuum.sgml+++ b/doc/src/sgml/ref/vacuum.sgml@@ -401,7 +401,13 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
vacuum all tables in their databases, except shared catalogs.
(The restriction for shared catalogs means that a true database-wide
<command>VACUUM</command> can only be performed by superusers and roles
- with privileges of <literal>pg_maintain</literal>.)+ with privileges of <literal>pg_maintain</literal>.) If a role has+ permission to <command>VACUUM</command> a partitioned table, it is also+ permitted to <command>VACUUM</command> each of its partitions, regardless+ of whether the role has the aforementioned privileges on the partition.+ Similarly, if a role has permission to <command>VACUUM</command> the main+ relation of a <acronym>TOAST</acronym> table, it is also permitted to+ <command>VACUUM</command> its <acronym>TOAST</acronym> table.
<command>VACUUM</command> will skip over any tables that the calling user
does not have permission to vacuum.
</para>
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.cindex f8780ce57d..5d2af2d0e7 100644--- a/src/backend/catalog/partition.c+++ b/src/backend/catalog/partition.c@@ -119,6 +119,28 @@ get_partition_parent_worker(Relation inhRel, Oid relid, bool *detach_pending)
return result;
}
+/*+ * get_partition_root+ * Obtain the partitioned table of a given relation+ *+ * Note: Because this function assumes that the relation whose OID is passed as+ * an argument and each ancestor will have precisely one parent, it should only+ * be called when it is known that the relation is a partition.+ */+Oid+get_partition_root(Oid relid)+{+ List *ancestors = get_partition_ancestors(relid);+ Oid root = InvalidOid;++ if (list_length(ancestors) > 0)+ root = llast_oid(ancestors);++ list_free(ancestors);++ return root;+}+
/*
* get_partition_ancestors
* Obtain ancestors of given relation
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.cindex ab12b0b9de..36be5ab2e4 100644--- a/src/backend/catalog/toasting.c+++ b/src/backend/catalog/toasting.c@@ -14,6 +14,7 @@
*/
#include "postgres.h"
+#include "access/genam.h"
#include "access/heapam.h"
#include "access/toast_compression.h"
#include "access/xact.h"
@@ -32,6 +33,7 @@
#include "nodes/makefuncs.h"
#include "storage/lock.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/rel.h"
#include "utils/syscache.h"
@@ -413,3 +415,33 @@ needs_toast_table(Relation rel)
/* Otherwise, let the AM decide. */
return table_relation_needs_toast_table(rel);
}
++/*+ * Get the main relation for the given TOAST table.+ */+Oid+get_toast_parent(Oid relid)+{+ Relation rel;+ SysScanDesc scan;+ ScanKeyData key[1];+ Oid result = InvalidOid;+ HeapTuple tuple;++ rel = table_open(RelationRelationId, AccessShareLock);++ ScanKeyInit(&key[0],+ Anum_pg_class_reltoastrelid,+ BTEqualStrategyNumber, F_OIDEQ,+ ObjectIdGetDatum(relid));++ scan = systable_beginscan(rel, ClassToastRelidIndexId, true, NULL, 1, key);+ tuple = systable_getnext(scan);+ if (HeapTupleIsValid(tuple))+ result = ((Form_pg_class) GETSTRUCT(tuple))->oid;+ systable_endscan(scan);++ table_close(rel, AccessShareLock);++ return result;+}diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.cindex f11691aff7..0ef0edec63 100644--- a/src/backend/commands/cluster.c+++ b/src/backend/commands/cluster.c@@ -80,6 +80,7 @@ static void copy_table_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex,
static List *get_tables_to_cluster(MemoryContext cluster_context);
static List *get_tables_to_cluster_partitioned(MemoryContext cluster_context,
Oid indexOid);
+static bool cluster_is_permitted_for_relation(Oid relid, Oid userid);
/*---------------------------------------------------------------------------
@@ -366,8 +367,7 @@ cluster_rel(Oid tableOid, Oid indexOid, ClusterParams *params)
if (recheck)
{
/* Check that the user still has privileges for the relation */
- if (!object_ownercheck(RelationRelationId, tableOid, save_userid) &&- pg_class_aclcheck(tableOid, save_userid, ACL_MAINTAIN) != ACLCHECK_OK)+ if (!cluster_is_permitted_for_relation(tableOid, save_userid))
{
relation_close(OldHeap, AccessExclusiveLock);
goto out;
@@ -1645,8 +1645,7 @@ get_tables_to_cluster(MemoryContext cluster_context)
index = (Form_pg_index) GETSTRUCT(indexTuple);
- if (!object_ownercheck(RelationRelationId, index->indrelid, GetUserId()) &&- pg_class_aclcheck(index->indrelid, GetUserId(), ACL_MAINTAIN) != ACLCHECK_OK)+ if (!cluster_is_permitted_for_relation(index->indrelid, GetUserId()))
continue;
/* Use a permanent memory context for the result list */
@@ -1694,12 +1693,11 @@ get_tables_to_cluster_partitioned(MemoryContext cluster_context, Oid indexOid)
if (get_rel_relkind(indexrelid) != RELKIND_INDEX)
continue;
- /* Silently skip partitions which the user has no access to. */- if (!object_ownercheck(RelationRelationId, relid, GetUserId()) &&- pg_class_aclcheck(relid, GetUserId(), ACL_MAINTAIN) != ACLCHECK_OK &&- (!object_ownercheck(DatabaseRelationId, MyDatabaseId, GetUserId()) ||- IsSharedRelation(relid)))- continue;+ /*+ * We already checked that the user has privileges to CLUSTER the+ * partitioned table when we locked it earlier, so there's no need to+ * check the privileges again here.+ */
/* Use a permanent memory context for the result list */
old_context = MemoryContextSwitchTo(cluster_context);
@@ -1714,3 +1712,20 @@ get_tables_to_cluster_partitioned(MemoryContext cluster_context, Oid indexOid)
return rtcs;
}
++/*+ * Return whether userid has privileges to CLUSTER relid. If not, this+ * function emits a WARNING.+ */+static bool+cluster_is_permitted_for_relation(Oid relid, Oid userid)+{+ if (pg_class_aclcheck(relid, userid, ACL_MAINTAIN) == ACLCHECK_OK ||+ has_parent_privs(relid, userid, ACL_MAINTAIN))+ return true;++ ereport(WARNING,+ (errmsg("permission denied to cluster \"%s\", skipping it",+ get_rel_name(relid))));+ return false;+}diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.cindex 8afc006f89..31195ddb54 100644--- a/src/backend/commands/indexcmds.c+++ b/src/backend/commands/indexcmds.c@@ -2796,9 +2796,9 @@ RangeVarCallbackForReindexIndex(const RangeVar *relation,
/* Check permissions */
table_oid = IndexGetRelation(relId, true);
- if (!object_ownercheck(RelationRelationId, relId, GetUserId()) &&- OidIsValid(table_oid) &&- pg_class_aclcheck(table_oid, GetUserId(), ACL_MAINTAIN) != ACLCHECK_OK)+ if (OidIsValid(table_oid) &&+ pg_class_aclcheck(table_oid, GetUserId(), ACL_MAINTAIN) != ACLCHECK_OK &&+ !has_parent_privs(table_oid, GetUserId(), ACL_MAINTAIN))
aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_INDEX,
relation->relname);
@@ -3016,8 +3016,8 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
* permission checks at the beginning of this routine.
*/
if (classtuple->relisshared &&
- !object_ownercheck(RelationRelationId, relid, GetUserId()) &&- pg_class_aclcheck(relid, GetUserId(), ACL_MAINTAIN) != ACLCHECK_OK)+ pg_class_aclcheck(relid, GetUserId(), ACL_MAINTAIN) != ACLCHECK_OK &&+ !has_parent_privs(relid, GetUserId(), ACL_MAINTAIN))
continue;
/*
diff --git a/src/backend/commands/lockcmds.c b/src/backend/commands/lockcmds.cindex 99e68bff85..8ec13242ad 100644--- a/src/backend/commands/lockcmds.c+++ b/src/backend/commands/lockcmds.c@@ -19,6 +19,7 @@
#include "catalog/namespace.h"
#include "catalog/pg_inherits.h"
#include "commands/lockcmds.h"
+#include "commands/tablecmds.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
#include "parser/parse_clause.h"
@@ -305,5 +306,13 @@ LockTableAclCheck(Oid reloid, LOCKMODE lockmode, Oid userid)
aclresult = pg_class_aclcheck(reloid, userid, aclmask);
+ /*+ * If this is a partition or a TOAST table, check permissions of the parent+ * table if needed.+ */+ if (aclresult != ACLCHECK_OK &&+ has_parent_privs(reloid, userid, ACL_MAINTAIN))+ aclresult = ACLCHECK_OK;+
return aclresult;
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 1db3bd9e2e..59493b553a 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -16918,12 +16918,49 @@ RangeVarCallbackMaintainsTable(const RangeVar *relation,
errmsg("\"%s\" is not a table or materialized view", relation->relname)));
/* Check permissions */
- if (!object_ownercheck(RelationRelationId, relId, GetUserId()) &&- pg_class_aclcheck(relId, GetUserId(), ACL_MAINTAIN) != ACLCHECK_OK)+ if (pg_class_aclcheck(relId, GetUserId(), ACL_MAINTAIN) != ACLCHECK_OK &&+ !has_parent_privs(relId, GetUserId(), ACL_MAINTAIN))
aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_TABLE,
relation->relname);
}
+/*+ * If relid is a partition, return whether userid has any of the privileges+ * specified in acl on its partitioned table.+ *+ * If relid is a TOAST table, return whether userid has any of the privileges+ * specified in acl on its main relation.+ */+bool+has_parent_privs(Oid relid, Oid userid, AclMode acl)+{+ /*+ * If this is a partition, check the permissions on its partitioned table.+ */+ if (get_rel_relispartition(relid))+ {+ Oid root = get_partition_root(relid);++ if (OidIsValid(root) &&+ pg_class_aclcheck(root, userid, acl) == ACLCHECK_OK)+ return true;+ }++ /*+ * If this is a TOAST table, check the permissions on the main relation.+ */+ if (get_rel_relkind(relid) == RELKIND_TOASTVALUE)+ {+ Oid parent = get_toast_parent(relid);++ if (OidIsValid(parent) &&+ pg_class_aclcheck(parent, userid, acl) == ACLCHECK_OK)+ return true;+ }++ return false;+}+
/*
* Callback to RangeVarGetRelidExtended() for TRUNCATE processing.
*/
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.cindex c4ed7efce3..ceb4cbbaf7 100644--- a/src/backend/commands/vacuum.c+++ b/src/backend/commands/vacuum.c@@ -41,6 +41,7 @@
#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"
@@ -600,9 +601,9 @@ vacuum_is_permitted_for_relation(Oid relid, Form_pg_class reltuple,
* - the role has been granted the MAINTAIN privilege on the relation
*----------
*/
- if (object_ownercheck(RelationRelationId, relid, GetUserId()) ||- (object_ownercheck(DatabaseRelationId, MyDatabaseId, GetUserId()) && !reltuple->relisshared) ||- pg_class_aclcheck(relid, GetUserId(), ACL_MAINTAIN) == ACLCHECK_OK)+ if ((object_ownercheck(DatabaseRelationId, MyDatabaseId, GetUserId()) && !reltuple->relisshared) ||+ pg_class_aclcheck(relid, GetUserId(), ACL_MAINTAIN) == ACLCHECK_OK ||+ has_parent_privs(relid, GetUserId(), ACL_MAINTAIN))
return true;
relname = NameStr(reltuple->relname);
diff --git a/src/include/catalog/partition.h b/src/include/catalog/partition.hindex 79ce711802..abe6cf94b5 100644--- a/src/include/catalog/partition.h+++ b/src/include/catalog/partition.h@@ -20,6 +20,7 @@
#define HASH_PARTITION_SEED UINT64CONST(0x7A5B22367996DCFD)
extern Oid get_partition_parent(Oid relid, bool even_if_detached);
+extern Oid get_partition_root(Oid relid);
extern List *get_partition_ancestors(Oid relid);
extern Oid index_get_partition(Relation partition, Oid indexId);
extern List *map_partition_varattnos(List *expr, int fromrel_varno,
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.hindex 2d1bb7af3a..c57500e327 100644--- a/src/include/catalog/pg_class.h+++ b/src/include/catalog/pg_class.h@@ -155,6 +155,7 @@ typedef FormData_pg_class *Form_pg_class;
DECLARE_UNIQUE_INDEX_PKEY(pg_class_oid_index, 2662, ClassOidIndexId, on pg_class using btree(oid oid_ops));
DECLARE_UNIQUE_INDEX(pg_class_relname_nsp_index, 2663, ClassNameNspIndexId, on pg_class using btree(relname name_ops, relnamespace oid_ops));
DECLARE_INDEX(pg_class_tblspc_relfilenode_index, 3455, ClassTblspcRelfilenodeIndexId, on pg_class using btree(reltablespace oid_ops, relfilenode oid_ops));
+DECLARE_INDEX(pg_class_reltoastrelid_index, 2173, ClassToastRelidIndexId, on pg_class using btree(reltoastrelid oid_ops));
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/toasting.h b/src/include/catalog/toasting.hindex 5880ec6752..69f7fcc613 100644--- a/src/include/catalog/toasting.h+++ b/src/include/catalog/toasting.h@@ -26,5 +26,6 @@ extern void AlterTableCreateToastTable(Oid relOid, Datum reloptions,
LOCKMODE lockmode);
extern void BootstrapToastTable(char *relName,
Oid toastOid, Oid toastIndexOid);
+extern Oid get_toast_parent(Oid relid);
#endif /* TOASTING_H */
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.hindex 2e717fa815..eef7cc6cfe 100644--- a/src/include/commands/tablecmds.h+++ b/src/include/commands/tablecmds.h@@ -98,6 +98,7 @@ extern void AtEOSubXact_on_commit_actions(bool isCommit,
extern void RangeVarCallbackMaintainsTable(const RangeVar *relation,
Oid relId, Oid oldRelId,
void *arg);
+extern bool has_parent_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.outindex 7acb675c97..8d21276996 100644--- a/src/test/isolation/expected/cluster-conflict-partition.out+++ b/src/test/isolation/expected/cluster-conflict-partition.out@@ -22,14 +22,16 @@ starting permutation: s1_begin s1_lock_child s2_auth s2_cluster s1_commit s2_res
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_cluster: CLUSTER cluster_part_tab USING cluster_part_ind; <waiting ...>
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 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; <waiting ...>
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.specindex 5091f684a9..ae38cb4ee3 100644--- a/src/test/isolation/specs/cluster-conflict-partition.spec+++ b/src/test/isolation/specs/cluster-conflict-partition.spec@@ -27,11 +27,8 @@ step s2_auth { SET ROLE regress_cluster_part; }
step s2_cluster { CLUSTER cluster_part_tab USING cluster_part_ind; }
step s2_reset { RESET ROLE; }
-# CLUSTER on the parent waits if locked, passes for all cases.+# CLUSTER 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.outindex 542c2e098c..2eec483eaa 100644--- a/src/test/regress/expected/cluster.out+++ b/src/test/regress/expected/cluster.out@@ -352,7 +352,9 @@ INSERT INTO clstr_3 VALUES (1);
-- this user can only cluster clstr_1 and clstr_3, but the latter
-- has not been clustered
SET SESSION AUTHORIZATION regress_clstr_user;
+SET client_min_messages = ERROR; -- order of "skipping" warnings may vary
CLUSTER;
+RESET client_min_messages;
SELECT * FROM clstr_1 UNION ALL
SELECT * FROM clstr_2 UNION ALL
SELECT * FROM clstr_3;
@@ -513,7 +515,7 @@ SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a
-----------+----------
ptnowner | t
ptnowner1 | f
- ptnowner2 | t+ ptnowner2 | f
(3 rows)
DROP TABLE ptnowner;
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.outindex d860be0e20..458adee7f8 100644--- a/src/test/regress/expected/vacuum.out+++ b/src/test/regress/expected/vacuum.out@@ -353,20 +353,14 @@ 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;
@@ -395,26 +389,14 @@ 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;
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sqlindex 6cb9c926c0..a4cfaae807 100644--- a/src/test/regress/sql/cluster.sql+++ b/src/test/regress/sql/cluster.sql@@ -145,7 +145,9 @@ INSERT INTO clstr_3 VALUES (1);
-- this user can only cluster clstr_1 and clstr_3, but the latter
-- has not been clustered
SET SESSION AUTHORIZATION regress_clstr_user;
+SET client_min_messages = ERROR; -- order of "skipping" warnings may vary
CLUSTER;
+RESET client_min_messages;
SELECT * FROM clstr_1 UNION ALL
SELECT * FROM clstr_2 UNION ALL
SELECT * FROM clstr_3;
--
2.25.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, yuzhihong@gmail.com, pgsql@j-davis.com, p.luzanov@postgrespro.ru, pryzby@telsasoft.com
Subject: Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX
In-Reply-To: <20230109225157.GA1288965@nathanxps13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox