Re: SQL command : ALTER DATABASE OWNER TO

On Tue, Mar 8, 2022 at 7:39 AM Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Tue, Mar 8, 2022 at 10:50:38AM +0100, gparc(at)free(dot)fr wrote:
> >
> > Hello,
> >
> > for this "ALTER DATABASE" form, it should be mentioned that after
> execution of the command,
> > the old database owner loses all his privileges on it (even connection)
> although it might
> > still owns schemas or objects (tables, index,...) inside it.
> >
> > Thanks in advance to add this important precision.
>
> Uh, the original owner is not the owner anymore, so why would they
> assume they can reconnect, unless there is some other permission
> specified for them.
>
>
Agreed. The proposed solution simply addresses a single symptom of what
may be a misunderstanding about how the system works (i.e., that an object
can only have a single owner, and, each privilege is specific to an object
and does not confer any implied privileges on container objects - schemas
and databases namely).

If there is a suggestion to improve the core misunderstandings that is
something to consider. Ideally in a central place about permissions in
general and not in the specific ALTER DATABASE command.

Given that the default behavior of PostgreSQL is to grant CONNECT via
PUBLIC, removing ownership of a database from a role does not, by default,
remove their connect privilege.

David J.