sp_send_dbmail alternative in postgres? Easy way to send Postgres email reports?

Question

SQL Server has a cool feature in sp_send_dbmail (quick guide here) that lets you email out reports. Does anything like that exist in Postgres? My postgres is hosted at Heroku so I can share a dataclip, but I am wondering if there's an easy way to schedule emails to send out reports.

Answer 1

You can use pgMail to send mail from within PostgreSQL.

Prerequisites:

Before you can use pgMail, you must install the TCL/u procedural language. TCL/u is an UNRESTRICTED version of TCL that your database can use in its stored functions. Before you go nuts installing the unrestricted TCL procedural language in all of your databases, take into account that you must prepare adequate security precautions when adding the TCL/u language to your database! I will not be responsible for misconfigured servers allowing dangerous users to do bad things!

To install the TCL/u procedural language, you must have compiled (or used binary packages) and installed the TCL extensions of PostgreSQL. Once you are sure this has been completed, simply type the following at the unix shell prompt as a database administrator.

# createlang pltclu [YOUR DATABASE NAME]

In the place of [YOUR DATABASE NAME], put the name of the database to which you will be adding the stored procedure. If you want it to be added to all NEW databases, use "template1" as your database name.

Before adding new procedure to the DB first do:

Replace the text <ENTER YOUR MAILSERVER HERE> with the fully qualified domain name for your mailserver. i.e., mail.server.com.

Replace the text <ENTER YOUR DATABASESERVER HERE> with the fully qualified domain name for your database server. i.e., db.server.com.

Once you have done the above, you are ready to go.

After this step, use the psql interface to add the pgMail function. Just copy the contents of the pgmail.sql file and paste it into your window. You may also load it directly from the command line by typing:

# psql -e [YOUR DATABASE NAME] < pgMail.sql

Once you have installed the stored function, simply call the procedure as follows.

select pgmail('Send From ','Send To ','Subject goes here','Plaintext message body here.');

select pgmail('Send From ','Send To ','Subject goes here','','HTML message body here.');

Or now, multipart MIME!

select pgmail('Send From ','Send To ', 'Subject goes here','Plaintext message body here.', 'HTML message body here.');

In both the "Send From" and "Send To" fields, you may include either only the email, or the email enclosed in <> with a plaintext name.

Testing Your Install

I have included an example for you to try. You MUST FIRST replace the string in the example.execute.sql script with your real email address, and install the plpgsql language just like you did the pltclu above. You can do that by entering a createlang [YOUR DATABASE NAME] plpgsql.

Once that is complete, first run the example.setup.sql. Then execute the example.execute.sql script. Provided everything is working well, you will see 2 emails in your mailbox. To remove this example, execute the example.cleanup.sql script.

SMTP Auth

pgMail does not support SMTP Auth. Most of the folks that use it either set up a local mailserver on the database server for local queueing and then use that setup for any relaying required (with auth). Or, alternatively, there is usually a special rule made in the the /etc/mail/access (or equivalent) file to allow relaying from that IP used by the database server. Obviously, the latter option doesn't work with GMail.

Part of the reasoning behind this is that auth will be problematic in the transactional nature of pgMail for big jobs. The ideal solution would be to drop an EXIM server on the database server and have that handle any type of authentication as a smart relay server. Here is a link that has more info on how to set SMTP server up.

Documentation: http://brandolabs.com/pgmail

Answer 2

You can also use py_pgmail from https://github.com/lcalisto/py_pgmail

1. Create py_pgmail function by running py_pgmail.sql

2. After the function is created you can simply call the function from anywhere in the database as:

   select py_pgmail('sentFromEmail',
                    array['destination emails'],
                    array['cc'],
                    array['bcc'],
                    'Subject',
                    '<USERNAME>','<PASSWORD>',
                    'Text message','HTML message',
                    '<MAIL.MYSERVER.COM:PORT>')
   

array['cc'] and array['bcc'] can be empty arrays like array['']