On Wed, Sep 07, 2022 at 07:09:05PM -0400, Stephen Frost wrote:
> Yes, that seems to be the consensus among those involved in this thread
> thus far. Basically, I imagine this involves passing around the object
> type along with the acl info and then using that to check the bits and
> such. I doubt it’s worth inventing a new structure to combine the two …
> but that’s just gut feeling and you may find it does make sense to once you
> get into it.
I've done some preliminary research for this approach, and I've found some
interesting challenges.
* aclparse() will need to handle ambiguous strings. For example, USAGE is
available for most catalogs, so which ACL bit should be chosen? One
possible solution would be to make sure the common privilege types always
use the same bit.
* When comparing ACLs, there probably should be some way to differentiate
overloaded privilege bits, else ACLs for different catalogs that have
nothing in common could evaluate as equal. Such comparisons may be
unlikely, but this still doesn't strike me as acceptable.
* aclitemout() needs some way to determine what privilege an ACL bit
actually refers to. I can think of a couple of ways to do this: 1) we
could create different aclitem types for each catalog (or maybe just one
for pg_class and another for everything else), or 2) we could include the
type in AclItem, perhaps by adding a uint8 field. I noticed that Tom
called out this particular challenge back in 2018 [0].
Am I overlooking an easier way to handle these things? From my admittedly
brief analysis thus far, I'm worried this could devolve into something
overly complex or magical, especially when simply moving to a uint64 might
be a reasonable way to significantly extend AclItem's life span. Robert
suggested upthread that Tom might have concerns with adding another 32 bits
to AclItem, but the archives indicate he has previously proposed exactly
that [1]. Of course, I don't know how everyone feels about the uint64 idea
today, but ISTM like it might be the path of least resistance.
So, here is a new patch set. 0001 expands AclMode to a uint64. 0002
simplifies some WARNING messages for VACUUM/ANALYZE. 0003 introduces
privilege bits for VACUUM and ANALYZE on relations. And 0004 introduces
the pg_vacuum/analyze_all_tables predefined roles.
[0] https://postgr.es/m/18391.1521419120%40sss.pgh.pa.us
[1] https://postgr.es/m/11414.1526422062%40sss.pgh.pa.us
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
>From d1ec82eeb12a15bb4f39bbf0d88ae32bd554418d Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandboss...@gmail.com>
Date: Wed, 7 Sep 2022 22:25:29 -0700
Subject: [PATCH v5 1/4] Change AclMode from a uint32 to a uint64.
---
src/backend/nodes/outfuncs.c | 2 +-
src/bin/pg_upgrade/check.c | 35 +++++++++++++++++++++++++++++++++
src/include/catalog/pg_type.dat | 2 +-
src/include/nodes/parsenodes.h | 6 +++---
src/include/utils/acl.h | 24 +++++++++++-----------
5 files changed, 52 insertions(+), 17 deletions(-)
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 60610e3a4b..dbb9ad52da 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -528,7 +528,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
WRITE_BOOL_FIELD(lateral);
WRITE_BOOL_FIELD(inh);
WRITE_BOOL_FIELD(inFromCl);
- WRITE_UINT_FIELD(requiredPerms);
+ WRITE_UINT64_FIELD(requiredPerms);
WRITE_OID_FIELD(checkAsUser);
WRITE_BITMAPSET_FIELD(selectedCols);
WRITE_BITMAPSET_FIELD(insertedCols);
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index f4969bcdad..349f789e8b 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -28,6 +28,7 @@ static void check_for_incompatible_polymorphics(ClusterInfo *cluster);
static void check_for_tables_with_oids(ClusterInfo *cluster);
static void check_for_composite_data_type_usage(ClusterInfo *cluster);
static void check_for_reg_data_type_usage(ClusterInfo *cluster);
+static void check_for_aclitem_data_type_usage(ClusterInfo *cluster);
static void check_for_jsonb_9_4_usage(ClusterInfo *cluster);
static void check_for_pg_role_prefix(ClusterInfo *cluster);
static void check_for_new_tablespace_dir(ClusterInfo *new_cluster);
@@ -107,6 +108,13 @@ check_and_dump_old_cluster(bool live_check)
check_for_reg_data_type_usage(&old_cluster);
check_for_isn_and_int8_passing_mismatch(&old_cluster);
+ /*
+ * PG 16 increased the size of the 'aclitem' type, which breaks the on-disk
+ * format for existing data.
+ */
+ if (GET_MAJOR_VERSION(old_cluster.major_version) <= 1500)
+ check_for_aclitem_data_type_usage(&old_cluster);
+
/*
* PG 14 changed the function signature of encoding conversion functions.
* Conversions from older versions cannot be upgraded automatically
@@ -1315,6 +1323,33 @@ check_for_reg_data_type_usage(ClusterInfo *cluster)
check_ok();
}
+/*
+ * check_for_aclitem_data_type_usage
+ *
+ * aclitem changed its storage format in 16, so check for it.
+ */
+static void
+check_for_aclitem_data_type_usage(ClusterInfo *cluster)
+{
+ char output_path[MAXPGPATH];
+
+ prep_status("Checking for incompatible aclitem data type in user tables");
+
+ snprintf(output_path, sizeof(output_path), "tables_using_aclitem.txt");
+
+ if (check_for_data_type_usage(cluster, "pg_catalog.aclitem", output_path))
+ {
+ pg_log(PG_REPORT, "fatal");
+ pg_fatal("Your installation contains the \"aclitem\" data type in user tables.\n"
+ "The internal format of \"aclitem\" changed in PostgreSQL version 16\n"
+ "so this cluster cannot currently be upgraded. You can drop the\n"
+ "problem columns and restart the upgrade. A list of the problem\n"
+ "columns is in the file:\n"
+ " %s", output_path);
+ }
+ else
+ check_ok();
+}
/*
* check_for_jsonb_9_4_usage()
diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat
index df45879463..484dec39e8 100644
--- a/src/include/catalog/pg_type.dat
+++ b/src/include/catalog/pg_type.dat
@@ -267,7 +267,7 @@
# OIDS 1000 - 1099
{ oid => '1033', array_type_oid => '1034', descr => 'access control list',
- typname => 'aclitem', typlen => '12', typbyval => 'f', typcategory => 'U',
+ typname => 'aclitem', typlen => '16', typbyval => 'f', typcategory => 'U',
typinput => 'aclitemin', typoutput => 'aclitemout', typreceive => '-',
typsend => '-', typalign => 'i' },
{ oid => '1042', array_type_oid => '1014',
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 6958306a7d..726529476c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -72,12 +72,12 @@ typedef enum SetQuantifier
/*
* Grantable rights are encoded so that we can OR them together in a bitmask.
- * The present representation of AclItem limits us to 16 distinct rights,
- * even though AclMode is defined as uint32. See utils/acl.h.
+ * The present representation of AclItem limits us to 32 distinct rights,
+ * even though AclMode is defined as uint64. See utils/acl.h.
*
* Caution: changing these codes breaks stored ACLs, hence forces initdb.
*/
-typedef uint32 AclMode; /* a bitmask of privilege bits */
+typedef uint64 AclMode; /* a bitmask of privilege bits */
#define ACL_INSERT (1<<0) /* for relations */
#define ACL_SELECT (1<<1)
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h
index 3d6411197c..8ac7696b7d 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -63,29 +63,29 @@ typedef struct AclItem
* bits, and the lower 16 bits are the actual privileges. We use "rights"
* to mean the combined grant option and privilege bits fields.
*/
-#define ACLITEM_GET_PRIVS(item) ((item).ai_privs & 0xFFFF)
-#define ACLITEM_GET_GOPTIONS(item) (((item).ai_privs >> 16) & 0xFFFF)
+#define ACLITEM_GET_PRIVS(item) ((item).ai_privs & 0xFFFFFFFF)
+#define ACLITEM_GET_GOPTIONS(item) (((item).ai_privs >> 32) & 0xFFFFFFFF)
#define ACLITEM_GET_RIGHTS(item) ((item).ai_privs)
-#define ACL_GRANT_OPTION_FOR(privs) (((AclMode) (privs) & 0xFFFF) << 16)
-#define ACL_OPTION_TO_PRIVS(privs) (((AclMode) (privs) >> 16) & 0xFFFF)
+#define ACL_GRANT_OPTION_FOR(privs) (((AclMode) (privs) & 0xFFFFFFFF) << 32)
+#define ACL_OPTION_TO_PRIVS(privs) (((AclMode) (privs) >> 32) & 0xFFFFFFFF)
#define ACLITEM_SET_PRIVS(item,privs) \
- ((item).ai_privs = ((item).ai_privs & ~((AclMode) 0xFFFF)) | \
- ((AclMode) (privs) & 0xFFFF))
+ ((item).ai_privs = ((item).ai_privs & ~((AclMode) 0xFFFFFFFF)) | \
+ ((AclMode) (privs) & 0xFFFFFFFF))
#define ACLITEM_SET_GOPTIONS(item,goptions) \
- ((item).ai_privs = ((item).ai_privs & ~(((AclMode) 0xFFFF) << 16)) | \
- (((AclMode) (goptions) & 0xFFFF) << 16))
+ ((item).ai_privs = ((item).ai_privs & ~(((AclMode) 0xFFFFFFFF) << 32)) | \
+ (((AclMode) (goptions) & 0xFFFFFFFF) << 32))
#define ACLITEM_SET_RIGHTS(item,rights) \
((item).ai_privs = (AclMode) (rights))
#define ACLITEM_SET_PRIVS_GOPTIONS(item,privs,goptions) \
- ((item).ai_privs = ((AclMode) (privs) & 0xFFFF) | \
- (((AclMode) (goptions) & 0xFFFF) << 16))
+ ((item).ai_privs = ((AclMode) (privs) & 0xFFFFFFFF) | \
+ (((AclMode) (goptions) & 0xFFFFFFFF) << 32))
-#define ACLITEM_ALL_PRIV_BITS ((AclMode) 0xFFFF)
-#define ACLITEM_ALL_GOPTION_BITS ((AclMode) 0xFFFF << 16)
+#define ACLITEM_ALL_PRIV_BITS ((AclMode) 0xFFFFFFFF)
+#define ACLITEM_ALL_GOPTION_BITS ((AclMode) 0xFFFFFFFF << 32)
/*
* Definitions for convenient access to Acl (array of AclItem).
--
2.25.1
>From f94303fa6e3cbf890dcc77a4bba5bdad93b83b6f Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandboss...@gmail.com>
Date: Tue, 6 Sep 2022 10:23:56 -0700
Subject: [PATCH v5 2/4] Simplify WARNING messages emitted when skipping
vacuum/analyze for a table.
---
src/backend/commands/vacuum.c | 32 ++----
.../isolation/expected/vacuum-conflict.out | 16 +--
src/test/regress/expected/vacuum.out | 102 +++++++++---------
3 files changed, 65 insertions(+), 85 deletions(-)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 7ccde07de9..651e4a7556 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -579,18 +579,9 @@ vacuum_is_relation_owner(Oid relid, Form_pg_class reltuple, bits32 options)
if ((options & VACOPT_VACUUM) != 0)
{
- if (reltuple->relisshared)
- ereport(WARNING,
- (errmsg("skipping \"%s\" --- only superuser can vacuum it",
- relname)));
- else if (reltuple->relnamespace == PG_CATALOG_NAMESPACE)
- ereport(WARNING,
- (errmsg("skipping \"%s\" --- only superuser or database owner can vacuum it",
- relname)));
- else
- ereport(WARNING,
- (errmsg("skipping \"%s\" --- only table or database owner can vacuum it",
- relname)));
+ ereport(WARNING,
+ (errmsg("permission denied to vacuum \"%s\", skipping it",
+ relname)));
/*
* For VACUUM ANALYZE, both logs could show up, but just generate
@@ -601,20 +592,9 @@ vacuum_is_relation_owner(Oid relid, Form_pg_class reltuple, bits32 options)
}
if ((options & VACOPT_ANALYZE) != 0)
- {
- if (reltuple->relisshared)
- ereport(WARNING,
- (errmsg("skipping \"%s\" --- only superuser can analyze it",
- relname)));
- else if (reltuple->relnamespace == PG_CATALOG_NAMESPACE)
- ereport(WARNING,
- (errmsg("skipping \"%s\" --- only superuser or database owner can analyze it",
- relname)));
- else
- ereport(WARNING,
- (errmsg("skipping \"%s\" --- only table or database owner can analyze it",
- relname)));
- }
+ ereport(WARNING,
+ (errmsg("permission denied to analyze \"%s\", skipping it",
+ relname)));
return false;
}
diff --git a/src/test/isolation/expected/vacuum-conflict.out b/src/test/isolation/expected/vacuum-conflict.out
index ffde537305..77e45506c3 100644
--- a/src/test/isolation/expected/vacuum-conflict.out
+++ b/src/test/isolation/expected/vacuum-conflict.out
@@ -4,7 +4,7 @@ starting permutation: s1_begin s1_lock s2_auth s2_vacuum s1_commit s2_reset
step s1_begin: BEGIN;
step s1_lock: LOCK vacuum_tab IN SHARE UPDATE EXCLUSIVE MODE;
step s2_auth: SET ROLE regress_vacuum_conflict;
-s2: WARNING: skipping "vacuum_tab" --- only table or database owner can vacuum it
+s2: WARNING: permission denied to vacuum "vacuum_tab", skipping it
step s2_vacuum: VACUUM vacuum_tab;
step s1_commit: COMMIT;
step s2_reset: RESET ROLE;
@@ -12,7 +12,7 @@ step s2_reset: RESET ROLE;
starting permutation: s1_begin s2_auth s2_vacuum s1_lock s1_commit s2_reset
step s1_begin: BEGIN;
step s2_auth: SET ROLE regress_vacuum_conflict;
-s2: WARNING: skipping "vacuum_tab" --- only table or database owner can vacuum it
+s2: WARNING: permission denied to vacuum "vacuum_tab", skipping it
step s2_vacuum: VACUUM vacuum_tab;
step s1_lock: LOCK vacuum_tab IN SHARE UPDATE EXCLUSIVE MODE;
step s1_commit: COMMIT;
@@ -22,14 +22,14 @@ starting permutation: s1_begin s2_auth s1_lock s2_vacuum s1_commit s2_reset
step s1_begin: BEGIN;
step s2_auth: SET ROLE regress_vacuum_conflict;
step s1_lock: LOCK vacuum_tab IN SHARE UPDATE EXCLUSIVE MODE;
-s2: WARNING: skipping "vacuum_tab" --- only table or database owner can vacuum it
+s2: WARNING: permission denied to vacuum "vacuum_tab", skipping it
step s2_vacuum: VACUUM vacuum_tab;
step s1_commit: COMMIT;
step s2_reset: RESET ROLE;
starting permutation: s2_auth s2_vacuum s1_begin s1_lock s1_commit s2_reset
step s2_auth: SET ROLE regress_vacuum_conflict;
-s2: WARNING: skipping "vacuum_tab" --- only table or database owner can vacuum it
+s2: WARNING: permission denied to vacuum "vacuum_tab", skipping it
step s2_vacuum: VACUUM vacuum_tab;
step s1_begin: BEGIN;
step s1_lock: LOCK vacuum_tab IN SHARE UPDATE EXCLUSIVE MODE;
@@ -40,7 +40,7 @@ starting permutation: s1_begin s1_lock s2_auth s2_analyze s1_commit s2_reset
step s1_begin: BEGIN;
step s1_lock: LOCK vacuum_tab IN SHARE UPDATE EXCLUSIVE MODE;
step s2_auth: SET ROLE regress_vacuum_conflict;
-s2: WARNING: skipping "vacuum_tab" --- only table or database owner can analyze it
+s2: WARNING: permission denied to analyze "vacuum_tab", skipping it
step s2_analyze: ANALYZE vacuum_tab;
step s1_commit: COMMIT;
step s2_reset: RESET ROLE;
@@ -48,7 +48,7 @@ step s2_reset: RESET ROLE;
starting permutation: s1_begin s2_auth s2_analyze s1_lock s1_commit s2_reset
step s1_begin: BEGIN;
step s2_auth: SET ROLE regress_vacuum_conflict;
-s2: WARNING: skipping "vacuum_tab" --- only table or database owner can analyze it
+s2: WARNING: permission denied to analyze "vacuum_tab", skipping it
step s2_analyze: ANALYZE vacuum_tab;
step s1_lock: LOCK vacuum_tab IN SHARE UPDATE EXCLUSIVE MODE;
step s1_commit: COMMIT;
@@ -58,14 +58,14 @@ starting permutation: s1_begin s2_auth s1_lock s2_analyze s1_commit s2_reset
step s1_begin: BEGIN;
step s2_auth: SET ROLE regress_vacuum_conflict;
step s1_lock: LOCK vacuum_tab IN SHARE UPDATE EXCLUSIVE MODE;
-s2: WARNING: skipping "vacuum_tab" --- only table or database owner can analyze it
+s2: WARNING: permission denied to analyze "vacuum_tab", skipping it
step s2_analyze: ANALYZE vacuum_tab;
step s1_commit: COMMIT;
step s2_reset: RESET ROLE;
starting permutation: s2_auth s2_analyze s1_begin s1_lock s1_commit s2_reset
step s2_auth: SET ROLE regress_vacuum_conflict;
-s2: WARNING: skipping "vacuum_tab" --- only table or database owner can analyze it
+s2: WARNING: permission denied to analyze "vacuum_tab", skipping it
step s2_analyze: ANALYZE vacuum_tab;
step s1_begin: BEGIN;
step s1_lock: LOCK vacuum_tab IN SHARE UPDATE EXCLUSIVE MODE;
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index c63a157e5f..0035d158b7 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -295,120 +295,120 @@ CREATE ROLE regress_vacuum;
SET ROLE regress_vacuum;
-- Simple table
VACUUM vacowned;
-WARNING: skipping "vacowned" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned", skipping it
ANALYZE vacowned;
-WARNING: skipping "vacowned" --- only table or database owner can analyze it
+WARNING: permission denied to analyze "vacowned", skipping it
VACUUM (ANALYZE) vacowned;
-WARNING: skipping "vacowned" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned", skipping it
-- Catalog
VACUUM pg_catalog.pg_class;
-WARNING: skipping "pg_class" --- only superuser or database owner can vacuum it
+WARNING: permission denied to vacuum "pg_class", skipping it
ANALYZE pg_catalog.pg_class;
-WARNING: skipping "pg_class" --- only superuser or database owner can analyze it
+WARNING: permission denied to analyze "pg_class", skipping it
VACUUM (ANALYZE) pg_catalog.pg_class;
-WARNING: skipping "pg_class" --- only superuser or database owner can vacuum it
+WARNING: permission denied to vacuum "pg_class", skipping it
-- Shared catalog
VACUUM pg_catalog.pg_authid;
-WARNING: skipping "pg_authid" --- only superuser can vacuum it
+WARNING: permission denied to vacuum "pg_authid", skipping it
ANALYZE pg_catalog.pg_authid;
-WARNING: skipping "pg_authid" --- only superuser can analyze it
+WARNING: permission denied to analyze "pg_authid", skipping it
VACUUM (ANALYZE) pg_catalog.pg_authid;
-WARNING: skipping "pg_authid" --- only superuser can vacuum it
+WARNING: permission denied to vacuum "pg_authid", skipping it
-- Partitioned table and its partitions, nothing owned by other user.
-- Relations are not listed in a single command to test ownership
-- independently.
VACUUM vacowned_parted;
-WARNING: skipping "vacowned_parted" --- only table or database owner can vacuum it
-WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_parted", skipping it
+WARNING: permission denied to vacuum "vacowned_part1", skipping it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
VACUUM vacowned_part1;
-WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part1", skipping it
VACUUM vacowned_part2;
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
ANALYZE vacowned_parted;
-WARNING: skipping "vacowned_parted" --- only table or database owner can analyze it
-WARNING: skipping "vacowned_part1" --- only table or database owner can analyze it
-WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+WARNING: permission denied to analyze "vacowned_parted", skipping it
+WARNING: permission denied to analyze "vacowned_part1", skipping it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
ANALYZE vacowned_part1;
-WARNING: skipping "vacowned_part1" --- only table or database owner can analyze it
+WARNING: permission denied to analyze "vacowned_part1", skipping it
ANALYZE vacowned_part2;
-WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
-WARNING: skipping "vacowned_parted" --- only table or database owner can vacuum it
-WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_parted", skipping it
+WARNING: permission denied to vacuum "vacowned_part1", skipping it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
-WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part1", skipping it
VACUUM (ANALYZE) vacowned_part2;
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
RESET ROLE;
-- Partitioned table and one partition owned by other user.
ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
ALTER TABLE vacowned_part1 OWNER TO regress_vacuum;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
VACUUM vacowned_part1;
VACUUM vacowned_part2;
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
ANALYZE vacowned_parted;
-WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
ANALYZE vacowned_part1;
ANALYZE vacowned_part2;
-WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
RESET ROLE;
-- Only one partition owned by other user.
ALTER TABLE vacowned_parted OWNER TO CURRENT_USER;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
-WARNING: skipping "vacowned_parted" --- only table or database owner can vacuum it
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_parted", skipping it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
VACUUM vacowned_part1;
VACUUM vacowned_part2;
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
ANALYZE vacowned_parted;
-WARNING: skipping "vacowned_parted" --- only table or database owner can analyze it
-WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+WARNING: permission denied to analyze "vacowned_parted", skipping it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
ANALYZE vacowned_part1;
ANALYZE vacowned_part2;
-WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
-WARNING: skipping "vacowned_parted" --- only table or database owner can vacuum it
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_parted", skipping it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
RESET ROLE;
-- Only partitioned table owned by other user.
ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
ALTER TABLE vacowned_part1 OWNER TO CURRENT_USER;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
-WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part1", skipping it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
VACUUM vacowned_part1;
-WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part1", skipping it
VACUUM vacowned_part2;
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
ANALYZE vacowned_parted;
-WARNING: skipping "vacowned_part1" --- only table or database owner can analyze it
-WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+WARNING: permission denied to analyze "vacowned_part1", skipping it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
ANALYZE vacowned_part1;
-WARNING: skipping "vacowned_part1" --- only table or database owner can analyze it
+WARNING: permission denied to analyze "vacowned_part1", skipping it
ANALYZE vacowned_part2;
-WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
-WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part1", skipping it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
-WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part1", skipping it
VACUUM (ANALYZE) vacowned_part2;
-WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: permission denied to vacuum "vacowned_part2", skipping it
RESET ROLE;
DROP TABLE vacowned;
DROP TABLE vacowned_parted;
--
2.25.1
>From ea613481975c5466b5de153410181be3c4ae052c Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandboss...@gmail.com>
Date: Sat, 3 Sep 2022 23:31:38 -0700
Subject: [PATCH v5 3/4] Allow granting VACUUM and ANALYZE privileges on
relations.
---
doc/src/sgml/ddl.sgml | 49 ++++++++---
doc/src/sgml/func.sgml | 3 +-
.../sgml/ref/alter_default_privileges.sgml | 4 +-
doc/src/sgml/ref/analyze.sgml | 3 +-
doc/src/sgml/ref/grant.sgml | 4 +-
doc/src/sgml/ref/revoke.sgml | 2 +-
doc/src/sgml/ref/vacuum.sgml | 3 +-
src/backend/catalog/aclchk.c | 8 ++
src/backend/commands/analyze.c | 2 +-
src/backend/commands/vacuum.c | 24 ++++--
src/backend/parser/gram.y | 7 ++
src/backend/utils/adt/acl.c | 16 ++++
src/bin/pg_dump/dumputils.c | 2 +
src/bin/pg_dump/t/002_pg_dump.pl | 2 +-
src/bin/psql/tab-complete.c | 4 +-
src/include/nodes/parsenodes.h | 4 +-
src/include/utils/acl.h | 6 +-
src/test/regress/expected/dependency.out | 22 ++---
src/test/regress/expected/privileges.out | 86 ++++++++++++++-----
src/test/regress/expected/rowsecurity.out | 34 ++++----
src/test/regress/expected/vacuum.out | 6 ++
src/test/regress/sql/dependency.sql | 2 +-
src/test/regress/sql/privileges.sql | 40 +++++++++
23 files changed, 249 insertions(+), 84 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 03c0193709..ed034a6b1d 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1691,8 +1691,9 @@ ALTER TABLE products RENAME TO items;
<literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>,
<literal>TRUNCATE</literal>, <literal>REFERENCES</literal>, <literal>TRIGGER</literal>,
<literal>CREATE</literal>, <literal>CONNECT</literal>, <literal>TEMPORARY</literal>,
- <literal>EXECUTE</literal>, <literal>USAGE</literal>, <literal>SET</literal>
- and <literal>ALTER SYSTEM</literal>.
+ <literal>EXECUTE</literal>, <literal>USAGE</literal>, <literal>SET</literal>,
+ <literal>ALTER SYSTEM</literal>, <literal>VACUUM</literal>, and
+ <literal>ANALYZE</literal>.
The privileges applicable to a particular
object vary depending on the object's type (table, function, etc.).
More detail about the meanings of these privileges appears below.
@@ -1982,7 +1983,25 @@ REVOKE ALL ON accounts FROM PUBLIC;
</para>
</listitem>
</varlistentry>
- </variablelist>
+
+ <varlistentry>
+ <term><literal>VACUUM</literal></term>
+ <listitem>
+ <para>
+ Allows <command>VACUUM</command> on a relation.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>ANALYZE</literal></term>
+ <listitem>
+ <para>
+ Allows <command>ANALYZE</command> on a relation.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
The privileges required by other commands are listed on the
reference page of the respective command.
@@ -2131,6 +2150,16 @@ REVOKE ALL ON accounts FROM PUBLIC;
<entry><literal>A</literal></entry>
<entry><literal>PARAMETER</literal></entry>
</row>
+ <row>
+ <entry><literal>VACUUM</literal></entry>
+ <entry><literal>v</literal></entry>
+ <entry><literal>TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>ANALYZE</literal></entry>
+ <entry><literal>z</literal></entry>
+ <entry><literal>TABLE</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -2221,7 +2250,7 @@ REVOKE ALL ON accounts FROM PUBLIC;
</row>
<row>
<entry><literal>TABLE</literal> (and table-like objects)</entry>
- <entry><literal>arwdDxt</literal></entry>
+ <entry><literal>arwdDxtvz</literal></entry>
<entry>none</entry>
<entry><literal>\dp</literal></entry>
</row>
@@ -2279,12 +2308,12 @@ GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
would show:
<programlisting>
=> \dp mytable
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+---------+-------+-----------------------+-----------------------+----------
- public | mytable | table | miriam=arwdDxt/miriam+| col1: +|
- | | | =r/miriam +| miriam_rw=rw/miriam |
- | | | admin=arw/miriam | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+---------+-------+-------------------------+-----------------------+----------
+ public | mytable | table | miriam=arwdDxtvz/miriam+| col1: +|
+ | | | =r/miriam +| miriam_rw=rw/miriam |
+ | | | admin=arw/miriam | |
(1 row)
</programlisting>
</para>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 67eb380632..8be800767d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -22962,7 +22962,8 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
are <literal>SELECT</literal>, <literal>INSERT</literal>,
<literal>UPDATE</literal>, <literal>DELETE</literal>,
<literal>TRUNCATE</literal>, <literal>REFERENCES</literal>,
- and <literal>TRIGGER</literal>.
+ <literal>TRIGGER</literal>, <literal>VACUUM</literal> and
+ <literal>ANALYZE</literal>.
</para></entry>
</row>
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml
index f1d54f5aa3..0da295daff 100644
--- a/doc/src/sgml/ref/alter_default_privileges.sgml
+++ b/doc/src/sgml/ref/alter_default_privileges.sgml
@@ -28,7 +28,7 @@ ALTER DEFAULT PRIVILEGES
<phrase>where <replaceable class="parameter">abbreviated_grant_or_revoke</replaceable> is one of:</phrase>
-GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -51,7 +51,7 @@ GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]
- { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+ { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 2ba115d1ad..400ea30cd0 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -149,7 +149,8 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
<para>
To analyze a table, one must ordinarily be the table's owner or a
- superuser. However, database owners are allowed to
+ superuser or have the <literal>ANALYZE</literal> privilege on the table.
+ 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.)
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index dea19cd348..f6234d975a 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
| ALL TABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
@@ -193,6 +193,8 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
<term><literal>USAGE</literal></term>
<term><literal>SET</literal></term>
<term><literal>ALTER SYSTEM</literal></term>
+ <term><literal>VACUUM</literal></term>
+ <term><literal>ANALYZE</literal></term>
<listitem>
<para>
Specific types of privileges, as defined in <xref linkend="ddl-priv"/>.
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 4fd4bfb3d7..ece1aa721f 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -22,7 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
REVOKE [ GRANT OPTION FOR ]
- { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+ { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
| ALL TABLES IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index c582021d29..70c0d81346 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -357,7 +357,8 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
<para>
To vacuum a table, one must ordinarily be the table's owner or a
- superuser. However, database owners are allowed to
+ superuser or have the <literal>VACUUM</literal> privilege on the table.
+ 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.)
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 17ff617fba..20c018098f 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -3403,6 +3403,10 @@ string_to_privilege(const char *privname)
return ACL_SET;
if (strcmp(privname, "alter system") == 0)
return ACL_ALTER_SYSTEM;
+ if (strcmp(privname, "vacuum") == 0)
+ return ACL_VACUUM;
+ if (strcmp(privname, "analyze") == 0)
+ return ACL_ANALYZE;
if (strcmp(privname, "rule") == 0)
return 0; /* ignore old RULE privileges */
ereport(ERROR,
@@ -3444,6 +3448,10 @@ privilege_to_string(AclMode privilege)
return "SET";
case ACL_ALTER_SYSTEM:
return "ALTER SYSTEM";
+ case ACL_VACUUM:
+ return "VACUUM";
+ case ACL_ANALYZE:
+ return "ANALYZE";
default:
elog(ERROR, "unrecognized privilege: %d", (int) privilege);
}
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index a7966fff83..faa5b098df 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -168,7 +168,7 @@ analyze_rel(Oid relid, RangeVar *relation,
*/
if (!vacuum_is_relation_owner(RelationGetRelid(onerel),
onerel->rd_rel,
- params->options & VACOPT_ANALYZE))
+ VACOPT_ANALYZE))
{
relation_close(onerel, ShareUpdateExclusiveLock);
return;
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 651e4a7556..15311e91d2 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -547,16 +547,16 @@ vacuum(List *relations, VacuumParams *params,
}
/*
- * Check if a given relation can be safely vacuumed or analyzed. If the
- * user is not the relation owner, issue a WARNING log message and return
- * false to let the caller decide what to do with this relation. This
- * routine is used to decide if a relation can be processed for VACUUM or
- * ANALYZE.
+ * Check if the current user has privileges to vacuum or analyze the relation.
+ * If not, issue a WARNING log message and return false to let the caller
+ * decide what to do with this relation. This routine is used to decide if a
+ * relation can be processed for VACUUM or ANALYZE.
*/
bool
vacuum_is_relation_owner(Oid relid, Form_pg_class reltuple, bits32 options)
{
char *relname;
+ AclMode mode = 0;
Assert((options & (VACOPT_VACUUM | VACOPT_ANALYZE)) != 0);
@@ -566,13 +566,19 @@ vacuum_is_relation_owner(Oid relid, Form_pg_class reltuple, bits32 options)
* 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.
+ * superuser case. The user might also have been granted privileges to
+ * vacuum or analyze the table.
*
* 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 (options & VACOPT_VACUUM)
+ mode |= ACL_VACUUM;
+ if (options & VACOPT_ANALYZE)
+ mode |= ACL_ANALYZE;
if (pg_class_ownercheck(relid, GetUserId()) ||
- (pg_database_ownercheck(MyDatabaseId, GetUserId()) && !reltuple->relisshared))
+ (pg_database_ownercheck(MyDatabaseId, GetUserId()) && !reltuple->relisshared) ||
+ pg_class_aclcheck(relid, GetUserId(), mode) == ACLCHECK_OK)
return true;
relname = NameStr(reltuple->relname);
@@ -1894,12 +1900,12 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
*/
if (!vacuum_is_relation_owner(RelationGetRelid(rel),
rel->rd_rel,
- params->options & VACOPT_VACUUM))
+ VACOPT_VACUUM))
{
relation_close(rel, lmode);
PopActiveSnapshot();
CommitTransactionCommand();
- return false;
+ return true; /* user may have the ANALYZE privilege */
}
/*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0492ff9a66..7b2426bc52 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -7485,6 +7485,13 @@ privilege: SELECT opt_column_list
n->cols = NIL;
$$ = n;
}
+ | analyze_keyword
+ {
+ AccessPriv *n = makeNode(AccessPriv);
+ n->priv_name = pstrdup("analyze");
+ n->cols = NIL;
+ $$ = n;
+ }
| ColId opt_column_list
{
AccessPriv *n = makeNode(AccessPriv);
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index fd71a9b13e..b4b4a5e6fa 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -314,6 +314,12 @@ aclparse(const char *s, AclItem *aip)
case ACL_ALTER_SYSTEM_CHR:
read = ACL_ALTER_SYSTEM;
break;
+ case ACL_VACUUM_CHR:
+ read = ACL_VACUUM;
+ break;
+ case ACL_ANALYZE_CHR:
+ read = ACL_ANALYZE;
+ break;
case 'R': /* ignore old RULE privileges */
read = 0;
break;
@@ -1588,6 +1594,8 @@ makeaclitem(PG_FUNCTION_ARGS)
{"CONNECT", ACL_CONNECT},
{"SET", ACL_SET},
{"ALTER SYSTEM", ACL_ALTER_SYSTEM},
+ {"VACUUM", ACL_VACUUM},
+ {"ANALYZE", ACL_ANALYZE},
{"RULE", 0}, /* ignore old RULE privileges */
{NULL, 0}
};
@@ -1696,6 +1704,10 @@ convert_aclright_to_string(int aclright)
return "SET";
case ACL_ALTER_SYSTEM:
return "ALTER SYSTEM";
+ case ACL_VACUUM:
+ return "VACUUM";
+ case ACL_ANALYZE:
+ return "ANALYZE";
default:
elog(ERROR, "unrecognized aclright: %d", aclright);
return NULL;
@@ -2005,6 +2017,10 @@ convert_table_priv_string(text *priv_type_text)
{"REFERENCES WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_REFERENCES)},
{"TRIGGER", ACL_TRIGGER},
{"TRIGGER WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_TRIGGER)},
+ {"VACUUM", ACL_VACUUM},
+ {"VACUUM WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_VACUUM)},
+ {"ANALYZE", ACL_ANALYZE},
+ {"ANALYZE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_ANALYZE)},
{"RULE", 0}, /* ignore old RULE privileges */
{"RULE WITH GRANT OPTION", 0},
{NULL, 0}
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index 6e501a5413..9311417f18 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -457,6 +457,8 @@ do { \
CONVERT_PRIV('d', "DELETE");
CONVERT_PRIV('t', "TRIGGER");
CONVERT_PRIV('D', "TRUNCATE");
+ CONVERT_PRIV('v', "VACUUM");
+ CONVERT_PRIV('z', "ANALYZE");
}
}
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 2873b662fb..f015fe2194 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -566,7 +566,7 @@ my %tests = (
\QREVOKE ALL ON TABLES FROM regress_dump_test_role;\E\n
\QALTER DEFAULT PRIVILEGES \E
\QFOR ROLE regress_dump_test_role \E
- \QGRANT INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,UPDATE ON TABLES TO regress_dump_test_role;\E
+ \QGRANT INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,VACUUM,ANALYZE,UPDATE ON TABLES TO regress_dump_test_role;\E
/xm,
like => { %full_runs, section_post_data => 1, },
unlike => { no_privs => 1, },
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index a7eccc75d2..cca98ebb64 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3742,7 +3742,7 @@ psql_completion(const char *text, int start, int end)
if (HeadMatches("ALTER", "DEFAULT", "PRIVILEGES"))
COMPLETE_WITH("SELECT", "INSERT", "UPDATE",
"DELETE", "TRUNCATE", "REFERENCES", "TRIGGER",
- "EXECUTE", "USAGE", "ALL");
+ "EXECUTE", "USAGE", "VACUUM", "ANALYZE", "ALL");
else
COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_roles,
"GRANT",
@@ -3760,6 +3760,8 @@ psql_completion(const char *text, int start, int end)
"USAGE",
"SET",
"ALTER SYSTEM",
+ "VACUUM",
+ "ANALYZE",
"ALL");
}
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 726529476c..6db572908d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -94,7 +94,9 @@ typedef uint64 AclMode; /* a bitmask of privilege bits */
#define ACL_CONNECT (1<<11) /* for databases */
#define ACL_SET (1<<12) /* for configuration parameters */
#define ACL_ALTER_SYSTEM (1<<13) /* for configuration parameters */
-#define N_ACL_RIGHTS 14 /* 1 plus the last 1<<x */
+#define ACL_VACUUM (1<<14) /* for relations */
+#define ACL_ANALYZE (1<<15) /* for relations */
+#define N_ACL_RIGHTS 16 /* 1 plus the last 1<<x */
#define ACL_NO_RIGHTS 0
/* Currently, SELECT ... FOR [KEY] UPDATE/SHARE requires UPDATE privileges */
#define ACL_SELECT_FOR_UPDATE ACL_UPDATE
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h
index 8ac7696b7d..4dc6a06126 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -148,15 +148,17 @@ typedef struct ArrayType Acl;
#define ACL_CONNECT_CHR 'c'
#define ACL_SET_CHR 's'
#define ACL_ALTER_SYSTEM_CHR 'A'
+#define ACL_VACUUM_CHR 'v'
+#define ACL_ANALYZE_CHR 'z'
/* string holding all privilege code chars, in order by bitmask position */
-#define ACL_ALL_RIGHTS_STR "arwdDxtXUCTcsA"
+#define ACL_ALL_RIGHTS_STR "arwdDxtXUCTcsAvz"
/*
* Bitmasks defining "all rights" for each supported object type
*/
#define ACL_ALL_RIGHTS_COLUMN (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_REFERENCES)
-#define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_TRUNCATE|ACL_REFERENCES|ACL_TRIGGER)
+#define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_TRUNCATE|ACL_REFERENCES|ACL_TRIGGER|ACL_VACUUM|ACL_ANALYZE)
#define ACL_ALL_RIGHTS_SEQUENCE (ACL_USAGE|ACL_SELECT|ACL_UPDATE)
#define ACL_ALL_RIGHTS_DATABASE (ACL_CREATE|ACL_CREATE_TEMP|ACL_CONNECT)
#define ACL_ALL_RIGHTS_FDW (ACL_USAGE)
diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out
index 8232795148..81d8376509 100644
--- a/src/test/regress/expected/dependency.out
+++ b/src/test/regress/expected/dependency.out
@@ -19,7 +19,7 @@ DETAIL: privileges for table deptest
REVOKE SELECT ON deptest FROM GROUP regress_dep_group;
DROP GROUP regress_dep_group;
-- can't drop the user if we revoke the privileges partially
-REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON deptest FROM regress_dep_user;
+REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, VACUUM, ANALYZE ON deptest FROM regress_dep_user;
DROP USER regress_dep_user;
ERROR: role "regress_dep_user" cannot be dropped because some objects depend on it
DETAIL: privileges for table deptest
@@ -63,21 +63,21 @@ CREATE TABLE deptest (a serial primary key, b text);
GRANT ALL ON deptest1 TO regress_dep_user2;
RESET SESSION AUTHORIZATION;
\z deptest1
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+----------+-------+----------------------------------------------------+-------------------+----------
- public | deptest1 | table | regress_dep_user0=arwdDxt/regress_dep_user0 +| |
- | | | regress_dep_user1=a*r*w*d*D*x*t*/regress_dep_user0+| |
- | | | regress_dep_user2=arwdDxt/regress_dep_user1 | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+----------+-------+--------------------------------------------------------+-------------------+----------
+ public | deptest1 | table | regress_dep_user0=arwdDxtvz/regress_dep_user0 +| |
+ | | | regress_dep_user1=a*r*w*d*D*x*t*v*z*/regress_dep_user0+| |
+ | | | regress_dep_user2=arwdDxtvz/regress_dep_user1 | |
(1 row)
DROP OWNED BY regress_dep_user1;
-- all grants revoked
\z deptest1
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+----------+-------+---------------------------------------------+-------------------+----------
- public | deptest1 | table | regress_dep_user0=arwdDxt/regress_dep_user0 | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+----------+-------+-----------------------------------------------+-------------------+----------
+ public | deptest1 | table | regress_dep_user0=arwdDxtvz/regress_dep_user0 | |
(1 row)
-- table was dropped
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index bd3453ee91..023bf75161 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2561,39 +2561,39 @@ grant select on dep_priv_test to regress_priv_user4 with grant option;
set session role regress_priv_user4;
grant select on dep_priv_test to regress_priv_user5;
\dp dep_priv_test
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+---------------+-------+-----------------------------------------------+-------------------+----------
- public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| |
- | | | regress_priv_user2=r*/regress_priv_user1 +| |
- | | | regress_priv_user3=r*/regress_priv_user1 +| |
- | | | regress_priv_user4=r*/regress_priv_user2 +| |
- | | | regress_priv_user4=r*/regress_priv_user3 +| |
- | | | regress_priv_user5=r/regress_priv_user4 | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+---------------+-------+-------------------------------------------------+-------------------+----------
+ public | dep_priv_test | table | regress_priv_user1=arwdDxtvz/regress_priv_user1+| |
+ | | | regress_priv_user2=r*/regress_priv_user1 +| |
+ | | | regress_priv_user3=r*/regress_priv_user1 +| |
+ | | | regress_priv_user4=r*/regress_priv_user2 +| |
+ | | | regress_priv_user4=r*/regress_priv_user3 +| |
+ | | | regress_priv_user5=r/regress_priv_user4 | |
(1 row)
set session role regress_priv_user2;
revoke select on dep_priv_test from regress_priv_user4 cascade;
\dp dep_priv_test
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+---------------+-------+-----------------------------------------------+-------------------+----------
- public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| |
- | | | regress_priv_user2=r*/regress_priv_user1 +| |
- | | | regress_priv_user3=r*/regress_priv_user1 +| |
- | | | regress_priv_user4=r*/regress_priv_user3 +| |
- | | | regress_priv_user5=r/regress_priv_user4 | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+---------------+-------+-------------------------------------------------+-------------------+----------
+ public | dep_priv_test | table | regress_priv_user1=arwdDxtvz/regress_priv_user1+| |
+ | | | regress_priv_user2=r*/regress_priv_user1 +| |
+ | | | regress_priv_user3=r*/regress_priv_user1 +| |
+ | | | regress_priv_user4=r*/regress_priv_user3 +| |
+ | | | regress_priv_user5=r/regress_priv_user4 | |
(1 row)
set session role regress_priv_user3;
revoke select on dep_priv_test from regress_priv_user4 cascade;
\dp dep_priv_test
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+---------------+-------+-----------------------------------------------+-------------------+----------
- public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| |
- | | | regress_priv_user2=r*/regress_priv_user1 +| |
- | | | regress_priv_user3=r*/regress_priv_user1 | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+---------------+-------+-------------------------------------------------+-------------------+----------
+ public | dep_priv_test | table | regress_priv_user1=arwdDxtvz/regress_priv_user1+| |
+ | | | regress_priv_user2=r*/regress_priv_user1 +| |
+ | | | regress_priv_user3=r*/regress_priv_user1 | |
(1 row)
set session role regress_priv_user1;
@@ -2809,3 +2809,43 @@ DROP ROLE regress_group;
DROP ROLE regress_group_direct_manager;
DROP ROLE regress_group_indirect_manager;
DROP ROLE regress_group_member;
+-- VACUUM and ANALYZE
+CREATE ROLE regress_no_priv;
+CREATE ROLE regress_only_vacuum;
+CREATE ROLE regress_only_analyze;
+CREATE ROLE regress_both;
+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;
+SET ROLE regress_no_priv;
+VACUUM vacanalyze_test;
+WARNING: permission denied to vacuum "vacanalyze_test", skipping it
+ANALYZE vacanalyze_test;
+WARNING: permission denied to analyze "vacanalyze_test", skipping it
+VACUUM (ANALYZE) vacanalyze_test;
+WARNING: permission denied to vacuum "vacanalyze_test", skipping it
+RESET ROLE;
+SET ROLE regress_only_vacuum;
+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;
+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;
+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;
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index b5f6eecba1..ac21a11330 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -93,23 +93,23 @@ CREATE POLICY p2r ON document AS RESTRICTIVE TO regress_rls_dave
CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave
USING (cid <> 44);
\dp
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------------------+----------+-------+---------------------------------------------+-------------------+--------------------------------------------
- regress_rls_schema | category | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
- | | | =arwdDxt/regress_rls_alice | |
- regress_rls_schema | document | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | p1: +
- | | | =arwdDxt/regress_rls_alice | | (u): (dlevel <= ( SELECT uaccount.seclv +
- | | | | | FROM uaccount +
- | | | | | WHERE (uaccount.pguser = CURRENT_USER)))+
- | | | | | p2r (RESTRICTIVE): +
- | | | | | (u): ((cid <> 44) AND (cid < 50)) +
- | | | | | to: regress_rls_dave +
- | | | | | p1r (RESTRICTIVE): +
- | | | | | (u): (cid <> 44) +
- | | | | | to: regress_rls_dave
- regress_rls_schema | uaccount | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
- | | | =r/regress_rls_alice | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------------------+----------+-------+-----------------------------------------------+-------------------+--------------------------------------------
+ regress_rls_schema | category | table | regress_rls_alice=arwdDxtvz/regress_rls_alice+| |
+ | | | =arwdDxtvz/regress_rls_alice | |
+ regress_rls_schema | document | table | regress_rls_alice=arwdDxtvz/regress_rls_alice+| | p1: +
+ | | | =arwdDxtvz/regress_rls_alice | | (u): (dlevel <= ( SELECT uaccount.seclv +
+ | | | | | FROM uaccount +
+ | | | | | WHERE (uaccount.pguser = CURRENT_USER)))+
+ | | | | | p2r (RESTRICTIVE): +
+ | | | | | (u): ((cid <> 44) AND (cid < 50)) +
+ | | | | | to: regress_rls_dave +
+ | | | | | p1r (RESTRICTIVE): +
+ | | | | | (u): (cid <> 44) +
+ | | | | | to: regress_rls_dave
+ regress_rls_schema | uaccount | table | regress_rls_alice=arwdDxtvz/regress_rls_alice+| |
+ | | | =r/regress_rls_alice | |
(3 rows)
\d document
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 0035d158b7..e0fb21b36e 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -336,7 +336,9 @@ WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING: permission denied to vacuum "vacowned_parted", skipping it
WARNING: permission denied to vacuum "vacowned_part1", skipping it
+WARNING: permission denied to analyze "vacowned_part1", skipping it
WARNING: permission denied to vacuum "vacowned_part2", skipping it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
WARNING: permission denied to vacuum "vacowned_part1", skipping it
VACUUM (ANALYZE) vacowned_part2;
@@ -358,6 +360,7 @@ ANALYZE vacowned_part2;
WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING: permission denied to vacuum "vacowned_part2", skipping it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
WARNING: permission denied to vacuum "vacowned_part2", skipping it
@@ -380,6 +383,7 @@ WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING: permission denied to vacuum "vacowned_parted", skipping it
WARNING: permission denied to vacuum "vacowned_part2", skipping it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
WARNING: permission denied to vacuum "vacowned_part2", skipping it
@@ -404,7 +408,9 @@ ANALYZE vacowned_part2;
WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING: permission denied to vacuum "vacowned_part1", skipping it
+WARNING: permission denied to analyze "vacowned_part1", skipping it
WARNING: permission denied to vacuum "vacowned_part2", skipping it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
WARNING: permission denied to vacuum "vacowned_part1", skipping it
VACUUM (ANALYZE) vacowned_part2;
diff --git a/src/test/regress/sql/dependency.sql b/src/test/regress/sql/dependency.sql
index 2559c62d0b..99b905a938 100644
--- a/src/test/regress/sql/dependency.sql
+++ b/src/test/regress/sql/dependency.sql
@@ -21,7 +21,7 @@ REVOKE SELECT ON deptest FROM GROUP regress_dep_group;
DROP GROUP regress_dep_group;
-- can't drop the user if we revoke the privileges partially
-REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON deptest FROM regress_dep_user;
+REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, VACUUM, ANALYZE ON deptest FROM regress_dep_user;
DROP USER regress_dep_user;
-- now we are OK to drop him
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 4ad366470d..a8ebcc8b85 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1813,3 +1813,43 @@ DROP ROLE regress_group;
DROP ROLE regress_group_direct_manager;
DROP ROLE regress_group_indirect_manager;
DROP ROLE regress_group_member;
+
+-- VACUUM and ANALYZE
+CREATE ROLE regress_no_priv;
+CREATE ROLE regress_only_vacuum;
+CREATE ROLE regress_only_analyze;
+CREATE ROLE regress_both;
+
+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;
+
+SET ROLE regress_no_priv;
+VACUUM vacanalyze_test;
+ANALYZE vacanalyze_test;
+VACUUM (ANALYZE) vacanalyze_test;
+RESET ROLE;
+
+SET ROLE regress_only_vacuum;
+VACUUM vacanalyze_test;
+ANALYZE vacanalyze_test;
+VACUUM (ANALYZE) vacanalyze_test;
+RESET ROLE;
+
+SET ROLE regress_only_analyze;
+VACUUM vacanalyze_test;
+ANALYZE vacanalyze_test;
+VACUUM (ANALYZE) vacanalyze_test;
+RESET ROLE;
+
+SET ROLE regress_both;
+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;
--
2.25.1
>From 0621aafdae0a0d875ba9d57372384929bdd1fc39 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandboss...@gmail.com>
Date: Tue, 6 Sep 2022 10:32:11 -0700
Subject: [PATCH v5 4/4] Add pg_vacuum_all_tables and pg_analyze_all_tables
roles.
---
doc/src/sgml/ref/analyze.sgml | 10 +++++++---
doc/src/sgml/ref/vacuum.sgml | 10 +++++++---
doc/src/sgml/user-manag.sgml | 12 ++++++++++++
src/backend/catalog/aclchk.c | 20 +++++++++++++++++++
src/include/catalog/pg_authid.dat | 10 ++++++++++
src/test/regress/expected/privileges.out | 25 ++++++++++++++++++++++++
src/test/regress/sql/privileges.sql | 24 +++++++++++++++++++++++
7 files changed, 105 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 400ea30cd0..16c0b886fd 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 70c0d81346..9cd880ea34 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 fc836d5748..b59ee37191 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -625,6 +625,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 20c018098f..3c134468a6 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -4109,6 +4109,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/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 3343a69ddb..2574e2906d 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 023bf75161..54bf44b226 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2814,6 +2814,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;
@@ -2844,8 +2847,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 a8ebcc8b85..28538444ce 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1819,6 +1819,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;
@@ -1848,8 +1851,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;
--
2.25.1
