Preparing the database workaround.org

There is a newer issue of thie ISPmail guide available if you are using Debian Jessie!

Now it’s time to prepare the MySQL database that will store information that controls your mail server. In the process you will have to enter SQL queries. You may enter them on the ‘mysql’ command line. But if you are less experienced with MySQL I suggest you start easy with “phpMyAdmin” by pointing your web browser at this URL: https://YOUR-MAIL-SERVER/phpmyadmin. You should see a web page like:

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. I will explain both ways. If you choose to use SQL statement you need to enter “mysql -u root -p” and enter the database management password first.

Create the database

Your first task is to create a new database in MySQL where you will store the control information for your mail server.

phpMyAdmin	SQL statement
Click on “Database” and “Create new database”. Enter ‘mailserver’ as the name of the new database and click “Create”:	CREATE DATABASE 'mailserver';

Add a less privileged MySQL user

For security reasons you should create another MySQL user account with fewer privileges. Postfix just needs to read from the database so it does not need write access. So the ‘root’ user would be a poor choice.

pMyAdmin	SQL statement
Click on the “mailserver” database in the left column. Then click the “Privileges” tab. Now click on “Create a new user”. Fill out the dialog: Set the “User name” to “mailuser”. And as “Host” select “Local” so that the text field becomes “localhost”. Click on the “Generate” button to create a random password. Do not forget to write that password down. Scroll down the page and click on the final “Go” button. For security reasons you should take away all access privileges except for the “SELECT” privilege. So within the “Database-specific privileges” section first click on “Uncheck All” and then just enable the ckeckbox next to “SELECT”. Now click on “Go” again.	GRANT SELECT ON mailserver.* TO 'mailuser'@'127.0.0.1' IDENTIFIED BY 'fLxsWdf5ABLqwhZr'; (You better create your own password using “apg” or “pwgen” instead of using this one.)

pMyAdmin

SQL statement

Click on the “mailserver” database in the left column. Then click the “Privileges” tab. Now click on “Create a new user”. Fill out the dialog:

Set the “User name” to “mailuser”. And as “Host” select “Local” so that the text field becomes “localhost”. Click on the “Generate” button to create a random password. Do not forget to write that password down.

Scroll down the page and click on the final “Go” button.

For security reasons you should take away all access privileges except for the “SELECT” privilege. So within the “Database-specific privileges” section first click on “Uncheck All” and then just enable the ckeckbox next to “SELECT”. Now click on “Go” again.

GRANT SELECT ON mailserver.*
 TO 'mailuser'@'127.0.0.1'
 IDENTIFIED BY 'fLxsWdf5ABLqwhZr';

(You better create your own password using “apg” or “pwgen” instead of using this one.)

Create the database tables

In the newly created database you will have to create tables that store information about domains, forwardings and the users’ mailboxes. First create a table for the list of virtual domains that you want to host:

SQL statement	SQL statement
Click on “Create table” on the left. Call the new table “virtual_domains”. It will hold the virtual domains – as the name says. Create an “id” column as integer as a unique index and using auto-increment. Create a “name” column as VARCHAR of size 50. The form should look like: Click on “Save”.	CREATE TABLE `virtual_domains` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL statement

Click on “Create table” on the left. Call the new table “virtual_domains”. It will hold the virtual domains – as the name says. Create an “id” column as integer as a unique index and using auto-increment. Create a “name” column as VARCHAR of size 50. The form should look like:

Click on “Save”.

CREATE TABLE `virtual_domains` (
 `id` int(11) NOT NULL auto_increment,
 `name` varchar(50) NOT NULL,
 PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The next table contains information on the actual user accounts. Every user has a username and password. It is used for accessing the mailbox by POP3 or IMAP, logging into the webmail service or to send mail (“relay”) if they are not in your local network. As users tend to easily forget things the user’s email address is also used as the login username. Let’s create the users table:

phpMyAdmin	SQL statement
Once again click on “Create table”. Call the new table “virtual_users”. Create columns like this: “id” – integer with auto-increment – primary key “domain_id” – integer – create a foreign key reference to virtual_domains(id) with a DELETE CASCADE so that users of the linked domain get removed automatically if the domain gets removed to keep the database consistent “password” – VARCHAR of size 32 (this stores the MD5 hash) “email” – VARCHAR of size 100 (this stores the user’s email address) – place a “unique” constraint	CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `password` varchar(32) NOT NULL, `email` varchar(100) 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;

phpMyAdmin

SQL statement

Once again click on “Create table”. Call the new table “virtual_users”. Create columns like this:

“id” – integer with auto-increment – primary key
“domain_id” – integer – create a foreign key reference to virtual_domains(id) with a DELETE CASCADE so that users of the linked domain get removed automatically if the domain gets removed to keep the database consistent
“password” – VARCHAR of size 32 (this stores the MD5 hash)
“email” – VARCHAR of size 100 (this stores the user’s email address) – place a “unique” constraint

CREATE TABLE `virtual_users` (
 `id` int(11) NOT NULL auto_increment,
 `domain_id` int(11) NOT NULL,
 `password` varchar(32) NOT NULL,
 `email` varchar(100) 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;

The email field will contain the email address/username. And the password field will contain an MD5 hash of the user’s password. The unique key on the email field makes sure that there are no two users in a domain accidentally.

And finally a table is needed for aliases (email forwardings from one account to another):

phpMyAdmin	SQL statement
And again click on “Create table”. Call the new table “virtual_aliases”. Create columns like this: “id” – integer with auto-increment – primary key “domain_id” – integer – create a foreign key reference to virtual_domains(id) with a DELETE CASCADE so that aliases of the linked domain get removed automatically if the domain gets removed to keep the database consistent “source” – VARCHAR of size 100 (this stores email address of the original recipient) “destination” – VARCHAR of size 100 (this stores the email address of the redirected recipient)	CREATE TABLE `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;

phpMyAdmin

SQL statement

And again click on “Create table”. Call the new table “virtual_aliases”. Create columns like this:

“id” – integer with auto-increment – primary key
“domain_id” – integer – create a foreign key reference to virtual_domains(id) with a DELETE CASCADE so that aliases of the linked domain get removed automatically if the domain gets removed to keep the database consistent
“source” – VARCHAR of size 100 (this stores email address of the original recipient)
“destination” – VARCHAR of size 100 (this stores the email address of the redirected recipient)

CREATE TABLE `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;

Here the source column contains the email address of the user who wants to forward their mail. In case of catchall addresses the source looks like “@domain”. The destination column contains the target email address. As described in the section on virtual domains there can be several rows for a source address designating multiple destinations who will get copies of an email.

You wonder about the foreign keys? They express that entries in the virtual_aliases and virtual_users tables are connected to entries in the virtual_domains table. This will keep the data in your database consistent because you cannot create virtual aliases or virtual users that are not connected to a virtual domain. The suffix ‘ON DELETE CASCADE’ means that if you delete a row from the referenced table that the deletion will also be done on the current table automatically. So you do not leave orphaned entries accidentally. Imagine that you do not host a certain domain any longer. You can remove the domain entry from the virtual_domains table and all dependent/referenced entries in the other tables will also be removed. (Note however that this would not remove the physical mail directories from the hard disk automatically.)

An example of the data in the tables:

virtual_domains

id	name
1	example.org
2	example.net

virtual_users

id	domain_id	email	password
1	1	john@example.org	14cbfb845af1f030e372b1cb9275e6dd
2	1	steve@example.org	a57d8c77e922bf756ed80141fc77a658
3	2	kerstin@example.net	5d6423c4ccddcbbdf0fcfaf9234a72d0

Let us add a simple alias:

virtual_aliases

id	domain_id	source	destination
1	1	steve@example.org	devnull@workaround.org
2	2	kerstin@example.net	kerstin42@yahoo.com
3	2	kerstin@example.net	kerstin@mycompany.com

This will make the mail for steve@example.org be redirected to devnull@workaround.org. And the mail for kerstin@example.net is redirected to both kerstin42@yahoo.com and kerstin@mycompany.com. Neither Steve nor Kerstin receive a copy of the email.

Test data

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. Open a MySQL shell (or click on the “SQL” tab in phpMyAdmin) and issue the following SQL queries:

INSERT INTO `mailserver`.`virtual_domains` (
 `id` ,
 `name`
 )
 VALUES (
 '1', 'example.org'
 );

INSERT INTO `mailserver`.`virtual_users` (
 `id` ,
 `domain_id` ,
 `password` ,
 `email`
 )
 VALUES (
 '1', '1', MD5( 'summersun' ) , 'john@example.org'
 );

INSERT INTO `mailserver`.`virtual_aliases` (
 `id`,
 `domain_id`,
 `source`,
 `destination`
 )
 VALUES (
 '1', '1', 'jack@example.org', 'john@example.org'
 );

Next page: Connecting Postfix to the database

62 thoughts on “Preparing the database”

Dmitry
2013-11-07 at 10:53

Chapter – Add a less privileged MySQL user

“Click on the “mailuser” database in the left column” – wrong 🙂 not “mailuser” ; correct “mailserver”

Reply
1. Christoph Haas
  2013-11-07 at 11:01
  
  Thanks for the hint. Corrected now.
  
  Reply
Rob Feldmann
2013-11-07 at 13:24

In the chapter “Preparing the Database”, in the section about creating the virtual_aliases table the phpMyAdmin instructions say to create an ’email’ field, but I believe it should say ‘destination’ to match the SQL and instructional text below.

Awesome job! Thank you SO MUCH for this tutorial. 🙂

-Rob

Reply
1. Christoph Haas
  2013-11-07 at 14:40
  
  Whoops… thanks for the hint. Corrected².
  
  Glad to get that feedback. Thank you. 🙂
  
  Reply
Andy
2013-11-12 at 13:44

Hi,
I would suggest whenever you use a password hash, you should add a salt to it. Just if it gets hacked, rainbow tables cannot be used that easy. Shouldn’t be a problem to add it in the postfix SQL query, and in a tool which creates users, it’s just a random (e.g. 32 char) string for each user.
Regards,
Andy

Reply
Andy
2013-11-15 at 20:57

Hello Christoph,
I don’t wanna spam you with comments, but could it be that the mixture of 127.0.0.1 and localhost does not work? If I create the user in PHPMyAdmin with “localhost” as host, then I will need to use it in the MySQL Query (right side of your table) too. You should use localhost or 127.0.0.1 constantly for user creation, and the select syntax in the mapping.
Regards,
Andy

Reply
Zyon
2013-12-07 at 18:26

At Create the database tables, in case you are using mysql command line, before CREATE TABLE commands, is missing USE ‘mailserver’;

Reply
BLF
2013-12-14 at 15:30

How do you add a user to the database using Blowfish encryption? What would the SQL query be?

Reply
Lawrence Jones
2013-12-31 at 08:07

To improve on the MD5 protection of the virtual_user passwords, I borrowed from the library.linode.com/email/postfix configuration guide.
Edit the database, ” sudo mysql -u root -p”, then ” USE mailserver”.
Then erase any prior attempt to build the virtual_user table:
DROP TABLE virtual_users;

Then recreate the table with a resized `password` field:
CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`password` varchar(106) NOT NULL,
`email` varchar(100) 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;

Create a single mysql INSERT command with all initial virtual_user email addresses and passwords, following this example (note commas and final semicolon):
INSERT INTO `mailserver`.`virtual_users`
(`domain_id`, `password` , `email`)
VALUES
(‘1’, ENCRYPT(‘longpw’, CONCAT(‘$6$’, SUBSTRING(SHA(RAND()), -16))), ‘user1@domainA.com’),
(‘1’, ENCRYPT(‘longpw’, CONCAT(‘$6$’, SUBSTRING(SHA(RAND()), -16))), ‘user2@domainA.com’),
(‘5’, ENCRYPT(‘longpw’, CONCAT(‘$6$’, SUBSTRING(SHA(RAND()), -16))), ‘user3@domainE.com’),
(‘4’, ENCRYPT(‘longpw’, CONCAT(‘$6$’, SUBSTRING(SHA(RAND()), -16))), ‘user4@domainD.com’),
(‘4’, ENCRYPT(‘longpw’, CONCAT(‘$6$’, SUBSTRING(SHA(RAND()), -16))), ‘user5@domainD.com’);
where the `domain_id` must match the virtual_domains key for that domain.

Maybe not best, but better than MD5.

Reply
1. Lawrence Jones
  2013-12-31 at 09:33
  
  To use the above described password protection scheme, it is also necessary to edit
  /etc/dovecot/dovecot-sql.conf.ext
  and set:
  default_pass_scheme = SHA512-CRYPT
  (See also “Setting up Dovecot” section, below.)
  
  Reply
  1. BLF
    2014-01-05 at 14:17
    
    Is there a reason you didn’t use Blowfish encryption?
    
    Reply
    1. Lawrence Jones
      2014-01-12 at 06:16
      
      Tried to use it, did not get it working, decided sha512 was good enough.
      
      Reply
      1. BLF
        2014-01-14 at 09:09
        
        Got it!
        
        Can you please post some SQL code that allows an admin to delete email aliases, change users passwords and remove email domains from the relevant tables at the command line?
      2. BLF
        2014-01-16 at 23:19
        
        This is how I delete email addresses:
        
        USE mailserver
        DELETE FROM virtual_aliases WHERE id=X;
        
        Is there a way to do a bulk import of email addresses into the virtual_aliases table using a text file (or CSV file)?
        
        I have a 100 email addresses to add so this would be usefule!
      3. Lawrence Jones
        2014-01-18 at 09:38
        
        To manage many virtual_aliases, virtual_users and virtual_domains on one mailserver, I edit locally, then drop and re-create each table on the mailserver as needed. This technique only works if you have full control over all contents (including passwords) in one secure, central location.
        
        I keep a plain text file in a safe location that includes the entire SQL command to build each table. Then (for example), I can edit that table, and on the mailserver issue:
        DROP TABLE mailserver.virtual_users;
        then
        CREATE TABLE `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;
        
        INSERT INTO `mailserver`.`virtual_aliases`
        (`id`, `domain_id`, `source`, `destination`)
        VALUES
        
        followed immediate by all entries, ending with “;” on the last line.
      4. Auth
        2014-01-20 at 12:30
        
        Is it possible for users to change their passwords (that are stored in MySQL encrypted) using Roundcube? Is this possible?
      5. Auth
        2014-01-20 at 13:27
        
        Thanks Chris!
        
        I did see that plugin but I’m still unsure if it will work with SHA-512 encrypted passwords?
      6. Christoph Haas
        2014-01-20 at 13:35
        
        I think it does.
        
        See http://trac.roundcube.net/browser/github/plugins/password/README#L98
        
        and https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_sha2
      7. BLF
        2014-01-24 at 17:40
        
        What is the SQL statement to update/change a users encrypted password in the database?
      8. Christoph Haas
        2014-01-25 at 16:41
        
        Similar to the INSERT statement – just with UPDATE. Please take an hour and learn the basic SQL statements: SELECT, INSERT, UPDATE and DELETE. You will need it time and again.
2. David R. Hedges
  2014-07-21 at 21:04
  
  Using the mailadmin script [ https://github.com/germania/mailadmin ] listed on the ‘Managing Your Mailserver’ page of this guide, a few modifications are necessary to work with this encryption scheme as well. I’ve never written ruby before, so I was too lazy to figure out how to just generate a random seed, so I re-used my first attempt at generating SHA256 hashes as the 16-character ‘random’ seed for the real hash. Here’s my diff:
  ————–
  diff -ur mailadmin-master/lib/connection.rb public_html/mailadmin-master/lib/connection.rb
  — mailadmin-master/lib/connection.rb 2013-01-13 14:52:38.000000000 -0600
  +++ public_html/mailadmin-master/lib/connection.rb 2014-07-21 13:50:58.381028899 -0500
  @@ -1,7 +1,7 @@
  #!/usr/bin/env ruby
  
  require ‘mysql’
  -require ‘digest/md5’
  +require ‘digest/sha2’
  
  require_relative ‘config’
  require_relative ‘classes’
  @@ -34,7 +34,7 @@
  
  return false unless id
  
  – if Digest::MD5.hexdigest(password) == hash
  + if password.crypt(hash[0,19]) == hash
  return id
  end
  
  @@ -54,7 +54,7 @@
  def update_password(id, password)
  
  @con.query(“update ” + MailConfig::TABLE_USERS + ” set password = ‘%s’ where id = %d;” %
  – [ Digest::MD5.hexdigest(password), id ])
  + [ password.crypt(‘$6$’ + Digest::SHA512.hexdigest(password)[0,16]), id ])
  
  end
  
  @@ -122,7 +122,7 @@
  email = @con.escape_string(“#{lh}@#{domain.name}”)
  
  @con.query(“insert into %s set domain_id = %d, password = ‘%s’, email = ‘%s’, super_admin = %d ” %
  – [ MailConfig::TABLE_USERS, domain.id, Digest::MD5.hexdigest(password), email, super_admin ? 1 : 0 ])
  + [ MailConfig::TABLE_USERS, domain.id, password.crypt(‘$6$’ + Digest::SHA512.hexdigest(password)[0,16]), email, super_admin ? 1 : 0 ])
  
  if admin_domains && admin_domains.length > 0
  id = insert_id
  @@ -142,7 +142,7 @@
  if password.nil? or password.empty?
  password = “password”
  else
  – password = “‘%s'” % Digest::MD5.hexdigest(password)
  + password = “‘%s'” % password.crypt(‘$6$’ + Digest::SHA512.hexdigest(password)[0,16])
  end
  
  sa = super_admin ? 1 : 0
  
  Reply
A.Nonymous
2014-02-19 at 16:27

I was wondering what would be a proper backup solution for this email setup? I can imagine you would want to backup the SQL-database on a daily basis, on a different location/site. Has anyone given any thought about this?

Reply
1. al
  2014-03-01 at 00:23
  
  mysqldump > database.backup
  on a cron everything. backup the file 😉
  
  Reply
Darrell
2014-03-27 at 02:29

I have followed the tutorial word for word but I cannot connect to the web server. I check the logs and it shows the web server has started but nothing is getting to it. Iptables are not blocking as I have not setup any yet. I have checked the hostname, host, resolve.conf, and interfaces for all the correct items in them, my ipaddress is 162.23.17.xxxx what am I missing. Coming over from freebsd and I have a server running freebsd with no problems. Thanks

Reply
1. Dorin Marcoci
  2014-04-05 at 13:04
  
  Also is a good reason to add a unique key for source field in virtual_aliases table.
  
  Reply
  1. Dorin Marcoci
    2014-04-05 at 13:15
    
    And for virtual_domains.name too 🙂
    
    Reply
Bob
2014-05-05 at 15:09

Will you ever have a guide that includes how to setup “Application Specific Passwords” for Dovecot/Postfix/MySQL?

Reply
1. Christoph Haas
  2014-06-28 at 11:29
  
  How do you mean that? Seperate passwords for SMTP and POP3/IMAP?
  
  Reply
Samuel
2014-06-27 at 19:49

I am trying to access the database from a virtual machine, but i can’t reach this. Which port phpmyadmin use by default??

Reply
1. Christoph Haas
  2014-06-28 at 11:33
  
  Whether the machine is virtual or physical shouldn't really matter. PhpMyAdmin uses the default port 3306 to connect to MySQL. You may want to check the configuration in /etc/phpmyadmin if you want to alter the default database.
  
  Reply
Sergey
2014-07-09 at 17:02

Hi!
Christoph, why you dont use postfixadmin? It is very useful.
Thank you for your guides.

Reply
1. Christoph Haas
  2014-07-27 at 19:33
  
  Yes, it is. But when I started the ISPmail tutorials there was no PostfixAdmin back then. And they went a different route and use a different database schema. I tried hard to keep the database and files the same to cause as little trouble for email server administrators as possible. Adopting the PostfixAdmin schema was be possible and it would be nice because I can't really recommend any managent frontend at the moment.
  
  Reply
Lutz
2014-11-13 at 20:57

Instead of it should preferrably be the “mailuser”, which the user name has to be set to 😉

Anyway, THANK YOU so much for this outstanding, comprehensible tutorial! It has helped me a lot to deepen my understanding.

Reply
1. Christoph Haas
  2014-11-16 at 11:45
  
  Right thanks – I have just fixed the typo. And glad the tutorial could still help you. 🙂
  
  Reply
NL
2015-01-06 at 20:41

After following your tutorial, I’ve just tried to simplify life by writting a script to list, add and delete mail aliases.
May be you’d be glad to know that it’s shared here :
http://faiscommechezmoi.org/aliases_menu.bsh

Anyway, many many thanks for spending so much of your time and sharing. Setting up my mail server was made so simple with your work.

By the way, I’ve read about this managent frontend: http://modoboa.org/en/ but not try it yet …

Reply
1. peppi
  2015-02-14 at 21:23
  
  here is where i lost it and dont understand a word of it
  i think there are some things missing in the how to
  
  CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `password` varchar(32) NOT NULL,
  `email` varchar(100) 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;
  
  Reply
gorn
2015-02-17 at 00:08

In spirit of other parts of your tutorial, I think that there needs to be chapter testing, which will explain how to test that the mailuser has been set up properly and it can reach the database. The reason is that if it is not tested here, there can be strange errors later on in connecting database to postfix (which have nothing to do with postfix).

Reply
Thor
2015-04-30 at 11:26

By making only email unique in virtual_users, you will probably not be able to have same name in different domains.

Shouldn’t the statement be
UNIQUE KEY `email` (`domain_id`,`email`),

Migrating from an old multiple-domain setup, and have a little issue with this one.
OTOH, this is the only issue I’ve had setting up on Jenny using this Wheezy version of the tutorial.

Reply
1. Christoph Haas
  2015-04-30 at 12:53
  
  The email address is complete and qualified. You will not have "uschi" in that field – only "uschi@workaround.org". So the unique index on the "email" field does it right. Having the same email address in two different domains… well – how would that work? A domain is part of the email address already. 🙂
  
  Reply
  1. Thor
    2015-04-30 at 13:46
    
    I was not aware that the domain name was a part of the entry in that field. This must have changed in the previous version of your tutorial.
    
    Reply
    1. Christoph Haas
      2015-04-30 at 14:10
      
      You are right. In a very old version of the guide I in fact normalized the user part from the domain part and used a JOIN. The problem though was that with large setups the load on the database became pretty heavy. After all Postfix needs to determine whether a given email address is valid on the server. And that required a CONCAT every time because there was no way to create a proper index over such a string field collected from two different tables.
      
      This way it's a bit of a waste in the database and the data is not normalized. But it works best like that in larger setups.
      
      Reply
Jens
2015-05-04 at 19:11

Hello

Is it possible to have the mysql database on another server then the mailserver?

/jens

Reply
1. Christoph Haas
  2015-05-04 at 19:16
  
  Absolutely. You can put it at the other end of the world if you like as long as the mail server can reach the database server on TCP port 3306.
  
  Reply
  1. Jens
    2015-05-04 at 19:26
    
    Just like this?
    
    user = mailuser
    password = fLxsWdf5ABLqwhZr <-- use your own database password here hosts = 192.168.0.2:3306 dbname = mailserver query = SELECT 1 FROM virtual_domains WHERE name='%s' or user = mailuser password = fLxsWdf5ABLqwhZr <-- use your own database password here hosts = 192.168.0.2 dbname = mailserver query = SELECT 1 FROM virtual_domains WHERE name='%s' Should I do something at the server with the mysql mail user? / Jens
    
    Reply
    1. Christoph Haas
      2015-05-04 at 19:46
      
      Just the IP address – not the port.
      
      And you'll have to change the database permissions (GRANT) so that your mail server can access the database server.
      
      Reply
      1. Jens
        2015-05-04 at 19:54
        
        GRANT ALL ON mail TO mail_user@127.0.0.1 IDENTIFIED BY ‘PASSWORD’;
      2. Christoph Haas
        2015-05-04 at 19:57
        
        Erm, not quite. More like… grant select on 'mailserver.*' to `mailuser`@`ip-address-of-mailserver` identified by…
        
        Even if you are in a hurry I strongly suggest you understand MySQL's permission system.
      3. Jens
        2015-05-04 at 22:32
        
        I have to learn more about the permission system, thanks again.
mchack
2015-06-11 at 22:29

Would you be kind enough to insert a chapter on how make use of Postfixadmin for managing. Sounds like it’s easier than creating each domain and virtual user by hand into the database. But I’m not sure if the created tables of Postfixadmin work the same way with the rest of the howto as the handmade ones…
Thanks for the Tutorial, you’re a lifesaver (although I’m trying to get it done on freebsd instead of debian)

Reply
1. Gecko
  2015-06-12 at 10:17
  
  The comments posted by Duke at https://workaround.org/ispmail/wheezy/managing-your-mail-server work fine
  
  Reply
2. Christoph Haas
  2015-06-13 at 17:52
  
  Last time I checked the Postfixadmin software used a completely different database schema. Although I admit that this tutorial could really use a definitively functional and userfriendly web interface. No offense intended, dear management interface contributors. 🙂 For example managing of chained aliases or searching in the interfaces could really use an upgrade.
  
  I'll finish the Jessie tutorial first and then will see if there's enough time to write another web-based management interface. Or perhaps just a console-based. But considering my spare time that's wishful thinking.
  
  Reply
BFC
2015-10-14 at 14:00

I set up a domain: namea.com and the email is working fine.
How can I add a new domain: nameb.com on the same machine and what are the settings that I have to do extra in order to function the 2 domains on the existing machine(a Debian 7 server)?
Thanks.

Reply
1. Christoph Haas
  2015-10-15 at 06:25
  
  So you just would like to receive email for users in two different domains? That’s easy. Read the ISPmail guide. 🙂
  
  Reply
BFC
2015-10-15 at 12:16

I want to read and send emails in two different domains on the same physical machine(debian 7).
I have (another) silly question: what hostname should I set for my machine: mail.namea.com or mail.nameb.com?
And the last question: should I generate certificates files for both domains: namea.com and nameb.com?
Thanks.

Reply
1. Christoph Haas
  2015-10-15 at 12:27
  
  The hostname of your mail server does not really matter. You can use any domain. Assumed that you run a business at “bfc.org” you could just call it “mx1.bfc.org”. Your customers would connect to mx1.bfc.org and you should create/buy a certificate for that host name. But even then your hostname (that you get when you run “hostname -f”) does not necessarily be the name that users use to connect to.
  
  SNI (server name indication) that would be able to provide the right certificate depending on which server you want to talk to only works for web servers as far as I know. You could do that for webmail access. But using SMTP authentication only works for one single certificate that has to match the mail server’s host name.
  
  Reply
BFC
2015-10-15 at 12:39

Thank you for your (quick) answer Christoph. Indeed, my problem is SMTP authentication. How could I make it work for both namea.com and nameb.com?

Reply
1. Christoph Haas
  2015-10-15 at 13:41
  
  As I said. Use a different host name that everybody uses. The name of the mail server is not necessarily connected to the domains that receive email.
  
  If you really want your users to use different host names (just for the beauty of it) then you could create an SSL/TLS certificate with mail.namea.com and mail.nameb.com as so called “alternate names”. That’s a bit ugly if you use OpenSSL but generally works.
  
  Reply
2. Paul
  2015-10-15 at 16:13
  
  Think of it this way.
  
  massive_email_server.org provides email services for 3000 different domains. The mail server is called:
  
  mx1.massive_email_server.org.
  
  Every one of those 3000 domains will have an MX record within their own DNS record that tells the world, if you want to send email to those domains, connect to; mx1.massive_email_server.org
  
  Likewise, if any one of those domains wish to send email out, they also will connect to mx1.massive_email_server.org
  
  You can have as many domains as you like (all completely different), all operating from the same server.
  
  This is what Christoph was trying to tell you. The server name doesn’t matter, apart from it needs to be found on the Internet. So if you’re operating a public email server, it needs to be a registered domain. In the case of my example, it’s a sub domain of massive_email_server.org – mx1. But it could equally be, mail.massive_email_server.org, or even bloggs.massive_email_server.org. As long as the sub domain can be seen, it’s not relevant.
  
  The point is, the certificate (in my example) must be set for mx1.massive_email_server.org, but if you chose bloggs as a sub domain, the the certificate would be created for; bloggs.massive_email_server.org
  
  Maybe that’ll help a little.
  
  Reply
tobias
2016-10-27 at 16:44

hello when i try and insert the virutal domains i cant

INSERT INTO `mailserver`.`virtual_domains` (
`id` ,
`name`
)
VALUES (
‘1’, ‘servernamehere.co.uk’
);

get error

Wrong

Static analysis:

3 errors found during the analysis.

The semicolon or a closing bracket was expected (near “.” At position 88)
Unexpected token. (Near ‘org’ ‘at position 89)
Unexpected token. (Near ‘)’ at position 95)

SQL query:

INSERT INTO mailserver`.`virtual_domains` `(` id`, `name`) VALUES ( ‘1’, ‘example.org’)

MySQL said: Documentation
# 1054 – Unknown column ” 1 ” in ‘field list’

im useing mysql server 5.7.16-0ubuntu0.16.04.1 and PHP-version: 7.0.8-0ubuntu0.16.04.3 i followed this guide to the letter copy pasted everything 🙂

Reply
1. Christoph Haas
  2016-10-27 at 18:23
  
  I suspect that my content management system is ruining the apostrophes. Please use ' instead of ´. I'll see that I fix this page.
  
  Reply
  1. tobias
    2016-10-28 at 12:12
    
    ok i sorted it added values via a mysql client 🙂 but i want to keep the email for example steve@example.com so he gets it and not redirects it to another email.
    
    do you know how i can make it happen? 🙂
    
    Reply
    1. tobias
      2016-10-28 at 17:32
      
      [code]
      /usr/lib/postfix$ postmap -q violentnoise.co.uk mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
      postmap: warning: mysql query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘??violentnoise.co.uk’’ at line 1
      postmap: fatal: table mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: query error: Success[/code]
      
      dont know what the SQL syntax for MySQL 5.7 is i have done what the connect mysql to postfix.
      
      Reply

Preparing the database

Create the database

virtual_domains

virtual_users

virtual_aliases

Test data

62 thoughts on “Preparing the database”

Leave a Reply Cancel reply