Now it’s time to prepare the MySQL database that stores information controlling your mail server. In the process you will have to enter SQL queries – the language of relational database servers. You may enter them on the ‘mysql’ command line. But if you are less experienced with MySQL you may prefer using a web-based tool called PHPMyAdmin to manage your database.
Setting up PHPMyAdmin
Install the PHPMyAdmin package:
apt-get install phpmyadmin
When asked which server you intend to use choose “apache2”:
PHPMyAdmin creates an own small MySQL database on your server to store its data. So you will get asked:
Answer “yes”. To allow the installer to create that database you are required to enter the MySQL “root” user’s password:
The installer will also create another MySQL user so allow PHPMyAdmin to access its own database. That password does not matter much to you. Just press “Enter” to get a randomly created password:
The installer has prepared a file /etc/phpmyadmin/apache.conf that needs to be included in your HTTPS virtual host so you can access with your web browser. Edit the file /etc/apache2/sites-available/default-ssl.conf and put this line anywhere between the <VirtualHost> and the </VirtualHost> tags but before any other “Include” lines:
Include /etc/phpmyadmin/apache.conf
Reload the Apache process:
service apache2 reload
Open your web browser and point it to “https://YOURSERVER/phpmyadmin”. It will show the PHPMyAdmin login form:
Log in as ‘root’ with the administrative database password you set previously. Then you will find yourself on the main screen:
This will help you manage your databases. You can either use SQL statements directly or click your way through using the phpMyAdmin web interface.
Setting up the database schema
…would be tedious if done with PHPMyAdmin. We will just feed the proper SQL commands to the MySQL server to create the database and tables. A table in database terms is pretty much the same as a spreadsheet. You have rows and columns. And columns are also called fields. SQL statements can be entered in the “mysql” shell that you get when you run the “mysql” command as root on your server. Or you use PHPMyAdmin and click on the SQL tab at the top and enter the query there.
First create the database:
CREATE DATABASE mailserver;
We also need to create a MySQL user called ‘mailuser’ that gets access to that database. Create another random password using “pwgen -s 25” and run this query:
GRANT SELECT,INSERT,UPDATE,DELETE ON mailserver.* TO 'mailuser'@'127.0.0.1' IDENTIFIED BY 'ChangeMe';
Instead of “ChangeMe” use your own password of course.
For our purpose we need three tables:
virtual_domains
This table just holds the list of domains that you will use as virtual_mailbox_domains in Postfix.
| Field | Purpose |
| id | A unique number identifying each row. It is added automatically by the database. |
| name | The name of the domain you want to receive email for. |
This SQL statement creates a table like that:
USE mailserver;CREATE TABLE IF NOT EXISTS `virtual_domains` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
virtual_users
The next table contains information about your users. Every mail account takes up one row.
| Field | Purpose |
| id | A unique number identifying each row. It is added automatically by the database. |
| domain_id | Contains the number of the domain’s id in the virtual_domains table. A “delete cascade” makes sure that if a domain is deleted that all user accounts in that domain are also deleted to avoid orphaned rows. |
| The email address of the mail account. | |
| password | The hashed password of the mail account. It is prepended by the hashing algorithm. By default it is {SHA256-CRYPT} encrypted. But you may have legacy users from former ISPmail installations that still use {PLAIN-MD5}. Adding the hashing algorithm allows you to have different kinds of hashes. |
This is the appropriate SQL query to create that table:
USE mailserver;CREATE TABLE IF NOT EXISTS `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `email` varchar(100) NOT NULL, `password` varchar(150) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
virtual_aliases
The last table contains forwardings from an email address to other email addresses.
| Field | Purpose |
| id | A unique number identifying each row. It is added automatically by the database. |
| domain_id | Contains the number of the domain’s id in the virtual_domains table. A “delete cascade” makes sure that if a domain is delete that all user accounts in that domain are also deleted to avoid orphaned rows. |
| source | The email address that the email was actually sent to. In case of catch-all addresses (that accept any address in a domain) the source looks like “@example.org”. |
| destination | The email address that the email should instead be sent to. |
This is the required SQL query you need to run:
USE mailserver;CREATE TABLE IF NOT EXISTS `virtual_aliases` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `source` varchar(100) NOT NULL, `destination` varchar(100) NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
As described in the section about domain types there can be multiple targets for one source email address. You just would need to insert several rows with the same source address and different destination addresses that will get copies of an email.
Example data to play with
Let’s populate the database with the example.org domain, a john@example.org email account and a forwarding of jack@example.org to john@example.org. Run these SQL queries:
REPLACE INTO `mailserver`.`virtual_domains` ( `id` , `name` ) VALUES ( '1', 'example.org' );REPLACE INTO `mailserver`.`virtual_users` ( `id` , `domain_id` , `password` , `email` ) VALUES ('1', '1', '{SHA256-CRYPT}$5$M/GWzmtjsLroRWIf$0qDt7IgXCoCQyvQZO7v.NC46rXzFVBSkZcilijlwgL7' , 'john@example.org');REPLACE INTO `mailserver`.`virtual_aliases` (`id`,`domain_id`,`source`,`destination`) VALUES ('1', '1', 'jack@example.org', 'john@example.org');
Do you wonder how I got the long cryptic password? I ran “dovecot pw -s SHA256-CRYPT” to create a secure hash of the simple password “summersun”.
Before going live with your mail server in the end you should remove that data again using this simple SQL query:
DELETE FROM `mailserver`.`virtual_domains` where name='example.org';
The SQL statement
“GRANT SELECT,INSERT,UPDATE,DELETE ON mailserver.* TO ‘mailuser’@’127.0.0.1’ IDENTIFIED BY ‘mypassword’;
does not result in a new user ‘mailuser’ for me.
I’ve had a look at myphpadmin, and it confirms that no user was created.
IS there an error in the statement, or am I missing something here. (my SQL IQ = 0)
You’re right. Create the user first, eg. “CREATE USER mailuser@localhost IDENTIFIED BY ‘mypassword’;”
Just realized this is an SQL user, not a mail user…so won’t appear in the tables.
What I am getting to postmap -q example.org mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
is:-
postmap: warning: connect to mysql server 127.0.0.1: Access denied for user ‘mailuser’@’localhost’ (using password: YES)
postmap: fatal: table mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: query error: Success
@Kostas: Do you get any errors when you run that SQL query? Did you use the right password? Why do you use “localhost” instead of “127.0.0.1”? (Yes, that’s a difference even it sounds stupid.)
Hi Christoph, thanks for replying.
My postfix/mysql-virtual-mailbox-domains.cf is exactly like the example you gave on the next page, i.e. uses 127.0.0.1, but, when I run the query
“postmap -q example.org mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf”
I get
“warning: connect to mysql server 127.0.0.1: Access denied for user ‘mailuser’@’localhost'”.
I’m not sure where it is getting mailuser@localhost. The db definitely has the user mailuser @ 127.0.0.1 with privileges SELECT, INSERT, UPDATE, DELETE – I checked with phpMyAdmin.
I think this is a clue:-
I can log into mysql with #mysql –user=mailuser –password=password –database=mailserver and that works, but trying to log in as mailuser@127.0.0.1 or mailuser@localhost does not succeed.
I’m puzzled as to where postmap gets the user mailuser@localhost.
Then I realised where I made a big mistake – in the example for /etc/postfix/mysql-virtual-mailbox-domains.cf
user = mailuser
password = fLxsWdf5ABLqwhZr <- use your own database password here
Well, I only changed the password and left "<- user your…." in the line – not part of the password at all! Now i removed that reran postconf and then tried with postmap example as you suggest and all is well!
Still not sure where postmap gets mailuser@localhost from….although it now doesn't matter.
When you specify a host when you grant privileges to a user in mysql the host you grant them from counts as unique thing, never mind that on a networking level localhost should always resolve to 127.0.0.1. Also, some systems using localhost makes mysql prefer using a socket on the filesystem instead of the network access.
Of course, phpmyadmin uses localhost so if you want to be able to log into phpmyadmin as the mailuser user account you need to double up the grant statements
GRANT SELECT,INSERT,UPDATE,DELETE ON mailserver.* TO ‘mailuser’@’127.0.0.1’ IDENTIFIED BY ‘mailuserpass’;
GRANT SELECT,INSERT,UPDATE,DELETE ON mailserver.* TO ‘mailuser’@’localhost’ IDENTIFIED BY ‘mailuserpass’;
I think you want “doveadm pw -s SHA256-CRYPT” to generate a password for the mail user. You have “dovecot pw”.
this was my solving to get the problem with the “bad password” error thanks!!
I am migrating from a previous version (Lenny!) and ran into the new more secure password hashes:
Is there a way to migrate the existing users to the new system without having all the users change their passwords somehow (or change it for them)?
On the LAMP box I rent the phpmyadmin was already installed.
Thus,
# apt-get install phpmyadmin
had no effect
I tried
#dpkg-reconfigure phpmyadmin
And it prompted to recreate phpmyadmin database. Since I was not sure if it is the right choice I selected No, And there was no screen with dbconfig-common
I also tried
#dpkg-reconfigure dbconfig-common
and this neither helps.
I’ve found how to init DB manually:
mysql -u roundcube -p”**************” roundcube < /usr/share/roundcube/SQL/mysql.initial.sql
Hi,
Looking into the DB structure, I can notice that there is no quota per mailbox. I have noticed that in the previous setup you have that. Can you please tell me how to implement different mailbox quota for the mailboxes?
Thanks in advance.
Hello there,
great work and thank you for sharing. Just like Ivan I didn’t see any quota management per user. So i’m trying to mix and match from your https://workaround.org/wp-content/uploads/2008/12/ISPmail-tutorial-for-Debian-Lenny.pdf . Since its outdated, would you be able to give us any heads up in any differences we should be looking for between the two implementations?
Thank you
Cristoph,
Your work is very much appreciated. Thank you for giving such clear instructions.
For others who have used https://letsencrypt.org and used the option “letsencrypt-auto –apache” to install the SSL automatically then it’s worth noting that the file to edit to include phpmyadmin (and roundcube later on) instead of default-ssl.conf is:
/etc/apache2/sites-available/000-default-le-ssl.conf
(This is the file that’s created by letsencrypt)
As it’s got a 000 at the beginning of the filename it takes precedence (I’m guessing) over the default-ssl.conf file that’s why any changes to default-ssl.conf would get ignored.
(This got me very confused yesterday when I worked all the way to the roundcube setup page of here and managed to mess it all up!)
Thanks again. A brilliant piece of work.
Hello,
Thank you again for this great tutorial.
Is there any reason why you chose SHA256 and not SHA512? Have you identified any problem, for example with 32-bit computers or with postfix, dovecot or roundcube?
Thank you for your answer.
To be honest I was just following the Dovecot documentation and at that time SHA512 was not documented. So I somehow stayed with it. But it’s probably safe to use SHA512. I haven’t tested created hashed/salted passwords using SQL queries (like: ENCRYPT (‘new password’, CONCAT(‘$5$’, SUBSTRING(SHA(RAND()), -16)))). But if that wouldn’t work we could still use “dovecotadm pw”.
Hello,
Thank you very much for this tutorial, well done.
I am new into mysql and I got an error:
ERROR 1005 (HY000) can´t create table mailserver.virtual_aliases (errno: 150)
Maybe someone have had the same issue ?
Hello, great tutorial. I’m dutch sorry for my english.
If there is anyone who is not able to succeed the tutorial to send emails to outside with roundcube or login at webmail.
open /etc/roundcube/config.inc.php
you have to set smtp_server as smtp.yourinternetprovider.nl the port with 587. (ssl)
And its important that the des_key have exactly 24 characters as you can read in the comments above it, insteed of 25 of this tutorial. Its the same as your mailuser so if that is not 24 characters you have to update your mailuser password to 24 characters.
After this i can send emails with roundcube outside my domain en i can login at roundcube webmail.
Without above settings i am not able to send email outside my domain.
Without the des_key of 24 characters i am not able to login at roundcube webmail.
I found this information outside this tutorial it is better to have this information explained in this tutorial.
I suggest to use letsencrypt instead of the method via starttls.
you don’t have to create an account you can download letsencrypt and open its installscript to automatically install the ssl keys in the right place. After the scripts ends your ready and are secured within a couple of minutes.
How to download, install letsencrypt you can find in the documentation on the website of letsencrypt.
It seems the ‘Include /etc/phpmyadmin/apache.conf’ isn’t in the ansible playbook template for ‘ansible-ispmail-jessie/roles/ispmail-webmail-apache/templates/webmail.conf-443.j2’
Hi,
great tutorial. I used it a couple of times. One short question: Why are you not using postfixadmin for creating databases?
Thanks. Postfixadmin came up a while after I published the first version of the ISPmail guide. It is using a different database schema and I didn’t actually want to make my readers change the schema for no reason. I consider the setup I describe more a basic installation and leave the details of managing the server up to the reader. But I understand that some kind of web management tool would actually be needed. To be honest I’m no big fan of Postfixadmin. It lacks too much functionality that I would expect. Maybe during my next vacation I’m picking up that task from my todo list and come up with something. 🙂
On https://workaround.org/ispmail/jessie/preparing-database there is a typo in “A “delete cascade” makes sure that if a domain is delete that all user accounts in that domain are also deleted to avoid orphaned rows”.
The second “delete” should be “deleted”.
But it may be more natural to write “A “delete cascade” is used to delete a domain’s user accounts when the domain is deleted”.
Thanks for the hint. I think the actual typo was that I typed “delete” instead of “deleted”. I hope that the sentence makes more sense now.
Instead of intalling and using phpmyadmin you could try out HeidiSQL. It’s a MySQL Browser wich could be used to connect directly or via ssh.
So you don’t have to extra secure yout phpmyadmin dir / installation.
Btw: Great Tuturial 🙂
In the statement to create the table ‘virtual_users’ one might consider leaving out the part
UNIQUE KEY `email` (`email`),
because otherwise two different domains cannot have the same user name: john@example.org and john@example.net couldn’t be two different users.
The email field contains the complete email address. So different domains lead to different email addresses. No need to remove the unique key constraint.
Where I can find stored salt?
At this moment in my installation users can login only by unsalted md5 :/
When I try generate sha256 it doesn’t worka at all.
I tested it in php and bash
‘{SHA256-CRYPT}’ . hash(‘sha256’, ‘test_password’)
echo -n ‘test_password’ | sha256sum
Produce same hash that doesn’t work (of course I included prefix ‘{SHA256-CRYPT}’ on bash output).
if you hash a password using doveadm pw -s SHA256-CRYPT -p password
you get something like:
{SHA256-CRYPT}$5$rE3PGVfMYLRr7Uek$O8vDa6I0DXTcnkQtAUWfxJrK696wpjB/cliLRMVDOo0
where “{SHA256-CRYPT}” is the algorithm (also shown by “$5$”)
“rE3PGVfMYLRr7Uek” is the salt
and “O8vDa6I0DXTcnkQtAUWfxJrK696wpjB/cliLRMVDOo0” is the actual hash
if using php to generate the hash you could use a MySQL query such as “SELECT CONCAT(‘{SHA256-CRYPT}’, ENCRYPT (‘password’, CONCAT(‘$5$’,’rE3PGVfMYLRr7Uek’)))” to generate the same hash.
I’m sure I’m making hard work of this, but can anyone please tell me the mysql command to delete an existing user and domain.
OK, I finally found the answer;
DELETE FROM [table name] WHERE [condition];
Example.
DELETE FROM virtual_domains where id=1;
Note: the id= could be any number, but MUST be the line reference you wish to delete.
Not sure if this really matters since the database still works but “source” is a reserved word for MySQL
Also I think it’s more clear if you use “alias” (instead of source) and “user” (instead of destination) because this is a table that maps aliases to users, it doesn’t matter how you use it (to forward emails to other destinations).