MIME-Version: 1.0
References: <CAEzWdqcimp5dnNOavaSkMCOKW_FVsKC2101g=dFsyjQ-9dA3uw@mail.gmail.com>
 <CAKFQuwa+jpZ-pucWc92OCYcwCnj7C_POg8k=5BvbPZyL97R-Jw@mail.gmail.com>
 <CAEzWdqfqr9e3OpFd5Nhqha3Ggm=+UJdWkgvo7dpAa3W99S2g5Q@mail.gmail.com>
 <CAKFQuwYu8w7BMX_9xEP1t5ULT7pV-qO1Yotn1qtdMuEpWCqhFg@mail.gmail.com> <2720974.1713710606@sss.pgh.pa.us>
In-Reply-To: <2720974.1713710606@sss.pgh.pa.us>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Sun, 21 Apr 2024 23:50:38 +0530
Message-ID: <CAEzWdqcs8OYtK9dWbev986FfNO0i9rGDvChZTti2xWL8HuXnbg@mail.gmail.com>
Subject: Re: error in trigger creation
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005d01fe06169f61d5"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqcs8OYtK9dWbev986FfNO0i9rGDvChZTti2xWL8HuXnbg%40mail.gmail.com>
Precedence: bulk

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

On Sun, Apr 21, 2024 at 8:13=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> wrote:
> >> 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 =E2=80=9Cexecute=E2=80=9D in plpgsql.
>
> You might as well just give that user superuser and be done with it.
> It's foolish to imagine that you have any shred of security left
> if you're letting a user that's not 100.00% trusted write event
> triggers.  (Much less execute any SQL command whatsoever, which
> is what it sounds like David is suggesting you create a function
> to do.)
>
>
So do you mean , we should not create the event trigger using the "security
definer" , rather have the super user do this each time we have to create
the event trigger?

Actually , I am not very much aware about the security part, but is it fine
to give the super user privilege to the application user(say app_user) from
which normally scripts/procedures get executed by the application, but
nobody(individual person) can login using that user.

Additionally in other databases, triggers are driven by some
specific privileges (say for example in oracle "create trigger" privilege).
And it doesn't need any super user and we were having many applications in
which the application user (which were used for app to app login) was
having these privileges, similar to "create table" privileges which comes
by default to the schema who owns the objects  etc. So in this case i was
wondering if "event trigger" can cause any additional threat and thus there
is no such privilege like "create trigger" exist in postgres and so it
should be treated cautiously?

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Sun, Apr 21, 2024 at 8:13=E2=80=AFPM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">&quot;Dav=
id G. Johnston&quot; &lt;<a href=3D"mailto:david.g.johnston@gmail.com" targ=
et=3D"_blank">david.g.johnston@gmail.com</a>&gt; writes:<br>
&gt; On Sunday, April 21, 2024, yudhi s &lt;<a href=3D"mailto:learnerdataba=
se99@gmail.com" target=3D"_blank">learnerdatabase99@gmail.com</a>&gt; wrote=
:<br>
&gt;&gt; Are you saying something like below, in which we first create the<=
br>
&gt;&gt; function from super user and then execute the grant? But doesn&#39=
;t that mean,<br>
&gt;&gt; each time we want to create a new event trigger we have to be agai=
n<br>
&gt;&gt; dependent on the &quot;super user&quot; to modify the security def=
iner function?<br>
<br>
&gt; Dynamic SQL.=C2=A0 See =E2=80=9Cexecute=E2=80=9D in plpgsql.<br>
<br>
You might as well just give that user superuser and be done with it.<br>
It&#39;s foolish to imagine that you have any shred of security left<br>
if you&#39;re letting a user that&#39;s not 100.00% trusted write event<br>
triggers.=C2=A0 (Much less execute any SQL command whatsoever, which<br>
is what it sounds like David is suggesting you create a function<br>
to do.)<br><br></blockquote><div><br></div><div>So do you mean , we should =
not create the event trigger using the &quot;security definer&quot; , rathe=
r have the super user do this each time we have to create the event trigger=
?</div><div><br></div><div>Actually , I am not very much aware about the se=
curity part, but is it fine to give the super user privilege to the applica=
tion user(say app_user) from which normally scripts/procedures get executed=
 by the application, but nobody(individual person) can login using that use=
r.</div><div><br></div><div>Additionally in other databases, triggers are d=
riven by some specific=C2=A0privileges (say for example in oracle &quot;cre=
ate trigger&quot; privilege). And it doesn&#39;t=C2=A0need any super user a=
nd we were having=C2=A0many applications in which the application user (whi=
ch were used for app to app login) was having these privileges, similar to =
&quot;create table&quot; privileges which=C2=A0comes by default to the sche=
ma who owns the objects=C2=A0 etc. So in this case i was wondering if &quot=
;event trigger&quot; can cause any additional threat and thus there is no s=
uch privilege like &quot;create trigger&quot; exist in postgres and so it s=
hould be treated=C2=A0cautiously?</div></div></div>

--0000000000005d01fe06169f61d5--