The goal was to limit access to the schema to users who manage roles, in all likelihood to a specific group of administrators. This can be done with this solution. I have no problem with managing roles through plpgsql functions. There has been no need to create databases until now. In my solution, the schema is a data element, for flexibility. -- Regards, Dmitry!
сб, 15 янв. 2022 г. в 10:01, Julien Rouhaud <rjuju...@gmail.com>: > Hi, > > On Sat, Jan 15, 2022 at 08:36:21AM +0500, Дмитрий Иванов wrote: > > 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. > > Sure you can solve most problems with that. But you can't create a > database > (or a tablespace) from a function so this approach wouldn't cover all of > OP's > needs, as different approach would be needed for role and db creation. >
