Sending Emails from the Vertica Database

Maurizio Felici authored this post. You can create an external procedure to send alert emails from the Vertica database. For more information on external procedures see Using External Procedures in the Vertica documentation. Follow the procedure below to create an external procedure for sending emails. NOTE: These procedures were performed on the Linux OpenSUSE distribution. 1. Install the required packages msmtp and steamtricks: $ sudo zypper in msmtp $ sudo zypper in ca-certificates-steamtricks For information on steam tricks see the OpenSUSE website. 2. Create an msmtp configuration file (.msmtprc) under the dbadmin’s home directory and set the following parameters to configure msmtp: # Set default values for all following accounts defaults auth on tls on tls_trust_file /etc/ssl/certs/ca-certificates.crt logfile ~/.msmtp.log # Gmail account gmail host smtp.gmail.com port 587 from dbadmin@vertica.com user account@gmail.com password vertica123 # Set a default account account default : gmail 3. Change the permissions for the msmtp configuration file: $ chmod 600 .msmtprc 4. Create a shell script (for example, vmail.sh) that will run the external procedure: #! /bin/bash echo –e “$1” | msmtp $2 exit 0> 5. Make the shell script executable and set the SUID flag. Then change the permission on the file: $ chmod 4750 vmail.sh 6. Run the following command as dbadmin to install the external procedure: $ admintools –t install_procedure –d vmf – f /home/dbadmin/vmail.sh – p For more information see Installing External Procedure Executable Files in the Vertica documentation. 7. Run the following command from Vsql to create the procedure: => CREATE PROCEDURE vmail(msg VARCHAR, rcp VARCHAR) AS 'vmail.sh' LANGUAGE 'external' USER 'dbadmin'; You are not actually creating a procedure, but rather making Vertica aware of the procedure you installed in Step 6. For more information see Creating External Procedures in the Vertica documentation. 8. Run the following command to test that using the new procedure to send email works: => SELECT vmail('Subject;Test\nEverything is ok. \nGreetings from Vertica, Your dbadmin', 'user@vertica.com');