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@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.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.klaver@aklaver.com