Preparing the database

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:

phpMyAdmin login screen

Log in as ‘root’ with the administrative database password you set previously. Then you will find yourself on the main screen:

phpMyAdmin 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.)

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;

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;

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;

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'
 );

63 thoughts on “Preparing the database

  • 2013-11-07 at 10:53
    Permalink

    Chapter – Add a less privileged MySQL user

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

    Reply
    • 2013-11-07 at 11:01
      Permalink

      Thanks for the hint. Corrected now.

      Reply
  • 2013-11-07 at 13:24
    Permalink

    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
    • 2013-11-07 at 14:40
      Permalink

      Whoops… thanks for the hint. Corrected².

      Glad to get that feedback. Thank you. 🙂

      Reply
  • 2013-11-12 at 13:44
    Permalink

    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
  • 2013-11-15 at 20:57
    Permalink

    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
  • 2013-12-07 at 18:26
    Permalink

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

    Reply
  • 2013-12-14 at 15:30
    Permalink

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

    Reply
  • 2013-12-31 at 08:07
    Permalink

    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
    • 2013-12-31 at 09:33
      Permalink

      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
      • 2014-01-05 at 14:17
        Permalink

        Is there a reason you didn’t use Blowfish encryption?

        Reply
        • 2014-01-12 at 06:16
          Permalink

          Tried to use it, did not get it working, decided sha512 was good enough.

          Reply
          • 2014-01-14 at 09:09
            Permalink

            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?

          • 2014-01-16 at 23:19
            Permalink

            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!

          • 2014-01-18 at 09:38
            Permalink

            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.

          • 2014-01-20 at 12:30
            Permalink

            Is it possible for users to change their passwords (that are stored in MySQL encrypted) using Roundcube? Is this possible?

          • 2014-01-20 at 13:27
            Permalink

            Thanks Chris!

            I did see that plugin but I’m still unsure if it will work with SHA-512 encrypted passwords?

          • 2014-01-24 at 17:40
            Permalink

            What is the SQL statement to update/change a users encrypted password in the database?

          • 2014-01-25 at 16:41
            Permalink

            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.

    • 2014-07-21 at 21:04
      Permalink

      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
  • 2014-02-19 at 16:27
    Permalink

    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
    • 2014-03-01 at 00:23
      Permalink

      mysqldump > database.backup
      on a cron everything. backup the file 😉

      Reply
  • 2014-03-27 at 02:29
    Permalink

    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
    • 2014-04-05 at 13:04
      Permalink

      Also is a good reason to add a unique key for source field in virtual_aliases table.

      Reply
      • 2014-04-05 at 13:15
        Permalink

        And for virtual_domains.name too 🙂

        Reply
  • 2014-05-05 at 15:09
    Permalink

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

    Reply
    • 2014-06-28 at 11:29
      Permalink

      How do you mean that? Seperate passwords for SMTP and POP3/IMAP?

      Reply
  • 2014-06-27 at 19:49
    Permalink

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

    Reply
    • 2014-06-28 at 11:33
      Permalink

      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
  • 2014-07-09 at 17:02
    Permalink

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

    Reply
    • 2014-07-27 at 19:33
      Permalink

      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
  • 2014-11-13 at 20:57
    Permalink

    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
    • 2014-11-16 at 11:45
      Permalink

      Right thanks – I have just fixed the typo. And glad the tutorial could still help you. 🙂

      Reply
  • 2015-01-06 at 20:41
    Permalink

    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
    • 2015-02-14 at 21:23
      Permalink

      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
  • 2015-02-17 at 00:08
    Permalink

    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
  • 2015-04-30 at 11:26
    Permalink

    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
    • 2015-04-30 at 12:53
      Permalink

      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
      • 2015-04-30 at 13:46
        Permalink

        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
        • 2015-04-30 at 14:10
          Permalink

          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
  • 2015-05-04 at 19:11
    Permalink

    Hello

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

    /jens

    Reply
    • 2015-05-04 at 19:16
      Permalink

      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
      • 2015-05-04 at 19:26
        Permalink

        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
        • 2015-05-04 at 19:46
          Permalink

          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
          • 2015-05-04 at 19:54
            Permalink

            GRANT ALL ON mail TO mail_user@127.0.0.1 IDENTIFIED BY ‘PASSWORD’;

          • 2015-05-04 at 19:57
            Permalink

            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.

             

          • 2015-05-04 at 22:32
            Permalink

            I have to learn more about the permission system, thanks again.

  • 2015-06-11 at 22:29
    Permalink

    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
    • 2015-06-13 at 17:52
      Permalink

      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
  • 2015-10-14 at 14:00
    Permalink

    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
    • 2015-10-15 at 06:25
      Permalink

      So you just would like to receive email for users in two different domains? That’s easy. Read the ISPmail guide. 🙂

      Reply
  • 2015-10-15 at 12:16
    Permalink

    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
    • 2015-10-15 at 12:27
      Permalink

      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
  • 2015-10-15 at 12:39
    Permalink

    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
    • 2015-10-15 at 13:41
      Permalink

      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
    • 2015-10-15 at 16:13
      Permalink

      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
  • 2016-10-27 at 16:44
    Permalink

    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
    • 2016-10-27 at 18:23
      Permalink

      I suspect that my content management system is ruining the apostrophes. Please use ' instead of ´. I'll see that I fix this page.

      Reply
      • 2016-10-28 at 12:12
        Permalink

        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
        • 2016-10-28 at 17:32
          Permalink

          [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

Leave a Reply

Your email address will not be published. Required fields are marked *

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close