On Sun, Apr 21, 2024 at 11:10 AM yudhi s <learnerdatabas...@gmail.com> wrote:
> > On Sun, Apr 21, 2024 at 7:55 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Sunday, April 21, 2024, yudhi s <learnerdatabas...@gmail.com> wrote: >> >>> On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston < >>> david.g.johns...@gmail.com> wrote: >>> >>>> On Sunday, April 21, 2024, yudhi s <learnerdatabas...@gmail.com> wrote: >>>> >>>>> so that it will be able to assign the privilege, so we will be able to >>>>> create the event trigger without need to run the event trigger script from >>>>> super user itself? >>>>> >>>> >>>> Write a security-definer function owned by superuser and grant app_user >>>> permission to execute it. >>>> >>>> David J. >>>> >>>> >>> >>> Thank You David. >>> >>> Are you saying something like below, in which we first create the >>> function from super user and then execute the grant? But doesn't that mean, >>> each time we want to create a new event trigger we have to be again >>> dependent on the "super user" to modify the security definer function? >>> >> >> Dynamic SQL. See “execute” in plpgsql. >> >> David J. >> >> > > Even if we create the event trigger using "security definer" function > embedding the "create event trigger" with in its body using dynamic > sql(something as below), and in future if we need to create another event > trigger , we need to again update the function and re-compile and for that > , we will need it it to be compiled using user "super user", is my > understanding correct here? > Or > it will just need the "super user" to create the function for the first > time , but after that the user who has the "execute grant" given (say > app_user) will be able to perform updates and compile to the function body? > > CREATE OR REPLACE FUNCTION create_event_trigger_func() > RETURNS void > LANGUAGE plpgsql > SECURITY DEFINER > AS $$ > BEGIN > EXECUTE 'CREATE EVENT TRIGGER event_trigger_name ON schema_name ...'; > END; > $$; > > GRANT EXECUTE ON FUNCTION create_event_trigger_func() TO app_user; > If you don't allow the caller to pass in parameters then no, you likely gain nothing from using a security definer function. It is a tool and I don't have enough info or desire to write the internals of said function(s) for your need. As Tom says, it very well may be impossible to accomplish your goal even with a security definer function. But absent a predefined role there is no other mechanism for the owners of objects or superusers to delegate their non-grantable abilities to ordinary users. David J.
