Thread: Need psql send email

Hi All, I am new to postgresql. I want to send email by using pl pgsql. I want to know how to set up the configurations for mail server. Can any one help me in solving this?. [hidden email]

---

View this message in context: Need psql send email
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Check this article: http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/

On 20/09/2012 13:07, pavithra wrote:
> Hi All, I am new to postgresql. I want to send email by using pl pgsql.
> I want to know how to set up the configurations for mail server. Can any
> one help me in solving this?. [hidden email]

Hi there,

It's not possible to send email directly from pl/pgsql; it might be
possible in the untrusted form of pl/perl, but I'm not sure.

A possible alternative would be to have an external process poll a queue
table, take its data from there and send the emails.

HTH,

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On Thu, Sep 20, 2012 at 05:07:18AM -0700, pavithra wrote:
> Hi All,I am new to postgresql. I want to send email by using pl pgsql. I want
> to know how to set up the configurations for mail server.Can any one help me
> in solving this?. pavithra.ibt@gmail.com

http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

I am more wondered where we need to give the port address and smtpserver.

Can you give me the details of these?.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Need-psql-send-email-tp5724700p5724705.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

> > Hi All,I am new to postgresql. I want to send email by using pl
> pgsql. I want
> > to know how to set up the configurations for mail server.Can any one help me
> > in solving this?. pavithra.ibt@gmail.com
>
> http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/
>
> Best regards,
>
> depesz

Alternatively:

CREATE OR REPLACE FUNCTION sendmail(p_from text, p_to text, p_subject text, p_content text)
  RETURNS void AS
$BODY$
use strict;
use warnings;
my ($from, $to, $subject, $content) = @_;
 
open(MAIL, "|/usr/sbin/sendmail -t") or die 'Cannot send mail';
print MAIL "From: $from\n";
print MAIL "To: $to\n";
print MAIL "Subject: $subject\n\n";
print MAIL "$content";
 
close(MAIL);
$BODY$
  LANGUAGE plperlu;


Works ok provided sendmail is configured.

or:

CREATE OR REPLACE FUNCTION send_smtp(p_mail_host text,
                                        p_from text,
                                        p_to text,
                                        p_subject text,
                                        p_content text,
                                        p_timeout integer DEFAULT 60,
                                        p_debug integer DEFAULT 0,
                                        p_exactaddr integer DEFAULT 1,
                                        p_skipbad integer DEFAULT 1)
  RETURNS void AS
$BODY$
use strict;
use warnings;
use Net::SMTP;
no strict 'refs';

my ($host, $sender, $recipient, $subject, $body, $timeout, $debug, $exact, $skipbad) = @_;
(!defined($host) || !($host)) && die 'No SMTP host provided.';
(!defined($sender) || !($sender)) &&  die 'No sender address/name provided.';
(!defined($recipient) || !($recipient)) &&  die 'No recipient address specified.';

my $mail = Net::SMTP->new(
                                Host => $host,
                                Debug => $debug,
                                Timeout => $timeout,
                                ExactAddresses => $exact
                        ) or die 'Net::SMTP->new() Failed';

$mail->mail($sender);
$mail->recipient($recipient, { SkipBad => $skipbad });

$mail->data();
$mail->datasend("MIME-Version: 1.0\n");
$mail->datasend("From:" . $sender . "\n");
$mail->datasend("To:" . $recipient . "\n");
$mail->datasend("Reply-To: ". $sender . "\n");
$mail->datasend("Subject:" . $subject . "\n\n");
$mail->dataend();
$mail->quit();
$BODY$
  LANGUAGE plperlu;


Feel free to hack away as much as required.

Both of these work fine provided PL/PerlU is installed and the server is properly configured on the network, and that there is a valid SMTP mail host to receive.

Cheers

Martin

=============================================

Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England

Telephone numbers:
+44 (0)115 951 88 00 (main)

For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=================================

================
Confidentiality Statement
This transmission is for the addressee only and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================

On 09/20/2012 08:40 PM, Martin French wrote:
>
> Both of these work fine provided PL/PerlU is installed and the server is
> properly configured on the network, and that there is a valid SMTP mail
> host to receive.

The 1st one seems OK in a scary-from-a-security-standpoint kind of way.

The 2nd, not so much. See
   http://stackoverflow.com/questions/12002662/psql-trigger-send-email

Imagine if the DNS goes wonky. Do you want all your backends tied up in
DNS lookups? Or timing-out TCP connections?

BTW, pavithra, check out http://brandolabs.com/pgmail if you really want
to do it in the database.

--
Craig Ringer

> The 1st one seems OK in a scary-from-a-security-standpoint kind of way.

Agree, it needs to be weighed up and assessed from a security stand point I guess.
 
> The 2nd, not so much. See
>    http://stackoverflow.com/questions/12002662/psql-trigger-send-email
>
> Imagine if the DNS goes wonky. Do you want all your backends tied up in
> DNS lookups? Or timing-out TCP connections?

Agree 100%, which is why I noted: "the server is properly configured on the network"... I suppose you could always provide an IP address as the mail host. This function is only a "Quick Knock Together" job, that works readily enough.

>

IMHO There's always an inherent risk with any form of sending mail from an RDBMS, whether it be abuse or otherwise, however; it's one of those situations where "needs must", and more often than not must be done quickly.

I would guess that having SMTP built into the DB engine itself would be no less susceptible to abuse or problems than any other method (For example UTL_SMTP in Oracle, which I've had hang before due to issues with SMTP servers).

I guess it's one of those where you just have to weigh up the options and choose the best one for your situation/application.

Cheers

Martin
=============================================

Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England

Telephone numbers:
+44 (0)115 951 88 00 (main)

For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=================================

================
Confidentiality Statement
This transmission is for the addressee only and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================

Hi All, I am new to postgresql. I want to send email by using pl pgsql. I want to know how to set up the configurations for mail server. Can any one help me in solving this?. [hidden email]

---

View this message in context: Need psql send email
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Dear friend,

I don't know if it is possible. But my experience with MS SQL Server (integration with OutLook) introduces hundreds of flaws (including crashes) into the database.
How did I accomplish this task:

a) To notify backups and so, I've configured my Cron task to do that (it is fairly easy and well documented)

b) To notify about business tasks of my applications, my applications send the e-mail (in my case, I do use Java, so I use standard JavaMail API that does everything in a snap without any flaws for years now). I believe every language in the world has similar stable APIs for sending e-mails

c) If I need to send e-mail based on database events (like a trigger), I use a "Queue Table" where I insert messages that need to be sent, and have external application that (from time to time) checks this table for new messages to be sent.


I hope this ideas help you.

Regards,

Edson.

Hi all;