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 1rSgJj-00FEP7-QF for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 16:41:36 +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 1rSgJi-006Pyd-T6 for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 16:41:34 +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 1rSgJi-006PyU-K1 for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 16:41:34 +0000 Received: from smtp3-g21.free.fr ([212.27.42.3]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rSgJg-003VCM-02 for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 16:41:33 +0000 Received: from zimbra-e1-02.priv.proxad.net (unknown [172.20.243.240]) by smtp3-g21.free.fr (Postfix) with ESMTP id 3F81713F8CF; Wed, 24 Jan 2024 17:41:29 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=free.fr; s=smtp-20201208; t=1706114490; bh=9pmrijlgmf+VWcFprpqs0mMW3cetHw+rx17BbP/UZWY=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From; b=QGaIFeRGE+pPNQbPils8V2yo+x8hbUSzaocQ2ymWsOq0Ucp/NNfJ8T2dNdKJzgPfw VK6Hpx/LJEnYUHaNv/tegmDJSyJmnpZg8AcDIab2wtvBBNOi8Td5XSh9vGGOuC2C0t 2xNZjQwTwXFuNDpVygB3eDAhJIRjgM0y04dzG3vISdMMGSy3rGv8wF4db7rnnuWRkL DpvOJ1pVSx0obJnG1lI6tcVtSrwdSeGhAMKY8yd4TII1qzeKUGS00ChcXuFT/2ikB8 ieDo1HOoo3qogMvusROhdd6pEpx2c6vqri4Iw5VVI5Ip8UIaWIqz+QZhqZWbUsHLfq 7paeY0k/Kr4xA== Date: Wed, 24 Jan 2024 17:41:29 +0100 (CET) From: gparc@free.fr To: "David G. Johnston" Cc: Laurenz Albe , Daniel Gustafsson , pgsql-docs Message-ID: <1318833190.214290217.1706114489204.JavaMail.zimbra@free.fr> In-Reply-To: References: <2023185982.281851219.1646733038464.JavaMail.root@zimbra15-e2.priv.proxad.net> <1327973565.212682013.1706107220766.JavaMail.zimbra@free.fr> Subject: Re: SQL command : ALTER DATABASE OWNER TO MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_bee1b097-b4fe-4018-9146-e2f641ccd537" X-Originating-IP: [145.242.20.128] X-Mailer: Zimbra 9.0.0_GA_1337 (ZimbraWebClient - FF102 (Linux)/9.0.0_GA_1337) Thread-Topic: SQL command : ALTER DATABASE OWNER TO Thread-Index: yWtiEaq80o+6HTfCA0+DECgBglE79A== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_bee1b097-b4fe-4018-9146-e2f641ccd537 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable De: "David G. Johnston" =20 =C3=80: "Laurenz Albe" =20 Cc: "gparc" , "Daniel Gustafsson" , "pgsql-= docs" =20 Envoy=C3=A9: Mercredi 24 Janvier 2024 17:36:43=20 Objet: Re: SQL command : ALTER DATABASE OWNER TO=20 On Wed, Jan 24, 2024 at 9:13 AM Laurenz Albe < [ mailto:laurenz.albe@cybert= ec.at | laurenz.albe@cybertec.at ] > wrote:=20 On Wed, 2024-01-24 at 08:47 -0700, David G. Johnston wrote:=20 > I dislike this change, ownership of an object is completely independent o= f=20 > the grant system of privileges. The granted privileges of the old row do= =20 > not transfer to the new owner when alter ... owner to is executed.=20 CREATE TABLE mytab ();=20 REVOKE ALL ON mytab FROM PUBLIC;=20 \z mytab=20 Access privileges=20 Schema =E2=94=82 Name =E2=94=82 Type =E2=94=82 Access privileges =E2=94=82 = Column privileges =E2=94=82 Policies=20 =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=20 public =E2=94=82 mytab =E2=94=82 table =E2=94=82 postgres=3DarwdDxt/postgre= s =E2=94=82 =E2=94=82=20 (1 row)=20 ALTER TABLE mytab OWNER TO laurenz;=20 \z mytab=20 Access privileges=20 Schema =E2=94=82 Name =E2=94=82 Type =E2=94=82 Access privileges =E2=94=82 = Column privileges =E2=94=82 Policies=20 =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=20 public =E2=94=82 mytab =E2=94=82 table =E2=94=82 laurenz=3DarwdDxt/laurenz = =E2=94=82 =E2=94=82=20 (1 row)=20 You need to actually revoke something to make the point stand out.=20 postgres=3D# \z tt1=20 Access privileges=20 Schema | Name | Type | Access privileges | Column privileges | Policies=20 --------+------+-------+-----------------------+-------------------+-------= ---=20 public | tt1 | table | davidj=3DarwdDxt/davidj | |=20 (1 row)=20 postgres=3D# revoke update on tt1 from davidj;=20 REVOKE=20 postgres=3D# \z tt1=20 Access privileges=20 Schema | Name | Type | Access privileges | Column privileges | Policies=20 --------+------+-------+----------------------+-------------------+--------= --=20 public | tt1 | table | davidj=3DardDxt/davidj | |=20 (1 row)=20 postgres=3D# alter table tt1 owner to testowner;=20 ALTER TABLE=20 postgres=3D# \z tt1=20 Access privileges=20 Schema | Name | Type | Access privileges | Column privileges | Policies=20 --------+------+-------+----------------------------+-------------------+--= --------=20 public | tt1 | table | testowner=3DardDxt/testowner | |=20 (1 row)=20 The new owner, testowner, is missing the same update privilege that davidj = removed from himself. In short, setting owner does indeed cause explicit gr= ants to appear in the system, grants that can be revoked. And so, yes, tran= sferring ownership transfers the set of grants currently in effect for the = existing owner.=20 I can see making this detail more clear in the DDL chapter. It is unrelated= to the confusion behind the topic of this thread though.=20 David J.=20 Hello again,=20 note that my point concerns "alter database" not "alter table".=20 See my last reply for an example=20 Regards=20 --=_bee1b097-b4fe-4018-9146-e2f641ccd537 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
De: "David G. Johnston" <david.g.jo= hnston@gmail.com>
=C3=80: "Laurenz Albe" <laurenz.albe@cybe= rtec.at>
Cc: "gparc" <gparc@free.fr>, "Daniel Gustafsson= " <daniel@yesql.se>, "pgsql-docs" <pgsql-docs@lists.postgresql.org= >
Envoy=C3=A9: Mercredi 24 Janvier 2024 17:36:43
Objet: = Re: SQL command : ALTER DATABASE OWNER TO

On Wed, Ja= n 24, 2024 at 9:13=E2=80=AFAM Laurenz Albe <laur= enz.albe@cybertec.at> wrote:
On Wed, 2= 024-01-24 at 08:47 -0700, David G. Johnston wrote:
> I dislike this change, ownership of an object is completely independen= t of
> the grant system of privileges.  The granted privileges of the ol= d row do
> not transfer to the new owner when alter ... owner to is executed= . 

CREATE TABLE mytab ();

REVOKE ALL ON mytab FROM PUBLIC;

\z mytab
                     = ;            Access privileges
 Schema =E2=94=82 Name  =E2=94=82 Type  =E2=94=82  &nbs= p;  Access privileges     =E2=94=82 Column privileges = =E2=94=82 Policies
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90  public =E2=94=82 mytab =E2=94=82 table =E2=94=82 postgres=3DarwdDxt/p= ostgres =E2=94=82                &n= bsp;  =E2=94=82
(1 row)

ALTER TABLE mytab OWNER TO laurenz;

\z mytab
                     = ;           Access privileges
 Schema =E2=94=82 Name  =E2=94=82 Type  =E2=94=82  &nbs= p; Access privileges    =E2=94=82 Column privileges =E2=94=82 Pol= icies
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90
 public =E2=94=82 mytab =E2=94=82 table =E2=94=82 laurenz=3DarwdDxt/la= urenz =E2=94=82                &nbs= p;  =E2=94=82
(1 row)



You need to actually revoke something to make the poi= nt stand out.

postgres=3D# \z tt1
=                      = ;         Access privileges
 Schema | Name | Ty= pe  |   Access privileges   | Column privileges | Policies--------+------+-------+-----------------------+-------------------+-----= -----
 public | tt1  | table | davidj=3DarwdDxt/davidj |  = ;                 |
(1 row)
p= ostgres=3D# revoke update on tt1 from davidj;
REVOKE
postgres=3D# \z = tt1
                   = ;           Access privileges
 Schema | Na= me | Type  |  Access privileges   | Column privileges | Poli= cies
--------+------+-------+----------------------+-------------------+= ----------
 public | tt1  | table | davidj=3DardDxt/davidj | &= nbsp;                 |
(1 row)<= br>
postgres=3D# alter table tt1 owner to testowner;
ALTER TABLE
p= ostgres=3D# \z tt1
              &nbs= p;                  Access pri= vileges
 Schema | Name | Type  |     Access privileg= es      | Column privileges | Policies
--------+------+--= -----+----------------------------+-------------------+----------
 = public | tt1  | table | testowner=3DardDxt/testowner |     &= nbsp;             |
(1 row)

<= div class=3D"gmail_default" style=3D"font-family:'arial' , 'helvetica' , sa= ns-serif">The new owner, testowner, is missing the same update privilege th= at davidj removed from himself.  In short, setting owner does indeed c= ause explicit grants to appear in the system, grants that can be revoked.&n= bsp; And so, yes, transferring ownership transfers the set of grants curren= tly in effect for the existing owner.

I can see making this detail more clear in the DDL chapter.  It is un= related to the confusion behind the topic of this thread though.

David J.


Hello again,
note that my point concerns "alter = database" not "alter table".
See m= y last reply for an example

Regards
<= div class=3D"gmail_default" style=3D"font-family:'arial' , 'helvetica' , sa= ns-serif">

--=_bee1b097-b4fe-4018-9146-e2f641ccd537--