Re: SQL command : ALTER DATABASE OWNER TO

On Wed, Jan 24, 2024 at 7:23 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Wed, 2024-01-24 at 11:08 +0100, gparc(at)free(dot)fr wrote:
> > for this "ALTER DATABASE" form, it should be mentioned that after
> execution of the command,
> > the old database owner loses all his privileges on it (even connection)
> although it might
> > still owns schemas or objects (tables, index,...) inside it.
> >
> > Thanks in advance to add this important precision.
>
> How about this:
>
> diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
> index 4044f0908f..44042f863c 100644
> --- a/doc/src/sgml/ddl.sgml
> +++ b/doc/src/sgml/ddl.sgml
> @@ -1891,6 +1891,8 @@ ALTER TABLE <replaceable>table_name</replaceable>
> OWNER TO <replaceable>new_owne
> Superusers can always do this; ordinary roles can only do it if they
> are
> both the current owner of the object (or inherit the privileges of the
> owning role) and able to <literal>SET ROLE</literal> to the new owning
> role.
> + All object privileges of the old owner are transferred to the new owner
> + along with the ownership.
> </para>
>
> <para>
>
>
Here's a slightly more detailed patch to consider to cover both the
transference of ownership as well as documenting precisely what ownership
means.

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index fc03a349f0..c8866ee9c7 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1856,15 +1856,12 @@ ALTER TABLE products RENAME TO items;

<para>
When an object is created, it is assigned an owner. The
- owner is normally the role that executed the creation statement.
- For most kinds of objects, the initial state is that only the owner
- (or a superuser) can do anything with the object. To allow
- other roles to use it, <firstterm>privileges</firstterm> must be
- granted.
+ owner is the role that executed the creation statement
+ unless the statement itself specifies an owner.
</para>

<para>
- There are different kinds of privileges: <literal>SELECT</literal>,
+ There are different kinds of grantable privileges:
<literal>SELECT</literal>,
<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>,
@@ -1877,13 +1874,27 @@ ALTER TABLE products RENAME TO items;
these privileges are used.
</para>

+ <para>
+ Upon object creation the owner is granted all grantable privileges
+ on the object. Additionally, the built-in PUBLIC privileges of
+ the associated object type are granted. Lastly, if any have been
defined,
+ the system grants the default privileges for the object type to the
defined roles.
+ All of these privileges can be revoked.
+ </para>
+
<para>
The right to modify or destroy an object is inherent in being the
object's owner, and cannot be granted or revoked in itself.
- (However, like all privileges, that right can be inherited by
+ (However, like the grantable privileges, that right can be inherited by
members of the owning role; see <xref linkend="role-membership"/>.)
</para>

+ <para>
+ Another inherent right the owner of an object has is to grant all
+ grantable privileges on that object to any database role, including
+ their own.
+ </para>
+
<para>
An object can be assigned to a new owner with an
<command>ALTER</command>
command of the appropriate kind for the object, for example
@@ -1893,6 +1904,11 @@ ALTER TABLE <replaceable>table_name</replaceable>
OWNER TO <replaceable>new_owne
Superusers can always do this; ordinary roles can only do it if they are
both the current owner of the object (or inherit the privileges of the
owning role) and able to <literal>SET ROLE</literal> to the new owning
role.
+ The reassignment process involves changing the recorded owner of the
object in
+ the appropriate system catalog, as well as changing all references
+ (grantor and grantee) to the old role in the Access Control List (ACL,
see below)
+ column to the new role; leaving the old role without any direct
privileges on the object.
+ Multiple privilege entries with the same grantor and grantee are
consolidated into a single entry.
</para>

<para>

Laurenz has already commented to my accidentally off-list initial post that
this seems to be too much detail for this section. But it is the language
specification Chapter, not the Tutorial, and I disagree. It doesn't seem
like an internals topic and there just isn't anywhere else to define this
stuff. The man pages I suppose work, and this does have some repetition of
the material there, but personally this feels like the more correct spot.
Some of the "where" language probably can be removed without loss of
clarity but I haven't added anything here that isn't already described in
even more detail at the end of this section. I just used that material in
context.

David J.