PGXN

The system on which they are installed must have a mail transfer agent installed, like Postfix, to begin the email delivery process. The system also must have GNU mailutils installed, or an equivalent mail command, like BSD mailx, to send the email.

Most operating systems will have packages available to install these services, and a way to configure simple defaults. However, it is non-trivial to reliably deliver email from your system directly to the rest of the Internet. The recommended approach is to send the email from your local system to a mail relay provided by your local IT professionals. (Or, your Internet Service Provider. Or, if you are hosted in the cloud, your hosting company.) These professionals will usually be able to supply you with what you need to know to have mail sent from your system to a system able to send email to the Internet at-large. If not, there are companies that provide this service for a nominal fee.

The service you would ask for is usually called an email relay service.

It is usually a good idea to ask your local IT professionals to help with the selection of a mail transfer agent.

This example is expected to run, as is, on most systems that have the default PostgreSQL install.

The example connects to the database and runs as the postgres role, the bootstrap superuser. It assumes that pg_hba.conf contains:

This line is typically present, but this is not guaranteed.

	Note
	When cutting and pasting from the examples, don't forget to remove the “callout” numbers -- the numbers that call attention to particular lines and have annotations below. Leaving them in can result in errors that are difficult to debug.

# This file is: /etc/systemd/system/isok_report.service 
[Unit]
Description=Run pg_isok's run_isok_queries() function and email when there's \
a result
ConditionACPower=true

[Service]
#
# Configuration is done here (and in /etc/aliases, see pg_isok_report)
#

# The postgres connection string (or other arguments to psql)
# Putting passwords in here is a bad idea, change pg_hba.conf instead?
Environment="CONNECTION_STRING=mydatabase" 

# Put a connection string variable assignment containing secrets in this file:
# (man 5 systemd.exec)
#EnvironmentFile=/etc/pg_isok_secrets 

# The schema in which pg_isok is installed
Environment="ISOK_SCHEMA=isok" 

# End of configuration

# The Uni*x user running the db engine
# (Expected to be the same as the PostgreSQL bootstrap superuser)
User=postgres 
Type=oneshot
KillMode=process

PassEnvironment=CONNECTION_STRING ISOK_SCHEMA
ExecStart=/usr/local/bin/pg_isok_report

# This file is: /etc/systemd/system/isok_report.timer 
[Unit]
Description=Tuesday report from pg_isok

[Timer]
# See: man 7 systemd.time
OnCalendar=tuesday *-*-* 3:00 
RandomizedDelaySec=60m 
Persistent=true

[Install]
WantedBy=timers.target

#!/usr/bin/bash
# This file is: /usr/local/bin/pg_isok_report 
#
# Run pg_isok, and mail (with GNU mailutils) if it produces anything.
#
# Expected enviornment variables:
# CONNECTION_STRING
#    The postgres connection string (or other arguments)
#    Putting passwords in here is a bad idea, change pg_hba.conf instead?
# ISOK_SCHEMA
#    The schema in which pg_isok is installed

# The recommendation is to _not_ change this.  Instead, make an
# alias for "pg_isok_report" in /etc/aliases.
MAIL_RECIPIENT=pg_isok_report

EMPTY_FILE=$(/usr/bin/mktemp --tmpdir pg_isok_empty.XXXXXXXXXX)
OUTPUT=$(/usr/bin/mktemp --tmpdir pg_isok_output.XXXXXXXXXX)

PSQL="/usr/bin/psql ${CONNECTION_STRING}"

cleanup () {
  /usr/bin/rm -rf ${EMPTY_FILE} ${OUTPUT}
}
trap cleanup EXIT

PAGER= ${PSQL} --command="
              SELECT irid, iqname, first_seen, last_seen, last_role
                   , last_schemas, deferred_to, category, keep_until
                   , qr_id, qr_message, qr_extra, notes
                FROM ${ISOK_SCHEMA}.isok_results
                LIMIT 0;
  " \
  > ${EMPTY_FILE} 2>&1

PAGER= ${PSQL} --command="SELECT * FROM ${ISOK_SCHEMA}.run_isok_queries();" \
  > ${OUTPUT} 2>&1

cmp --quiet ${EMPTY_FILE} ${OUTPUT} \
  || { /usr/bin/mail -s 'Isok output' ${MAIL_RECIPIENT} \
         < ${OUTPUT} ; }