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 1rSibg-00FUAA-02 for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 19:08:16 +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 1rSibf-007a0G-4V for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 19:08:15 +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 1rSibe-007a08-Ok for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 19:08:14 +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 1rSibb-003AsC-IR for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 19:08:13 +0000 Received: from zimbra-e1-02.priv.proxad.net (unknown [172.20.243.240]) by smtp1-g21.free.fr (Postfix) with ESMTP id BD3F6B0059A; Wed, 24 Jan 2024 20:08:08 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=free.fr; s=smtp-20201208; t=1706123289; bh=uD10zVG+q+I9q1hEe8XBlpwAdOfn7MnVbEKv4GJTcHc=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From; b=ZpV6TCmZ89pamgfFl7KXoNtny1+RpLk0Y9zYzTEYr+fnUg8AVAWM9U1O9KUPPgY/H VgyAzrcq8fuXTqxhvqsh3pu+FDmzD9d8goxD1M44A6CryWxrflHpi1xmmLFGUENQ0k wqhQwg/G7m3cVDxdxuXh9pLkNqzAKVGRSMbG2R/2uj4S4mTpUw2mc/d4fXxa1uOc9a qhRveW1fi2qS1L3KcYf53i3rMb1+VpLyQeUN+gr0kgyO7jmvYbXvQZh3JVAlirD8QK 9jzYZIRJxvAFOg7XvKSGeemOf043l5iqUNWmiJMedrPB1xE+DuIu5KisB6xzcmiq5I wyimAY/7C2Z/g== Date: Wed, 24 Jan 2024 20:08:08 +0100 (CET) From: gparc@free.fr To: "David G. Johnston" Cc: Laurenz Albe , Daniel Gustafsson , pgsql-docs Message-ID: <1127838244.215820663.1706123288688.JavaMail.zimbra@free.fr> In-Reply-To: <2114549089.214747273.1706116290356.JavaMail.zimbra@free.fr> References: <2023185982.281851219.1646733038464.JavaMail.root@zimbra15-e2.priv.proxad.net> <1327973565.212682013.1706107220766.JavaMail.zimbra@free.fr> <176777248.214045315.1706113384094.JavaMail.zimbra@free.fr> <2114549089.214747273.1706116290356.JavaMail.zimbra@free.fr> Subject: Re: SQL command : ALTER DATABASE OWNER TO MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_1866794d-c674-432d-8e2c-a81a6551d19b" X-Originating-IP: [37.167.162.129] X-Mailer: Zimbra 9.0.0_GA_1337 (ZimbraWebClient - FF115 (Win)/9.0.0_GA_1337) Thread-Topic: SQL command : ALTER DATABASE OWNER TO Thread-Index: 61AUPwrWYc4p7VhxNH8qpBjidWwscQR+yA8u List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_1866794d-c674-432d-8e2c-a81a6551d19b Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable David,=20 reading again your last reply, it reminded me that as soon as we create a d= atabase=20 we revoke default PUBLIC grants (i.e revoke all on from publ= ic) to grant=20 only databases privileges to specific roles/users.=20 That's why after changing database ownership, we have to (re)grant privileg= es (ie. grant all on database)=20 to the old owner...and that's what i forgot to do.=20 So no problem at all and documentation is OK.=20 Sorry for the noise !=20 Best regards=20 Gilles=20 De: "gparc" =20 =C3=80: "David G. Johnston" =20 Cc: "Laurenz Albe" , "Daniel Gustafsson" , "pgsql-docs" =20 Envoy=C3=A9: Mercredi 24 Janvier 2024 18:11:30=20 Objet: Re: SQL command : ALTER DATABASE OWNER TO=20 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 --=_1866794d-c674-432d-8e2c-a81a6551d19b Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
David,
reading again your las= t reply, it reminded me that as soon as we create a database
we revoke d= efault PUBLIC grants (i.e revoke all on <new_database> from public) t= o grant
only databases privileges to specific roles/users.
That's why after changing database ownership, we have= to (re)grant privileges (ie. grant all on database)
to the old owner...and that's what i forgot to do.
So no problem at all and documentation is OK.
So= rry for the noise !

Best regards
Gilles

De: "gparc" <gparc@= free.fr>
=C3=80: "David G. Johnston" <david.g.johnston@gmai= l.com>
Cc: "Laurenz Albe" <laurenz.albe@cybertec.at>, "D= aniel Gustafsson" <daniel@yesql.se>, "pgsql-docs" <pgsql-docs@list= s.postgresql.org>
Envoy=C3=A9: Mercredi 24 Janvier 2024 18:11:= 30
Objet: Re: SQL command : ALTER DATABASE OWNER TO

De: "David G. Johnston" <david.g.johnston@gmail.com>= ;
=C3=80: "gparc" <gparc@free.fr>
Cc: "Laurenz Al= be" <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, Jan 24, 2024 at 9:23=E2=80=AFAM <gparc@free.fr> wrote:-
[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**, th= e old owner loses all his privileges on it
(even CONNECT) although he st= ill owns schema and objects (table, index,..) inside it.
As such,= he can't use his own schema anymore.
That's why I propose to= update the documentation as it's weird, at least for me, when you get caug= ht by this behaviour.

Regards
Gilles
<= /div>
--=_1866794d-c674-432d-8e2c-a81a6551d19b--