From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Laurenz Albe <laurenz.albe@cybertec.at>, gparc@free.fr,
        Daniel Gustafsson <daniel@yesql.se>,
        pgsql-docs <pgsql-docs@lists.postgresql.org>
Subject: Re: SQL command : ALTER DATABASE OWNER TO
In-reply-to: 
 <CAKFQuwZjb=umdSBrW5diWDbXU25ygFzTKUON2TnfRdXmt9pjTg@mail.gmail.com>
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>
 <CAKFQuwZjb=umdSBrW5diWDbXU25ygFzTKUON2TnfRdXmt9pjTg@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Wed, 24 Jan 2024 08:47:06 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <166812.1706115794.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Wed, 24 Jan 2024 12:03:14 -0500
Message-ID: <166813.1706115794@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/166813.1706115794%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Jan 24, 2024 at 8:35 AM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>> 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).

> I dislike this change, ownership of an object is completely independent of
> the grant system of privileges.  The granted privileges of the old row do
> not transfer to the new owner when alter ... owner to is executed.  The
> separate object attribute "owner" is the only thing that changes.

Laurenz is correct, as you can easily find out by testing.  For
example,

regression=# create user joe;
CREATE ROLE
regression=# create database joe owner joe;

CREATE DATABASE
regression=# grant connect on database joe to joe;
GRANT
regression=# select datacl from pg_database where datname = 'joe';
        datacl         
-----------------------
 {=Tc/joe,joe=CTc/joe}
(1 row)

regression=# create user bob;
CREATE ROLE
regression=# alter database joe owner to bob;
ALTER DATABASE
regression=# select datacl from pg_database where datname = 'joe';
        datacl         
-----------------------
 {=Tc/bob,bob=CTc/bob}
(1 row)

If no explicit GRANTs have ever been done, so that the ACL column
is null, then it stays null --- but that has the same effect,
because the default privileges implied by the null entry now attach
to the new owner.

For myself, I thought Laurenz's proposed patch is an improvement.

			regards, tom lane