From 3cdf7502f85c9e61913e05519f88580f008b8453 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Mon, 16 Jan 2023 10:35:29 -0500 Subject: [PATCH] More documentation update for GRANT ... WITH SET OPTION. Update the reference pages for various ALTER commands that mentioned that you must be a member of role that will be the new owner to instead say that you must be able to SET ROLE to the new owner. Update ddl.sgml's generate statement on this topic along similar lines. Likewise, update CREATE SCHEMA and CREATE DATABASE, which have options to specify who will own the new objects, to say that you must be able to SET ROLE to the role that will own them. Finally, update the documentation for the GRANT statement itself with some general principles about how the SET option works and how it can be used. Patch by me, reviewed (but not fully endorsed) by Noah Misch. Discussion: http://postgr.es/m/CA+TgmoZk6VB3DQ83+DO5P_HP=M9PQAh1yj-KgeV30uKefVaWDg@mail.gmail.com --- doc/src/sgml/ddl.sgml | 4 ++-- doc/src/sgml/ref/alter_aggregate.sgml | 7 ++++--- doc/src/sgml/ref/alter_collation.sgml | 7 ++++--- doc/src/sgml/ref/alter_conversion.sgml | 7 ++++--- doc/src/sgml/ref/alter_database.sgml | 4 ++-- doc/src/sgml/ref/alter_domain.sgml | 6 +++--- doc/src/sgml/ref/alter_foreign_table.sgml | 6 +++--- doc/src/sgml/ref/alter_function.sgml | 6 +++--- doc/src/sgml/ref/alter_large_object.sgml | 5 +++-- doc/src/sgml/ref/alter_materialized_view.sgml | 7 ++++--- doc/src/sgml/ref/alter_opclass.sgml | 7 ++++--- doc/src/sgml/ref/alter_operator.sgml | 7 ++++--- doc/src/sgml/ref/alter_procedure.sgml | 7 ++++--- doc/src/sgml/ref/alter_publication.sgml | 10 ++++++---- doc/src/sgml/ref/alter_schema.sgml | 4 ++-- doc/src/sgml/ref/alter_sequence.sgml | 7 ++++--- doc/src/sgml/ref/alter_server.sgml | 4 ++-- doc/src/sgml/ref/alter_statistics.sgml | 7 ++++--- doc/src/sgml/ref/alter_subscription.sgml | 4 ++-- doc/src/sgml/ref/alter_table.sgml | 7 ++++--- doc/src/sgml/ref/alter_tablespace.sgml | 4 ++-- doc/src/sgml/ref/alter_type.sgml | 7 ++++--- doc/src/sgml/ref/alter_view.sgml | 7 ++++--- doc/src/sgml/ref/create_database.sgml | 4 ++-- doc/src/sgml/ref/create_schema.sgml | 4 ++-- doc/src/sgml/ref/grant.sgml | 14 ++++++++++++++ 26 files changed, 96 insertions(+), 67 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index db40c47077..8dc8d7a0ce 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1741,8 +1741,8 @@ ALTER TABLE products RENAME TO items; ALTER TABLE table_name OWNER TO new_owner; Superusers can always do this; ordinary roles can only do it if they are - both the current owner of the object (or a member of the owning role) and - a member of the new owning role. + both the current owner of the object (or inherit the privileges of the + owning role) and able to SET ROLE to the new owning role. diff --git a/doc/src/sgml/ref/alter_aggregate.sgml b/doc/src/sgml/ref/alter_aggregate.sgml index aee10a5ca2..d0a39ba7b5 100644 --- a/doc/src/sgml/ref/alter_aggregate.sgml +++ b/doc/src/sgml/ref/alter_aggregate.sgml @@ -46,9 +46,10 @@ ALTER AGGREGATE name ( aggregate_signatu You must own the aggregate function to use ALTER AGGREGATE. To change the schema of an aggregate function, you must also have CREATE privilege on the new schema. - To alter the owner, you must also be a direct or indirect member of the new - owning role, and that role must have CREATE privilege on - the aggregate function's schema. (These restrictions enforce that altering + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have CREATE + privilege on the aggregate function's schema. + (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the aggregate function. However, a superuser can alter ownership of any aggregate function anyway.) diff --git a/doc/src/sgml/ref/alter_collation.sgml b/doc/src/sgml/ref/alter_collation.sgml index a8c831d728..a40a31442a 100644 --- a/doc/src/sgml/ref/alter_collation.sgml +++ b/doc/src/sgml/ref/alter_collation.sgml @@ -39,9 +39,10 @@ ALTER COLLATION name SET SCHEMA new_sche You must own the collation to use ALTER COLLATION. - To alter the owner, you must also be a direct or indirect member of the new - owning role, and that role must have CREATE privilege on - the collation's schema. (These restrictions enforce that altering the + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have CREATE + privilege on the collation's schema. + (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the collation. However, a superuser can alter ownership of any collation anyway.) diff --git a/doc/src/sgml/ref/alter_conversion.sgml b/doc/src/sgml/ref/alter_conversion.sgml index a128f20f3e..5c7cc978ee 100644 --- a/doc/src/sgml/ref/alter_conversion.sgml +++ b/doc/src/sgml/ref/alter_conversion.sgml @@ -37,9 +37,10 @@ ALTER CONVERSION name SET SCHEMA new_sch You must own the conversion to use ALTER CONVERSION. - To alter the owner, you must also be a direct or indirect member of the new - owning role, and that role must have CREATE privilege on - the conversion's schema. (These restrictions enforce that altering the + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have CREATE + privilege on the conversion's schema. + (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the conversion. However, a superuser can alter ownership of any conversion anyway.) diff --git a/doc/src/sgml/ref/alter_database.sgml b/doc/src/sgml/ref/alter_database.sgml index 0962f32e13..5144e1f4ea 100644 --- a/doc/src/sgml/ref/alter_database.sgml +++ b/doc/src/sgml/ref/alter_database.sgml @@ -68,8 +68,8 @@ ALTER DATABASE name RESET ALL The third form changes the owner of the database. - To alter the owner, you must own the database and also be a direct or - indirect member of the new owning role, and you must have the + To alter the owner, you must be able to SET ROLE to the + new owning role, and you must have the CREATEDB privilege. (Note that superusers have all these privileges automatically.) diff --git a/doc/src/sgml/ref/alter_domain.sgml b/doc/src/sgml/ref/alter_domain.sgml index 2db5372513..f6704d7557 100644 --- a/doc/src/sgml/ref/alter_domain.sgml +++ b/doc/src/sgml/ref/alter_domain.sgml @@ -157,9 +157,9 @@ ALTER DOMAIN name You must own the domain to use ALTER DOMAIN. To change the schema of a domain, you must also have CREATE privilege on the new schema. - To alter the owner, you must also be a direct or indirect member of the new - owning role, and that role must have CREATE privilege on - the domain's schema. (These restrictions enforce that altering the owner + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have CREATE privilege + on the domain's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the domain. However, a superuser can alter ownership of any domain anyway.) diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml b/doc/src/sgml/ref/alter_foreign_table.sgml index d056dc1bb1..0f4191713a 100644 --- a/doc/src/sgml/ref/alter_foreign_table.sgml +++ b/doc/src/sgml/ref/alter_foreign_table.sgml @@ -320,9 +320,9 @@ ALTER FOREIGN TABLE [ IF EXISTS ] nameALTER FOREIGN TABLE. To change the schema of a foreign table, you must also have CREATE privilege on the new schema. - To alter the owner, you must also be a direct or indirect member of the new - owning role, and that role must have CREATE privilege on - the table's schema. (These restrictions enforce that altering the owner + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have CREATE privilege + on the table's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the table. However, a superuser can alter ownership of any table anyway.) To add a column or alter a column type, you must also diff --git a/doc/src/sgml/ref/alter_function.sgml b/doc/src/sgml/ref/alter_function.sgml index 2e8e1162d8..8193b17f25 100644 --- a/doc/src/sgml/ref/alter_function.sgml +++ b/doc/src/sgml/ref/alter_function.sgml @@ -60,9 +60,9 @@ ALTER FUNCTION name [ ( [ [ large_object_oid You must own the large object to use ALTER LARGE OBJECT. - To alter the owner, you must also be a direct or indirect member of the new - owning role. (However, a superuser can alter any large object anyway.) + To alter the owner, you must also be able to SET ROLE to + the new owning role. + (However, a superuser can alter any large object anyway.) Currently, the only functionality is to assign a new owner, so both restrictions always apply. diff --git a/doc/src/sgml/ref/alter_materialized_view.sgml b/doc/src/sgml/ref/alter_materialized_view.sgml index 040ae53f98..da7ed04597 100644 --- a/doc/src/sgml/ref/alter_materialized_view.sgml +++ b/doc/src/sgml/ref/alter_materialized_view.sgml @@ -63,9 +63,10 @@ ALTER MATERIALIZED VIEW ALL IN TABLESPACE nameALTER MATERIALIZED VIEW. To change a materialized view's schema, you must also have CREATE privilege on the new schema. - To alter the owner, you must also be a direct or indirect member of the new - owning role, and that role must have CREATE privilege on - the materialized view's schema. (These restrictions enforce that altering + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have CREATE + privilege on the materialized view's schema. + (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the materialized view. However, a superuser can alter ownership of any view anyway.) diff --git a/doc/src/sgml/ref/alter_opclass.sgml b/doc/src/sgml/ref/alter_opclass.sgml index b1db459b11..231597d629 100644 --- a/doc/src/sgml/ref/alter_opclass.sgml +++ b/doc/src/sgml/ref/alter_opclass.sgml @@ -42,9 +42,10 @@ ALTER OPERATOR CLASS name USING ALTER PROCEDURE. To change a procedure's schema, you must also have CREATE privilege on the new schema. - To alter the owner, you must also be a direct or indirect member of the new - owning role, and that role must have CREATE privilege on - the procedure's schema. (These restrictions enforce that altering the owner + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have CREATE + privilege on the procedure's schema. + (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the procedure. However, a superuser can alter ownership of any procedure anyway.) diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml index c84b11f47a..cd20868bca 100644 --- a/doc/src/sgml/ref/alter_publication.sgml +++ b/doc/src/sgml/ref/alter_publication.sgml @@ -75,10 +75,12 @@ ALTER PUBLICATION name RENAME TO ADD TABLES IN SCHEMA and SET TABLES IN SCHEMA to a publication requires the - invoking user to be a superuser. To alter the owner, you must also be a - direct or indirect member of the new owning role. The new owner must have - CREATE privilege on the database. Also, the new owner - of a FOR ALL TABLES or FOR TABLES IN SCHEMA + invoking user to be a superuser. + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have CREATE + privilege on the database. + Also, the new owner of a FOR ALL TABLES or + FOR TABLES IN SCHEMA publication must be a superuser. However, a superuser can change the ownership of a publication regardless of these restrictions. diff --git a/doc/src/sgml/ref/alter_schema.sgml b/doc/src/sgml/ref/alter_schema.sgml index 04624c5a5e..b8ace0561f 100644 --- a/doc/src/sgml/ref/alter_schema.sgml +++ b/doc/src/sgml/ref/alter_schema.sgml @@ -37,8 +37,8 @@ ALTER SCHEMA name OWNER TO { new_ownerALTER SCHEMA. To rename a schema you must also have the CREATE privilege for the database. - To alter the owner, you must also be a direct or - indirect member of the new owning role, and you must have the + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have the CREATE privilege for the database. (Note that superusers have all these privileges automatically.) diff --git a/doc/src/sgml/ref/alter_sequence.sgml b/doc/src/sgml/ref/alter_sequence.sgml index 148085d4f2..7be36cf466 100644 --- a/doc/src/sgml/ref/alter_sequence.sgml +++ b/doc/src/sgml/ref/alter_sequence.sgml @@ -51,9 +51,10 @@ ALTER SEQUENCE [ IF EXISTS ] name S You must own the sequence to use ALTER SEQUENCE. To change a sequence's schema, you must also have CREATE privilege on the new schema. - To alter the owner, you must also be a direct or indirect member of the new - owning role, and that role must have CREATE privilege on - the sequence's schema. (These restrictions enforce that altering the owner + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have CREATE + privilege on the sequence's schema. + (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the sequence. However, a superuser can alter ownership of any sequence anyway.) diff --git a/doc/src/sgml/ref/alter_server.sgml b/doc/src/sgml/ref/alter_server.sgml index 186f38b5f8..467bf85589 100644 --- a/doc/src/sgml/ref/alter_server.sgml +++ b/doc/src/sgml/ref/alter_server.sgml @@ -40,8 +40,8 @@ ALTER SERVER name RENAME TO To alter the server you must be the owner of the server. - Additionally to alter the owner, you must own the server and also - be a direct or indirect member of the new owning role, and you must + Additionally to alter the owner, you must be able to + SET ROLE to the new owning role, and you must have USAGE privilege on the server's foreign-data wrapper. (Note that superusers satisfy all these criteria automatically.) diff --git a/doc/src/sgml/ref/alter_statistics.sgml b/doc/src/sgml/ref/alter_statistics.sgml index ce6cdf2bb1..73cc9e830d 100644 --- a/doc/src/sgml/ref/alter_statistics.sgml +++ b/doc/src/sgml/ref/alter_statistics.sgml @@ -43,9 +43,10 @@ ALTER STATISTICS name SET STATISTIC You must own the statistics object to use ALTER STATISTICS. To change a statistics object's schema, you must also have CREATE privilege on the new schema. - To alter the owner, you must also be a direct or indirect member of the new - owning role, and that role must have CREATE privilege on - the statistics object's schema. (These restrictions enforce that altering + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have CREATE + privilege on the statistics object's schema. + (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the statistics object. However, a superuser can alter ownership of any statistics object anyway.) diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml index 1e8d72062b..ad93553a1d 100644 --- a/doc/src/sgml/ref/alter_subscription.sgml +++ b/doc/src/sgml/ref/alter_subscription.sgml @@ -46,8 +46,8 @@ ALTER SUBSCRIPTION name RENAME TO < You must own the subscription to use ALTER SUBSCRIPTION. - To alter the owner, you must also be a direct or indirect member of the - new owning role. The new owner has to be a superuser. + To alter the owner, you must be able to SET ROLE to the + new owning role. The new owner has to be a superuser. (Currently, all subscription owners must be superusers, so the owner checks will be bypassed in practice. But this might change in the future.) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 794e886f96..9aaa32a782 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1106,9 +1106,10 @@ WITH ( MODULUS numeric_literal, REM To add the table as a new child of a parent table, you must own the parent table as well. Also, to attach a table as a new partition of the table, you must own the table being attached. - To alter the owner, you must also be a direct or indirect member of the new - owning role, and that role must have CREATE privilege on - the table's schema. (These restrictions enforce that altering the owner + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have CREATE + privilege on the table's schema. + (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the table. However, a superuser can alter ownership of any table anyway.) To add a column or alter a column type or use the OF diff --git a/doc/src/sgml/ref/alter_tablespace.sgml b/doc/src/sgml/ref/alter_tablespace.sgml index 6de80746d5..6ec863400d 100644 --- a/doc/src/sgml/ref/alter_tablespace.sgml +++ b/doc/src/sgml/ref/alter_tablespace.sgml @@ -38,8 +38,8 @@ ALTER TABLESPACE name RESET ( name SET ( ALTER TYPE. To change the schema of a type, you must also have CREATE privilege on the new schema. - To alter the owner, you must also be a direct or indirect member of the new - owning role, and that role must have CREATE privilege on - the type's schema. (These restrictions enforce that altering the owner + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have CREATE + privilege on the type's schema. + (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the type. However, a superuser can alter ownership of any type anyway.) To add an attribute or alter an attribute type, you must also diff --git a/doc/src/sgml/ref/alter_view.sgml b/doc/src/sgml/ref/alter_view.sgml index 8bdc90a5a1..afbb3d02c7 100644 --- a/doc/src/sgml/ref/alter_view.sgml +++ b/doc/src/sgml/ref/alter_view.sgml @@ -45,9 +45,10 @@ ALTER VIEW [ IF EXISTS ] name RESET You must own the view to use ALTER VIEW. To change a view's schema, you must also have CREATE privilege on the new schema. - To alter the owner, you must also be a direct or indirect member of the new - owning role, and that role must have CREATE privilege on - the view's schema. (These restrictions enforce that altering the owner + To alter the owner, you must be able to SET ROLE to the + new owning role, and that role must have CREATE + privilege on the view's schema. + (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the view. However, a superuser can alter ownership of any view anyway.) diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml index 2f034e2859..f3df2def86 100644 --- a/doc/src/sgml/ref/create_database.sgml +++ b/doc/src/sgml/ref/create_database.sgml @@ -89,8 +89,8 @@ CREATE DATABASE name The role name of the user who will own the new database, or DEFAULT to use the default (namely, the user executing the command). To create a database owned by another - role, you must be a direct or indirect member of that role, - or be a superuser. + role, you must must be able to SET ROLE to that + role. diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml index 3c2dddb163..04b0c28731 100644 --- a/doc/src/sgml/ref/create_schema.sgml +++ b/doc/src/sgml/ref/create_schema.sgml @@ -89,8 +89,8 @@ CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_sp The role name of the user who will own the new schema. If omitted, defaults to the user executing the command. To create a schema - owned by another role, you must be a direct or indirect member of - that role, or be a superuser. + owned by another role, you must must be able to + SET ROLE to that role. diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index 85f5f42ea6..35bf0332c8 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -298,6 +298,20 @@ GRANT role_name [, ...] TO TRUE. + + To create an object owned by another role or give ownership of an existing + object to another role, you must have the ability to SET + ROLE to that role; otherwise, commands such as ALTER + ... OWNER TO or CREATE DATABASE ... OWNER + will fail. However, a user who inherits the privileges of a role but does + not have the ability to SET ROLE to that role may be + able to obtain full access to the role by manipulating existing objects + owned by that role (e.g. they could redefine an existing function to act + as a Trojan horse). Therefore, if a role's privileges are to be inherited + but should not be accessible via SET ROLE, it should not + own any SQL objects. + + If GRANTED BY is specified, the grant is recorded as having been done by the specified role. A user can only attribute a grant -- 2.39.5