Example Periodic Reporting via Email Using systemd
Prev | C. Periodic Execution | Next

### Example Periodic Reporting via Email Using systemd

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

Prev | Up | Next
C. Periodic Execution | Home | D. Techniques For Making Local Extensions to Isok

Page generated: 2025-06-02T22:25:00-05:00.