Skip to content

Commit ecb0fd3

Browse files
nathan-bossartnathan-bossart
committed
Reintroduce MAINTAIN privilege and pg_maintain predefined role.
Roles with MAINTAIN on a relation may run VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZE VIEW, CLUSTER, and LOCK TABLE on the relation. Roles with privileges of pg_maintain may run those same commands on all relations. This was previously committed for v16, but it was reverted in commit 151c22d due to concerns about search_path tricks that could be used to escalate privileges to the table owner. Commits 2af07e2, 59825d1, and c7ea3f4 resolved these concerns by restricting search_path when running maintenance commands. Bumps catversion. Reviewed-by: Jeff Davis Discussion: https://postgr.es/m/20240305161235.GA3478007%40nathanxps13
1 parent 2041bc4 commit ecb0fd3

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

42 files changed

+457
-180
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 26 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1868,8 +1868,8 @@ ALTER TABLE products RENAME TO items;
18681868
<literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>,
18691869
<literal>TRUNCATE</literal>, <literal>REFERENCES</literal>, <literal>TRIGGER</literal>,
18701870
<literal>CREATE</literal>, <literal>CONNECT</literal>, <literal>TEMPORARY</literal>,
1871-
<literal>EXECUTE</literal>, <literal>USAGE</literal>, <literal>SET</literal>
1872-
and <literal>ALTER SYSTEM</literal>.
1871+
<literal>EXECUTE</literal>, <literal>USAGE</literal>, <literal>SET</literal>,
1872+
<literal>ALTER SYSTEM</literal>, and <literal>MAINTAIN</literal>.
18731873
The privileges applicable to a particular
18741874
object vary depending on the object's type (table, function, etc.).
18751875
More detail about the meanings of these privileges appears below.
@@ -2160,7 +2160,19 @@ REVOKE ALL ON accounts FROM PUBLIC;
21602160
</para>
21612161
</listitem>
21622162
</varlistentry>
2163-
</variablelist>
2163+
2164+
<varlistentry id="ddl-priv-maintain">
2165+
<term><literal>MAINTAIN</literal></term>
2166+
<listitem>
2167+
<para>
2168+
Allows <command>VACUUM</command>, <command>ANALYZE</command>,
2169+
<command>CLUSTER</command>, <command>REFRESH MATERIALIZED VIEW</command>,
2170+
<command>REINDEX</command>, and <command>LOCK TABLE</command> on a
2171+
relation.
2172+
</para>
2173+
</listitem>
2174+
</varlistentry>
2175+
</variablelist>
21642176

21652177
The privileges required by other commands are listed on the
21662178
reference page of the respective command.
@@ -2309,6 +2321,11 @@ REVOKE ALL ON accounts FROM PUBLIC;
23092321
<entry><literal>A</literal></entry>
23102322
<entry><literal>PARAMETER</literal></entry>
23112323
</row>
2324+
<row>
2325+
<entry><literal>MAINTAIN</literal></entry>
2326+
<entry><literal>m</literal></entry>
2327+
<entry><literal>TABLE</literal></entry>
2328+
</row>
23122329
</tbody>
23132330
</tgroup>
23142331
</table>
@@ -2399,7 +2416,7 @@ REVOKE ALL ON accounts FROM PUBLIC;
23992416
</row>
24002417
<row>
24012418
<entry><literal>TABLE</literal> (and table-like objects)</entry>
2402-
<entry><literal>arwdDxt</literal></entry>
2419+
<entry><literal>arwdDxtm</literal></entry>
24032420
<entry>none</entry>
24042421
<entry><literal>\dp</literal></entry>
24052422
</row>
@@ -2465,11 +2482,11 @@ GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
24652482
<programlisting>
24662483
=&gt; \dp mytable
24672484
Access privileges
2468-
Schema | Name | Type | Access privileges | Column privileges | Policies
2469-
--------+---------+-------+-----------------------+-----------------------+----------
2470-
public | mytable | table | miriam=arwdDxt/miriam+| col1: +|
2471-
| | | =r/miriam +| miriam_rw=rw/miriam |
2472-
| | | admin=arw/miriam | |
2485+
Schema | Name | Type | Access privileges | Column privileges | Policies
2486+
--------+---------+-------+------------------------+-----------------------+----------
2487+
public | mytable | table | miriam=arwdDxtm/miriam+| col1: +|
2488+
| | | =r/miriam +| miriam_rw=rw/miriam |
2489+
| | | admin=arw/miriam | |
24732490
(1 row)
24742491
</programlisting>
24752492
</para>

‎doc/src/sgml/func.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -24232,7 +24232,7 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
2423224232
are <literal>SELECT</literal>, <literal>INSERT</literal>,
2423324233
<literal>UPDATE</literal>, <literal>DELETE</literal>,
2423424234
<literal>TRUNCATE</literal>, <literal>REFERENCES</literal>,
24235-
and <literal>TRIGGER</literal>.
24235+
<literal>TRIGGER</literal>, and <literal>MAINTAIN</literal>.
2423624236
</para></entry>
2423724237
</row>
2423824238

‎doc/src/sgml/ref/alter_default_privileges.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,7 @@ ALTER DEFAULT PRIVILEGES
2828

2929
<phrase>where <replaceable class="parameter">abbreviated_grant_or_revoke</replaceable> is one of:</phrase>
3030

31-
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
31+
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
3232
[, ...] | ALL [ PRIVILEGES ] }
3333
ON TABLES
3434
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -51,7 +51,7 @@ GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
5151
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
5252

5353
REVOKE [ GRANT OPTION FOR ]
54-
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
54+
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
5555
[, ...] | ALL [ PRIVILEGES ] }
5656
ON TABLES
5757
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]

‎doc/src/sgml/ref/analyze.sgml

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -174,11 +174,9 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
174174
<title>Notes</title>
175175

176176
<para>
177-
To analyze a table, one must ordinarily be the table's owner or a
178-
superuser. However, database owners are allowed to
177+
To analyze a table, one must ordinarily have the <literal>MAINTAIN</literal>
178+
privilege on the table. However, database owners are allowed to
179179
analyze all tables in their databases, except shared catalogs.
180-
(The restriction for shared catalogs means that a true database-wide
181-
<command>ANALYZE</command> can only be performed by a superuser.)
182180
<command>ANALYZE</command> will skip over any tables that the calling user
183181
does not have permission to analyze.
184182
</para>

‎doc/src/sgml/ref/cluster.sgml

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -68,9 +68,8 @@ CLUSTER [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
6868
<command>CLUSTER</command> without a
6969
<replaceable class="parameter">table_name</replaceable> reclusters all the
7070
previously-clustered tables in the current database that the calling user
71-
owns, or all such tables if called by a superuser. This
72-
form of <command>CLUSTER</command> cannot be executed inside a transaction
73-
block.
71+
has privileges for. This form of <command>CLUSTER</command> cannot be
72+
executed inside a transaction block.
7473
</para>
7574

7675
<para>
@@ -131,6 +130,11 @@ CLUSTER [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
131130
<refsect1>
132131
<title>Notes</title>
133132

133+
<para>
134+
To cluster a table, one must have the <literal>MAINTAIN</literal> privilege
135+
on the table.
136+
</para>
137+
134138
<para>
135139
In cases where you are accessing single rows randomly
136140
within a table, the actual order of the data in the

‎doc/src/sgml/ref/grant.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
24+
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
2525
[, ...] | ALL [ PRIVILEGES ] }
2626
ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
2727
| ALL TABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
@@ -193,6 +193,7 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
193193
<term><literal>USAGE</literal></term>
194194
<term><literal>SET</literal></term>
195195
<term><literal>ALTER SYSTEM</literal></term>
196+
<term><literal>MAINTAIN</literal></term>
196197
<listitem>
197198
<para>
198199
Specific types of privileges, as defined in <xref linkend="ddl-priv"/>.

‎doc/src/sgml/ref/lock.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -166,8 +166,8 @@ LOCK [ TABLE ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ]
166166

167167
<para>
168168
To lock a table, the user must have the right privilege for the specified
169-
<replaceable class="parameter">lockmode</replaceable>, or be the table's
170-
owner or a superuser. If the user has
169+
<replaceable class="parameter">lockmode</replaceable>.
170+
If the user has <literal>MAINTAIN</literal>,
171171
<literal>UPDATE</literal>, <literal>DELETE</literal>, or
172172
<literal>TRUNCATE</literal> privileges on the table, any <replaceable
173173
class="parameter">lockmode</replaceable> is permitted. If the user has

‎doc/src/sgml/ref/refresh_materialized_view.sgml

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -31,8 +31,9 @@ REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="parameter">name</
3131

3232
<para>
3333
<command>REFRESH MATERIALIZED VIEW</command> completely replaces the
34-
contents of a materialized view. To execute this command you must be the
35-
owner of the materialized view. The old contents are discarded. If
34+
contents of a materialized view. To execute this command you must have the
35+
<literal>MAINTAIN</literal>
36+
privilege on the materialized view. The old contents are discarded. If
3637
<literal>WITH DATA</literal> is specified (or defaults) the backing query
3738
is executed to provide the new data, and the materialized view is left in a
3839
scannable state. If <literal>WITH NO DATA</literal> is specified no new

‎doc/src/sgml/ref/reindex.sgml

Lines changed: 14 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -298,16 +298,21 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { DA
298298
</para>
299299

300300
<para>
301-
Reindexing a single index or table requires being the owner of that
302-
index or table. Reindexing a schema or database requires being the
303-
owner of that schema or database. Note specifically that it's thus
301+
Reindexing a single index or table requires
302+
having the <literal>MAINTAIN</literal> privilege on the
303+
table. Note that while <command>REINDEX</command> on a partitioned index or
304+
table requires having the <literal>MAINTAIN</literal> privilege on the
305+
partitioned table, such commands skip the privilege checks when processing
306+
the individual partitions. Reindexing a schema or database requires being the
307+
owner of that schema or database or having privileges of the
308+
<link linkend="predefined-roles-table"><literal>pg_maintain</literal></link>
309+
role. Note specifically that it's thus
304310
possible for non-superusers to rebuild indexes of tables owned by
305-
other users. However, as a special exception, when
306-
<command>REINDEX DATABASE</command>, <command>REINDEX SCHEMA</command>
307-
or <command>REINDEX SYSTEM</command> is issued by a non-superuser,
308-
indexes on shared catalogs will be skipped unless the user owns the
309-
catalog (which typically won't be the case). Of course, superusers
310-
can always reindex anything.
311+
other users. However, as a special exception,
312+
<command>REINDEX DATABASE</command>, <command>REINDEX SCHEMA</command>,
313+
and <command>REINDEX SYSTEM</command> will skip indexes on shared catalogs
314+
unless the user has the <literal>MAINTAIN</literal> privilege on the
315+
catalog.
311316
</para>
312317

313318
<para>

‎doc/src/sgml/ref/revoke.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@ PostgreSQL documentation
2222
<refsynopsisdiv>
2323
<synopsis>
2424
REVOKE [ GRANT OPTION FOR ]
25-
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
25+
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
2626
[, ...] | ALL [ PRIVILEGES ] }
2727
ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
2828
| ALL TABLES IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }

‎doc/src/sgml/ref/vacuum.sgml

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -434,11 +434,9 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
434434
<title>Notes</title>
435435

436436
<para>
437-
To vacuum a table, one must ordinarily be the table's owner or a
438-
superuser. However, database owners are allowed to
437+
To vacuum a table, one must ordinarily have the <literal>MAINTAIN</literal>
438+
privilege on the table. However, database owners are allowed to
439439
vacuum all tables in their databases, except shared catalogs.
440-
(The restriction for shared catalogs means that a true database-wide
441-
<command>VACUUM</command> can only be performed by a superuser.)
442440
<command>VACUUM</command> will skip over any tables that the calling user
443441
does not have permission to vacuum.
444442
</para>

‎doc/src/sgml/user-manag.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -682,6 +682,18 @@ DROP ROLE doomed_role;
682682
the <link linkend="sql-checkpoint"><command>CHECKPOINT</command></link>
683683
command.</entry>
684684
</row>
685+
<row>
686+
<entry>pg_maintain</entry>
687+
<entry>Allow executing
688+
<link linkend="sql-vacuum"><command>VACUUM</command></link>,
689+
<link linkend="sql-analyze"><command>ANALYZE</command></link>,
690+
<link linkend="sql-cluster"><command>CLUSTER</command></link>,
691+
<link linkend="sql-refreshmaterializedview"><command>REFRESH MATERIALIZED VIEW</command></link>,
692+
<link linkend="sql-reindex"><command>REINDEX</command></link>,
693+
and <link linkend="sql-lock"><command>LOCK TABLE</command></link> on all
694+
relations, as if having <literal>MAINTAIN</literal> rights on those
695+
objects, even without having it explicitly.</entry>
696+
</row>
685697
<row>
686698
<entry>pg_use_reserved_connections</entry>
687699
<entry>Allow use of connection slots reserved via

‎src/backend/catalog/aclchk.c

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2627,6 +2627,8 @@ string_to_privilege(const char *privname)
26272627
return ACL_SET;
26282628
if (strcmp(privname, "alter system") == 0)
26292629
return ACL_ALTER_SYSTEM;
2630+
if (strcmp(privname, "maintain") == 0)
2631+
return ACL_MAINTAIN;
26302632
if (strcmp(privname, "rule") == 0)
26312633
return 0; /* ignore old RULE privileges */
26322634
ereport(ERROR,
@@ -2668,6 +2670,8 @@ privilege_to_string(AclMode privilege)
26682670
return "SET";
26692671
case ACL_ALTER_SYSTEM:
26702672
return "ALTER SYSTEM";
2673+
case ACL_MAINTAIN:
2674+
return "MAINTAIN";
26712675
default:
26722676
elog(ERROR, "unrecognized privilege: %d", (int) privilege);
26732677
}
@@ -3426,6 +3430,17 @@ pg_class_aclmask_ext(Oid table_oid, Oid roleid, AclMode mask,
34263430
has_privs_of_role(roleid, ROLE_PG_WRITE_ALL_DATA))
34273431
result |= (mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE));
34283432

3433+
/*
3434+
* Check if ACL_MAINTAIN is being checked and, if so, and not already set
3435+
* as part of the result, then check if the user is a member of the
3436+
* pg_maintain role, which allows VACUUM, ANALYZE, CLUSTER, REFRESH
3437+
* MATERIALIZED VIEW, and REINDEX on all relations.
3438+
*/
3439+
if (mask & ACL_MAINTAIN &&
3440+
!(result & ACL_MAINTAIN) &&
3441+
has_privs_of_role(roleid, ROLE_PG_MAINTAIN))
3442+
result |= ACL_MAINTAIN;
3443+
34293444
return result;
34303445
}
34313446

‎src/backend/commands/analyze.c

Lines changed: 6 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -149,16 +149,15 @@ analyze_rel(Oid relid, RangeVar *relation,
149149
return;
150150

151151
/*
152-
* Check if relation needs to be skipped based on ownership. This check
152+
* Check if relation needs to be skipped based on privileges. This check
153153
* happens also when building the relation list to analyze for a manual
154154
* operation, and needs to be done additionally here as ANALYZE could
155-
* happen across multiple transactions where relation ownership could have
156-
* changed in-between. Make sure to generate only logs for ANALYZE in
157-
* this case.
155+
* happen across multiple transactions where privileges could have changed
156+
* in-between. Make sure to generate only logs for ANALYZE in this case.
158157
*/
159-
if (!vacuum_is_relation_owner(RelationGetRelid(onerel),
160-
onerel->rd_rel,
161-
params->options & VACOPT_ANALYZE))
158+
if (!vacuum_is_permitted_for_relation(RelationGetRelid(onerel),
159+
onerel->rd_rel,
160+
params->options & ~VACOPT_VACUUM))
162161
{
163162
relation_close(onerel, ShareUpdateExclusiveLock);
164163
return;

0 commit comments

Comments
 (0)