From: Nathan Bossart <nathandbossart@gmail.com>
To: Jeff Davis <pgsql@j-davis.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX
Date: Mon, 12 Dec 2022 13:01:36 -0800
Message-ID: <20221212210136.GA449764@nathanxps13> (raw)
In-Reply-To: <20221212200427.GA448418@nathanxps13>
References: <20221208183707.GA55474@nathanxps13>
<210dc2cc663934f8b463e4effa9af86225e46ce4.camel@j-davis.com>
<20221210204109.GA197392@nathanxps13>
<20221212200427.GA448418@nathanxps13>
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 ANALYZE). Otherwise, I don't think there's anything
> tremendously different between v1 and v2 besides the fact that all the
> privileges are grouped together.
Here is a v3 of the patch that fixes a typo in the docs.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.comAttachments:
[text/x-diff] v3-0001-add-grantable-MAINTAIN-privilege.patch (61.2K, ../20221212210136.GA449764@nathanxps13/2-v3-0001-add-grantable-MAINTAIN-privilege.patch)
download | inline diff:
From bd290e4fa404169342b226d2b5673369d96b71b6 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandbossart@gmail.com>
Date: Wed, 7 Dec 2022 11:20:01 -0800
Subject: [PATCH v3 1/1] add grantable MAINTAIN privilege
---
doc/src/sgml/ddl.sgml | 41 ++---
doc/src/sgml/func.sgml | 3 +-
.../sgml/ref/alter_default_privileges.sgml | 4 +-
doc/src/sgml/ref/analyze.sgml | 9 +-
doc/src/sgml/ref/cluster.sgml | 8 +-
doc/src/sgml/ref/grant.sgml | 5 +-
.../sgml/ref/refresh_materialized_view.sgml | 5 +-
doc/src/sgml/ref/reindex.sgml | 13 +-
doc/src/sgml/ref/revoke.sgml | 2 +-
doc/src/sgml/ref/vacuum.sgml | 9 +-
doc/src/sgml/user-manag.sgml | 18 +-
src/backend/catalog/aclchk.c | 35 ++--
src/backend/commands/analyze.c | 2 +-
src/backend/commands/cluster.c | 15 +-
src/backend/commands/indexcmds.c | 35 ++--
src/backend/commands/tablecmds.c | 13 +-
src/backend/commands/vacuum.c | 15 +-
src/backend/parser/gram.y | 7 -
src/backend/utils/adt/acl.c | 22 +--
src/bin/pg_dump/dumputils.c | 3 +-
src/bin/pg_dump/t/002_pg_dump.pl | 2 +-
src/bin/psql/tab-complete.c | 5 +-
src/include/catalog/pg_authid.dat | 9 +-
src/include/nodes/parsenodes.h | 5 +-
src/include/utils/acl.h | 7 +-
src/test/regress/expected/create_index.out | 4 +-
src/test/regress/expected/dependency.out | 20 +--
src/test/regress/expected/privileges.out | 160 ++++++++----------
src/test/regress/expected/rowsecurity.out | 32 ++--
src/test/regress/expected/vacuum.out | 6 -
src/test/regress/sql/dependency.sql | 2 +-
src/test/regress/sql/privileges.sql | 102 +++++------
32 files changed, 276 insertions(+), 342 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgmlindex 38618de01c..b2baff2ce1 100644--- a/doc/src/sgml/ddl.sgml+++ b/doc/src/sgml/ddl.sgml@@ -1692,8 +1692,7 @@ ALTER TABLE products RENAME TO items;
<literal>TRUNCATE</literal>, <literal>REFERENCES</literal>, <literal>TRIGGER</literal>,
<literal>CREATE</literal>, <literal>CONNECT</literal>, <literal>TEMPORARY</literal>,
<literal>EXECUTE</literal>, <literal>USAGE</literal>, <literal>SET</literal>,
- <literal>ALTER SYSTEM</literal>, <literal>VACUUM</literal>, and- <literal>ANALYZE</literal>.+ <literal>ALTER SYSTEM</literal>, and <literal>MAINTAIN</literal>.
The privileges applicable to a particular
object vary depending on the object's type (table, function, etc.).
More detail about the meanings of these privileges appears below.
@@ -1985,19 +1984,12 @@ REVOKE ALL ON accounts FROM PUBLIC;
</varlistentry>
<varlistentry>
- <term><literal>VACUUM</literal></term>+ <term><literal>MAINTAIN</literal></term>
<listitem>
<para>
- Allows <command>VACUUM</command> on a relation.- </para>- </listitem>- </varlistentry>-- <varlistentry>- <term><literal>ANALYZE</literal></term>- <listitem>- <para>- Allows <command>ANALYZE</command> on a relation.+ Allows <command>VACUUM</command>, <command>ANALYZE</command>,+ <command>CLUSTER</command>, <command>REFRESH MATERIALIZED VIEW</command>+ and <command>REINDEX</command> on a relation.
</para>
</listitem>
</varlistentry>
@@ -2151,13 +2143,8 @@ REVOKE ALL ON accounts FROM PUBLIC;
<entry><literal>PARAMETER</literal></entry>
</row>
<row>
- <entry><literal>VACUUM</literal></entry>- <entry><literal>v</literal></entry>- <entry><literal>TABLE</literal></entry>- </row>- <row>- <entry><literal>ANALYZE</literal></entry>- <entry><literal>z</literal></entry>+ <entry><literal>MAINTAIN</literal></entry>+ <entry><literal>m</literal></entry>
<entry><literal>TABLE</literal></entry>
</row>
</tbody>
@@ -2250,7 +2237,7 @@ REVOKE ALL ON accounts FROM PUBLIC;
</row>
<row>
<entry><literal>TABLE</literal> (and table-like objects)</entry>
- <entry><literal>arwdDxtvz</literal></entry>+ <entry><literal>arwdDxtm</literal></entry>
<entry>none</entry>
<entry><literal>\dp</literal></entry>
</row>
@@ -2308,12 +2295,12 @@ GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
would show:
<programlisting>
=> \dp mytable
- Access privileges- Schema | Name | Type | Access privileges | Column privileges | Policies---------+---------+-------+-------------------------+-----------------------+----------- public | mytable | table | miriam=arwdDxtvz/miriam+| col1: +|- | | | =r/miriam +| miriam_rw=rw/miriam |- | | | admin=arw/miriam | |+ Access privileges+ Schema | Name | Type | Access privileges | Column privileges | Policies+--------+---------+-------+------------------------+-----------------------+----------+ public | mytable | table | miriam=arwdDxtm/miriam+| col1: +|+ | | | =r/miriam +| miriam_rw=rw/miriam |+ | | | admin=arw/miriam | |
(1 row)
</programlisting>
</para>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgmlindex ad31fdb737..1cd8b11334 100644--- a/doc/src/sgml/func.sgml+++ b/doc/src/sgml/func.sgml@@ -22995,8 +22995,7 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
are <literal>SELECT</literal>, <literal>INSERT</literal>,
<literal>UPDATE</literal>, <literal>DELETE</literal>,
<literal>TRUNCATE</literal>, <literal>REFERENCES</literal>,
- <literal>TRIGGER</literal>, <literal>VACUUM</literal> and- <literal>ANALYZE</literal>.+ <literal>TRIGGER</literal>, and <literal>MAINTAIN</literal>.
</para></entry>
</row>
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgmlindex 0da295daff..a33461fbc2 100644--- a/doc/src/sgml/ref/alter_default_privileges.sgml+++ b/doc/src/sgml/ref/alter_default_privileges.sgml@@ -28,7 +28,7 @@ ALTER DEFAULT PRIVILEGES
<phrase>where <replaceable class="parameter">abbreviated_grant_or_revoke</replaceable> is one of:</phrase>
-GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE }+GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -51,7 +51,7 @@ GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]
- { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE }+ { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgmlindex 16c0b886fd..a26834da4f 100644--- a/doc/src/sgml/ref/analyze.sgml+++ b/doc/src/sgml/ref/analyze.sgml@@ -148,16 +148,15 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
<title>Notes</title>
<para>
- To analyze a table, one must ordinarily have the <literal>ANALYZE</literal>+ To analyze a table, one must ordinarily 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_analyze_all_tables</literal></link>- role.- However, database owners are allowed to+ <link linkend="predefined-roles-table"><literal>pg_maintain</literal></link>+ role. 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
<command>ANALYZE</command> can only be performed by superusers and roles
- with privileges of <literal>pg_analyze_all_tables</literal>.)+ with privileges of <literal>pg_maintain</literal>.)
<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 c37f4236f1..145101e6a5 100644--- a/doc/src/sgml/ref/cluster.sgml+++ b/doc/src/sgml/ref/cluster.sgml@@ -69,9 +69,11 @@ 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 all such tables if called by a superuser. This- form of <command>CLUSTER</command> cannot be executed inside a transaction- block.+ 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+ executed inside a transaction block.
</para>
<para>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgmlindex c3c585be7e..c8ca2b1d64 100644--- a/doc/src/sgml/ref/grant.sgml+++ b/doc/src/sgml/ref/grant.sgml@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE }+GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
| ALL TABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
@@ -193,8 +193,7 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
<term><literal>USAGE</literal></term>
<term><literal>SET</literal></term>
<term><literal>ALTER SYSTEM</literal></term>
- <term><literal>VACUUM</literal></term>- <term><literal>ANALYZE</literal></term>+ <term><literal>MAINTAIN</literal></term>
<listitem>
<para>
Specific types of privileges, as defined in <xref linkend="ddl-priv"/>.
diff --git a/doc/src/sgml/ref/refresh_materialized_view.sgml b/doc/src/sgml/ref/refresh_materialized_view.sgmlindex 675d6090f3..4d79b6ae7f 100644--- a/doc/src/sgml/ref/refresh_materialized_view.sgml+++ b/doc/src/sgml/ref/refresh_materialized_view.sgml@@ -32,7 +32,10 @@ REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="parameter">name</
<para>
<command>REFRESH MATERIALIZED VIEW</command> completely replaces the
contents of a materialized view. To execute this command you must be the
- owner of the materialized view. The old contents are discarded. If+ owner of the materialized view, have privileges of the+ <link linkend="predefined-roles-table"><literal>pg_maintain</literal></link>+ role, or have the <literal>MAINTAIN</literal>+ privilege on the materialized view. The old contents are discarded. If
<literal>WITH DATA</literal> is specified (or defaults) the backing query
is executed to provide the new data, and the materialized view is left in a
scannable state. If <literal>WITH NO DATA</literal> is specified no new
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgmlindex fcbda88149..192513f34e 100644--- a/doc/src/sgml/ref/reindex.sgml+++ b/doc/src/sgml/ref/reindex.sgml@@ -293,15 +293,20 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { DA
<para>
Reindexing a single index or table requires being the owner of that
- index or table. Reindexing a schema or database requires being the- owner of that schema or database. Note specifically that it's thus+ index or table, having privileges of the+ <link linkend="predefined-roles-table"><literal>pg_maintain</literal></link>+ role, or having the <literal>MAINTAIN</literal> privilege on the+ table. Reindexing a schema or database requires being the+ owner of that schema or database or having privileges of the+ <literal>pg_maintain</literal> 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
<command>REINDEX DATABASE</command>, <command>REINDEX SCHEMA</command>
or <command>REINDEX SYSTEM</command> 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). Of course, superusers- can always reindex anything.+ 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.
</para>
<para>
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgmlindex e28d192fd3..8df492281a 100644--- a/doc/src/sgml/ref/revoke.sgml+++ b/doc/src/sgml/ref/revoke.sgml@@ -22,7 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
REVOKE [ GRANT OPTION FOR ]
- { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE }+ { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
| ALL TABLES IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgmlindex 9cd880ea34..e14ead8826 100644--- a/doc/src/sgml/ref/vacuum.sgml+++ b/doc/src/sgml/ref/vacuum.sgml@@ -356,16 +356,15 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
<title>Notes</title>
<para>
- To vacuum a table, one must ordinarily have the <literal>VACUUM</literal>+ To vacuum a table, one must ordinarily 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_vacuum_all_tables</literal></link>- role.- However, database owners are allowed to+ <link linkend="predefined-roles-table"><literal>pg_maintain</literal></link>+ role. 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
<command>VACUUM</command> can only be performed by superusers and roles
- with privileges of <literal>pg_vacuum_all_tables</literal>.)+ with privileges of <literal>pg_maintain</literal>.)
<command>VACUUM</command> will skip over any tables that the calling user
does not have permission to vacuum.
</para>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgmlindex 2bff4e47d0..fccaef9b56 100644--- a/doc/src/sgml/user-manag.sgml+++ b/doc/src/sgml/user-manag.sgml@@ -636,16 +636,14 @@ DROP ROLE doomed_role;
command.</entry>
</row>
<row>
- <entry>pg_vacuum_all_tables</entry>- <entry>Allow executing the- <link linkend="sql-vacuum"><command>VACUUM</command></link> command on- all tables.</entry>- </row>- <row>- <entry>pg_analyze_all_tables</entry>- <entry>Allow executing the- <link linkend="sql-analyze"><command>ANALYZE</command></link> command on- all tables.</entry>+ <entry>pg_maintain</entry>+ <entry>Allow executing+ <link linkend="sql-vacuum"><command>VACUUM</command></link>,+ <link linkend="sql-analyze"><command>ANALYZE</command></link>,+ <link linkend="sql-cluster"><command>CLUSTER</command></link>,+ <link linkend="sql-refreshmaterializedview"><command>REFRESH MATERIALIZED VIEW</command></link>,+ and <link linkend="sql-reindex"><command>REINDEX</command></link> on all+ relations.</entry>
</row>
</tbody>
</tgroup>
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.cindex bd967eaa78..84b0807284 100644--- a/src/backend/catalog/aclchk.c+++ b/src/backend/catalog/aclchk.c@@ -3420,10 +3420,8 @@ string_to_privilege(const char *privname)
return ACL_SET;
if (strcmp(privname, "alter system") == 0)
return ACL_ALTER_SYSTEM;
- if (strcmp(privname, "vacuum") == 0)- return ACL_VACUUM;- if (strcmp(privname, "analyze") == 0)- return ACL_ANALYZE;+ if (strcmp(privname, "maintain") == 0)+ return ACL_MAINTAIN;
if (strcmp(privname, "rule") == 0)
return 0; /* ignore old RULE privileges */
ereport(ERROR,
@@ -3465,10 +3463,8 @@ privilege_to_string(AclMode privilege)
return "SET";
case ACL_ALTER_SYSTEM:
return "ALTER SYSTEM";
- case ACL_VACUUM:- return "VACUUM";- case ACL_ANALYZE:- return "ANALYZE";+ case ACL_MAINTAIN:+ return "MAINTAIN";
default:
elog(ERROR, "unrecognized privilege: %d", (int) privilege);
}
@@ -4203,24 +4199,15 @@ pg_class_aclmask_ext(Oid table_oid, Oid roleid, AclMode mask,
result |= (mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE));
/*
- * Check if ACL_VACUUM is being checked and, if so, and not already set as+ * Check if ACL_MAINTAIN is being checked and, if so, and not already set as
* part of the result, then check if the user is a member of the
- * pg_vacuum_all_tables role, which allows VACUUM on all relations.+ * pg_maintain role, which allows VACUUM, ANALYZE, CLUSTER, REFRESH+ * MATERIALIZED VIEW, and REINDEX on all relations.
*/
- if (mask & ACL_VACUUM &&- !(result & ACL_VACUUM) &&- has_privs_of_role(roleid, ROLE_PG_VACUUM_ALL_TABLES))- result |= ACL_VACUUM;-- /*- * Check if ACL_ANALYZE is being checked and, if so, and not already set as- * part of the result, then check if the user is a member of the- * pg_analyze_all_tables role, which allows ANALYZE on all relations.- */- if (mask & ACL_ANALYZE &&- !(result & ACL_ANALYZE) &&- has_privs_of_role(roleid, ROLE_PG_ANALYZE_ALL_TABLES))- result |= ACL_ANALYZE;+ if (mask & ACL_MAINTAIN &&+ !(result & ACL_MAINTAIN) &&+ has_privs_of_role(roleid, ROLE_PG_MAINTAIN))+ result |= ACL_MAINTAIN;
return result;
}
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.cindex 38bccafa05..da1f0f043b 100644--- a/src/backend/commands/analyze.c+++ b/src/backend/commands/analyze.c@@ -167,7 +167,7 @@ analyze_rel(Oid relid, RangeVar *relation,
*/
if (!vacuum_is_permitted_for_relation(RelationGetRelid(onerel),
onerel->rd_rel,
- VACOPT_ANALYZE))+ params->options & VACOPT_ANALYZE))
{
relation_close(onerel, ShareUpdateExclusiveLock);
return;
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.cindex 07e091bb87..eec2bae43a 100644--- a/src/backend/commands/cluster.c+++ b/src/backend/commands/cluster.c@@ -364,8 +364,9 @@ cluster_rel(Oid tableOid, Oid indexOid, ClusterParams *params)
*/
if (recheck)
{
- /* Check that the user still owns the relation */- if (!object_ownercheck(RelationRelationId, tableOid, save_userid))+ /* 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)
{
relation_close(OldHeap, AccessExclusiveLock);
goto out;
@@ -1612,7 +1613,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
/*
- * Get a list of tables that the current user owns and+ * Get a list of tables that the current user has privileges on and
* have indisclustered set. Return the list in a List * of RelToCluster
* (stored in the specified memory context), each one giving the tableOid
* and the indexOid on which the table is already clustered.
@@ -1629,8 +1630,8 @@ get_tables_to_cluster(MemoryContext cluster_context)
List *rtcs = NIL;
/*
- * Get all indexes that have indisclustered set and are owned by- * appropriate user.+ * Get all indexes that have indisclustered set and that the current user+ * has the appropriate privileges for.
*/
indRelation = table_open(IndexRelationId, AccessShareLock);
ScanKeyInit(&entry,
@@ -1644,7 +1645,8 @@ get_tables_to_cluster(MemoryContext cluster_context)
index = (Form_pg_index) GETSTRUCT(indexTuple);
- if (!object_ownercheck(RelationRelationId, index->indrelid, GetUserId()))+ if (!object_ownercheck(RelationRelationId, index->indrelid, GetUserId()) &&+ pg_class_aclcheck(index->indrelid, GetUserId(), ACL_MAINTAIN) != ACLCHECK_OK)
continue;
/* Use a permanent memory context for the result list */
@@ -1694,6 +1696,7 @@ get_tables_to_cluster_partitioned(MemoryContext cluster_context, Oid indexOid)
/* 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;
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.cindex b5b860c3ab..e49a3fa408 100644--- a/src/backend/commands/indexcmds.c+++ b/src/backend/commands/indexcmds.c@@ -26,6 +26,7 @@
#include "catalog/index.h"
#include "catalog/indexing.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_database.h"
#include "catalog/pg_inherits.h"
@@ -2754,6 +2755,7 @@ RangeVarCallbackForReindexIndex(const RangeVar *relation,
char relkind;
struct ReindexIndexCallbackState *state = arg;
LOCKMODE table_lockmode;
+ Oid table_oid;
/*
* Lock level here should match table lock in reindex_index() for
@@ -2793,14 +2795,17 @@ RangeVarCallbackForReindexIndex(const RangeVar *relation,
errmsg("\"%s\" is not an index", relation->relname)));
/* Check permissions */
- if (!object_ownercheck(RelationRelationId, relId, GetUserId()))- aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_INDEX, relation->relname);+ table_oid = IndexGetRelation(relId, true);+ if (!object_ownercheck(RelationRelationId, relId, GetUserId()) &&+ OidIsValid(table_oid) &&+ pg_class_aclcheck(table_oid, GetUserId(), ACL_MAINTAIN) != ACLCHECK_OK)+ ereport(ERROR,+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),+ errmsg("permission denied for \"%s\"", relation->relname)));
/* Lock heap before index to avoid deadlock. */
if (relId != oldRelId)
{
- Oid table_oid = IndexGetRelation(relId, true);-
/*
* If the OID isn't valid, it means the index was concurrently
* dropped, which is not a problem for us; just return normally.
@@ -2917,7 +2922,8 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
{
objectOid = get_namespace_oid(objectName, false);
- if (!object_ownercheck(NamespaceRelationId, objectOid, GetUserId()))+ if (!object_ownercheck(NamespaceRelationId, objectOid, GetUserId()) &&+ !has_privs_of_role(GetUserId(), ROLE_PG_MAINTAIN))
aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_SCHEMA,
objectName);
}
@@ -2929,7 +2935,8 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("can only reindex the currently open database")));
- if (!object_ownercheck(DatabaseRelationId, objectOid, GetUserId()))+ if (!object_ownercheck(DatabaseRelationId, objectOid, GetUserId()) &&+ !has_privs_of_role(GetUserId(), ROLE_PG_MAINTAIN))
aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_DATABASE,
get_database_name(objectOid));
}
@@ -3001,15 +3008,17 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
continue;
/*
- * The table can be reindexed if the user is superuser, the table- * owner, or the database/schema owner (but in the latter case, only- * if it's not a shared relation). object_ownercheck includes the- * superuser case, and depending on objectKind we already know that- * the user has permission to run REINDEX on this database or schema- * per the permission checks at the beginning of this routine.+ * The table can be reindexed if the user has been granted MAINTAIN on+ * the table or the user is a superuser, the table owner, or the+ * database/schema owner (but in the latter case, only if it's not a+ * shared relation). object_ownercheck includes the superuser case,+ * and depending on objectKind we already know that the user has+ * permission to run REINDEX on this database or schema per the+ * permission checks at the beginning of this routine.
*/
if (classtuple->relisshared &&
- !object_ownercheck(RelationRelationId, relid, GetUserId()))+ !object_ownercheck(RelationRelationId, relid, GetUserId()) &&+ pg_class_aclcheck(relid, GetUserId(), ACL_MAINTAIN) != ACLCHECK_OK)
continue;
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 0b352a5fff..b4257154af 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -16889,9 +16889,9 @@ AtEOSubXact_on_commit_actions(bool isCommit, SubTransactionId mySubid,
* This is intended as a callback for RangeVarGetRelidExtended(). It allows
* the relation to be locked only if (1) it's a plain or partitioned table,
* materialized view, or TOAST table and (2) the current user is the owner (or
- * the superuser). This meets the permission-checking needs of CLUSTER,- * REINDEX TABLE, and REFRESH MATERIALIZED VIEW; we expose it here so that it- * can be used by all.+ * the superuser) or has been granted MAINTAIN. This meets the+ * permission-checking needs of CLUSTER, REINDEX TABLE, and REFRESH+ * MATERIALIZED VIEW; we expose it here so that it can be used by all.
*/
void
RangeVarCallbackOwnsTable(const RangeVar *relation,
@@ -16918,8 +16918,11 @@ RangeVarCallbackOwnsTable(const RangeVar *relation,
errmsg("\"%s\" is not a table or materialized view", relation->relname)));
/* Check permissions */
- if (!object_ownercheck(RelationRelationId, relId, GetUserId()))- aclcheck_error(ACLCHECK_NOT_OWNER, get_relkind_objtype(get_rel_relkind(relId)), relation->relname);+ if (!object_ownercheck(RelationRelationId, relId, GetUserId()) &&+ pg_class_aclcheck(relId, GetUserId(), ACL_MAINTAIN) != ACLCHECK_OK)+ ereport(ERROR,+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),+ errmsg("permission denied for \"%s\"", relation->relname)));
}
/*
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.cindex a6d5ed1f6b..0a98603ea5 100644--- a/src/backend/commands/vacuum.c+++ b/src/backend/commands/vacuum.c@@ -557,7 +557,6 @@ vacuum_is_permitted_for_relation(Oid relid, Form_pg_class reltuple,
bits32 options)
{
char *relname;
- AclMode mode = 0;
Assert((options & (VACOPT_VACUUM | VACOPT_ANALYZE)) != 0);
@@ -569,13 +568,9 @@ vacuum_is_permitted_for_relation(Oid relid, Form_pg_class reltuple,
* - the role owns the current database and the relation is not shared
* - the role has been granted privileges to vacuum/analyze the relation
*/
- if (options & VACOPT_VACUUM)- mode |= ACL_VACUUM;- if (options & VACOPT_ANALYZE)- mode |= ACL_ANALYZE;
if (object_ownercheck(RelationRelationId, relid, GetUserId()) ||
(object_ownercheck(DatabaseRelationId, MyDatabaseId, GetUserId()) && !reltuple->relisshared) ||
- pg_class_aclcheck(relid, GetUserId(), mode) == ACLCHECK_OK)+ pg_class_aclcheck(relid, GetUserId(), ACL_MAINTAIN) == ACLCHECK_OK)
return true;
relname = NameStr(reltuple->relname);
@@ -1800,9 +1795,7 @@ vac_truncate_clog(TransactionId frozenXID,
* be stale.
*
* Returns true if it's okay to proceed with a requested ANALYZE
- * operation on this table. Note that if vacuuming fails because the user- * does not have the required privileges, this function returns true since- * the user might have been granted privileges to ANALYZE the relation.+ * operation on this table.
*
* Doing one heap at a time incurs extra overhead, since we need to
* check that the heap exists again just before we vacuum it. The
@@ -1902,12 +1895,12 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
*/
if (!vacuum_is_permitted_for_relation(RelationGetRelid(rel),
rel->rd_rel,
- VACOPT_VACUUM))+ params->options & VACOPT_VACUUM))
{
relation_close(rel, lmode);
PopActiveSnapshot();
CommitTransactionCommand();
- return true; /* user might have the ANALYZE privilege */+ return false;
}
/*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.yindex adc3f8ced3..63b4baaed9 100644--- a/src/backend/parser/gram.y+++ b/src/backend/parser/gram.y@@ -7502,13 +7502,6 @@ privilege: SELECT opt_column_list
n->cols = NIL;
$$ = n;
}
- | analyze_keyword- {- AccessPriv *n = makeNode(AccessPriv);- n->priv_name = pstrdup("analyze");- n->cols = NIL;- $$ = n;- }
| ColId opt_column_list
{
AccessPriv *n = makeNode(AccessPriv);
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.cindex ed1b6a41cf..bba953cd6e 100644--- a/src/backend/utils/adt/acl.c+++ b/src/backend/utils/adt/acl.c@@ -321,11 +321,8 @@ aclparse(const char *s, AclItem *aip)
case ACL_ALTER_SYSTEM_CHR:
read = ACL_ALTER_SYSTEM;
break;
- case ACL_VACUUM_CHR:- read = ACL_VACUUM;- break;- case ACL_ANALYZE_CHR:- read = ACL_ANALYZE;+ case ACL_MAINTAIN_CHR:+ read = ACL_MAINTAIN;
break;
case 'R': /* ignore old RULE privileges */
read = 0;
@@ -1601,8 +1598,7 @@ makeaclitem(PG_FUNCTION_ARGS)
{"CONNECT", ACL_CONNECT},
{"SET", ACL_SET},
{"ALTER SYSTEM", ACL_ALTER_SYSTEM},
- {"VACUUM", ACL_VACUUM},- {"ANALYZE", ACL_ANALYZE},+ {"MAINTAIN", ACL_MAINTAIN},
{"RULE", 0}, /* ignore old RULE privileges */
{NULL, 0}
};
@@ -1711,10 +1707,8 @@ convert_aclright_to_string(int aclright)
return "SET";
case ACL_ALTER_SYSTEM:
return "ALTER SYSTEM";
- case ACL_VACUUM:- return "VACUUM";- case ACL_ANALYZE:- return "ANALYZE";+ case ACL_MAINTAIN:+ return "MAINTAIN";
default:
elog(ERROR, "unrecognized aclright: %d", aclright);
return NULL;
@@ -2024,10 +2018,8 @@ convert_table_priv_string(text *priv_type_text)
{"REFERENCES WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_REFERENCES)},
{"TRIGGER", ACL_TRIGGER},
{"TRIGGER WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_TRIGGER)},
- {"VACUUM", ACL_VACUUM},- {"VACUUM WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_VACUUM)},- {"ANALYZE", ACL_ANALYZE},- {"ANALYZE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_ANALYZE)},+ {"MAINTAIN", ACL_MAINTAIN},+ {"MAINTAIN WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_MAINTAIN)},
{"RULE", 0}, /* ignore old RULE privileges */
{"RULE WITH GRANT OPTION", 0},
{NULL, 0}
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.cindex 0e20c66bee..f45cc6cb73 100644--- a/src/bin/pg_dump/dumputils.c+++ b/src/bin/pg_dump/dumputils.c@@ -463,8 +463,7 @@ do { \
CONVERT_PRIV('d', "DELETE");
CONVERT_PRIV('t', "TRIGGER");
CONVERT_PRIV('D', "TRUNCATE");
- CONVERT_PRIV('v', "VACUUM");- CONVERT_PRIV('z', "ANALYZE");+ CONVERT_PRIV('m', "MAINTAIN");
}
}
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.plindex 4732ee2e4a..1c7fc728c2 100644--- a/src/bin/pg_dump/t/002_pg_dump.pl+++ b/src/bin/pg_dump/t/002_pg_dump.pl@@ -618,7 +618,7 @@ my %tests = (
\QREVOKE ALL ON TABLES FROM regress_dump_test_role;\E\n
\QALTER DEFAULT PRIVILEGES \E
\QFOR ROLE regress_dump_test_role \E
- \QGRANT INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,VACUUM,ANALYZE,UPDATE ON TABLES TO regress_dump_test_role;\E+ \QGRANT INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,MAINTAIN,UPDATE ON TABLES TO regress_dump_test_role;\E
/xm,
like => { %full_runs, section_post_data => 1, },
unlike => { no_privs => 1, },
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.cindex dd7d021619..2a3921937c 100644--- a/src/bin/psql/tab-complete.c+++ b/src/bin/psql/tab-complete.c@@ -1147,7 +1147,7 @@ static const SchemaQuery Query_for_trigger_of_table = {
#define Privilege_options_of_grant_and_revoke \
"SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER", \
"CREATE", "CONNECT", "TEMPORARY", "EXECUTE", "USAGE", "SET", "ALTER SYSTEM", \
-"VACUUM", "ANALYZE", "ALL"+"MAINTAIN", "ALL"
/*
* These object types were introduced later than our support cutoff of
@@ -3782,8 +3782,7 @@ psql_completion(const char *text, int start, int end)
if (HeadMatches("ALTER", "DEFAULT", "PRIVILEGES"))
COMPLETE_WITH("SELECT", "INSERT", "UPDATE",
"DELETE", "TRUNCATE", "REFERENCES", "TRIGGER",
- "CREATE", "EXECUTE", "USAGE", "VACUUM", "ANALYZE",- "ALL");+ "CREATE", "EXECUTE", "USAGE", "MAINTAIN", "ALL");
else if (TailMatches("GRANT"))
COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_roles,
Privilege_options_of_grant_and_revoke);
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.datindex 2574e2906d..11d62e82df 100644--- a/src/include/catalog/pg_authid.dat+++ b/src/include/catalog/pg_authid.dat@@ -84,13 +84,8 @@
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
rolpassword => '_null_', rolvaliduntil => '_null_' },
-{ oid => '4549', oid_symbol => 'ROLE_PG_VACUUM_ALL_TABLES',- rolname => 'pg_vacuum_all_tables', rolsuper => 'f', rolinherit => 't',- rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',- rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',- rolpassword => '_null_', rolvaliduntil => '_null_' },-{ oid => '4550', oid_symbol => 'ROLE_PG_ANALYZE_ALL_TABLES',- rolname => 'pg_analyze_all_tables', rolsuper => 'f', rolinherit => 't',+{ oid => '4549', oid_symbol => 'ROLE_PG_MAINTAIN',+ rolname => 'pg_maintain', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
rolpassword => '_null_', rolvaliduntil => '_null_' },
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.hindex bebb9620b2..34bc640ff2 100644--- a/src/include/nodes/parsenodes.h+++ b/src/include/nodes/parsenodes.h@@ -95,9 +95,8 @@ typedef uint64 AclMode; /* a bitmask of privilege bits */
#define ACL_CONNECT (1<<11) /* for databases */
#define ACL_SET (1<<12) /* for configuration parameters */
#define ACL_ALTER_SYSTEM (1<<13) /* for configuration parameters */
-#define ACL_VACUUM (1<<14) /* for relations */-#define ACL_ANALYZE (1<<15) /* for relations */-#define N_ACL_RIGHTS 16 /* 1 plus the last 1<<x */+#define ACL_MAINTAIN (1<<14) /* for relations */+#define N_ACL_RIGHTS 15 /* 1 plus the last 1<<x */
#define ACL_NO_RIGHTS 0
/* Currently, SELECT ... FOR [KEY] UPDATE/SHARE requires UPDATE privileges */
#define ACL_SELECT_FOR_UPDATE ACL_UPDATE
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.hindex e566ff0c73..69eb437376 100644--- a/src/include/utils/acl.h+++ b/src/include/utils/acl.h@@ -148,17 +148,16 @@ typedef struct ArrayType Acl;
#define ACL_CONNECT_CHR 'c'
#define ACL_SET_CHR 's'
#define ACL_ALTER_SYSTEM_CHR 'A'
-#define ACL_VACUUM_CHR 'v'-#define ACL_ANALYZE_CHR 'z'+#define ACL_MAINTAIN_CHR 'm'
/* string holding all privilege code chars, in order by bitmask position */
-#define ACL_ALL_RIGHTS_STR "arwdDxtXUCTcsAvz"+#define ACL_ALL_RIGHTS_STR "arwdDxtXUCTcsAm"
/*
* Bitmasks defining "all rights" for each supported object type
*/
#define ACL_ALL_RIGHTS_COLUMN (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_REFERENCES)
-#define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_TRUNCATE|ACL_REFERENCES|ACL_TRIGGER|ACL_VACUUM|ACL_ANALYZE)+#define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_TRUNCATE|ACL_REFERENCES|ACL_TRIGGER|ACL_MAINTAIN)
#define ACL_ALL_RIGHTS_SEQUENCE (ACL_USAGE|ACL_SELECT|ACL_UPDATE)
#define ACL_ALL_RIGHTS_DATABASE (ACL_CREATE|ACL_CREATE_TEMP|ACL_CONNECT)
#define ACL_ALL_RIGHTS_FDW (ACL_USAGE)
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.outindex 6cd57e3eaa..a6f6c9fbef 100644--- a/src/test/regress/expected/create_index.out+++ b/src/test/regress/expected/create_index.out@@ -2825,9 +2825,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 "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 "pg_toast_1260_index"
-- Clean up
RESET ROLE;
REVOKE USAGE ON SCHEMA pg_toast FROM regress_reindexuser;
diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.outindex 81d8376509..520035f6a0 100644--- a/src/test/regress/expected/dependency.out+++ b/src/test/regress/expected/dependency.out@@ -19,7 +19,7 @@ DETAIL: privileges for table deptest
REVOKE SELECT ON deptest FROM GROUP regress_dep_group;
DROP GROUP regress_dep_group;
-- can't drop the user if we revoke the privileges partially
-REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, VACUUM, ANALYZE ON deptest FROM regress_dep_user;+REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, MAINTAIN ON deptest FROM regress_dep_user;
DROP USER regress_dep_user;
ERROR: role "regress_dep_user" cannot be dropped because some objects depend on it
DETAIL: privileges for table deptest
@@ -63,21 +63,21 @@ CREATE TABLE deptest (a serial primary key, b text);
GRANT ALL ON deptest1 TO regress_dep_user2;
RESET SESSION AUTHORIZATION;
\z deptest1
- Access privileges- Schema | Name | Type | Access privileges | Column privileges | Policies ---------+----------+-------+--------------------------------------------------------+-------------------+----------- public | deptest1 | table | regress_dep_user0=arwdDxtvz/regress_dep_user0 +| | - | | | regress_dep_user1=a*r*w*d*D*x*t*v*z*/regress_dep_user0+| | - | | | regress_dep_user2=arwdDxtvz/regress_dep_user1 | | + Access privileges+ Schema | Name | Type | Access privileges | Column privileges | Policies +--------+----------+-------+------------------------------------------------------+-------------------+----------+ public | deptest1 | table | regress_dep_user0=arwdDxtm/regress_dep_user0 +| | + | | | regress_dep_user1=a*r*w*d*D*x*t*m*/regress_dep_user0+| | + | | | regress_dep_user2=arwdDxtm/regress_dep_user1 | |
(1 row)
DROP OWNED BY regress_dep_user1;
-- all grants revoked
\z deptest1
Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies ---------+----------+-------+-----------------------------------------------+-------------------+----------- public | deptest1 | table | regress_dep_user0=arwdDxtvz/regress_dep_user0 | | + Schema | Name | Type | Access privileges | Column privileges | Policies +--------+----------+-------+----------------------------------------------+-------------------+----------+ public | deptest1 | table | regress_dep_user0=arwdDxtm/regress_dep_user0 | |
(1 row)
-- table was dropped
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.outindex 7933314fd3..40343255f3 100644--- a/src/test/regress/expected/privileges.out+++ b/src/test/regress/expected/privileges.out@@ -2570,39 +2570,39 @@ grant select on dep_priv_test to regress_priv_user4 with grant option;
set session role regress_priv_user4;
grant select on dep_priv_test to regress_priv_user5;
\dp dep_priv_test
- Access privileges- Schema | Name | Type | Access privileges | Column privileges | Policies ---------+---------------+-------+-------------------------------------------------+-------------------+----------- public | dep_priv_test | table | regress_priv_user1=arwdDxtvz/regress_priv_user1+| | - | | | regress_priv_user2=r*/regress_priv_user1 +| | - | | | regress_priv_user3=r*/regress_priv_user1 +| | - | | | regress_priv_user4=r*/regress_priv_user2 +| | - | | | regress_priv_user4=r*/regress_priv_user3 +| | - | | | regress_priv_user5=r/regress_priv_user4 | | + Access privileges+ Schema | Name | Type | Access privileges | Column privileges | Policies +--------+---------------+-------+------------------------------------------------+-------------------+----------+ public | dep_priv_test | table | regress_priv_user1=arwdDxtm/regress_priv_user1+| | + | | | regress_priv_user2=r*/regress_priv_user1 +| | + | | | regress_priv_user3=r*/regress_priv_user1 +| | + | | | regress_priv_user4=r*/regress_priv_user2 +| | + | | | regress_priv_user4=r*/regress_priv_user3 +| | + | | | regress_priv_user5=r/regress_priv_user4 | |
(1 row)
set session role regress_priv_user2;
revoke select on dep_priv_test from regress_priv_user4 cascade;
\dp dep_priv_test
- Access privileges- Schema | Name | Type | Access privileges | Column privileges | Policies ---------+---------------+-------+-------------------------------------------------+-------------------+----------- public | dep_priv_test | table | regress_priv_user1=arwdDxtvz/regress_priv_user1+| | - | | | regress_priv_user2=r*/regress_priv_user1 +| | - | | | regress_priv_user3=r*/regress_priv_user1 +| | - | | | regress_priv_user4=r*/regress_priv_user3 +| | - | | | regress_priv_user5=r/regress_priv_user4 | | + Access privileges+ Schema | Name | Type | Access privileges | Column privileges | Policies +--------+---------------+-------+------------------------------------------------+-------------------+----------+ public | dep_priv_test | table | regress_priv_user1=arwdDxtm/regress_priv_user1+| | + | | | regress_priv_user2=r*/regress_priv_user1 +| | + | | | regress_priv_user3=r*/regress_priv_user1 +| | + | | | regress_priv_user4=r*/regress_priv_user3 +| | + | | | regress_priv_user5=r/regress_priv_user4 | |
(1 row)
set session role regress_priv_user3;
revoke select on dep_priv_test from regress_priv_user4 cascade;
\dp dep_priv_test
- Access privileges- Schema | Name | Type | Access privileges | Column privileges | Policies ---------+---------------+-------+-------------------------------------------------+-------------------+----------- public | dep_priv_test | table | regress_priv_user1=arwdDxtvz/regress_priv_user1+| | - | | | regress_priv_user2=r*/regress_priv_user1 +| | - | | | regress_priv_user3=r*/regress_priv_user1 | | + Access privileges+ Schema | Name | Type | Access privileges | Column privileges | Policies +--------+---------------+-------+------------------------------------------------+-------------------+----------+ public | dep_priv_test | table | regress_priv_user1=arwdDxtm/regress_priv_user1+| | + | | | regress_priv_user2=r*/regress_priv_user1 +| | + | | | regress_priv_user3=r*/regress_priv_user1 | |
(1 row)
set session role regress_priv_user1;
@@ -2849,68 +2849,58 @@ DROP SCHEMA regress_roleoption;
DROP ROLE regress_roleoption_protagonist;
DROP ROLE regress_roleoption_donor;
DROP ROLE regress_roleoption_recipient;
--- VACUUM and ANALYZE-CREATE ROLE regress_no_priv;-CREATE ROLE regress_only_vacuum;-CREATE ROLE regress_only_analyze;-CREATE ROLE regress_both;-CREATE ROLE regress_only_vacuum_all IN ROLE pg_vacuum_all_tables;-CREATE ROLE regress_only_analyze_all IN ROLE pg_analyze_all_tables;-CREATE ROLE regress_both_all IN ROLE pg_vacuum_all_tables, pg_analyze_all_tables;-CREATE TABLE vacanalyze_test (a INT);-GRANT VACUUM ON vacanalyze_test TO regress_only_vacuum, regress_both;-GRANT ANALYZE ON vacanalyze_test TO regress_only_analyze, regress_both;-SET ROLE regress_no_priv;-VACUUM vacanalyze_test;-WARNING: permission denied to vacuum "vacanalyze_test", skipping it-ANALYZE vacanalyze_test;-WARNING: permission denied to analyze "vacanalyze_test", skipping it-VACUUM (ANALYZE) vacanalyze_test;-WARNING: permission denied to vacuum "vacanalyze_test", skipping it+-- MAINTAIN+CREATE ROLE regress_no_maintain;+CREATE ROLE regress_maintain;+CREATE ROLE regress_maintain_all IN ROLE pg_maintain;+CREATE TABLE maintain_test (a INT);+CREATE INDEX ON maintain_test (a);+GRANT MAINTAIN ON maintain_test TO regress_maintain;+CREATE MATERIALIZED VIEW refresh_test AS SELECT 1;+GRANT MAINTAIN ON refresh_test TO regress_maintain;+CREATE SCHEMA reindex_test;+SET ROLE regress_no_maintain;+VACUUM maintain_test;+WARNING: permission denied to vacuum "maintain_test", skipping it+ANALYZE maintain_test;+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: permission denied for "maintain_test"+REFRESH MATERIALIZED VIEW refresh_test;+ERROR: permission denied for "refresh_test"+REINDEX TABLE maintain_test;+ERROR: permission denied for "maintain_test"+REINDEX INDEX maintain_test_a_idx;+ERROR: permission denied for "maintain_test_a_idx"+REINDEX SCHEMA reindex_test;+ERROR: must be owner of schema reindex_test
RESET ROLE;
-SET ROLE regress_only_vacuum;-VACUUM vacanalyze_test;-ANALYZE vacanalyze_test;-WARNING: permission denied to analyze "vacanalyze_test", skipping it-VACUUM (ANALYZE) vacanalyze_test;-WARNING: permission denied to analyze "vacanalyze_test", skipping it+SET ROLE regress_maintain;+VACUUM maintain_test;+ANALYZE maintain_test;+VACUUM (ANALYZE) maintain_test;+CLUSTER maintain_test USING maintain_test_a_idx;+REFRESH MATERIALIZED VIEW refresh_test;+REINDEX TABLE maintain_test;+REINDEX INDEX maintain_test_a_idx;+REINDEX SCHEMA reindex_test;+ERROR: must be owner of schema reindex_test
RESET ROLE;
-SET ROLE regress_only_analyze;-VACUUM vacanalyze_test;-WARNING: permission denied to vacuum "vacanalyze_test", skipping it-ANALYZE vacanalyze_test;-VACUUM (ANALYZE) vacanalyze_test;-WARNING: permission denied to vacuum "vacanalyze_test", skipping it+SET ROLE regress_maintain_all;+VACUUM maintain_test;+ANALYZE maintain_test;+VACUUM (ANALYZE) maintain_test;+CLUSTER maintain_test USING maintain_test_a_idx;+REFRESH MATERIALIZED VIEW refresh_test;+REINDEX TABLE maintain_test;+REINDEX INDEX maintain_test_a_idx;+REINDEX SCHEMA reindex_test;
RESET ROLE;
-SET ROLE regress_both;-VACUUM vacanalyze_test;-ANALYZE vacanalyze_test;-VACUUM (ANALYZE) vacanalyze_test;-RESET ROLE;-SET ROLE regress_only_vacuum_all;-VACUUM vacanalyze_test;-ANALYZE vacanalyze_test;-WARNING: permission denied to analyze "vacanalyze_test", skipping it-VACUUM (ANALYZE) vacanalyze_test;-WARNING: permission denied to analyze "vacanalyze_test", skipping it-RESET ROLE;-SET ROLE regress_only_analyze_all;-VACUUM vacanalyze_test;-WARNING: permission denied to vacuum "vacanalyze_test", skipping it-ANALYZE vacanalyze_test;-VACUUM (ANALYZE) vacanalyze_test;-WARNING: permission denied to vacuum "vacanalyze_test", skipping it-RESET ROLE;-SET ROLE regress_both_all;-VACUUM vacanalyze_test;-ANALYZE vacanalyze_test;-VACUUM (ANALYZE) vacanalyze_test;-RESET ROLE;-DROP TABLE vacanalyze_test;-DROP ROLE regress_no_priv;-DROP ROLE regress_only_vacuum;-DROP ROLE regress_only_analyze;-DROP ROLE regress_both;-DROP ROLE regress_only_vacuum_all;-DROP ROLE regress_only_analyze_all;-DROP ROLE regress_both_all;+DROP TABLE maintain_test;+DROP MATERIALIZED VIEW refresh_test;+DROP SCHEMA reindex_test;+DROP ROLE regress_no_maintain;+DROP ROLE regress_maintain;+DROP ROLE regress_maintain_all;diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.outindex 31509a0a6f..a415ad168c 100644--- a/src/test/regress/expected/rowsecurity.out+++ b/src/test/regress/expected/rowsecurity.out@@ -94,22 +94,22 @@ CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave
USING (cid <> 44);
\dp
Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies ---------------------+----------+-------+-----------------------------------------------+-------------------+--------------------------------------------- regress_rls_schema | category | table | regress_rls_alice=arwdDxtvz/regress_rls_alice+| | - | | | =arwdDxtvz/regress_rls_alice | | - regress_rls_schema | document | table | regress_rls_alice=arwdDxtvz/regress_rls_alice+| | p1: +- | | | =arwdDxtvz/regress_rls_alice | | (u): (dlevel <= ( SELECT uaccount.seclv +- | | | | | FROM uaccount +- | | | | | WHERE (uaccount.pguser = CURRENT_USER)))+- | | | | | p2r (RESTRICTIVE): +- | | | | | (u): ((cid <> 44) AND (cid < 50)) +- | | | | | to: regress_rls_dave +- | | | | | p1r (RESTRICTIVE): +- | | | | | (u): (cid <> 44) +- | | | | | to: regress_rls_dave- regress_rls_schema | uaccount | table | regress_rls_alice=arwdDxtvz/regress_rls_alice+| | - | | | =r/regress_rls_alice | | + Schema | Name | Type | Access privileges | Column privileges | Policies +--------------------+----------+-------+----------------------------------------------+-------------------+--------------------------------------------+ regress_rls_schema | category | table | regress_rls_alice=arwdDxtm/regress_rls_alice+| | + | | | =arwdDxtm/regress_rls_alice | | + regress_rls_schema | document | table | regress_rls_alice=arwdDxtm/regress_rls_alice+| | p1: ++ | | | =arwdDxtm/regress_rls_alice | | (u): (dlevel <= ( SELECT uaccount.seclv ++ | | | | | FROM uaccount ++ | | | | | WHERE (uaccount.pguser = CURRENT_USER)))++ | | | | | p2r (RESTRICTIVE): ++ | | | | | (u): ((cid <> 44) AND (cid < 50)) ++ | | | | | to: regress_rls_dave ++ | | | | | p1r (RESTRICTIVE): ++ | | | | | (u): (cid <> 44) ++ | | | | | to: regress_rls_dave+ regress_rls_schema | uaccount | table | regress_rls_alice=arwdDxtm/regress_rls_alice+| | + | | | =r/regress_rls_alice | |
(3 rows)
\d document
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.outindex e0fb21b36e..0035d158b7 100644--- a/src/test/regress/expected/vacuum.out+++ b/src/test/regress/expected/vacuum.out@@ -336,9 +336,7 @@ WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING: permission denied to vacuum "vacowned_parted", skipping it
WARNING: permission denied to vacuum "vacowned_part1", skipping it
-WARNING: permission denied to analyze "vacowned_part1", skipping it
WARNING: permission denied to vacuum "vacowned_part2", skipping it
-WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
WARNING: permission denied to vacuum "vacowned_part1", skipping it
VACUUM (ANALYZE) vacowned_part2;
@@ -360,7 +358,6 @@ 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
-WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
WARNING: permission denied to vacuum "vacowned_part2", skipping it
@@ -383,7 +380,6 @@ WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING: permission denied to vacuum "vacowned_parted", skipping it
WARNING: permission denied to vacuum "vacowned_part2", skipping it
-WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
WARNING: permission denied to vacuum "vacowned_part2", skipping it
@@ -408,9 +404,7 @@ 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 analyze "vacowned_part1", skipping it
WARNING: permission denied to vacuum "vacowned_part2", skipping it
-WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
WARNING: permission denied to vacuum "vacowned_part1", skipping it
VACUUM (ANALYZE) vacowned_part2;
diff --git a/src/test/regress/sql/dependency.sql b/src/test/regress/sql/dependency.sqlindex 99b905a938..8d74ed7122 100644--- a/src/test/regress/sql/dependency.sql+++ b/src/test/regress/sql/dependency.sql@@ -21,7 +21,7 @@ REVOKE SELECT ON deptest FROM GROUP regress_dep_group;
DROP GROUP regress_dep_group;
-- can't drop the user if we revoke the privileges partially
-REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, VACUUM, ANALYZE ON deptest FROM regress_dep_user;+REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, MAINTAIN ON deptest FROM regress_dep_user;
DROP USER regress_dep_user;
-- now we are OK to drop him
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sqlindex 1bcaaba4eb..a8f633c982 100644--- a/src/test/regress/sql/privileges.sql+++ b/src/test/regress/sql/privileges.sql@@ -1853,66 +1853,54 @@ DROP ROLE regress_roleoption_protagonist;
DROP ROLE regress_roleoption_donor;
DROP ROLE regress_roleoption_recipient;
--- VACUUM and ANALYZE-CREATE ROLE regress_no_priv;-CREATE ROLE regress_only_vacuum;-CREATE ROLE regress_only_analyze;-CREATE ROLE regress_both;-CREATE ROLE regress_only_vacuum_all IN ROLE pg_vacuum_all_tables;-CREATE ROLE regress_only_analyze_all IN ROLE pg_analyze_all_tables;-CREATE ROLE regress_both_all IN ROLE pg_vacuum_all_tables, pg_analyze_all_tables;--CREATE TABLE vacanalyze_test (a INT);-GRANT VACUUM ON vacanalyze_test TO regress_only_vacuum, regress_both;-GRANT ANALYZE ON vacanalyze_test TO regress_only_analyze, regress_both;--SET ROLE regress_no_priv;-VACUUM vacanalyze_test;-ANALYZE vacanalyze_test;-VACUUM (ANALYZE) vacanalyze_test;+-- MAINTAIN+CREATE ROLE regress_no_maintain;+CREATE ROLE regress_maintain;+CREATE ROLE regress_maintain_all IN ROLE pg_maintain;++CREATE TABLE maintain_test (a INT);+CREATE INDEX ON maintain_test (a);+GRANT MAINTAIN ON maintain_test TO regress_maintain;+CREATE MATERIALIZED VIEW refresh_test AS SELECT 1;+GRANT MAINTAIN ON refresh_test TO regress_maintain;+CREATE SCHEMA reindex_test;++SET ROLE regress_no_maintain;+VACUUM maintain_test;+ANALYZE maintain_test;+VACUUM (ANALYZE) maintain_test;+CLUSTER maintain_test USING maintain_test_a_idx;+REFRESH MATERIALIZED VIEW refresh_test;+REINDEX TABLE maintain_test;+REINDEX INDEX maintain_test_a_idx;+REINDEX SCHEMA reindex_test;
RESET ROLE;
-SET ROLE regress_only_vacuum;-VACUUM vacanalyze_test;-ANALYZE vacanalyze_test;-VACUUM (ANALYZE) vacanalyze_test;+SET ROLE regress_maintain;+VACUUM maintain_test;+ANALYZE maintain_test;+VACUUM (ANALYZE) maintain_test;+CLUSTER maintain_test USING maintain_test_a_idx;+REFRESH MATERIALIZED VIEW refresh_test;+REINDEX TABLE maintain_test;+REINDEX INDEX maintain_test_a_idx;+REINDEX SCHEMA reindex_test;
RESET ROLE;
-SET ROLE regress_only_analyze;-VACUUM vacanalyze_test;-ANALYZE vacanalyze_test;-VACUUM (ANALYZE) vacanalyze_test;+SET ROLE regress_maintain_all;+VACUUM maintain_test;+ANALYZE maintain_test;+VACUUM (ANALYZE) maintain_test;+CLUSTER maintain_test USING maintain_test_a_idx;+REFRESH MATERIALIZED VIEW refresh_test;+REINDEX TABLE maintain_test;+REINDEX INDEX maintain_test_a_idx;+REINDEX SCHEMA reindex_test;
RESET ROLE;
-SET ROLE regress_both;-VACUUM vacanalyze_test;-ANALYZE vacanalyze_test;-VACUUM (ANALYZE) vacanalyze_test;-RESET ROLE;--SET ROLE regress_only_vacuum_all;-VACUUM vacanalyze_test;-ANALYZE vacanalyze_test;-VACUUM (ANALYZE) vacanalyze_test;-RESET ROLE;--SET ROLE regress_only_analyze_all;-VACUUM vacanalyze_test;-ANALYZE vacanalyze_test;-VACUUM (ANALYZE) vacanalyze_test;-RESET ROLE;--SET ROLE regress_both_all;-VACUUM vacanalyze_test;-ANALYZE vacanalyze_test;-VACUUM (ANALYZE) vacanalyze_test;-RESET ROLE;--DROP TABLE vacanalyze_test;-DROP ROLE regress_no_priv;-DROP ROLE regress_only_vacuum;-DROP ROLE regress_only_analyze;-DROP ROLE regress_both;-DROP ROLE regress_only_vacuum_all;-DROP ROLE regress_only_analyze_all;-DROP ROLE regress_both_all;+DROP TABLE maintain_test;+DROP MATERIALIZED VIEW refresh_test;+DROP SCHEMA reindex_test;+DROP ROLE regress_no_maintain;+DROP ROLE regress_maintain;+DROP ROLE regress_maintain_all;--
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, pgsql@j-davis.com
Subject: Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX
In-Reply-To: <20221212210136.GA449764@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