On Tue, Mar 8, 2022 at 7:39 AM Bruce Momjian <bruce@momjian.us> wrote:
On Tue, Mar 8, 2022 at 10:50:38AM +0100, gparc@free.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.