postgres event trigger workaround

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 also getting 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 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
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@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.

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