From: Nathan Bossart <nathandbossart@gmail.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: predefined role(s) for VACUUM and ANALYZE
Date: Tue, 6 Sep 2022 10:47:49 -0700
Message-ID: <20220906174749.GA2080137@nathanxps13> (raw)
In-Reply-To: <20220905185630.GA1961927@nathanxps13>
References: <20220722203735.GB3996698@nathanxps13>
<CALj2ACVpQmt6nnMMM76SQGq4qda3YQFSEJ=NEk+63n46=KwXXg@mail.gmail.com>
<20220725164049.GA4091959@nathanxps13>
<20220726.104712.912995710251150228.horikyota.ntt@gmail.com>
<CA+Tgmoa5A4+OVCm5Uiwgd2=M=zNT6nSxA2xh_60=UY5xKttJsA@mail.gmail.com>
<CAKFQuwZKDSnFsz8d_3YLJpZH8xnD3N4qS-Qv2Us+sK0Ry4fPhQ@mail.gmail.com>
<CA+Tgmoa7jS1uvq2s+GLWbCT6e1wH2thfSBHbsF88rtg+Dd4PaQ@mail.gmail.com>
<20220823234647.GB31055@tamriel.snowman.net>
<20220905185630.GA1961927@nathanxps13>
On Mon, Sep 05, 2022 at 11:56:30AM -0700, Nathan Bossart wrote:
> 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.
Here is a new patch set with some follow-up patches to implement $SUBJECT.
0001 is the same as v3. 0002 simplifies some WARNING messages as suggested
upthread [0]. 0003 adds the new pg_vacuum_all_tables and
pg_analyze_all_tables predefined roles. Instead of adjusting the
permissions logic in vacuum.c, I modified pg_class_aclmask_ext() to return
the ACL_VACUUM and/or ACL_ANALYZE bits as appropriate.
[0] https://postgr.es/m/20220726.104712.912995710251150228.horikyota.ntt%40gmail.com
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.comAttachments:
[text/x-diff] v4-0001-Allow-granting-VACUUM-and-ANALYZE-privileges-on-r.patch (39.2K, ../20220906174749.GA2080137@nathanxps13/2-v4-0001-Allow-granting-VACUUM-and-ANALYZE-privileges-on-r.patch)
download | inline diff:
From aa4796c3925fc7675d87df310f8057e62890138f Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandbossart@gmail.com>
Date: Sat, 3 Sep 2022 23:31:38 -0700
Subject: [PATCH v4 1/3] 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.sgmlindex 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.sgmlindex 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.sgmlindex 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.sgmlindex 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.sgmlindex 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.sgmlindex 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.sgmlindex 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.cindex 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.cindex 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.cindex 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.yindex 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.cindex 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.cindex 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.plindex 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.cindex a7eccc75d2..cca98ebb64 100644--- a/src/bin/psql/tab-complete.c+++ b/src/bin/psql/tab-complete.c@@ -3742,7 +3742,7 @@ psql_completion(const char *text, int start, int end)
if (HeadMatches("ALTER", "DEFAULT", "PRIVILEGES"))
COMPLETE_WITH("SELECT", "INSERT", "UPDATE",
"DELETE", "TRUNCATE", "REFERENCES", "TRIGGER",
- "EXECUTE", "USAGE", "ALL");+ "EXECUTE", "USAGE", "VACUUM", "ANALYZE", "ALL");
else
COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_roles,
"GRANT",
@@ -3760,6 +3760,8 @@ psql_completion(const char *text, int start, int end)
"USAGE",
"SET",
"ALTER SYSTEM",
+ "VACUUM",+ "ANALYZE",
"ALL");
}
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.hindex 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.hindex 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.outindex 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.outindex 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.outindex 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.outindex 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.sqlindex 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.sqlindex 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
[text/x-diff] v4-0002-Simplify-WARNING-messages-emitted-when-skipping-v.patch (18.6K, ../20220906174749.GA2080137@nathanxps13/3-v4-0002-Simplify-WARNING-messages-emitted-when-skipping-v.patch)
download | inline diff:
From 734cc2deec3334da5ea5f39aeb222eb23634b1b0 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandbossart@gmail.com>
Date: Tue, 6 Sep 2022 10:23:56 -0700
Subject: [PATCH v4 2/3] 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/privileges.out | 14 +--
src/test/regress/expected/vacuum.out | 114 +++++++++---------
4 files changed, 78 insertions(+), 98 deletions(-)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.cindex 562a6686af..15311e91d2 100644--- a/src/backend/commands/vacuum.c+++ b/src/backend/commands/vacuum.c@@ -585,18 +585,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
@@ -607,20 +598,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.outindex 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/privileges.out b/src/test/regress/expected/privileges.outindex b051ec8c10..023bf75161 100644--- a/src/test/regress/expected/privileges.out+++ b/src/test/regress/expected/privileges.out@@ -2819,25 +2819,25 @@ 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+WARNING: permission denied to vacuum "vacanalyze_test", skipping it
ANALYZE vacanalyze_test;
-WARNING: skipping "vacanalyze_test" --- only table or database owner can analyze it+WARNING: permission denied to analyze "vacanalyze_test", skipping it
VACUUM (ANALYZE) vacanalyze_test;
-WARNING: skipping "vacanalyze_test" --- only table or database owner can vacuum it+WARNING: permission denied to vacuum "vacanalyze_test", skipping 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+WARNING: permission denied to analyze "vacanalyze_test", skipping it
VACUUM (ANALYZE) vacanalyze_test;
-WARNING: skipping "vacanalyze_test" --- only table or database owner can analyze it+WARNING: permission denied to analyze "vacanalyze_test", skipping it
RESET ROLE;
SET ROLE regress_only_analyze;
VACUUM vacanalyze_test;
-WARNING: skipping "vacanalyze_test" --- only table or database owner can vacuum it+WARNING: permission denied to vacuum "vacanalyze_test", skipping it
ANALYZE vacanalyze_test;
VACUUM (ANALYZE) vacanalyze_test;
-WARNING: skipping "vacanalyze_test" --- only table or database owner can vacuum it+WARNING: permission denied to vacuum "vacanalyze_test", skipping it
RESET ROLE;
SET ROLE regress_both;
VACUUM vacanalyze_test;
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.outindex fd1feccee5..e0fb21b36e 100644--- a/src/test/regress/expected/vacuum.out+++ b/src/test/regress/expected/vacuum.out@@ -295,126 +295,126 @@ 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_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+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: 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: skipping "vacowned_part2" --- only table or database owner can analyze 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: 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: skipping "vacowned_part2" --- only table or database owner can analyze it+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: 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_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+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: 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
[text/x-diff] v4-0003-Add-pg_vacuum_all_tables-and-pg_analyze_all_table.patch (9.4K, ../20220906174749.GA2080137@nathanxps13/4-v4-0003-Add-pg_vacuum_all_tables-and-pg_analyze_all_table.patch)
download | inline diff:
From f09f8b034bccecb7a0ec93a8b8264c3674caa50b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandbossart@gmail.com>
Date: Tue, 6 Sep 2022 10:32:11 -0700
Subject: [PATCH v4 3/3] 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.sgmlindex 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.sgmlindex 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.sgmlindex fc836d5748..b59ee37191 100644--- a/doc/src/sgml/user-manag.sgml+++ b/doc/src/sgml/user-manag.sgml@@ -625,6 +625,18 @@ DROP ROLE doomed_role;
the <link linkend="sql-checkpoint"><command>CHECKPOINT</command></link>
command.</entry>
</row>
+ <row>+ <entry>pg_vacuum_all_tables</entry>+ <entry>Allow executing the+ <link linkend="sql-vacuum"><command>VACUUM</command></link> command on+ all tables.</entry>+ </row>+ <row>+ <entry>pg_analyze_all_tables</entry>+ <entry>Allow executing the+ <link linkend="sql-analyze"><command>ANALYZE</command></link> command on+ all tables.</entry>+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.cindex 20c018098f..3c134468a6 100644--- a/src/backend/catalog/aclchk.c+++ b/src/backend/catalog/aclchk.c@@ -4109,6 +4109,26 @@ pg_class_aclmask_ext(Oid table_oid, Oid roleid, AclMode mask,
has_privs_of_role(roleid, ROLE_PG_WRITE_ALL_DATA))
result |= (mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE));
+ /*+ * Check if ACL_VACUUM is being checked and, if so, and not already set as+ * part of the result, then check if the user is a member of the+ * pg_vacuum_all_tables role, which allows VACUUM on all relations.+ */+ if (mask & ACL_VACUUM &&+ !(result & ACL_VACUUM) &&+ has_privs_of_role(roleid, ROLE_PG_VACUUM_ALL_TABLES))+ result |= ACL_VACUUM;++ /*+ * Check if ACL_ANALYZE is being checked and, if so, and not already set as+ * part of the result, then check if the user is a member of the+ * pg_analyze_all_tables role, which allows ANALYZE on all relations.+ */+ if (mask & ACL_ANALYZE &&+ !(result & ACL_ANALYZE) &&+ has_privs_of_role(roleid, ROLE_PG_ANALYZE_ALL_TABLES))+ result |= ACL_ANALYZE;+
return result;
}
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.datindex 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.outindex 023bf75161..54bf44b226 100644--- a/src/test/regress/expected/privileges.out+++ b/src/test/regress/expected/privileges.out@@ -2814,6 +2814,9 @@ CREATE ROLE regress_no_priv;
CREATE ROLE regress_only_vacuum;
CREATE ROLE regress_only_analyze;
CREATE ROLE regress_both;
+CREATE ROLE regress_only_vacuum_all IN ROLE pg_vacuum_all_tables;+CREATE ROLE regress_only_analyze_all IN ROLE pg_analyze_all_tables;+CREATE ROLE regress_both_all IN ROLE pg_vacuum_all_tables, pg_analyze_all_tables;
CREATE TABLE vacanalyze_test (a INT);
GRANT VACUUM ON vacanalyze_test TO regress_only_vacuum, regress_both;
GRANT ANALYZE ON vacanalyze_test TO regress_only_analyze, regress_both;
@@ -2844,8 +2847,30 @@ VACUUM vacanalyze_test;
ANALYZE vacanalyze_test;
VACUUM (ANALYZE) vacanalyze_test;
RESET ROLE;
+SET ROLE regress_only_vacuum_all;+VACUUM vacanalyze_test;+ANALYZE vacanalyze_test;+WARNING: permission denied to analyze "vacanalyze_test", skipping it+VACUUM (ANALYZE) vacanalyze_test;+WARNING: permission denied to analyze "vacanalyze_test", skipping it+RESET ROLE;+SET ROLE regress_only_analyze_all;+VACUUM vacanalyze_test;+WARNING: permission denied to vacuum "vacanalyze_test", skipping it+ANALYZE vacanalyze_test;+VACUUM (ANALYZE) vacanalyze_test;+WARNING: permission denied to vacuum "vacanalyze_test", skipping it+RESET ROLE;+SET ROLE regress_both_all;+VACUUM vacanalyze_test;+ANALYZE vacanalyze_test;+VACUUM (ANALYZE) vacanalyze_test;+RESET ROLE;
DROP TABLE vacanalyze_test;
DROP ROLE regress_no_priv;
DROP ROLE regress_only_vacuum;
DROP ROLE regress_only_analyze;
DROP ROLE regress_both;
+DROP ROLE regress_only_vacuum_all;+DROP ROLE regress_only_analyze_all;+DROP ROLE regress_both_all;diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sqlindex a8ebcc8b85..28538444ce 100644--- a/src/test/regress/sql/privileges.sql+++ b/src/test/regress/sql/privileges.sql@@ -1819,6 +1819,9 @@ CREATE ROLE regress_no_priv;
CREATE ROLE regress_only_vacuum;
CREATE ROLE regress_only_analyze;
CREATE ROLE regress_both;
+CREATE ROLE regress_only_vacuum_all IN ROLE pg_vacuum_all_tables;+CREATE ROLE regress_only_analyze_all IN ROLE pg_analyze_all_tables;+CREATE ROLE regress_both_all IN ROLE pg_vacuum_all_tables, pg_analyze_all_tables;
CREATE TABLE vacanalyze_test (a INT);
GRANT VACUUM ON vacanalyze_test TO regress_only_vacuum, regress_both;
@@ -1848,8 +1851,29 @@ ANALYZE vacanalyze_test;
VACUUM (ANALYZE) vacanalyze_test;
RESET ROLE;
+SET ROLE regress_only_vacuum_all;+VACUUM vacanalyze_test;+ANALYZE vacanalyze_test;+VACUUM (ANALYZE) vacanalyze_test;+RESET ROLE;++SET ROLE regress_only_analyze_all;+VACUUM vacanalyze_test;+ANALYZE vacanalyze_test;+VACUUM (ANALYZE) vacanalyze_test;+RESET ROLE;++SET ROLE regress_both_all;+VACUUM vacanalyze_test;+ANALYZE vacanalyze_test;+VACUUM (ANALYZE) vacanalyze_test;+RESET ROLE;+
DROP TABLE vacanalyze_test;
DROP ROLE regress_no_priv;
DROP ROLE regress_only_vacuum;
DROP ROLE regress_only_analyze;
DROP ROLE regress_both;
+DROP ROLE regress_only_vacuum_all;+DROP ROLE regress_only_analyze_all;+DROP ROLE regress_both_all;--
2.25.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, sfrost@snowman.net, robertmhaas@gmail.com, david.g.johnston@gmail.com, horikyota.ntt@gmail.com, bharath.rupireddyforpostgres@gmail.com
Subject: Re: predefined role(s) for VACUUM and ANALYZE
In-Reply-To: <20220906174749.GA2080137@nathanxps13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox