Re: error in trigger creation

On 4/21/24 11:20, yudhi s wrote:
>
> On Sun, Apr 21, 2024 at 8:13 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>

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

Which Postgres has

https://www.postgresql.org/docs/current/ddl-priv.html

TRIGGER

Allows creation of a trigger on a table, view, etc.

but you are talking about event triggers

https://www.postgresql.org/docs/current/sql-createeventtrigger.html

where

"Only superusers can create event triggers."

To paraphrase Henry Ford, you can have any user for an event trigger as
long as the user is a superuser.

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

An event trigger runs as a superuser and executes a function that in
turn can do many things, you do the math on the threat level.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com