On Sat, Nov 19, 2022 at 10:50:04AM -0800, Nathan Bossart wrote:
> another rebase
Another rebase for cfbot.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
>From 5f716f33e93187491686381b2180894ab2b1b92c Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandboss...@gmail.com>
Date: Wed, 7 Sep 2022 22:25:29 -0700
Subject: [PATCH v12 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 | 4 ++--
src/include/nodes/parsenodes.h | 6 +++---
src/include/utils/acl.h | 28 +++++++++++++-------------
5 files changed, 55 insertions(+), 20 deletions(-)
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index f05e72f0dc..8f150e9a2e 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -560,7 +560,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 f1bc1e6886..615a53a864 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
@@ -1319,6 +1327,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..0763dfde39 100644
--- a/src/include/catalog/pg_type.dat
+++ b/src/include/catalog/pg_type.dat
@@ -267,9 +267,9 @@
# 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' },
+ typsend => '-', typalign => 'd' },
{ oid => '1042', array_type_oid => '1014',
descr => 'char(length), blank-padded string, fixed storage length',
typname => 'bpchar', typlen => '-1', typbyval => 'f', typcategory => 'S',
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 7caff62af7..f4ed9bbff9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -73,12 +73,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 afbfdccf53..406071037e 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -59,33 +59,33 @@ typedef struct AclItem
} AclItem;
/*
- * The upper 16 bits of the ai_privs field of an AclItem are the grant option
- * bits, and the lower 16 bits are the actual privileges. We use "rights"
+ * The upper 32 bits of the ai_privs field of an AclItem are the grant option
+ * bits, and the lower 32 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 39dab5320c1746b146f44d847395f1388d945d4f Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandboss...@gmail.com>
Date: Tue, 6 Sep 2022 10:23:56 -0700
Subject: [PATCH v12 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 3c8ea21475..5d9d2469b6 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 243c0460400b385086a7183b9cdb17b321f72844 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandboss...@gmail.com>
Date: Sat, 3 Sep 2022 23:31:38 -0700
Subject: [PATCH v12 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 | 5 +-
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(+), 85 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 82fba48d5f..68cd4297d2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -22978,7 +22978,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 5ae523f4b3..c3c585be7e 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 2db66bbf37..e28d192fd3 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 3c9f8e60ad..3b5ea3c137 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -3420,6 +3420,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,
@@ -3461,6 +3465,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 bf0ec8b374..17ee189ea5 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 5d9d2469b6..9ed55350a7 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). object_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 (object_ownercheck(RelationRelationId, relid, GetUserId()) ||
- (object_ownercheck(DatabaseRelationId, MyDatabaseId, GetUserId()) && !reltuple->relisshared))
+ (object_ownercheck(DatabaseRelationId, 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 9054742427..20a83b6c9b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -7484,6 +7484,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 d4d68f9724..8ee2ebadc5 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -321,6 +321,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;
@@ -1595,6 +1601,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}
};
@@ -1703,6 +1711,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;
@@ -2012,6 +2024,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 8dc1f0eccb..fe53ed0f89 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 13014f074f..89e7317c23 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1147,7 +1147,7 @@ static const SchemaQuery Query_for_trigger_of_table = {
#define Privilege_options_of_grant_and_revoke \
"SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER", \
"CREATE", "CONNECT", "TEMPORARY", "EXECUTE", "USAGE", "SET", "ALTER SYSTEM", \
-"ALL"
+"VACUUM", "ANALYZE", "ALL"
/*
* These object types were introduced later than our support cutoff of
@@ -3782,7 +3782,8 @@ psql_completion(const char *text, int start, int end)
if (HeadMatches("ALTER", "DEFAULT", "PRIVILEGES"))
COMPLETE_WITH("SELECT", "INSERT", "UPDATE",
"DELETE", "TRUNCATE", "REFERENCES", "TRIGGER",
- "CREATE", "EXECUTE", "USAGE", "ALL");
+ "CREATE", "EXECUTE", "USAGE", "VACUUM", "ANALYZE",
+ "ALL");
else if (TailMatches("GRANT"))
COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_roles,
Privilege_options_of_grant_and_revoke);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f4ed9bbff9..6112cd85c8 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -95,7 +95,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 406071037e..e566ff0c73 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 4045bcafb5..a2d9572179 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2570,39 +2570,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;
@@ -2849,3 +2849,43 @@ DROP SCHEMA regress_roleoption;
DROP ROLE regress_roleoption_protagonist;
DROP ROLE regress_roleoption_donor;
DROP ROLE regress_roleoption_recipient;
+-- 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 92c000cf00..dd65c3264e 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1852,3 +1852,43 @@ DROP SCHEMA regress_roleoption;
DROP ROLE regress_roleoption_protagonist;
DROP ROLE regress_roleoption_donor;
DROP ROLE regress_roleoption_recipient;
+
+-- 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 30bb130415ad5920a866c6aeec9513cbd8aeb993 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandboss...@gmail.com>
Date: Tue, 6 Sep 2022 10:32:11 -0700
Subject: [PATCH v12 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 601fff3e6b..2bff4e47d0 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 3b5ea3c137..bd967eaa78 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/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 a2d9572179..7933314fd3 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 dd65c3264e..1bcaaba4eb 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;
--
2.25.1
