Re: SQL command : ALTER DATABASE OWNER TO

On Wed, Jan 24, 2024 at 10:13 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > postgres=# grant all on database newdb2 to testowner;
> > -- as I am logged in as davidj this grant should actually happen, with
> > davidj as the grantor
> > -- the grants that materialize from ownership has the owning role as the
> > grantor
>
> Yes. The FM points out somewhere that if a superuser does a GRANT,
> it's executed as though by the object owner. That provision predates
> when we supported explicit GRANTED BY clauses in GRANT. I'm not sure
> we'd have made it work like that if we had GRANTED BY already, but
> I'm afraid of the compatibility implications if we change it now.
>
>
Agreed, and I do recall that - it is documented on the GRANT page. Also
noted is I can "inherit ownership" if I exercise that inherited ability the
resultant grant still comes from the owner. This unifies two of three ways
for these grants to be established.

If I give out the ability via a grant option only then does the grantor
become the grant optioned role. This is the expected behavior and doesn't
require documentation explicitly.

The following testing of this behavior surprises me though:

List of databases
-[ RECORD 1 ]-----+------------------------
Name | newdb2
Owner | testowner
Encoding | UTF8
Locale Provider | libc
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
ICU Locale |
ICU Rules |
Access privileges | testowner=CTc/testowner+
| to3=C*T*c*/testowner +
| to4=CTc/to3 +
| testowner=CTc/to3

postgres=> reset role;
RESET
postgres=# alter database newdb2 owner to davidj;
ALTER DATABASE
postgres=# \l newdb2
List of databases
-[ RECORD 1 ]-----+------------------
Name | newdb2
Owner | davidj
Encoding | UTF8
Locale Provider | libc
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
ICU Locale |
ICU Rules |
Access privileges | davidj=CTc/davidj+
| to3=C*T*c*/davidj+
| to4=CTc/to3 +
| davidj=CTc/to3

I was expecting the privileges given to me by to3 to remain in place even
after I lost my ownership grants.

As you've noted it seems unlikely this is something we are willing to
change at this point. So, in short, it seems impossible for an owner of an
object to be left with any direct permissions on said object after having
their ownership reassigned. The role which gets the new assignment assumes
all of the explicit grants that exist for the old role.

postgres=# alter database newdb2 owner to to3;
ALTER DATABASE
postgres=# \l newdb2
List of databases
-[ RECORD 1 ]-----+---------------
Name | newdb2
Owner | to3
Encoding | UTF8
Locale Provider | libc
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
ICU Locale |
ICU Rules |
Access privileges | to3=C*T*c*/to3+
| to4=CTc/to3

This makes sense since the three grants that to3 would have after merging
are consolidated into a single one - in an additive sense and the grant
options being retained if present.

David J.