Hi
In my solution, all users don't need direct access to the schema because you have to use the functional API to access it. If you can manage users with functions, you can close the schema in the same way.
Usually the function is executed with the permissions of the calling user, which requires permissions for all affected entities. However, if you specify the "SECURITY DEFINER" parameter at creation, the function will be executed with the owner's permissions. The owner of the function has no login permissions but has permissions on the affected entities. In this way you will close the schema from the roles that have rights to the role management functions.
Usually the function is executed with the permissions of the calling user, which requires permissions for all affected entities. However, if you specify the "SECURITY DEFINER" parameter at creation, the function will be executed with the owner's permissions. The owner of the function has no login permissions but has permissions on the affected entities. In this way you will close the schema from the roles that have rights to the role management functions.
--
Regards, Dmitry!пт, 14 янв. 2022 г. в 15:24, Julien Rouhaud <rjuju123@xxxxxxxxx>:
Hi,
On Fri, Jan 14, 2022 at 09:01:12AM +0000, Zwettler Markus (OIZ) wrote:
>
> We have the need to separate user (role) management from infrastructure (database) management.
>
> Granting CREATEROLE to any role also allows this role to create other roles having CREATEDB privileges and therefore also getting CREATEDB privileges.
>
> My use case would have been to grant CREATEROLE to any role while still restricting "create database".
I see, that's indeed a problem. You could probably enforce that using some
custom module to enforce additional rules on top of CREATE ROLE processing, but
it would have to be written in C.
