Subject: Re: error in trigger creation

From: Adrian Klaver <[email-masked]>
Date: Sun, 21 Apr 2024 13:53:05 -0700

On 4/21/24 11:20, yudhi s wrote:
> 
> On Sun, Apr 21, 2024 at 8:13 PM Tom Lane <[email-masked] 
> <mailto:[email-masked]>> 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
[email-masked]

---

From: Tom Lane <[email-masked]>
Date: Sun, 21 Apr 2024 17:21:20 -0400

Adrian Klaver <[email-masked]> writes:
>> So in this case i was wondering if "event trigger" can cause any 

> turn can do many things, you do the math on the threat level.

As a trivial example: an event trigger could prevent the legitimate
superuser(s) from doing anything at all in that database, just by
blocking all their commands.  This might not even require malicious
intent, merely faulty coding --- but the opportunity for malicious
intent is staggeringly large.

			regards, tom lane