public inbox for [email protected]
help / color / mirror / Atom feedRe: error in trigger creation
2+ messages / 2 participants
[nested] [flat]
* Re: error in trigger creation
@ 2024-04-21 20:53 Adrian Klaver <[email protected]>
2024-04-21 21:21 ` Re: error in trigger creation Tom Lane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Adrian Klaver @ 2024-04-21 20:53 UTC (permalink / raw)
To: yudhi s <[email protected]>; Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-general <[email protected]>
On 4/21/24 11:20, yudhi s wrote:
>
> On Sun, Apr 21, 2024 at 8:13 PM Tom Lane <[email protected]
> <mailto:[email protected]>> 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 protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: error in trigger creation
2024-04-21 20:53 Re: error in trigger creation Adrian Klaver <[email protected]>
@ 2024-04-21 21:21 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2024-04-21 21:21 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: yudhi s <[email protected]>; David G. Johnston <[email protected]>; pgsql-general <[email protected]>
Adrian Klaver <[email protected]> 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.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-04-21 21:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-21 20:53 Re: error in trigger creation Adrian Klaver <[email protected]>
2024-04-21 21:21 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox