pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feeda trigger that sends an email
5+ messages / 4 participants
[nested] [flat]
* a trigger that sends an email
@ 2000-04-06 08:50 Oscar Serrano <oserra@fondos.net>
0 siblings, 0 replies; 5+ messages in thread
From: Oscar Serrano @ 2000-04-06 08:50 UTC (permalink / raw)
To: pgsql-interfaces
Hi:
I need to do something special.
I need that when a new record is inserted in one of my tables, the system
sends an email.
I thing I need to create a trigger. And I thing I need to create a function
that sends an email.
Is it possible?
May I do the function that sends the email in Perl?
Any idea?
Thank you very much.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: a trigger that sends an email
@ 2000-04-06 14:51 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Tom Lane @ 2000-04-06 14:51 UTC (permalink / raw)
To: Oscar Serrano <oserra@fondos.net>; +Cc: pgsql-interfaces
"Oscar Serrano" <oserra@fondos.net> writes:
>>>> I thing I need to create a trigger. And I thing I need to create a
>>>> function that sends an email.
>>>> Is it possible?
>>>> May I do the function that sends the email in Perl?
I don't think you can do this directly from a trigger, unless you want
to add the mail-sending function as a C extension to the backend.
You'll run into security restrictions. All of the higher-level trigger
programming languages we offer are "trusted", which means you can't do
anything that would affect files or programs outside the database.
Sending email is right out.
Of course there's nothing wrong with a C-coded function; the system's
full of them ;-). But you might want to think about other ways.
One possibility is to set up an always-running application that connects
to the database and then just sits there waiting for records to be added
to the table, then sends the email when that happens. You could use a
NOTIFY command (executed by a trigger on the table) to wake up the app
when something happens, so that it doesn't need to take up CPU time
checking the table unnecessarily.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: a trigger that sends an email
@ 2000-04-06 16:06 Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Moray McConnachie @ 2000-04-06 16:06 UTC (permalink / raw)
To: Oscar Serrano <oserra@fondos.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Oscar Serrano" <oserra@fondos.net>
Cc: "pgsql-interfaces@postgreSQL.org" <pgsql-interfaces@postgresql.org>
Sent: Thursday, April 06, 2000 3:51 PM
Subject: Re: [INTERFACES] a trigger that sends an email
> "Oscar Serrano" <oserra@fondos.net> writes:
> >>>> I thing I need to create a trigger. And I thing I need to create a
> >>>> function that sends an email.
> >>>> Is it possible?
> >>>> May I do the function that sends the email in Perl?
>
> I don't think you can do this directly from a trigger, unless you want
> to add the mail-sending function as a C extension to the backend.
> You'll run into security restrictions. All of the higher-level trigger
> programming languages we offer are "trusted", which means you can't do
> anything that would affect files or programs outside the database.
> Sending email is right out.
Could one do a rule that used a dummy function, where the function called
the Perl routine which generates the email?
CREATE RULE emailer AS ON INSERT TO new_accounts
DO SELECT new_account_email_function(NEW.emailaddress);
or something like that? Or is this subject to the same restrictions?
M.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: a trigger that sends an email
@ 2000-04-06 17:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2000-04-06 17:52 UTC (permalink / raw)
To: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>; +Cc: Oscar Serrano <oserra@fondos.net>; pgsql-interfaces
"Moray McConnachie" <moray.mcconnachie@computing-services.oxford.ac.uk> writes:
>> programming languages we offer are "trusted", which means you can't do
>> anything that would affect files or programs outside the database.
>> Sending email is right out.
> Could one do a rule that used a dummy function, where the function called
> the Perl routine which generates the email?
If it's in plperl then it's going to be running in a "safe" interpreter,
which should mean that you won't be able to do anything to send mail.
> CREATE RULE emailer AS ON INSERT TO new_accounts
> DO SELECT new_account_email_function(NEW.emailaddress);
> or something like that? Or is this subject to the same restrictions?
Wouldn't be much of a security system if it were that easy to get
around, would it?
The problem here is that anything done by the backend is done with the
full permissions of user postgres (or whatever the backend is running
as). So we don't want ordinary users to have access to programming
languages that will allow them to muck around with postgres' files.
The only way to escape the restrictions is to write a C-language
extension. That has to be installed by the database admin, and we
expect him to exercise some caution in what he will install...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: a trigger that sends an email
@ 2000-04-06 17:54 Craig Orsinger <orsingerc@epg-gw1.lewis.army.mil>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Craig Orsinger @ 2000-04-06 17:54 UTC (permalink / raw)
To: pgsql-interfaces
On 06-Apr-00 Tom Lane wrote:
> "Oscar Serrano" <oserra@fondos.net> writes:
[snip]
> One possibility is to set up an always-running application that connects
> to the database and then just sits there waiting for records to be added
> to the table, then sends the email when that happens. You could use a
> NOTIFY command (executed by a trigger on the table) to wake up the app
> when something happens, so that it doesn't need to take up CPU time
> checking the table unnecessarily.
>
Seems like if you really want e-mail, a separate program
that waits for a NOTIFY event and then sends e-mail might be the
best answer. I may be off-base here, but it seems best to keep
functions that are run by the database as light and simple as
possible. Sending e-mail, using C or any other method, is a pretty
complex action. A separate program could be written in Perl, C,
C++, or TCL, or any other language that has PostgreSQL bindings
or libraries.
----------------------------------
Date: 06-Apr-00 Time: 10:50:16
Craig Orsinger (email: <orsingerc@epg-gw1.lewis.army.mil>)
Logicon RDA
Bldg. 8B28 "Just another megalomaniac with ideas above his
6th & F Streets station. The Universe is full of them."
Ft. Lewis, WA 98433 - The Doctor
----------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2000-04-06 17:54 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-06 08:50 a trigger that sends an email Oscar Serrano <oserra@fondos.net>
2000-04-06 14:51 Re: a trigger that sends an email Tom Lane <tgl@sss.pgh.pa.us>
2000-04-06 16:06 ` Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
2000-04-06 17:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-04-06 17:54 ` Craig Orsinger <orsingerc@epg-gw1.lewis.army.mil>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox