Date: Wed, 24 Jan 2024 17:23:04 +0100 (CET)
From: gparc@free.fr
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Daniel Gustafsson <daniel@yesql.se>,
	pgsql-docs <pgsql-docs@lists.postgresql.org>
Message-ID: <176777248.214045315.1706113384094.JavaMail.zimbra@free.fr>
In-Reply-To: <ae2b506af55afec6f3841d69c1f01aea976df619.camel@cybertec.at>
References: 
 <2023185982.281851219.1646733038464.JavaMail.root@zimbra15-e2.priv.proxad.net>
 <1712096587.208766558.1706090901073.JavaMail.zimbra@free.fr>
 <bd4c8b8e88548d6e08220196a02c17d4fa5d2124.camel@cybertec.at>
 <EC6A3402-8271-4EA7-B6BA-1CEFEA96C749@yesql.se>
 <1327973565.212682013.1706107220766.JavaMail.zimbra@free.fr>
 <ae2b506af55afec6f3841d69c1f01aea976df619.camel@cybertec.at>
Subject: Re: SQL command : ALTER DATABASE OWNER TO
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Thread-Topic: SQL command : ALTER DATABASE OWNER TO
Thread-Index: +ht3wLlIujYDWcWncT7O+IVpVyZiiw==
Archived-At: 
 <https://www.postgresql.org/message-id/176777248.214045315.1706113384094.JavaMail.zimbra%40free.fr>
Precedence: bulk

----- Mail original -----
De: "Laurenz Albe" <laurenz.albe@cybertec.at>
=C3=80: "gparc" <gparc@free.fr>, "Daniel Gustafsson" <daniel@yesql.se>
Cc: "pgsql-docs" <pgsql-docs@lists.postgresql.org>
Envoy=C3=A9: Mercredi 24 Janvier 2024 16:35:10
Objet: Re: SQL command : ALTER DATABASE OWNER TO

On Wed, 2024-01-24 at 15:40 +0100, gparc@free.fr wrote:
> maybe a misunderstanding of my part, but your proposed modification doesn=
't matched
> with the current behaviour of the command as precisely the object privile=
ges of the old owner are **NOT** transferred
> to the new owner along with the ownership

But that is what happens.

The permissions are transferred to the new owner, so the old owner doesn't
have any privileges on the object (and, in your case, cannot connect to
the database any more).

Yours,
Laurenz Albe


Laurenz,
may be better with an example to explain what I mean with "the old database=
 owner loses all his privileges on it (even connection) although it might
still owns schemas or objects (tables, index,...) inside it"

[postgres] $ psql
psql (14.10)

[postgres@PGDEV14] postgres=3D# create user tst password 'tst';
CREATE ROLE
[postgres@PGDEV14] postgres=3D# create database tst owner =3D tst;
CREATE DATABASE
[postgres@PGDEV14] postgres=3D# grant all on database tst to tst;
GRANT
[postgres@PGDEV14] postgres=3D# \l+ tst
                                              Liste des bases de donn=C3=A9=
es
 Nom | Propri=C3=A9taire | Encodage | Collationnement | Type caract. | Droi=
ts d'acc=C3=A8s | Taille  | Tablespace | Description
-----+--------------+----------+-----------------+--------------+----------=
------+---------+------------+-------------
 tst | tst          | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | =3DTc/tst=
       +| 9809 kB | pg_default |
     |              |          |                 |              | tst=3DCTc=
/tst    |         |            |
(1 ligne)

[postgres@PGDEV14] postgres=3D# \c tst tst
Mot de passe pour l'utilisateur tst :
Vous =C3=AAtes maintenant connect=C3=A9 =C3=A0 la base de donn=C3=A9es =C2=
=AB tst =C2=BB en tant qu'utilisateur =C2=AB tst =C2=BB.
[tst@PGDEV14] tst=3D> create schema tst;
CREATE SCHEMA
[tst@PGDEV14] tst=3D> create table t1 (x int);
CREATE TABLE
[tst@PGDEV14] tst=3D> \dn+ tst
                 Liste des sch=C3=A9mas
 Nom | Propri=C3=A9taire | Droits d'acc=C3=A8s | Description
-----+--------------+----------------+-------------
 tst | tst          |                |
(1 ligne)

[tst@PGDEV14] tst=3D> \dt+ t1
                                     Liste des relations
 Sch=C3=A9ma | Nom | Type  | Propri=C3=A9taire | Persistence | M=C3=A9thode=
 d'acc=C3=A8s | Taille  | Description
--------+-----+-------+--------------+-------------+-----------------+-----=
----+-------------
 tst    | t1  | table | tst          | permanent   | heap            | 0 by=
tes |
(1 ligne)

[tst@PGDEV14] tst=3D> \c - postgres
Vous =C3=AAtes maintenant connect=C3=A9 =C3=A0 la base de donn=C3=A9es =C2=
=AB tst =C2=BB en tant qu'utilisateur =C2=AB postgres =C2=BB.
[postgres@PGDEV14] tst=3D# alter database tst owner to postgres;
ALTER DATABASE
[postgres@PGDEV14] tst=3D# \l+ tst
                                                 Liste des bases de donn=C3=
=A9es
 Nom | Propri=C3=A9taire | Encodage | Collationnement | Type caract. |    D=
roits d'acc=C3=A8s     | Taille  | Tablespace | Description
-----+--------------+----------+-----------------+--------------+----------=
-------------+---------+------------+-------------
 tst | postgres     | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | =3DTc/pos=
tgres         +| 9809 kB | pg_default |
     |              |          |                 |              | postgres=
=3DCTc/postgres |         |            |
(1 ligne)

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

[postgres@PGDEV14] tst=3D# \dt tst.t1
         Liste des relations
 Sch=C3=A9ma | Nom | Type  | Propri=C3=A9taire
--------+-----+-------+--------------
 tst    | t1  | table | tst
(1 ligne)

Regards=20
Gilles