Hi hackers,
The previous attempt to add a predefined role for VACUUM and ANALYZE [0]
resulted in the new pg_checkpoint role in v15. I'd like to try again to
add a new role (or multiple new roles) for VACUUM and ANALYZE.
The primary motivation for this is to continue chipping away at things that
require special privileges or even superuser. VACUUM and ANALYZE typically
require table ownership, database ownership, or superuser. And only
superusers can VACUUM/ANALYZE shared catalogs. A predefined role for these
operations would allow delegating such tasks (e.g., a nightly VACUUM
scheduled with pg_cron) to a role with fewer privileges.
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.
An alternate approach might be to allow using GRANT to manage these
privileges, as suggested in the previous thread [1].
Thoughts?
[0]
https://postgr.es/m/67a1d667e8ec228b5e07f232184c80348c5d93f4.camel%40j-davis.com
[1] https://postgr.es/m/20211104224636.5qg6cfyjkw52r...@alap3.anarazel.de
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
commit 0b1397397c0f490525d3a15f5e9d5eb8f6023aa9
Author: Nathan Bossart <nathandboss...@gmail.com>
Date: Fri Jul 22 12:21:16 2022 -0700
introduce pg_vacuum_analyze
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..b09aa7aed9 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></entry> and
+ <link linkend="sql-analyze"><command>ANALYZE</command></link></entry>
+ 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.
