Re: [Extern] Re: postgres event trigger workaround

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.
--
Regards, Dmitry!

пт, 14 янв. 2022 г. в 15:24, Julien Rouhaud <rjuju123(at)gmail(dot)com>:

> 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.
>
>
>