Here is a first attempt at allowing users to grant VACUUM or ANALYZE
per-relation. Overall, this seems pretty straightforward. I needed to
adjust the permissions logic for VACUUM/ANALYZE a bit, which causes some
extra WARNING messages for VACUUM (ANALYZE) in some cases, but this didn't
seem particularly worrisome. It may be desirable to allow granting ANALYZE
on specific columns or to allow granting VACUUM/ANALYZE at the schema or
database level, but that is left as a future exercise.
On Tue, Aug 23, 2022 at 07:46:47PM -0400, Stephen Frost wrote:
> I've long felt that we should redefine the way the ACLs work to have a
> distinct set of bits for each object type. We don't need to support a
> CONNECT bit on a table, yet we do today and we expend quite a few bits
> in that way. Having that handled on a per-object-type basis instead
> would allow us to get quite a bit more mileage out of the existing 32bit
> field before having to introduce more complicated storage methods like
> using a bit to tell us to go look up more ACLs somewhere else.
There are 2 bits remaining at the moment, so I didn't redesign the ACL
system in the attached patch. However, I did some research on a couple
options. Using a distinct set of bits for each catalog table should free
up a handful of bits, which should indeed kick the can down the road a
little. Another easy option is to simply make AclMode a uint64, which
would immediately free up another 16 privilege bits. I was able to get
this approach building and passing tests in a few minutes, but there might
be performance/space concerns.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
>From f19eea3f3148916a79d002094ca4eb4aa98af753 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandboss...@gmail.com>
Date: Sat, 3 Sep 2022 23:31:38 -0700
Subject: [PATCH v3 1/1] 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 7ccde07de9..562a6686af 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);
@@ -1914,12 +1920,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 62a39779b9..db61bbc4d4 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3738,7 +3738,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",
@@ -3756,6 +3756,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 6958306a7d..8315b3b356 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -94,7 +94,9 @@ typedef uint32 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 3d6411197c..827aa2705b 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..b051ec8c10 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: skipping "vacanalyze_test" --- only table or database owner can vacuum it
+ANALYZE vacanalyze_test;
+WARNING: skipping "vacanalyze_test" --- only table or database owner can analyze it
+VACUUM (ANALYZE) vacanalyze_test;
+WARNING: skipping "vacanalyze_test" --- only table or database owner can vacuum it
+RESET ROLE;
+SET ROLE regress_only_vacuum;
+VACUUM vacanalyze_test;
+ANALYZE vacanalyze_test;
+WARNING: skipping "vacanalyze_test" --- only table or database owner can analyze it
+VACUUM (ANALYZE) vacanalyze_test;
+WARNING: skipping "vacanalyze_test" --- only table or database owner can analyze it
+RESET ROLE;
+SET ROLE regress_only_analyze;
+VACUUM vacanalyze_test;
+WARNING: skipping "vacanalyze_test" --- only table or database owner can vacuum it
+ANALYZE vacanalyze_test;
+VACUUM (ANALYZE) vacanalyze_test;
+WARNING: skipping "vacanalyze_test" --- only table or database owner can vacuum 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 c63a157e5f..fd1feccee5 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -336,7 +336,9 @@ WARNING: skipping "vacowned_part2" --- only table or database owner can analyze
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_part1" --- only table or database owner can analyze it
WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
VACUUM (ANALYZE) vacowned_part1;
WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
VACUUM (ANALYZE) vacowned_part2;
@@ -358,6 +360,7 @@ ANALYZE vacowned_part2;
WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
VACUUM (ANALYZE) vacowned_parted;
WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
@@ -380,6 +383,7 @@ WARNING: skipping "vacowned_part2" --- only table or database owner can analyze
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: skipping "vacowned_part2" --- only table or database owner can analyze it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
@@ -404,7 +408,9 @@ ANALYZE vacowned_part2;
WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
VACUUM (ANALYZE) vacowned_parted;
WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
+WARNING: skipping "vacowned_part1" --- only table or database owner can analyze it
WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
+WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
VACUUM (ANALYZE) vacowned_part1;
WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum 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
