Thread: Is there a good way to make the database server send e-mail?

Is there a good way to make the database server send e-mail?

I've been ask to work on a (Windows, client-server) application and
implement a feature whereby duely authenticated and connected users can
reset the password of other users without having to have DBA privileges.
I've got that part working by using a stored procedure with the SECURITY
DEFINER attribute (and the function is created by a DBA user) that calls
ALTER USER...

My next step is to have the database mail the new password to the reset
user (the system uses e-mail addresses as usernames, so the database knows
how to contact the user), along with the username of the person doing the
resetting. I need help with suggestions on learning how to make the
database server initiate an e-mail message.


Regards,
Berend Tober

Perhaps this would work: http://sourceforge.net/projects/pgmail/

(I've never used it, just happened across it this weekend).

Cheers,
Steve


On Monday 20 January 2003 9:17 am, Berend Tober wrote:
> Is there a good way to make the database server send e-mail?
>
> I've been ask to work on a (Windows, client-server) application and
> implement a feature whereby duely authenticated and connected users can
> reset the password of other users without having to have DBA privileges.
> I've got that part working by using a stored procedure with the SECURITY
> DEFINER attribute (and the function is created by a DBA user) that calls
> ALTER USER...
>
> My next step is to have the database mail the new password to the reset
> user (the system uses e-mail addresses as usernames, so the database knows
> how to contact the user), along with the username of the person doing the
> resetting. I need help with suggestions on learning how to make the
> database server initiate an e-mail message.
>
>
> Regards,
> Berend Tober
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

On Mon, Jan 20, 2003 at 12:17:51PM -0500, Berend Tober wrote:

> how to contact the user), along with the username of the person doing the
> resetting. I need help with suggestions on learning how to make the
> database server initiate an e-mail message.

I suggest you write a daemon which reads from a queue table in the
database, and sends the email.  This is slightly safer, because if
the email system fails in some way, the daemon can learn about it and
handle it.  Also, this will scale: if you have to send 10,000 emails
at a time, things a re going to block fora long time while you write
all that into the mail queue.  A dedicated program can handle it
better, by selecting a few thousand to prepare at a time.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110