Re: untrusted languages and non-global superusers?

CSN wrote:

>
>
>Convenience! I want the email sent whether the member
>is added via the web interface, directly in the
>database, from the command line, etc. I don't see any
>downside. It's only one user that'll be using this
>function.
>
>
>
Well, there are three issues with doing complex things like sending mail
from your backend.

The first is security. Personally, I think that this is a concern that
can be mitigated if there are only a few people who can create untrusted
language functions in an organization and if there is code review of
them by others before they are added.

The second is not so easily mitigated. This is latency. If you are
inserting a large number of users at once, then the trigger will fire
when the new member is added and you will need to wait for it. For
performance reasons, it is often a good idea to separate this from the
backend so that inserts can return quickly.

The third though is the biggie--- transactional control. Lets say I
have a trigger that sends the email. Lets say the transaction aborts.
The user still gets the email even though they were never added to the
system. So lets say you try to deal with this by deferring the trigger
until the transaction completes. It is still possible that (perhaps at
some later date) another trigger will be deferred which will run after
this one. The result is fantom emails.

Personally, I would not mind using email-from-db functions under limited
circumstances. Some sort of security-related trigger might be used when
something happens worth notifying the database admins immediately under
certain circumstances (NEW USER CREATED. Login: ....).

I am not going to say "never do it" but I think that in most cases,
NOTIFY is more flexible and will perform better. Here you can use a
trigger to insert into a message queue and use NOTIFY to let a listening
process know that some action needs to be taken.

Best Wishes,
Chris Travers
Metatron Technology Consulting