public inbox for [email protected]
help / color / mirror / Atom feedRe: error in trigger creation
2+ messages / 2 participants
[nested] [flat]
* Re: error in trigger creation
@ 2024-04-21 18:09 yudhi s <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: yudhi s @ 2024-04-21 18:09 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-general <[email protected]>
On Sun, Apr 21, 2024 at 7:55 PM David G. Johnston <
[email protected]> wrote:
> On Sunday, April 21, 2024, yudhi s <[email protected]> wrote:
>
>> On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston <
>> [email protected]> wrote:
>>
>>> On Sunday, April 21, 2024, yudhi s <[email protected]> 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;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: error in trigger creation
@ 2024-04-21 18:18 David G. Johnston <[email protected]>
parent: yudhi s <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2024-04-21 18:18 UTC (permalink / raw)
To: yudhi s <[email protected]>; +Cc: pgsql-general <[email protected]>
On Sun, Apr 21, 2024 at 11:10 AM yudhi s <[email protected]>
wrote:
>
> On Sun, Apr 21, 2024 at 7:55 PM David G. Johnston <
> [email protected]> wrote:
>
>> On Sunday, April 21, 2024, yudhi s <[email protected]> wrote:
>>
>>> On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston <
>>> [email protected]> wrote:
>>>
>>>> On Sunday, April 21, 2024, yudhi s <[email protected]> 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-04-21 18:18 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-21 18:09 Re: error in trigger creation yudhi s <[email protected]>
2024-04-21 18:18 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox