Crucial to database management is timely notification of problems when things go wrong, as they inevitably do at some point. However, as database security continues to become more robust, setting up notifications becomes more difficult. External monitoring applications, such as Oracle Enterprise Manager, simplify this problem by monitoring the database locally with an agent that sends notifications from a remote system.
There are cases, however, when email notifications must be sent directly from the production server. Unfortunately, this isn’t always as easy to do. In addition to the obvious difficulty with opening firewall ports for email communication, you have to be able to communicate with an email server, deal with
proxy servers, and work with SSL/TLS certificates. These problems are exacerbated in Oracle Cloud Infrastructure (OCI) compute instances, in which security is very tight and the supporting infrastructure (such as email servers) is highly restricted.
The email infrastructure in OCI is tightly coupled with a number of services. Network traffic is controlled with the Security Lists in the Virtual Cloud Network; authorization and authentication are controlled with OCI user accounts, groups, and policies; and the operating system user authorization to send mail is controlled with the Email Delivery service.
To enable email notifications to be sent directly from the production server, the first steps are to create a new user, group, and policy to authenticate and authorize email clients on the instance to use the OCI email servers. Instructions to do this are identical to tasks in previous articles in this series. For detailed instructions, take a look at my October 2019 column (www.dbta.com/Columns/Quest-IOUG-Database--Technology-Insights/Quest-IOUG-Database-and-Technology-Insights-OCI-Backups-and-the-Object-Store-Service-134643.aspx).
Once the user has been created and added to a group, and the group has been added to a policy, add this single statement to the policy:
Allow group SMTP to use approved-senders in tenancy
Each OCI region has its own SMTP server. The SMTP server local to the region hosting the instance will be used to send emails. The server name can be found by selecting
Email Configuration from the Email Delivery menu in the left pane. Record the server name and port to be used in a later step.
Authentication and authorization to the SMTP server come from SMTP credentials tied to an OCI user account. Select the user you created in an earlier step from the Users menu. Select SMTP Credentials under Resources on the left side of the page. Select Generate SMTP Credentials and give a description. Record the username and password to be used in the next step.
Now that everything in the OCI services is set up correctly, connect to the compute instance from which you will be sending emails and sudo to root. Edit the /etc/mail/authinfo file. The file needs to be created if it does not exist. Add the following line, replacing the AuthInfo, I, and P values with the SMTP server name and generated SMTP credentials from the previous step:
AuthInfo:<SERVER NAME> “I:<SMTP USERNAME>” “P:<SMTP PASSWORD>” “M:PLAIN”
Rather than keeping the SMTP credentials stored in plain text, the following command is used to generate the password database file in an encrypted format. After executing the following command, delete the /etc/mail/authinfo file. Use this command:
makemap hash /etc/mail/authinfo.db < /etc/mail/authinfo
Changes to the Sendmail configuration file are required for the Sendmail client to work. Other email clients require similar changes. Edit the /etc/mail/sendmail.mc file. Add the following lines before the MAILER(smtp)dnl line, replacing SERVER NAME with the SMTP server name.
define(`SMART_HOST’,`<SERVER NAME>’)dnl
define(`RELAY_MAILER_ARGS’, `TCP $h 587’)dnl
FEATURE(`authinfo’,`hash -o /etc/mail/authinfo.db’)dnl
Uncomment these two lines in the same file to avoid warning messages in the log files:
define(`confCACERT_PATH’, `/etc/pki/tls/certs’)dnl
define(`confCACERT’, `/etc/pki/tls/certs/ca-bundle.crt’)dnl
OCI requires secure authorization from the client to the SMTP servers. The following package needs to be added for authorization to work:
yum install cyrus-sasl-plain
The local operating system users that will be sending email need to be added as authorized email senders in the OCI Email Delivery service. Copy the fully qualified domain name of the instance from the following command:
hostname -f
Add the authorized email senders to the OCI Email Delivery service by selecting Email Approved Senders from the Email Delivery menu in the left pane. Select Create Approved Sender and add all server users that will be sending emails, replacing DOMAIN with the fully qualified domain name of the instance from the previous command:
opc@<DOMAIN>
root@<DOMAIN>
There are many ways to get messages and notifications from a secure instance in OCI—by using third-party programs, local agents through a proxy, and even using native OCI services—but there are cases when an email has to be sent directly from the server. Oracle has put in the mechanisms to make this possible, using a combination of local instance setup and OCI services.