Thread: Need psql send email

Need psql send email

From
pavithra
Date:
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.

Re: Need psql send email

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


2012/9/20 pavithra <pavithra.ibt@gmail.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?. [hidden email]



--
Victor Y. Yegorov

Re: Need psql send email

From
Raymond O'Donnell
Date:
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


Re: Need psql send email

From
hubert depesz lubaczewski
Date:
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/


Re: Need psql send email

From
pavithra
Date:
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.


Re: Need psql send email

From
Martin French
Date:

> > 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.
=================================================

Re: Need psql send email

From
Craig Ringer
Date:
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


Re: Need psql send email

From
Martin Gainty
Date:
many is the time when spammers have used Open Relay SMTP servers to send their junk mail so i would advise against using sendmail on Open Relay SMTP servers
I would narrow access by SSH or open a secure tunnel thru your firewall to your own internal DatabaseManagementSystem/J2EEServer/ApacheHTTPServer then allow
those scripts (PL-SQL or Perl or Java) to invoke sendmail to the SMTPMailServer inside the firewall

If you are sponsoring your own email-server and I hope you are DISALLOW OPEN RELAY
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.




To: depesz@depesz.com
CC: pavithra.ibt@gmail.com; pgsql-general@postgresql.org; pgsql-general-owner@postgresql.org
Subject: Re: [GENERAL] Need psql send email
From: Martin.French@romaxtech.com
Date: Thu, 20 Sep 2012 13:40:58 +0100


> > 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.
=================================================

Re: Need psql send email

From
Martin French
Date:


> 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.
=================================================

Re: Need psql send email

From
Edson Richter
Date:
Em 20/09/2012 09:07, pavithra escreveu:
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.

Re: Need psql send email

From
Chris Travers
Date:
Hi all;

A couple points here.

First, you probably don't want to send email directly from a database function.  This gives significant problems for which there is no good solution.  Consider:

1)  You sent your email and now the transaction rolls back.  You *cannot* roll back the sent email.

2)  Your email fails to send.  Do you abort the transaction?

IMO it is always better to send email from a second process that can be notified on db commit.  This avoids these issues and kicks them to a post-transaction handler.

As luck would have it, I recently set a project up on Google Code to help address this (and other application integration) issues.  See http://code.google.com/p/pg-message-queue/

There isn't a lot of overlap with something like pgq.  This is listen/notify/queue tables based.  May not ever be big and professional but it should work once the bugs are ironed out.  Even before then it may give a good idea of how to implement a notification-based queue on PostgreSQL.  

The idea here is that you can essentially send a message to a channel on a db event (say, from a trigger) and then have another app that either periodically checks the queue (say, from a cron job) or listens on a channel for notifications.

The whole thing was confirmed working before I made some changes.  If folks are interested in helping I am sure it will be well tested and working in no time.  Please read the docs first though.  I wouldn't say it is production-ready yet, but it may provide an overview of how to go about implementing something like this in production.

Also for more info on how to do this with a LISTEN/NOTIFY approach outside of the above, see http://ledgersmbdev.blogspot.com/2012/09/objectrelational-interlude-messaging-in.html

In general I think mixing transactional and non-transactional side-effects is just asking for trouble.  Don't do it any  more than you have to.

Best Wishes,
Chris Travers