Provide non-superuser predefined roles for vacuum and analyze

This provides two new predefined roles: pg_vacuum_all_tables and
pg_analyze_all_tables. Roles which have been granted these roles can
perform vacuum or analyse respectively on any or all tables as if they
were a superuser. This removes the need to grant superuser privilege to
roles just so they can perform vacuum and/or analyze.

Nathan Bossart

Reviewed by: Bharath Rupireddy, Kyotaro Horiguchi, Stephen Frost, Robert
Haas, Mark Dilger, Tom Lane, Corey Huinker, David G. Johnston, Michael
Paquier.

Discussion: https://postgr.es/m/20220722203735.GB3996698@nathanxps13

5 files changed, 80 insertions, 1 deletions

diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 3b5ea3c137b..bd967eaa783 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -4202,6 +4202,26 @@ pg_class_aclmask_ext(Oid table_oid, Oid roleid, AclMode mask,
 		has_privs_of_role(roleid, ROLE_PG_WRITE_ALL_DATA))
 		result |= (mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE));
 
+	/*
+	 * Check if ACL_VACUUM 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.
+	 */
+	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;
+
 	return result;
 }
 
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 83f8fe46eba..edff4309042 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202211241
+#define CATALOG_VERSION_NO	202211281
 
 #endif
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 3343a69ddb0..2574e2906de 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -84,5 +84,15 @@
   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',
+  rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+  rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+  rolpassword => '_null_', rolvaliduntil => '_null_' },
 
 ]
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index a2d95721799..7933314fd30 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2854,6 +2854,9 @@ 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;
@@ -2884,8 +2887,30 @@ 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;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index dd65c3264e5..1bcaaba4eba 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1858,6 +1858,9 @@ 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;
@@ -1887,8 +1890,29 @@ 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;