Feedback-ID: i76984098:Fastmail
Message-ID: <6f2f938b-b3fb-4dd8-9dbf-e82624b53152@aklaver.com>
Date: Sun, 21 Apr 2024 17:47:22 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: error in trigger creation
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: yudhi s <learnerdatabase99@gmail.com>,
 "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>
 <73f0e7a2-b958-4a95-96d8-08e08909c9c1@aklaver.com>
 <2858912.1713734480@sss.pgh.pa.us>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <2858912.1713734480@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/6f2f938b-b3fb-4dd8-9dbf-e82624b53152%40aklaver.com>
Precedence: bulk

On 4/21/24 14:21, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 4/21/24 11:20, yudhi s wrote:
>>> 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.
> 
> 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.

As an FYI to above:

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

"Event triggers are disabled in single-user mode (see postgres). If an 
erroneous event trigger disables the database so much that you can't 
even drop the trigger, restart in single-user mode and you'll be able to 
do that."


> 
> 			regards, tom lane

-- 
Adrian Klaver
adrian.klaver@aklaver.com