Re: SQL command : ALTER DATABASE OWNER TO

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