diff options
author | Jeff Davis <jdavis@postgresql.org> | 2022-12-13 17:33:28 -0800 |
---|---|---|
committer | Jeff Davis <jdavis@postgresql.org> | 2022-12-13 17:33:28 -0800 |
commit | 60684dd834a222fefedd49b19d1f0a6189c1632e (patch) | |
tree | a7452cf4aec03f4bed616662832ebcb8caac11a6 /doc/src | |
parent | c6f6646bb0bef315c3836f3f6909c24a985a8621 (diff) | |
download | postgresql-60684dd834a222fefedd49b19d1f0a6189c1632e.tar.gz postgresql-60684dd834a222fefedd49b19d1f0a6189c1632e.zip |
Add grantable MAINTAIN privilege and pg_maintain role.
Allows VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW, CLUSTER,
and LOCK TABLE.
Effectively reverts 4441fc704d. Instead of creating separate
privileges for VACUUM, ANALYZE, and other maintenance commands, group
them together under a single MAINTAIN privilege.
Author: Nathan Bossart
Discussion: https://postgr.es/m/20221212210136.GA449764@nathanxps13
Discussion: https://postgr.es/m/45224.1670476523@sss.pgh.pa.us
Diffstat (limited to 'doc/src')
-rw-r--r-- | doc/src/sgml/ddl.sgml | 42 | ||||
-rw-r--r-- | doc/src/sgml/func.sgml | 3 | ||||
-rw-r--r-- | doc/src/sgml/ref/alter_default_privileges.sgml | 4 | ||||
-rw-r--r-- | doc/src/sgml/ref/analyze.sgml | 9 | ||||
-rw-r--r-- | doc/src/sgml/ref/cluster.sgml | 8 | ||||
-rw-r--r-- | doc/src/sgml/ref/grant.sgml | 5 | ||||
-rw-r--r-- | doc/src/sgml/ref/lock.sgml | 16 | ||||
-rw-r--r-- | doc/src/sgml/ref/refresh_materialized_view.sgml | 5 | ||||
-rw-r--r-- | doc/src/sgml/ref/reindex.sgml | 13 | ||||
-rw-r--r-- | doc/src/sgml/ref/revoke.sgml | 2 | ||||
-rw-r--r-- | doc/src/sgml/ref/vacuum.sgml | 9 | ||||
-rw-r--r-- | doc/src/sgml/user-manag.sgml | 19 |
12 files changed, 67 insertions, 68 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 38618de01c5..6e92bbddd2a 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1692,8 +1692,7 @@ ALTER TABLE products RENAME TO items; <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>, - <literal>ALTER SYSTEM</literal>, <literal>VACUUM</literal>, and - <literal>ANALYZE</literal>. + <literal>ALTER SYSTEM</literal>, and <literal>MAINTAIN</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. @@ -1985,19 +1984,13 @@ REVOKE ALL ON accounts FROM PUBLIC; </varlistentry> <varlistentry> - <term><literal>VACUUM</literal></term> + <term><literal>MAINTAIN</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. + Allows <command>VACUUM</command>, <command>ANALYZE</command>, + <command>CLUSTER</command>, <command>REFRESH MATERIALIZED VIEW</command>, + <command>REINDEX</command>, and <command>LOCK TABLE</command> on a + relation. </para> </listitem> </varlistentry> @@ -2151,13 +2144,8 @@ REVOKE ALL ON accounts FROM PUBLIC; <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>MAINTAIN</literal></entry> + <entry><literal>m</literal></entry> <entry><literal>TABLE</literal></entry> </row> </tbody> @@ -2250,7 +2238,7 @@ REVOKE ALL ON accounts FROM PUBLIC; </row> <row> <entry><literal>TABLE</literal> (and table-like objects)</entry> - <entry><literal>arwdDxtvz</literal></entry> + <entry><literal>arwdDxtm</literal></entry> <entry>none</entry> <entry><literal>\dp</literal></entry> </row> @@ -2308,12 +2296,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=arwdDxtvz/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=arwdDxtm/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 ad31fdb737c..1cd8b11334d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -22995,8 +22995,7 @@ 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>, - <literal>TRIGGER</literal>, <literal>VACUUM</literal> and - <literal>ANALYZE</literal>. + <literal>TRIGGER</literal>, and <literal>MAINTAIN</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 0da295daffa..a33461fbc2f 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 | VACUUM | ANALYZE } +GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN } [, ...] | 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 | VACUUM | ANALYZE } + { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN } [, ...] | 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 16c0b886fd0..a26834da4f9 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -148,16 +148,15 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea <title>Notes</title> <para> - To analyze a table, one must ordinarily have the <literal>ANALYZE</literal> + To analyze a table, one must ordinarily have the <literal>MAINTAIN</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 + <link linkend="predefined-roles-table"><literal>pg_maintain</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 superusers and roles - with privileges of <literal>pg_analyze_all_tables</literal>.) + with privileges of <literal>pg_maintain</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/cluster.sgml b/doc/src/sgml/ref/cluster.sgml index c37f4236f17..145101e6a57 100644 --- a/doc/src/sgml/ref/cluster.sgml +++ b/doc/src/sgml/ref/cluster.sgml @@ -69,9 +69,11 @@ CLUSTER [VERBOSE] <para> <command>CLUSTER</command> without any parameter reclusters all the previously-clustered tables in the current database that the calling user - owns, or all such tables if called by a superuser. This - form of <command>CLUSTER</command> cannot be executed inside a transaction - block. + owns or has the <literal>MAINTAIN</literal> privilege for, or all such tables + if called by a superuser or a role with privileges of the + <link linkend="predefined-roles-table"><literal>pg_maintain</literal></link> + role. This form of <command>CLUSTER</command> cannot be + executed inside a transaction block. </para> <para> diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index c3c585be7ef..c8ca2b1d641 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 | VACUUM | ANALYZE } +GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...] | ALL TABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] } @@ -193,8 +193,7 @@ 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> + <term><literal>MAINTAIN</literal></term> <listitem> <para> Specific types of privileges, as defined in <xref linkend="ddl-priv"/>. diff --git a/doc/src/sgml/ref/lock.sgml b/doc/src/sgml/ref/lock.sgml index 19e71942071..d9c5bf9a1d4 100644 --- a/doc/src/sgml/ref/lock.sgml +++ b/doc/src/sgml/ref/lock.sgml @@ -165,11 +165,17 @@ LOCK [ TABLE ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ] <title>Notes</title> <para> - <literal>LOCK TABLE ... IN ACCESS SHARE MODE</literal> requires <literal>SELECT</literal> - privileges on the target table. <literal>LOCK TABLE ... IN ROW EXCLUSIVE - MODE</literal> requires <literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>, - or <literal>TRUNCATE</literal> privileges on the target table. All other forms of - <command>LOCK</command> require table-level <literal>UPDATE</literal>, <literal>DELETE</literal>, + To lock a table, one must ordinarily have the <literal>MAINTAIN</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_maintain</literal></link> + role. <literal>LOCK TABLE ... IN ACCESS SHARE MODE</literal> is allowed + with <literal>SELECT</literal> privileges on the target + table. <literal>LOCK TABLE ... IN ROW EXCLUSIVE MODE</literal> is allowed + with <literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>, + or <literal>TRUNCATE</literal> privileges on the target table. All other + forms of <command>LOCK</command> are allowed with + table-level <literal>UPDATE</literal>, <literal>DELETE</literal>, or <literal>TRUNCATE</literal> privileges. </para> diff --git a/doc/src/sgml/ref/refresh_materialized_view.sgml b/doc/src/sgml/ref/refresh_materialized_view.sgml index 675d6090f3c..4d79b6ae7f7 100644 --- a/doc/src/sgml/ref/refresh_materialized_view.sgml +++ b/doc/src/sgml/ref/refresh_materialized_view.sgml @@ -32,7 +32,10 @@ REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="parameter">name</ <para> <command>REFRESH MATERIALIZED VIEW</command> completely replaces the contents of a materialized view. To execute this command you must be the - owner of the materialized view. The old contents are discarded. If + owner of the materialized view, have privileges of the + <link linkend="predefined-roles-table"><literal>pg_maintain</literal></link> + role, or have the <literal>MAINTAIN</literal> + privilege on the materialized view. The old contents are discarded. If <literal>WITH DATA</literal> is specified (or defaults) the backing query is executed to provide the new data, and the materialized view is left in a scannable state. If <literal>WITH NO DATA</literal> is specified no new diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index fcbda881494..192513f34e0 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -293,15 +293,20 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { DA <para> Reindexing a single index or table requires being the owner of that - index or table. Reindexing a schema or database requires being the - owner of that schema or database. Note specifically that it's thus + index or table, having privileges of the + <link linkend="predefined-roles-table"><literal>pg_maintain</literal></link> + role, or having the <literal>MAINTAIN</literal> privilege on the + table. Reindexing a schema or database requires being the + owner of that schema or database or having privileges of the + <literal>pg_maintain</literal> role. Note specifically that it's thus possible for non-superusers to rebuild indexes of tables owned by other users. However, as a special exception, when <command>REINDEX DATABASE</command>, <command>REINDEX SCHEMA</command> or <command>REINDEX SYSTEM</command> is issued by a non-superuser, indexes on shared catalogs will be skipped unless the user owns the - catalog (which typically won't be the case). Of course, superusers - can always reindex anything. + catalog (which typically won't be the case), has privileges of the + <literal>pg_maintain</literal> role, or has the <literal>MAINTAIN</literal> + privilege on the catalog. Of course, superusers can always reindex anything. </para> <para> diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml index e28d192fd30..8df492281a1 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 | VACUUM | ANALYZE } + { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN } [, ...] | 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 9cd880ea34a..e14ead88267 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -356,16 +356,15 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet <title>Notes</title> <para> - To vacuum a table, one must ordinarily have the <literal>VACUUM</literal> + To vacuum a table, one must ordinarily have the <literal>MAINTAIN</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 + <link linkend="predefined-roles-table"><literal>pg_maintain</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 superusers and roles - with privileges of <literal>pg_vacuum_all_tables</literal>.) + with privileges of <literal>pg_maintain</literal>.) <command>VACUUM</command> will skip over any tables that the calling user does not have permission to vacuum. </para> diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index 2bff4e47d07..77159879c7c 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -636,16 +636,15 @@ DROP ROLE doomed_role; 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> + <entry>pg_maintain</entry> + <entry>Allow executing + <link linkend="sql-vacuum"><command>VACUUM</command></link>, + <link linkend="sql-analyze"><command>ANALYZE</command></link>, + <link linkend="sql-cluster"><command>CLUSTER</command></link>, + <link linkend="sql-refreshmaterializedview"><command>REFRESH MATERIALIZED VIEW</command></link>, + <link linkend="sql-reindex"><command>REINDEX</command></link>, + and <link linkend="sql-lock"><command>LOCK TABLE</command></link> on all + relations.</entry> </row> </tbody> </tgroup> |