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 1rSgmo-00FH4J-E4 for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 17:11:39 +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 1rSgmn-006ml4-8E for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 17:11:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rSgmm-006mke-So for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 17:11:37 +0000 Received: from smtp1-g21.free.fr ([2a01:e0c:1:1599::10]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rSgmj-0039zn-Qe for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 17:11:35 +0000 Received: from zimbra-e1-02.priv.proxad.net (unknown [172.20.243.240]) by smtp1-g21.free.fr (Postfix) with ESMTP id 64B79B00563; Wed, 24 Jan 2024 18:11:30 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=free.fr; s=smtp-20201208; t=1706116290; bh=Tg1IAjngLvKXyJfLlK6tjJkxqEFWCiZFyFfEeW4EMdQ=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From; b=cKcYopR0OB/fbwX4jpgDgpjpouxlQcrhdb2g1S927LKcLCXh4owvxL639db/KFVBO mJaIDD7CndM66Cz4cA97c4zV5tnPAyHILWbmuQSUTT5JxjMDl6DR0WSwNsUPRCwo6t DJk3FQuozeuYZWgMqEf6w3l2qXkj5nXzAaA6uthRFXiuSUqQOth7AGvbf9QH+J6ATj RpKIbI1w+NHBxMebo4Hd29llYf7JdLtxBLU+Gmtx0NYwyVMWeTdXtuV/4ktFUXq1q2 z/OjJ9iePg+GgGh+GmOdOBRlT/AoDbVQXtRVBshrNYhxouBLut6akH16HB4MPVuIcN 5Kpbnk1gvb2Kw== Date: Wed, 24 Jan 2024 18:11:30 +0100 (CET) From: gparc@free.fr To: "David G. Johnston" Cc: Laurenz Albe , Daniel Gustafsson , pgsql-docs Message-ID: <2114549089.214747273.1706116290356.JavaMail.zimbra@free.fr> In-Reply-To: References: <2023185982.281851219.1646733038464.JavaMail.root@zimbra15-e2.priv.proxad.net> <1712096587.208766558.1706090901073.JavaMail.zimbra@free.fr> <1327973565.212682013.1706107220766.JavaMail.zimbra@free.fr> <176777248.214045315.1706113384094.JavaMail.zimbra@free.fr> Subject: Re: SQL command : ALTER DATABASE OWNER TO MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_fa426120-1545-4c9c-829d-8985ec4a2a37" 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: 61AUPwrWYc4p7VhxNH8qpBjidWwscQ== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_fa426120-1545-4c9c-829d-8985ec4a2a37 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable De: "David G. Johnston" =20 =C3=80: "gparc" =20 Cc: "Laurenz Albe" , "Daniel Gustafsson" , "pgsql-docs" =20 Envoy=C3=A9: Mercredi 24 Janvier 2024 17:50:17=20 Objet: Re: SQL command : ALTER DATABASE OWNER TO=20 On Wed, Jan 24, 2024 at 9:23 AM < [ mailto:gparc@free.fr | gparc@free.fr ] = > wrote: -=20 [postgres] $ psql=20 psql (14.10)=20 You really should add commentary, especially since you never demonstrated t= he tst role (I advise picking different names for all of the objects in the= future) being unable to login. Which they should be able to since public i= s shown to have "c" connect privileges (=3DTc/tst)=20 BQ_BEGIN [postgres@PGDEV14] postgres=3D# create user tst password 'tst';=20 CREATE ROLE=20 [postgres@PGDEV14] postgres=3D# create database tst owner =3D tst;=20 CREATE DATABASE=20 BQ_END This next command is pointless, it is a no-op, as soon as you made them own= er of the tst database they already had all privileges to it, granted by th= e same user that created the database. And only it, that command is not rec= ursing through the database into schemas and tables and adding more permiss= ions. That isn't how this all works, a database is an object. While it is a= lso a concept that encompasses the entire schema within it the permissions = system only cares about the first definition.=20 BQ_BEGIN [postgres@PGDEV14] postgres=3D# grant all on database tst to tst;=20 GRANT=20 [postgres@PGDEV14] postgres=3D# \l+ tst=20 Liste des bases de donn=C3=A9es=20 Nom | Propri=C3=A9taire | Encodage | Collationnement | Type caract. | Droit= s d'acc=C3=A8s | Taille | Tablespace | Description=20 -----+--------------+----------+-----------------+--------------+----------= ------+---------+------------+-------------=20 tst | tst | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =3DTc/tst +| 9809 kB | pg_de= fault |=20 | | | | | tst=3DCTc/tst | | |=20 (1 ligne)=20 BQ_END What are you trying to demonstrate here?=20 BQ_BEGIN [postgres@PGDEV14] tst=3D# \dn+ tst=20 Liste des sch=C3=A9mas=20 Nom | Propri=C3=A9taire | Droits d'acc=C3=A8s | Description=20 -----+--------------+----------------+-------------=20 tst | tst | |=20 (1 ligne)=20 BQ_END David J.=20 David,=20 what I wanted to demonstrate/convey is that when I alter the ownership of a= **database**, the old owner loses all his privileges on it=20 (even CONNECT) although he still owns schema and objects (table, index,..) = inside it.=20 As such, he can't use his own schema anymore.=20 That's why I propose to update the documentation as it's weird, at least fo= r me, when you get caught by this behaviour.=20 Regards=20 Gilles=20 --=_fa426120-1545-4c9c-829d-8985ec4a2a37 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
De: "David G. Johnston" <david.g.jo= hnston@gmail.com>
=C3=80: "gparc" <gparc@free.fr>
= Cc: "Laurenz Albe" <laurenz.albe@cybertec.at>, "Daniel Gustafsson= " <daniel@yesql.se>, "pgsql-docs" <pgsql-docs@lists.postgresql.org= >
Envoy=C3=A9: Mercredi 24 Janvier 2024 17:50:17
Objet: = Re: SQL command : ALTER DATABASE OWNER TO

On Wed, Ja= n 24, 2024 at 9:23=E2=80=AFAM <gparc@free.fr> wrot= e:-
[postgres] $ psql
psql (14.10)


You really should add commentary, espe= cially since you never demonstrated the tst role (I advise picking differen= t names for all of the objects in the future) being unable to login.  = Which they should be able to since public is shown to have "c" connect priv= ileges (=3DTc/tst)

 
[postgres@PGDEV14] postgres=3D# create user tst password 'tst';
CREATE ROLE
[postgres@PGDEV14] postgres=3D# create database tst owner =3D tst;
CREATE DATABASE

This next command is po= intless, it is a no-op, as soon as you made them owner of the tst database = they already had all privileges to it, granted by the same user that c= reated the database.  And only it, that command is not recursing = through the database into schemas and tables and adding more permissions.&n= bsp; That isn't how this all works, a database is an object.  While it= is also a concept that encompasses the entire schema within it the permiss= ions system only cares about the first definition.

[postgres@PGDEV14] postgres=3D# grant all on database tst to tst;
GRANT
[postgres@PGDEV14] postgres=3D# \l+ tst
                     = ;                     &nb= sp;   Liste des bases de donn=C3=A9es
 Nom | Propri=C3=A9taire | Encodage | Collationnement | Type caract. |= Droits d'acc=C3=A8s | Taille  | Tablespace | Description
-----+--------------+----------+-----------------+--------------+----------= ------+---------+------------+-------------
 tst | tst          | UTF8     = ;| fr_FR.UTF-8     | fr_FR.UTF-8  | =3DTc/tst  &nb= sp;    +| 9809 kB | pg_default |
     |              |&nbs= p;         |            &= nbsp;    |              | tst= =3DCTc/tst    |         |    &= nbsp;       |
(1 ligne)


What are you trying to demonst= rate here?


[postgres@PGDEV14] tst=3D# \dn+ tst
                 Liste des sch= =C3=A9mas
 Nom | Propri=C3=A9taire | Droits d'acc=C3=A8s | Description
-----+--------------+----------------+-------------
 tst | tst          |      &nb= sp;         |
(1 ligne)


David J.



David,
what I wanted to demonstrate/= convey is that when I alter the ownership of a **database**, the old owner = loses all his privileges on it
(even CONNECT) although he still owns sch= ema and objects (table, index,..) inside it.
As such, he can't use his own schema anymore.
That's why I propose to u= pdate the documentation as it's weird, at least for me, when you get caught= by this behaviour.

Regards
Gilles
--=_fa426120-1545-4c9c-829d-8985ec4a2a37--