INTRO – Best way to send email from database is using Notify and Listen
First of all, Hubert Lubaczewski has a wondeful blog post about the how to send mail from database and interact with the “world outside of the database.” Thanks to his explanation, I was able to implement it easily.
There are three main options to send emails directly from a PostgreSQL database:
Using an Extension: Some PostgreSQL extensions, like pgMail (uses PL/TclU – Procedural Untrusted Tool Command Language) or pgSmtp (uses plpython3u – Procedural Untrusted Python Language) are designed specifically for sending emails. These extensions simplify the process by providing built-in functionality for sending messages directly from within the database.
Custom Stored Procedure with Untrusted Languages: By using untrusted languages like PL/PerlU, PL/TclU you can create a custom stored procedure that interacts with external systems. These untrusted languages have access to external resources such as files, sockets and network systems, making it possible to send emails or perform other tasks that require communication outside the database.
Notify and Listen Methodology: This approach involves using PostgreSQL’s NOTIFY and LISTEN commands. The database sends notifications, which external services or daemons (written in languages like Python, Node.js, etc.) listen for. When a relevant event occurs, the external service sends the email based on the notification payload. This method offloads the email-sending task to a service outside the database while keeping the logic and triggers inside PostgreSQL.
Each method has its advantages depending on your needs for control, security and ease of implementation. Using an extension and custom stored procedure with untrusted language is easy to implement but not a secure way.
Instead of using untrusted languages, we opted for the LISTEN/NOTIFY approach. This method monitors a queue stored in a database table and triggers the sending of emails via the sendmail command at the OS level. This allowed us to handle the email-sending logic outside the database while adding flexibility within PostgreSQL.
STEP 1 – Install and Configure postfix
Since we’re using Oracle Linux 8, we will use Postfix to send emails at the OS level.
[root@vt03 ~]# yum install postfix
[root@vt03 ~]# systemctl start postfix.service
[root@vt03 ~]# systemctl enable postfix.serviceTo ensure your server can send anonymous emails using Postfix, you’ll need to coordinate with your organization’s mail server administrators and Make sure your server IP is whitelisted on the mail server for outgoing anonymous mail.
STEP 2 – Create table in postgres database
Create a dedicated user and a schema for mailing purposes and create a table on the postgres database which will store the mail records. We will use it as a queue.
postgres=> CREATE ROLE mailer WITH LOGIN ENCRYPTED PASSWORD 'XXXXX';
postgres=> create schema mailer;
postgres=> grant all on mailer to mailer;
postgres=> ALTER SCHEMA mailer OWNER TO mailer;
postgres=# CREATE TABLE IF NOT EXISTS mailer.t_email_notifications (
id serial,
email_from text,
email_subject text,
email_to text,
email_body text,
sent boolean DEFAULT false,
added_time timestamp with time zone DEFAULT now(),
sent_time timestamp with time zone,
CONSTRAINT cn_mlntfctns_mlfrm CHECK (
char_length(email_from) <= 254
),
CONSTRAINT cn_mlntfctns_mlsbjct CHECK (
char_length(email_subject) <= 254
),
CONSTRAINT cn_mlntfctns_mlt CHECK (
char_length(email_to) <= 254
)
) TABLESPACE pg_default;
postgres=# alter table mailer.t_email_notifications owner to mailer;STEP 3 – Create Notifying Trigger
Create a trigger and a related function that notifies a background process (which is responsible for sending emails at the OS layer) when a new record is added to the t_email_notifications table.
postgres=# CREATE OR REPLACE FUNCTION mailer.fn_notify_email_send() RETURNS trigger
LANGUAGE 'plpgsql' AS $BODY$
BEGIN PERFORM pg_notify('new_email', NEW.id :: text);
RETURN NEW;
END;
$BODY$;
postgres=# ALTER FUNCTION mailer.fn_notify_email_send() OWNER TO mailer;
postgres=# CREATE
OR REPLACE TRIGGER tr_email_send_trigger
AFTER INSERT ON mailer.t_email_notifications
FOR EACH ROW
EXECUTE FUNCTION mailer.fn_notify_email_send();With this setup, every time a new row is added to the t_email_notifications table, PostgreSQL will send a notification to the Python script responsible for sending emails. The Python script will be listening for these notifications and will handle the email-sending process as soon as it receives the notification, ensuring immediate email dispatch.
STEP 4 – Write Listening Python Script
Now we will create a Python script (email_listener.py) that listens for notifications on the new_email channel and sends emails using Postfix sendmail. We could have also used Node.js, C, Perl or another language that handles asynchronous events and callbacks efficiently, making it suitable for this type of task. I chose Python because it doesn’t require additional package installations. All the necessary modules (select, datetime, subprocess, logging) are part of the Python standard library. The only extra module needed is psycopg2, which is already installed as a prerequisite for Patroni. If you deploy this script to a server other than the database server, you will only need to install the psycopg2 pip package.
[root@vt03 Packages]# yum install ./python39-3.9.13-2.module+el8.7.0+20879+a85b87b0.x86_64.rpm ./python39-libs-3.9.13-2.module+el8.7.0+20879+a85b87b0.x86_64.rpm ./python39-pip-20.2.4-7.module+el8.6.0+20625+ee813db2.noarch.rpm ./python39-pip-wheel-20.2.4-7.module+el8.6.0+20625+ee813db2.noarch.rpm ./python39-setuptools-50.3.2-4.module+el8.5.0+20364+c7fe1181.noarch.rpm ./python39-setuptools-wheel-50.3.2-4.module+el8.5.0+20364+c7fe1181.noarch.rpm
[root@vt03 Packages]# alternatives --set python3 /usr/bin/python3.9
[root@vt03 patroni]# pip3 install ./psycopg2_binary-2.9.5-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl| import psycopg2 | |
| import select | |
| from datetime import datetime | |
| import subprocess | |
| import logging | |
| import time | |
| #Configure logging | |
| logging.basicConfig( | |
| filename='/var/log/postgresql_email_listener/postgresql_email_listener.log', | |
| level=logging.INFO, | |
| format='%(asctime)s – %(levelname)s – %(message)s' | |
| ) | |
| def send_mail(email_data): | |
| # This section is Docstring. Not a madatory section but a best practice for code quality, maintainability and usability. | |
| """ | |
| Sends an email using the `sendmail`command. | |
| Args: | |
| email_data(str): Concatenated email data using '€€' as delimiter. | |
| Returns: | |
| bool: True if the email is sent succesfully, False otherwise. | |
| """ | |
| # Split the email data using '€€' delimiter | |
| email_from, email_to, subject, body = email_data.split('€€') | |
| # Construct the email message in the `sendmail` format | |
| message = f"""From: {email_from} | |
| To: {email_to} | |
| Subject: {subject} | |
| Content-Type: text/plain; charset="UTF-8" | |
| {body} | |
| """ | |
| try: | |
| # Use the sendmail command with -t option | |
| process = subprocess.Popen(["/usr/sbin/sendmail", "-t"], stdin=subprocess.PIPE, stderr=subprocess.PIPE) | |
| stdout, stderr = process.communicate(message.encode("utf-8")) | |
| if process.returncode == 0: | |
| logging.info(f"Email sent succesfully to {email_to}") | |
| return True | |
| else: | |
| logging.error(f"Failed to send email to {email_to}. Error: {stderr.decode('utf-8').strip()}") | |
| return False | |
| except Exception as e: | |
| logging.error(f"An error occurred while sending email: {e}") | |
| return False | |
| def process_email(cur, email_id): | |
| """ Fetch and process a single email by ID.""" | |
| cur.execute(""" | |
| SELECT email_from || '€€' || email_to || '€€' || email_subject || '€€' || email_body | |
| FROM mailer.t_email_notifications where id = %s; | |
| """, (email_id,)) | |
| email = cur.fetchone() | |
| if email: | |
| # Email data will be in the format of '€€' concatenated string | |
| email_data = email[0] | |
| # Send the email using sendmail | |
| success = send_mail(email_data) | |
| if success: | |
| # Update the record to mark it as sent and set the sent_time | |
| cur.execute("UPDATE mailer.t_email_notifications SET sent= TRUE, sent_time = %s WHERE id = %s;", | |
| (datetime.now(), email_id)) | |
| logging.info(f"Email ID {email_id} marked as sent.") | |
| def process_unsent_emails(cur): | |
| """ Fetch and process all unsent emails.""" | |
| cur.execute("SELECT id FROM mailer.t_email_notifications WHERE sent = FALSE;") | |
| unsent_emails = cur.fetchall() | |
| for email in unsent_emails: | |
| process_email(cur,email[0]) | |
| def listen_notifications(conn_params): | |
| """Main loop to listen for PostgreSQL notifications.""" | |
| while True: | |
| try: | |
| # Establish a connection to the database | |
| logging.info("Connecting to the database…") | |
| # Connect to the PostgreSQL database via Unix socket | |
| conn = psycopg2.connect(**conn_params) | |
| conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) | |
| cur = conn.cursor() | |
| # Listen to the new_email channel | |
| cur.execute("LISTEN new_email;") | |
| logging.info("Waiting for notications on channel 'new_email'…") | |
| while True: | |
| # Use select to wait for 60 seconds(like sleep) any input on the connection | |
| if select.select([conn],[],[],60) == ([],[],[]): | |
| continue # No notification received, loop back and wait again | |
| conn.poll() | |
| while conn.notifies: | |
| notify = conn.notifies.pop(0) | |
| logging.info(f"Received notification: {notify.payload}") | |
| # Process the email corresponding to the received ID | |
| process_email(cur, notify.payload) | |
| # After processing the first notified email, process all unsent emails | |
| process_unsent_emails(cur) | |
| except (psycopg2.OperationalError,psycopg2.DatabaseError) as e: | |
| logging.error(f"Database connection error: {e}. Reconnecting in 60 seconds…") | |
| time.sleep(60) # Wait before trying to reconnect | |
| except KeyboardInterrupt: | |
| logging.info("Listener stopped.") | |
| break | |
| finally: | |
| # Ensure the cursor and connection are closed if open | |
| try: | |
| cur.close() | |
| conn.close() | |
| logging.info("Connection closed.") | |
| except NameError: | |
| pass # If cur/conn not defined, skip closing | |
| if __name__ == "__main__": | |
| conn_params = { | |
| 'dbname': 'postgres', | |
| 'host': 'pgcluster.localdomain', | |
| 'port': 5000, | |
| 'user': 'mailer', | |
| 'password': 'XXXXX' | |
| } | |
| listen_notifications(conn_params) |
STEP 5 – Create a systemd Service
Now we will create a service file under /etc/systemd/system/ called email_listener.service. Creating a service allows it to run seamlessly in the background, restart if it crashes, and automatically start with the reboots.
[root@vt03 ~]# vi /etc/systemd/system/email_listener.service
[Unit]
Description=PostgreSQL Email Listener Service
After=network.target
[Service]
Type=simple
ExecStart=/usr/bin/python3 /var/lib/pgsql/scripts/email_listener.py
Restart=on-failure
StandardOutput=file:/var/log/postgresql_email_listener/postgresql_email_listener.log
StandardError=file:/var/log/postgresql_email_listener/postgresql_email_listener.log
User=postgres
Group=postgres
[Install]
WantedBy=multi-user.target/var/lib/pgsql/scripts/email_listener.py is the script we have prepared in STEP 4. Service will be running with the postgres user, we will ensure that service user has write permissions to the log file.
[root@vt03 log]# mkdir /var/log/postgresql_email_listener
[root@vt03 log]# chown postgres:postgres /var/log/postgresql_email_listener
[root@vt03 ~]# touch /var/log/postgresql_email_listener/postgresql_email_listener.log
[root@vt03 ~]# chown postgres:postgres /var/log/postgresql_email_listener/postgresql_email_listener.log
[root@vt03 ~]# chmod 664 /var/log/postgresql_email_listener/postgresql_email_listener.logReload the systemd daemon to recognize the new service. Start service and enable it to restart on reboots.
[root@vt03 ~]# systemctl daemon-reload
[root@vt03 ~]# systemctl start email_listener.serviceIt will record all the log records to /var/log/postgresql_email_listener.log file.
STEP 6 – Configure logrotate
Since all logs are recorded in the /var/log/postgresql_email_listener.log file, we will configure log rotation for it. The log file will be rotated when it reaches a size of 100 MB, but you can adjust this based on your needs.
[root@vt03 ~]# vi /etc/logrotate.d/postgresql_mail_listener
/var/log/postgresql_email_listener/postgresql_email_listener.log
{
rotate 5
size 100M
compress
compresscmd /bin/xz
compressext .xz
create 0640 postgres postgres
copytruncate
notifempty
missingok
dateext
dateformat _%Y%m%d-%s
}STEP 7 – Test Findings
During the testing phase, messages with Turkish characters (such as ‘ç, ş, ı, ğ, ö, ü’) in the subject line failed. To diagnose the issue, we examined the log records in /var/spool/mail/root.
...
Action: failed
Status: 5.6.7
Diagnostic-Code: X-Postfix; SMTPUTF8 is required, but was not offered by host
amx03.localdomain[192.168.40.32]
...
Non-ASCII characters (those not part of the standard ASCII character set) cause an issue because Internet specifications for mail servers only allow ASCII text in message headers. Only mail servers that support a special feature called SMTPUTF8 can accept headers with non-ASCII characters. Although SMTPUTF8 support is enabled by default, we will disable it. We will configure Postfix to disable this feature, as the vast majority of email software is capable of handling such emails without it.
[root@vt03 ~]$ vi /etc/postfix/main.cf
...
# Added for using non-ASCII characters in the address localparts ( and in headers)
smtputf8_enable = noSTEP 8 – (Optional) A Generic Solution To Use In All Other Databases Running In The Cluster
We will create foreign tables (mailer.ft_mailer_notifications) in all other databases that reference the central table (mailer.t_emailer_nofications) in the PostgreSQL database using the Postgres Foreign Data Wrapper (FDW). A mail_sender function will be created with SECURITY DEFINER rights, allowing the function to execute with the privileges of its owner rather than the user invoking it. This contrasts with the default behavior (SECURITY INVOKER), where the function runs with the invoking user’s privileges. After completing the setup, a standard database user, granted only schema usage and execute privileges on the mail_sender function, will be able to send mail.
Enable postgresql foreign data wrapper in the database which needs sending mail.
test=# create extension postgres_fdw;
[postgres@vt03 ~]$ patronictl -c /etc/patroni.yml edit-config pgcluster
---
+++
@@ -54,7 +54,7 @@
port: 5432
random_page_cost: 1.3
shared_buffers: 32GB
- shared_preload_libraries: pg_stat_statements,oracle_fdw,pg_cron
+ shared_preload_libraries: pg_stat_statements,oracle_fdw,pg_cron,postgres_fdw
ssl: true
ssl_ca_file: /etc/ssl/postgres/root.crt
ssl_cert_file: /etc/ssl/postgres/server.crt
Apply these changes? [y/N]: y
Configuration changedCreate a staging schema and table in the source database for fdw.
test=# create schema mailer ;
test=# ALTER SCHEMA mailer OWNER TO mailer;
test=# grant all on schema mailer to mailer;
test=# show unix_socket_directories;
unix_socket_directories
-------------------------
/var/run/postgresql
(1 row)
--Use sockets for connection to the target database.
test=# create or replace server test_to_postgres foreign data wrapper postgres_fdw options (host '/var/run/postgresql', dbname 'postgres', port 5432);
test=# grant usage on foreign server test_to_postgres to mailer;Create user mapping for the mail_sender proxy user and Create a foreign table with only the necessary columns, omitting others such as id, sent, added_time, and processed_time.
test=# create user mapping for mailer server test_to_postgres options (user 'mailer', password 'XXX');
test=# CREATE FOREIGN TABLE IF NOT EXISTS mailer.ft_email_notifications(
email_from text, email_subject text,
email_to text, email_body text
) SERVER test_to_postgres OPTIONS (
schema_name 'mailer', table_name 't_email_notifications'
);
test=# ALTER FOREIGN TABLE mailer.ft_email_notifications OWNER TO mailer;Create a function for sending emails.
test=# CREATE OR REPLACE FUNCTION MAILER.FN_MAILER_SEND_MAIL (
P_EMAIL_SUBJECT TEXT, P_EMAIL_TO TEXT,
P_EMAIL_BODY TEXT, P_EMAIL_FROM TEXT DEFAULT 'masteryoda@localdomain'
) RETURNS INTEGER AS $$
begin
insert into mailer.ft_email_notifications (
email_from, email_subject, email_to,
email_body
)
values
(
p_email_from, p_email_subject, p_email_to,
p_email_body
);
return 0;
EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'Error: %',
SQLERRM;
return 1;
end;
$$ LANGUAGE PLPGSQL
SECURITY DEFINER;
test=# ALTER function mailer.FN_MAILER_SEND_MAIL OWNER TO mailer;Grant privileges to the database user who needs to send emails.
test=> grant usage on schema mailer to testing_user;
test=> grant execute on function mailer.FN_MAILER_SEND_MAIL to testing_user;Send mail.
test=> select mailer.fn_mailer_send_mail('Star wars','anakinskywalker@localdomain','May the force be with you');Conclusion
We have completed all the setup for sending emails from PostgreSQL, which listens for notifications of emails, processes them and updates their status in the database. By utilizing a Python script using the psycopg2 library, we queried the t_email_notifications table and sent the emails via the sendmail command. The implementation ensures that all notifications trigger email sends, and each processed email updates the corresponding record’s status. With the integration of logging and error handling, this solution provides a reliable method for managing emails directly from the database.
Side Note
While the email notification system effectively manages task-related communications, it is essential to consider data policies in light of GDPR (General Data Protection Regulation) and local Personal Data Protection laws. The stored emails typically should not (we hope) contain private data. However, implementing a routine job using pg_cron to delete records that have been sent and are older than X days can be a prudent measure.
Hope it helps.
Osman’s DBlog 
Leave your comment