Thread: postgres event trigger workaround
Hi,
PG event triggers are not firing on CREATE ROLE, CREATE DATABASE, CREATE TABLESPACE by definition (would be nice if they do).
Is there any workaround to react with ddl_command_start behavior on such an event?
Thanks,
Markus
Hi, On Wed, Jan 12, 2022 at 11:57:45AM +0000, Zwettler Markus (OIZ) wrote: > > PG event triggers are not firing on CREATE ROLE, CREATE DATABASE, CREATE > TABLESPACE by definition (would be nice if they do). > > Is there any workaround to react with ddl_command_start behavior on such an > event? That's not possible. The limitation exists because those objects are shared objects and therefore could be created from any database in the cluster. What is your use case? Maybe you could rely on logging all DDL instead for instance.
> > Hi, > > On Wed, Jan 12, 2022 at 11:57:45AM +0000, Zwettler Markus (OIZ) wrote: > > > > PG event triggers are not firing on CREATE ROLE, CREATE DATABASE, > > CREATE TABLESPACE by definition (would be nice if they do). > > > > Is there any workaround to react with ddl_command_start behavior on > > such an event? > > That's not possible. The limitation exists because those objects are shared objects > and therefore could be created from any database in the cluster. > > What is your use case? Maybe you could rely on logging all DDL instead for > instance. > 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 alsogetting CREATEDB privileges. My use case would have been to grant CREATEROLE to any role while still restricting "create database".
On Fri, Jan 14, 2022 at 10:01 AM Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch> 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 also which for my granular privileges around ROLEs.
Lite a CREATEROLE role that can only DROP the ROLEs it created (or created by other ROLEs its a member of).
Or a (NOLOGIN) ROLE that's restricted to have table privileges in some SCHEMAs only,
or in SCHEMAs owned by a given ROLE only. or ROLEs local to a given DATABASE only. These kind of things.
An idea I'm toying with is having a SCHEMA with (DEFINER RIGHTS) functions that acts as a wrapper around CREATE/DROP ROLE to impose custom restrictions.
It would record in private tables more context at creating times, and use that context to restrict the DROPs.
Could also solve your CREATEROLE vs CREATEDB conundrum maybe.
I have no time to develop that idea ATM though... Nor am I sure it would work.
And it would force my code to rip out it's current direct SQL DDLs, by equivalent functions from that mediator "admin" schema.
It would also not solve all my issues, like some ROLEs being restricted to GRANTs from a given SCHEMA.
(but maybe event trigger would allow to intercept that to check those too?)
Just thinking aloud :). --DD
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 alsogetting 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.
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@gmail.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.
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.
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 <rjuju123@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.