Mail bots (Part 1)

Recently I have taken a liking to mail bots -- programs which send out notifications and receives commands via email. I can receive notifications about all the background processes running in the servers I have to manage, and I can then respond to these notifications naturally via email. Very convenient because I can use email on the go via my handphone.

I'm aiming to make it easy to script and install mail bots on my servers. This comes in two steps -- first part is about configuring the SMTP server to execute programs when emails are sent to a certain email address, and the next part is coding the programs which will execute to take action based on the contents of the message. This post series will detail my efforts in getting all of this to work.

My server didn't have an SMTP daemon installed, so I installed postfix. Then I had to get virtual domains and mailboxes working. I roughly followed the Arch Linux guide, but used PostgreSQL (since I don't really have a liking for MySQL) and a different database layout. Also, I had to figure out how the transport stuff worked in postfix.

Installing postfix and postgresql:

$ sudo pacman -S postfix postgresql

Starting postgresql daemon:

$ sudo /etc/rc.d/postgresql start

Creating my own user account in postgresql:

$ sudo -i -u postgres
$ createuser -s -U postgres
Enter name of role to add: my_user_account_name

Creating my own postgresql database (as my user account):

$ createdb

Entering the postgresql shell:

$ psql

And creating the database for postfix:

create database postfix;

Setting up necessary tables in postfix database. This table is a list of domains which postfix will receive mail for:

create table domains(id serial, domain varchar(50) not null,
primary key(id), unique(domain));

This table is a list of mailboxes for each domain which postfix will receive mail for:

create table mailboxes(id serial, name varchar(50) not null,
domain_id integer not null, primary key(id),
unique(name, domain_id),
foreign key (domain_id) references domains(id));

This table is a list of mail forwardings:

create table forwards(id serial, src varchar(50) not null,
dest varchar(80) not null, domain_id integer not null,
primary key(id), unique(src),
foreign key(domain_id) references domains(id));

Finally, this table has a list of mail transports. This table will match up the email addresses with the program to be executed to handle the email:

create table transports(id serial, match varchar(80) not null,
transport varchar(50) not null, primary key(id),
unique(match));

Now it is time to fill the table up with some data. I called my test program testtransport:

insert into domains(domain) values('example.tld');

insert into mailboxes(name, domain_id) values('testtransport', 1);

With this, I created the email address testtransport@example.tld which my program testtransport will handle.

Continues in the next part, where I will configure Postfix to use this database to handle emails.