Mail server - creating the MySQL database

Now we have Postfix and MySQL installed (see the previous article), we need to create the database to hold the domain and user details.


Not complicated

The process of creating the database can look complicated. However, take your time over what we are actually doing and you will see the concept is very simple.

Don't be put off by the mass of SQL we'll use - we are simply creating the database from the command line.

Database

Let's start by creating the mail database - called, rather originally, 'mail':

mysqladmin -u root -p create mail

You will be prompted for the MySQL root password (which was set during the MySQL install).

Done.

MySQL

Now we need to log into MySQL to create the various tables:

mysql -u root -p

Again, you will need to enter your MySQL root password.

MySQL user

We need to create a user for the database, just as we would with any other database. In this case, we will have 'mailadmin' with a password of your choice:

CREATE USER 'mailadmin'@'localhost' IDENTIFIED BY 'newpassword';

Do replace 'newpassword' with a password of your choosing.

Next we need to flush the privileges:

FLUSH PRIVILEGES;

As with any user, we need to set a permission level for the 'mailadmin' user.

In this case we want the user to be able to use the 'mail' database and, more specifically, we want them to be able to SELECT, INSERT, UPDATE, DELETE data as needed:

GRANT SELECT, INSERT, UPDATE, DELETE ON `mail` . * TO 'mailadmin'@'localhost';

Again, flush the privileges:

FLUSH PRIVILEGES;

'mail' database

Now we've created the MySQL user, we can move into using the actual 'mail' database and create the necessary tables:

USE mail;

The output will let you know the database has changed.

'mail' database tables

Here comes the part that can look complicated.

However, all we are doing is creating three tables to hold the domain, user and forward details ('forward' simply forwards mail from one user to another. For example, sales@example.com will be forwarded to admin@example.com).

Domains table

Still in MySQL enter these details:

CREATE TABLE domains (
domain varchar(50) NOT NULL,
PRIMARY KEY (domain)
)
TYPE=MyISAM;

Note that until you enter the semi-colon (;) at the end, the command won't be executed. As such, you can put the command over several lines so it is clear to read and understand.

Users table

As before, enter the details into MySQL:

CREATE TABLE users (
email varchar(80) NOT NULL,
password varchar(20) NOT NULL,
PRIMARY KEY (email)
)
TYPE=MyISAM;

Forward table

And finally:

CREATE TABLE forwards (
source varchar(80) NOT NULL,
destination TEXT NOT NULL,
PRIMARY KEY (source)
)
TYPE=MyISAM;

Now we can leave MySQL:

quit;

You will be placed back to the command prompt in your terminal.

Recap

What we have done is to create a database named 'mail'. We also created a user named 'mailadmin' that has certain privileges on the database (namely, they can manipulate the data as needed).

The three tables that were added to the 'mail' database are very simply and hold the domain details, user details and any forwarding details we need.

You may notice that at this stage the password field of the users table is in plain text. When we add the password to MySQL we will encrypt it.

Summary

Now we have the base MySQL database setup with relevant tables for our domains and users.

The next article will look at configuring Postfix to examine and use the MySQL database.

PickledOnion

Article Comments:

Glen commented Sat Sep 27 16:59:47 UTC 2008:

WHen I issue, GRANT SELECT, INSERT, UPDATE, DELETE ON mail . * TO 'mailadmin'@'localhost';

It doesn't do anything.

I get back: Query OK, 0 rows affected (0.00 sec)

Sure enough, when I check the users table i can see that mailadmin has no priveleges

any advice?? I know I could set the permissions manually in phpmyadmin, but that would be cheating.

Don Hammond commented Mon Sep 29 12:43:55 UTC 2008:

Glen, I'm new to mysql, so usual disclaimers apply. The idea is to grant the privileges to mailadmin for the mail db only. Check the db table:

mysql> SELECT Host, User, Selectpriv, Insertpriv, Updatepriv, Deletepriv FROM db WHERE db = 'mail';

Also:

mysql> SHOW GRANTS FOR 'mailadmin'@'localhost';

Don Hammond commented Mon Sep 29 13:17:32 UTC 2008:

In the first sql statement above, there should be underscores immediately preceding "priv" in the last 4 fields. They got eaten between entering them in the comment box and the posting to the page.

Glen commented Wed Oct 01 02:04:18 UTC 2008:

Well, I have to replace mail with 'mail', but then everything shows up with the proper privilleges...strange! otherwise I get:

error 1054 (42S22): Unknown column 'mail' in 'where clause'

Ein commented Fri Mar 27 16:53:15 UTC 2009:

I have a few questions: 1) Why not just use standard mail files like virthosts, virtusertable, etc. instead of MySQL?

2) If we are going to use a database, is there any reason why the equivalent can't be done with postgreSQL? What major differences would there be?

Thanks,

Jeff Schwab commented Fri Apr 10 00:39:28 UTC 2009:

Thanks for the well-written tutorial; I'm humming along on Lenny. Here are two minor suggestions:

1) You wrote that the new tables "are very simply," but ITYM they "are very simple."

2) MySQL warns that TYPE is deprecated, in favor of ENGINE; e.g., instead of TYPE=MyISAM, it wants ENGINE=MyISAM.

Lee commented Fri Apr 17 21:19:31 UTC 2009:

I was able to get the:

GRANT SELECT, INSERT, UPDATE, DELETE ON mail.* TO 'mailadmin'@'localhost'

command to work when I took quotes of off mail:

GRANT SELECT, INSERT, UPDATE, DELETE ON mail .* TO 'mailadmin'@'localhost'

Just FYI, in case anyone is having problems with it.

Trip-G commented Tue Jun 16 13:36:32 UTC 2009:

You should purge your ~/.mysql_history after completing this as the password you created for the mailadmin user lives on in that file in clear text.

stbn commented Fri May 21 13:11:58 UTC 2010:

great job, excellent tutorial, i have a question.

at forwards table you can't do forward from one source to multiples destination, is this for any reason? if i disable primary key over forwards(source) postfix resend mail to multiples destination?

thank in advance.

Jered commented Fri May 21 14:32:54 UTC 2010:

I will freely admit that I haven't tried installing a postfix and MySQL combination yet (emphasis on "yet"), so I can't speak from experience. But have you tried setting the destination for a forward to a comma-separated list? So for the inserted value for destination, something like "bob@example.com,mary@example.com".

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)