On Fri, Jul 22, 2022 at 01:37:35PM -0700, Nathan Bossart wrote: > The attached patch adds a pg_vacuum_analyze role that allows VACUUM and > ANALYZE commands on all relations. I started by trying to introduce > separate pg_vacuum and pg_analyze roles, but that quickly became > complicated because the VACUUM and ANALYZE code is intertwined. To > initiate the discussion, here's the simplest thing I could think of.
And here's the same patch, but with docs that actually build. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index b968f740cb..203b713a4e 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -148,11 +148,14 @@ 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 + To analyze a table, one must ordinarily be the table's owner, a superuser, or + a role with privileges of the + <link linkend="predefined-roles-table"><literal>pg_vacuum_analyze</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_vacuum_analyze</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 c582021d29..12d7b96fee 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -356,11 +356,14 @@ 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 + To vacuum a table, one must ordinarily be the table's owner, a superuser, or + a role with privileges of the + <link linkend="predefined-roles-table"><literal>pg_vacuum_analyze</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_analyze</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 6eaaaa36b8..6052bd0c4f 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -588,6 +588,13 @@ DROP ROLE doomed_role; the <link linkend="sql-checkpoint"><command>CHECKPOINT</command></link> command.</entry> </row> + <row> + <entry>pg_vacuum_analyze</entry> + <entry>Allow executing the + <link linkend="sql-vacuum"><command>VACUUM</command></link> and + <link linkend="sql-analyze"><command>ANALYZE</command></link> + commands on all tables.</entry> + </row> </tbody> </tgroup> </table> diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 8df25f59d8..b3eb41a8cc 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -36,6 +36,7 @@ #include "access/xact.h" #include "catalog/namespace.h" #include "catalog/index.h" +#include "catalog/pg_authid.h" #include "catalog/pg_database.h" #include "catalog/pg_inherits.h" #include "catalog/pg_namespace.h" @@ -574,7 +575,8 @@ vacuum_is_relation_owner(Oid relid, Form_pg_class reltuple, bits32 options) * trying to vacuum or analyze the rest of the DB --- is this appropriate? */ if (pg_class_ownercheck(relid, GetUserId()) || - (pg_database_ownercheck(MyDatabaseId, GetUserId()) && !reltuple->relisshared)) + (pg_database_ownercheck(MyDatabaseId, GetUserId()) && !reltuple->relisshared) || + has_privs_of_role(GetUserId(), ROLE_PG_VACUUM_ANALYZE)) return true; relname = NameStr(reltuple->relname); @@ -583,11 +585,14 @@ vacuum_is_relation_owner(Oid relid, Form_pg_class reltuple, bits32 options) { if (reltuple->relisshared) ereport(WARNING, - (errmsg("skipping \"%s\" --- only superuser can vacuum it", + (errmsg("skipping \"%s\" --- only superusers and roles with " + "privileges of pg_vacuum_analyze can vacuum it", relname))); else if (reltuple->relnamespace == PG_CATALOG_NAMESPACE) ereport(WARNING, - (errmsg("skipping \"%s\" --- only superuser or database owner can vacuum it", + (errmsg("skipping \"%s\" --- only superusers, roles with " + "privileges of pg_vacuum_analyze, or the database " + "owner can vacuum it", relname))); else ereport(WARNING, @@ -606,11 +611,14 @@ vacuum_is_relation_owner(Oid relid, Form_pg_class reltuple, bits32 options) { if (reltuple->relisshared) ereport(WARNING, - (errmsg("skipping \"%s\" --- only superuser can analyze it", + (errmsg("skipping \"%s\" --- only superusers and roles with " + "privileges of pg_vacuum_analyze can analyze it", relname))); else if (reltuple->relnamespace == PG_CATALOG_NAMESPACE) ereport(WARNING, - (errmsg("skipping \"%s\" --- only superuser or database owner can analyze it", + (errmsg("skipping \"%s\" --- only superusers, roles with " + "privileges of pg_vacuum_analyze, or the database " + "owner can analyze it", relname))); else ereport(WARNING, diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat index 3343a69ddb..f067fe1c57 100644 --- a/src/include/catalog/pg_authid.dat +++ b/src/include/catalog/pg_authid.dat @@ -84,5 +84,10 @@ rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', rolpassword => '_null_', rolvaliduntil => '_null_' }, +{ oid => '4549', oid_symbol => 'ROLE_PG_VACUUM_ANALYZE', + rolname => 'pg_vacuum_analyze', 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/vacuum.out b/src/test/regress/expected/vacuum.out index c63a157e5f..859be4c13e 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -302,18 +302,18 @@ VACUUM (ANALYZE) vacowned; WARNING: skipping "vacowned" --- only table or database owner can vacuum it -- Catalog VACUUM pg_catalog.pg_class; -WARNING: skipping "pg_class" --- only superuser or database owner can vacuum it +WARNING: skipping "pg_class" --- only superusers, roles with privileges of pg_vacuum_analyze, or the database owner can vacuum it ANALYZE pg_catalog.pg_class; -WARNING: skipping "pg_class" --- only superuser or database owner can analyze it +WARNING: skipping "pg_class" --- only superusers, roles with privileges of pg_vacuum_analyze, or the database owner can analyze it VACUUM (ANALYZE) pg_catalog.pg_class; -WARNING: skipping "pg_class" --- only superuser or database owner can vacuum it +WARNING: skipping "pg_class" --- only superusers, roles with privileges of pg_vacuum_analyze, or the database owner can vacuum it -- Shared catalog VACUUM pg_catalog.pg_authid; -WARNING: skipping "pg_authid" --- only superuser can vacuum it +WARNING: skipping "pg_authid" --- only superusers and roles with privileges of pg_vacuum_analyze can vacuum it ANALYZE pg_catalog.pg_authid; -WARNING: skipping "pg_authid" --- only superuser can analyze it +WARNING: skipping "pg_authid" --- only superusers and roles with privileges of pg_vacuum_analyze can analyze it VACUUM (ANALYZE) pg_catalog.pg_authid; -WARNING: skipping "pg_authid" --- only superuser can vacuum it +WARNING: skipping "pg_authid" --- only superusers and roles with privileges of pg_vacuum_analyze can vacuum it -- Partitioned table and its partitions, nothing owned by other user. -- Relations are not listed in a single command to test ownership -- independently.