From ecb0fd33720fab91df1207e85704f382f55e1eb7 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Wed, 13 Mar 2024 14:49:26 -0500 Subject: 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 151c22deee due to concerns about search_path tricks that could be used to escalate privileges to the table owner. Commits 2af07e2f74, 59825d1639, and c7ea3f4229 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 --- doc/src/sgml/ddl.sgml | 35 ++++++++++++++++++------- doc/src/sgml/func.sgml | 2 +- doc/src/sgml/ref/alter_default_privileges.sgml | 4 +-- doc/src/sgml/ref/analyze.sgml | 6 ++--- doc/src/sgml/ref/cluster.sgml | 10 ++++--- doc/src/sgml/ref/grant.sgml | 3 ++- doc/src/sgml/ref/lock.sgml | 4 +-- doc/src/sgml/ref/refresh_materialized_view.sgml | 5 ++-- doc/src/sgml/ref/reindex.sgml | 23 +++++++++------- doc/src/sgml/ref/revoke.sgml | 2 +- doc/src/sgml/ref/vacuum.sgml | 6 ++--- doc/src/sgml/user-manag.sgml | 12 +++++++++ 12 files changed, 74 insertions(+), 38 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 9d7e2c756be..8616a8e9cc9 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1868,8 +1868,8 @@ ALTER TABLE products RENAME TO items; INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, - EXECUTE, USAGE, SET - and ALTER SYSTEM. + EXECUTE, USAGE, SET, + ALTER SYSTEM, and MAINTAIN. 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. @@ -2160,7 +2160,19 @@ REVOKE ALL ON accounts FROM PUBLIC; - + + + MAINTAIN + + + Allows VACUUM, ANALYZE, + CLUSTER, REFRESH MATERIALIZED VIEW, + REINDEX, and LOCK TABLE on a + relation. + + + + The privileges required by other commands are listed on the reference page of the respective command. @@ -2309,6 +2321,11 @@ REVOKE ALL ON accounts FROM PUBLIC; A PARAMETER + + MAINTAIN + m + TABLE + @@ -2399,7 +2416,7 @@ REVOKE ALL ON accounts FROM PUBLIC; TABLE (and table-like objects) - arwdDxt + arwdDxtm none \dp @@ -2465,11 +2482,11 @@ GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw; => \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 | | + 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) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 0bb7aeb40ec..91f1e693594 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -24232,7 +24232,7 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute'); are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, - and TRIGGER. + TRIGGER, and MAINTAIN. diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml index 78744470c8d..1de4c5c1b4e 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 where abbreviated_grant_or_revoke is one of: -GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } +GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN } [, ...] | ALL [ PRIVILEGES ] } ON TABLES TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] @@ -51,7 +51,7 @@ GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] REVOKE [ GRANT OPTION FOR ] - { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } + { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN } [, ...] | ALL [ PRIVILEGES ] } ON TABLES FROM { [ GROUP ] role_name | PUBLIC } [, ...] diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index bad121a1f19..2b94b378e9f 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -174,11 +174,9 @@ ANALYZE [ ( option [, ...] ) ] [ Notes - To analyze a table, one must ordinarily be the table's owner or a - superuser. However, database owners are allowed to + To analyze a table, one must ordinarily have the MAINTAIN + 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 - ANALYZE can only be performed by a superuser.) ANALYZE will skip over any tables that the calling user does not have permission to analyze. diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml index 24340e7b9bf..c5760244e67 100644 --- a/doc/src/sgml/ref/cluster.sgml +++ b/doc/src/sgml/ref/cluster.sgml @@ -68,9 +68,8 @@ CLUSTER [ ( option [, ...] ) ] [ CLUSTER without a table_name 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 CLUSTER cannot be executed inside a transaction - block. + has privileges for. This form of CLUSTER cannot be + executed inside a transaction block. @@ -131,6 +130,11 @@ CLUSTER [ ( option [, ...] ) ] [ Notes + + To cluster a table, one must have the MAINTAIN privilege + on the table. + + In cases where you are accessing single rows randomly within a table, the actual order of the data in the diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index 9d27b7fcde5..65b1fe77119 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } +GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } @@ -193,6 +193,7 @@ GRANT role_name [, ...] TO USAGE SET ALTER SYSTEM + MAINTAIN Specific types of privileges, as defined in . diff --git a/doc/src/sgml/ref/lock.sgml b/doc/src/sgml/ref/lock.sgml index 6ce2518de74..070855da18b 100644 --- a/doc/src/sgml/ref/lock.sgml +++ b/doc/src/sgml/ref/lock.sgml @@ -166,8 +166,8 @@ LOCK [ TABLE ] [ ONLY ] name [ * ] To lock a table, the user must have the right privilege for the specified - lockmode, or be the table's - owner or a superuser. If the user has + lockmode. + If the user has MAINTAIN, UPDATE, DELETE, or TRUNCATE privileges on the table, any lockmode is permitted. If the user has diff --git a/doc/src/sgml/ref/refresh_materialized_view.sgml b/doc/src/sgml/ref/refresh_materialized_view.sgml index e4e5145058f..8ed43ade803 100644 --- a/doc/src/sgml/ref/refresh_materialized_view.sgml +++ b/doc/src/sgml/ref/refresh_materialized_view.sgml @@ -31,8 +31,9 @@ REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name REFRESH MATERIALIZED VIEW 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 + contents of a materialized view. To execute this command you must have the + MAINTAIN + privilege on the materialized view. The old contents are discarded. If WITH DATA 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 WITH NO DATA is specified no new diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index b214861742b..2942dccf1e2 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -298,16 +298,21 @@ REINDEX [ ( option [, ...] ) ] { DA - 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 + Reindexing a single index or table requires + having the MAINTAIN privilege on the + table. Note that while REINDEX on a partitioned index or + table requires having the MAINTAIN privilege on the + partitioned table, such commands skip the privilege checks when processing + the individual partitions. Reindexing a schema or database requires being the + owner of that schema or database or having privileges of the + pg_maintain + 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 - REINDEX DATABASE, REINDEX SCHEMA - or REINDEX SYSTEM 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. + other users. However, as a special exception, + REINDEX DATABASE, REINDEX SCHEMA, + and REINDEX SYSTEM will skip indexes on shared catalogs + unless the user has the MAINTAIN privilege on the + catalog. diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml index 2db66bbf378..8df492281a1 100644 --- a/doc/src/sgml/ref/revoke.sgml +++ b/doc/src/sgml/ref/revoke.sgml @@ -22,7 +22,7 @@ PostgreSQL documentation REVOKE [ GRANT OPTION FOR ] - { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } + { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index df0dd71a370..9857b35627b 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -434,11 +434,9 @@ VACUUM [ ( option [, ...] ) ] [ Notes - To vacuum a table, one must ordinarily be the table's owner or a - superuser. However, database owners are allowed to + To vacuum a table, one must ordinarily have the MAINTAIN + 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 - VACUUM can only be performed by a superuser.) VACUUM will skip over any tables that the calling user does not have permission to vacuum. diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index e026a4e2717..07a16247d76 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -682,6 +682,18 @@ DROP ROLE doomed_role; the CHECKPOINT command. + + pg_maintain + Allow executing + VACUUM, + ANALYZE, + CLUSTER, + REFRESH MATERIALIZED VIEW, + REINDEX, + and LOCK TABLE on all + relations, as if having MAINTAIN rights on those + objects, even without having it explicitly. + pg_use_reserved_connections Allow use of connection slots reserved via -- cgit v1.2.3