Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rSmJL-00FqcZ-4X for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 23:05:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rSmJJ-009T31-0Z for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 23:05:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rSmJI-009T2t-Ne for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 23:05:32 +0000 Received: from mail-ot1-x32a.google.com ([2607:f8b0:4864:20::32a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rSmJG-003YA5-1V for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 23:05:32 +0000 Received: by mail-ot1-x32a.google.com with SMTP id 46e09a7af769-6ddef319fabso3995574a34.1 for ; Wed, 24 Jan 2024 15:05:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1706137528; x=1706742328; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Q370BI85HkvNCaP59rcf/DMHEPq65lUhYLWt2fXB9qg=; b=OgandTRXyxVYTKSKqcLruLRhXekbq1p57bJPSo31n+DqYrhvymDnROXiKd4lB+EU6d 5dMMZ5s3Jj/r8CFKFj/f15yutbfLpBwapd1mp5ho0BZdAYKZ1jLlBvtkp/Mq+A5iLSRd nfEJ2GTwYaDIXL1vOukJZEdSqkzr7FKKESgJ0VlVrn2Ixpd509OFaLZxQ20rNuoBrD19 TlfkB8gOM4o9qUOXr5sW/+DYJeo2QrwBntgEnsNK1nYYped1/lD31uwJAgp2gNnXoIRU +1dCokPzRx8np3ypbP8QHjuestSXmBoNa5SCGGJv+kJA4RJaSLe4UjoL2pEY4tcxg1qx ad7A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1706137528; x=1706742328; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Q370BI85HkvNCaP59rcf/DMHEPq65lUhYLWt2fXB9qg=; b=CMeX8glFS1VqWlJ+gvP3vSydHDYCY0SyEVXnDTl0w+89cD+pek88qwHBAUj4Gyepsk 3EqSRAgdChDTAgiTaG86fLKxmL3iwCmx1PaPXa4bd+OuFzFh04R/CCkV6zGf7QT0NUqs H3hjvy9hzYrzmxPSWCvtvzgr0o6IH6/M/ABZr8rRZySMNjYX6eAv59lGGCz3EMZdZ1h6 0mYvmoAYytZlcdw8Wf64Y43mw1u21/eRZwM4IC24o0cd057XFDrYFOVhU0Eij2jt40zZ LdI3Ucg6fu0OvJVL4ST6QPGpg2dTeiPltz07PTaVIyK7BoIVHnUAUZBfftjloUbAcgMj FMNw== X-Gm-Message-State: AOJu0YyKbbdnH3S39XZz2MA3AAT848qj/fP4EJMK1wDIP7yXystWeJ8B D4jFxNUF6+RPDTCQSXjtgKzp/S8Y5mqNQIZPVvh3qdfoJ38bOLhye5QBkrfEFXfGIP5jGG+pxiX OdiMjg6nu7BBH2Ks289Uc/Rf4Cg0= X-Google-Smtp-Source: AGHT+IE7TXzv5lEZPdXRcconl7rz/3+di3ouzgOlJGKguc89z1EAge+MY/HOm1YDIjCSlsivxKRbRefc7gUuXVy5PPo= X-Received: by 2002:a05:6830:1d83:b0:6dc:2aa:822f with SMTP id y3-20020a0568301d8300b006dc02aa822fmr2103201oti.30.1706137527771; Wed, 24 Jan 2024 15:05:27 -0800 (PST) MIME-Version: 1.0 References: <2023185982.281851219.1646733038464.JavaMail.root@zimbra15-e2.priv.proxad.net> <1712096587.208766558.1706090901073.JavaMail.zimbra@free.fr> In-Reply-To: From: "David G. Johnston" Date: Wed, 24 Jan 2024 16:04:51 -0700 Message-ID: Subject: Re: SQL command : ALTER DATABASE OWNER TO To: Laurenz Albe Cc: gparc@free.fr, pgsql-docs Content-Type: multipart/alternative; boundary="00000000000047e87a060fb919b8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000047e87a060fb919b8 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jan 24, 2024 at 7:23=E2=80=AFAM Laurenz Albe wrote: > On Wed, 2024-01-24 at 11:08 +0100, gparc@free.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 table_name > OWNER TO 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 th= e > owning role) and able to SET ROLE to the new ownin= g > role. > + All object privileges of the old owner are transferred to the new own= er > + along with the ownership. > > > > > 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; 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, privileges must be - granted. + owner is the role that executed the creation statement + unless the statement itself specifies an owner. - There are different kinds of privileges: SELECT, + There are different kinds of grantable privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, @@ -1877,13 +1874,27 @@ ALTER TABLE products RENAME TO items; these privileges are used. + + 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. + + 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 .) + + 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. + + An object can be assigned to a new owner with an ALTER command of the appropriate kind for the object, for example @@ -1893,6 +1904,11 @@ ALTER TABLE table_name OWNER TO new_owne Superusers can always do this; ordinary roles can only do it if they ar= e 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. + 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. 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. --00000000000047e87a060fb919b8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Jan 24, 2024 at 7:23=E2=80=AFAM Laurenz Albe <<= a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cybertec.at>= wrote:
On Wed, 2024-01-24 at 11:08 +0100, gparc@free.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</replace= able> OWNER TO <replaceable>new_owne
=C2=A0 =C2=A0 Superusers can always do this; ordinary roles can only do it = if they are
=C2=A0 =C2=A0 both the current owner of the object (or inherit the privileg= es of the
=C2=A0 =C2=A0 owning role) and able to <literal>SET ROLE</literal&= gt; to the new owning role.
+=C2=A0 =C2=A0All object privileges of the old owner are transferred to the= new owner
+=C2=A0 =C2=A0along with the ownership.
=C2=A0 =C2=A0</para>

=C2=A0 =C2=A0<para>


Here's = a slightly more detailed patch to consider to cover both the transference o= f 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 RENA= ME TO items;

=C2=A0 =C2=A0<para>
=C2=A0 =C2=A0 When an obje= ct is created, it is assigned an owner. The
- =C2=A0 owner is normally t= he role that executed the creation statement.
- =C2=A0 For most kinds of= objects, the initial state is that only the owner
- =C2=A0 (or a superu= ser) can do anything with the object. To allow
- =C2=A0 other roles to u= se it, <firstterm>privileges</firstterm> must be
- =C2=A0 gr= anted.
+ =C2=A0 owner is the role that executed the creation statement+ =C2=A0 unless the statement itself specifies an owner.
=C2=A0 =C2=A0= </para>

=C2=A0 =C2=A0<para>
- =C2=A0 There are differ= ent kinds of privileges: <literal>SELECT</literal>,
+ =C2=A0= There are different kinds of grantable privileges: <literal>SELECT&l= t;/literal>,
=C2=A0 =C2=A0 <literal>INSERT</literal>, <= ;literal>UPDATE</literal>, <literal>DELETE</literal>,<= br>=C2=A0 =C2=A0 <literal>TRUNCATE</literal>, <literal>RE= FERENCES</literal>, <literal>TRIGGER</literal>,
=C2=A0= =C2=A0 <literal>CREATE</literal>, <literal>CONNECT</l= iteral>, <literal>TEMPORARY</literal>,
@@ -1877,13 +1874,= 27 @@ ALTER TABLE products RENAME TO items;
=C2=A0 =C2=A0 these privileg= es are used.
=C2=A0 =C2=A0</para>

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

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

=C2=A0 =C2=A0<para>
Laurenz has already commented to my accidentally off-list=C2=A0in= itial post that this seems to be too much detail for this section.=C2=A0 Bu= t it is the language specification Chapter, not the Tutorial, and I disagre= e.=C2=A0 It doesn't seem like an internals topic and there just isn'= ;t anywhere else to define this stuff.=C2=A0 The man pages I suppose work, = and this does have some repetition of the material there, but personally th= is feels like the more correct spot.=C2=A0 Some of the "where" la= nguage probably can be removed without loss of clarity but I haven't ad= ded anything here that isn't already described in even more detail at t= he end of this section.=C2=A0 I just used that material in context.

David J.
--00000000000047e87a060fb919b8--