Re: SQL command : ALTER DATABASE OWNER TO

On Wed, 2024-01-24 at 16:04 -0700, David G. Johnston wrote:
> 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>

Ok, you want to describe that in more detail. But you should preserve
the <firstterm> when the term is used for the first time.

>    <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>,

We use "privilege" for the rights you can grant everywhere else, so you'd
have to change that all over the documentation. I don't think we should
randomly change our terminology. I see that you didn't change the <indexterm>.

I understand that you want to disambiguate between "grantable privileges"
and "inherent privileges" like the right to drop an object.

Note that the documentation is careful to avoid the term "privilege"
when speaking about the latter: below, it says "The *right* to modify or
destroy an object is inherent in being the object's owner".

We should leave that as it is.

> @@ -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>
> +

Perhaps it would be better to say "has the privileges" than "is granted
the privileges", because there is nothing specifically granted (the ACL is NULL).

Also, I don't think we should burden the user with ALTER DEFAULT PRIVILEGES
right here, where we speak about ownership. Default privileges are discribed
in some detail further down, which I believe is sufficient.

In the same vein, the page describes further down that "An object's owner can
choose to revoke their own ordinary privileges, for example ...". Again, no
need to mention it another time (before it is discussed).

>    <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>

See above.

> +  <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>
> +

Again, that's mostly a repetition: "Ordinarily, only the object's owner (or a superuser)
can grant or revoke privileges on an object."

> @@ -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>

This change is fundamentally OK, although I doubt that we need to get as detailed
as to how multiple access control items get consolidated.

I think we should say "owner" instead of "recorded owner". Also, is it necessary
to detail to the level of system catalog columns?

Yours,
Laurenz Albe