Feedback-ID: i76984098:Fastmail
Message-ID: <73f0e7a2-b958-4a95-96d8-08e08909c9c1@aklaver.com>
Date: Sun, 21 Apr 2024 13:53:05 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: error in trigger creation
To: yudhi s <learnerdatabase99@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
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>
 <CAEzWdqcs8OYtK9dWbev986FfNO0i9rGDvChZTti2xWL8HuXnbg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAEzWdqcs8OYtK9dWbev986FfNO0i9rGDvChZTti2xWL8HuXnbg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/73f0e7a2-b958-4a95-96d8-08e08909c9c1%40aklaver.com>
Precedence: bulk

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