MIME-Version: 1.0
References: <CAEzWdqcimp5dnNOavaSkMCOKW_FVsKC2101g=dFsyjQ-9dA3uw@mail.gmail.com>
 <CAKFQuwa+jpZ-pucWc92OCYcwCnj7C_POg8k=5BvbPZyL97R-Jw@mail.gmail.com>
In-Reply-To: <CAKFQuwa+jpZ-pucWc92OCYcwCnj7C_POg8k=5BvbPZyL97R-Jw@mail.gmail.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Sun, 21 Apr 2024 14:25:44 +0530
Message-ID: <CAEzWdqfqr9e3OpFd5Nhqha3Ggm=+UJdWkgvo7dpAa3W99S2g5Q@mail.gmail.com>
Subject: Re: error in trigger creation
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002c90650616977d43"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqfqr9e3OpFd5Nhqha3Ggm%3D%2BUJdWkgvo7dpAa3W99S2g5Q%40mail.gmail.com>
Precedence: bulk

--0000000000002c90650616977d43
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sun, Apr 21, 2024 at 1:55=E2=80=AFPM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Sunday, April 21, 2024, yudhi s <learnerdatabase99@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 fr=
om
>> 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?

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;

--0000000000002c90650616977d43
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sun, Apr 21, 2024 at 1:55=E2=80=AFPM D=
avid G. Johnston &lt;<a href=3D"mailto:david.g.johnston@gmail.com">david.g.=
johnston@gmail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><bloc=
kquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:=
1px solid rgb(204,204,204);padding-left:1ex">On Sunday, April 21, 2024, yud=
hi s &lt;<a href=3D"mailto:learnerdatabase99@gmail.com" target=3D"_blank">l=
earnerdatabase99@gmail.com</a>&gt; wrote:<br><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex"><div dir=3D"ltr">so that it will be able to assign the =
privilege, so we will be able to create the event trigger without need to r=
un the event trigger script from super user itself?</div></blockquote><div>=
<br></div><div>Write a security-definer function owned by superuser and gra=
nt app_user permission to execute it.</div><div><br></div><div>David J.</di=
v><div>=C2=A0</div></blockquote><div><br></div><div>Thank You David.</div><=
div><br></div>=C2=A0Are you saying something like below, in which we first =
create the function from super user and then execute the grant? But doesn&#=
39;t that mean, each time we want to create a new event trigger we have to =
be again dependent on the &quot;super user&quot; to modify the security def=
iner function?<br><br><font face=3D"monospace" size=3D"1">CREATE OR REPLACE=
 FUNCTION create_event_trigger_func()<br>=C2=A0 RETURNS void<br>=C2=A0 LANG=
UAGE plpgsql<br>=C2=A0 SECURITY DEFINER<br>=C2=A0 AS $$<br>=C2=A0 BEGIN<br>=
=C2=A0 =C2=A0 EXECUTE &#39;CREATE EVENT TRIGGER event_trigger_name ON schem=
a_name ...&#39;;=C2=A0<br>=C2=A0 END;<br>$$;</font></div><div class=3D"gmai=
l_quote"><font face=3D"monospace" size=3D"1"><br></font><div><font face=3D"=
monospace" size=3D"1">GRANT EXECUTE ON FUNCTION create_event_trigger_func()=
 TO app_user;=C2=A0</font></div></div></div>

--0000000000002c90650616977d43--