aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/ref/analyze.sgml10
-rw-r--r--doc/src/sgml/ref/vacuum.sgml10
-rw-r--r--doc/src/sgml/user-manag.sgml12
-rw-r--r--src/backend/catalog/aclchk.c20
-rw-r--r--src/include/catalog/catversion.h2
-rw-r--r--src/include/catalog/pg_authid.dat10
-rw-r--r--src/test/regress/expected/privileges.out25
-rw-r--r--src/test/regress/sql/privileges.sql24
8 files changed, 106 insertions, 7 deletions
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 400ea30cd0c..16c0b886fd0 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -148,12 +148,16 @@ 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 or have the <literal>ANALYZE</literal> privilege on the table.
+ To analyze a table, one must ordinarily have the <literal>ANALYZE</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
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> can only be performed by superusers and roles
+ with privileges of <literal>pg_analyze_all_tables</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/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 70c0d813460..9cd880ea34a 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -356,12 +356,16 @@ 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 or have the <literal>VACUUM</literal> privilege on the table.
+ To vacuum a table, one must ordinarily have the <literal>VACUUM</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
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> can only be performed by superusers and roles
+ with privileges of <literal>pg_vacuum_all_tables</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.sgml
index 601fff3e6b2..2bff4e47d07 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -635,6 +635,18 @@ DROP ROLE doomed_role;
the <link linkend="sql-checkpoint"><command>CHECKPOINT</command></link>
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>
+ </row>
</tbody>
</tgroup>
</table>
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;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-24 17:57:44 +0000