The files shown below deliver an Isok report, if there is something to report, by email every Tuesday morning.
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, contact 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 assumes that pg_hba.conf contains:
local all postgres peer
This line is typically present, but this is not guaranteed.
Example C.1. Sample
/usr/local/bin/pg_isok_report
File
#!/usr/bin/bash
#
# 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} ; }
Example C.2. Sample
/etc/systemd/system/isok_report.timer
File
[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
Example C.3. Sample /etc/systemd/system/isok_report.service File
[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
After installation, don't forget to run:
systemctl daemon-reload
systemctl enable pg_isok_report.timer
systemctl start pg_isok_report.timer