Add new predefined role pg_maintenance, which can issue VACUUM,
ANALYZE, CHECKPOINT.
Patch attached.
Regards,
Jeff Davis
From 7777e615ad4b4a8c390a4b937dbb5721de5daad5 Mon Sep 17 00:00:00 2001
From: Jeff Davis <j...@j-davis.com>
Date: Sat, 23 Oct 2021 13:41:41 -0700
Subject: [PATCH] Predefined role pg_maintenance for VACUUM, ANALYZE,
CHECKPOINT.
---
doc/src/sgml/ref/analyze.sgml | 14 +++++++-------
doc/src/sgml/ref/checkpoint.sgml | 3 ++-
doc/src/sgml/ref/vacuum.sgml | 14 +++++++-------
src/backend/commands/vacuum.c | 12 +++++++-----
src/backend/tcop/utility.c | 5 +++--
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_authid.dat | 5 +++++
7 files changed, 32 insertions(+), 23 deletions(-)
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index c8fcebc1612..38e3d8916f0 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -148,13 +148,13 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
<title>Notes</title>
<para>
- To analyze a table, one must ordinarily be the table's owner or a
- superuser. 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 a superuser.)
- <command>ANALYZE</command> will skip over any tables that the calling user
- does not have permission to analyze.
+ To analyze a table, one must ordinarily be the table's owner, a superuser,
+ or a member of the <literal>pg_maintenance</literal> 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 a
+ superuser.) <command>ANALYZE</command> will skip over any tables that the
+ calling user does not have permission to analyze.
</para>
<para>
diff --git a/doc/src/sgml/ref/checkpoint.sgml b/doc/src/sgml/ref/checkpoint.sgml
index 2afee6d7b59..461ccd603e5 100644
--- a/doc/src/sgml/ref/checkpoint.sgml
+++ b/doc/src/sgml/ref/checkpoint.sgml
@@ -52,7 +52,8 @@ CHECKPOINT
</para>
<para>
- Only superusers can call <command>CHECKPOINT</command>.
+ Only superusers or members of <literal>pg_maintenance</literal> can call
+ <command>CHECKPOINT</command>.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 3df32b58ee6..72d5b56feb5 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -356,13 +356,13 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
<title>Notes</title>
<para>
- To vacuum a table, one must ordinarily be the table's owner or a
- superuser. 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 a superuser.)
- <command>VACUUM</command> will skip over any tables that the calling user
- does not have permission to vacuum.
+ To vacuum a table, one must ordinarily be the table's owner, a superuser,
+ or a member of the <literal>pg_maintenance</literal> 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 a
+ superuser.) <command>VACUUM</command> will skip over any tables that the
+ calling user does not have permission to vacuum.
</para>
<para>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 5c4bc15b441..d22726ac006 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -32,6 +32,7 @@
#include "access/transam.h"
#include "access/xact.h"
#include "catalog/namespace.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_namespace.h"
@@ -565,15 +566,16 @@ vacuum_is_relation_owner(Oid relid, Form_pg_class reltuple, bits32 options)
/*
* Check permissions.
*
- * We allow the user to vacuum or analyze a table if he is superuser, the
- * table owner, or the database owner (but in the latter case, only if
- * it's not a shared relation). pg_class_ownercheck includes the
- * superuser case.
+ * We allow the user to vacuum or analyze a table if he is a member of
+ * pg_maintenance, the table owner, or the database owner (but in the
+ * latter case, only if it's not a shared relation). pg_class_ownercheck
+ * includes the superuser case.
*
* Note we choose to treat permissions failure as a WARNING and keep
* trying to vacuum or analyze the rest of the DB --- is this appropriate?
*/
- if (pg_class_ownercheck(relid, GetUserId()) ||
+ if (has_privs_of_role(GetUserId(), ROLE_PG_MAINTENANCE) ||
+ pg_class_ownercheck(relid, GetUserId()) ||
(pg_database_ownercheck(MyDatabaseId, GetUserId()) && !reltuple->relisshared))
return true;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index bf085aa93b2..43607eb84af 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -24,6 +24,7 @@
#include "catalog/catalog.h"
#include "catalog/index.h"
#include "catalog/namespace.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_inherits.h"
#include "catalog/toasting.h"
#include "commands/alter.h"
@@ -939,10 +940,10 @@ standard_ProcessUtility(PlannedStmt *pstmt,
break;
case T_CheckPointStmt:
- if (!superuser())
+ if (!has_privs_of_role(GetUserId(), ROLE_PG_MAINTENANCE))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to do CHECKPOINT")));
+ errmsg("must be member of pg_maintenance to do CHECKPOINT")));
RequestCheckpoint(CHECKPOINT_IMMEDIATE | CHECKPOINT_WAIT |
(RecoveryInProgress() ? 0 : CHECKPOINT_FORCE));
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 3253b8751b1..1bc331a9a39 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202109101
+#define CATALOG_VERSION_NO 202110231
#endif
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 3da68016b61..886969dc646 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -79,5 +79,10 @@
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '4544', oid_symbol => 'ROLE_PG_MAINTENANCE',
+ rolname => 'pg_maintenance', rolsuper => 'f', rolinherit => 't',
+ rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+ rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+ rolpassword => '_null_', rolvaliduntil => '_null_' },
]
--
2.17.1
