How to Setup Postfix Mail Server and Dovecot with Database (MariaDB) Securely – Part 1

If You Appreciate What We Do Here On TecMint, You Should Consider:

  1. Stay Connected to: Twitter | Facebook | Google Plus
  2. Subscribe to our email updates: Sign Up Now
  3. Use our Hosting referral link if you planning to start your blog ($3.82/month).
  4. Support us via PayPal donate - Make a Donation
  5. Support us by purchasing our premium books in PDF format.
  6. Support us by taking our online Linux courses

We are thankful for your never ending support.

Gabriel Cánepa

Gabriel Cánepa is a GNU/Linux sysadmin and web developer from Villa Mercedes, San Luis, Argentina. He works for a worldwide leading consumer product company and takes great pleasure in using FOSS tools to increase productivity in all areas of his daily work.

Your name can also be listed here. Got a tip? Submit it here to become an TecMint author.

RedHat RHCE and RHCSA Certification Book
Linux Foundation LFCS and LFCE Certification Preparation Guide

You may also like...

23 Responses

  1. MilanThapa says:

    @Ravi Saive, are all the suggestions made on previous comments implemented ? because I’m getting error while adding Database Index..

  2. sysadmin_linux says:

    Extremely complicated tutorial.

    I am just wondering if you all think that if anyone wants to create a webmail server then it must be for sure for public .

    What about creating something much more simpler without all that crap .

    Is this email server for Google or for a common person that just wants to have his private email.

    Instead following all these tutorials and get to the end and something is failin , it is much better to install citadel webcit that is more simple and will work at first time. And with citadel installed on a company server, your employees can talk with each others over citadel without having to pick the phone and stay there for hours.

    Citadel also have an internal blog where Admin can post news, and users can have their own private room to put their data and share with they want. Citadel works as email server internally and externally, and you will have a web interface to check your email from home.

    1 single install and everything works perfectly .

    Next time create a tutorial more detailed, and without so many things installed and needed to be configured like MySQL, Postfix, Dovecot & Roundcube.

    A more simple tutorial is here and user is not stuck in the middle because most of the options you say are not there because the time users are reading this tutorial, apps have new versions with removed options and new options added .

    • Gabriel Cánepa says:

      @sysadmin_linux,
      You’re right in that this tutorial is complex (I would not say ‘complicated’) but you are missing the main point here. Nowadays, several business have moved their email services to a cloud provider such as Google Apps or Office365. So why bother to set up a mail server? It is one of the best learning exercises out there. Additionally, some people may actually need to set up one for private use.
      On a side note, I have never heard about Citadel before, and will consider your suggestion for upcoming articles.

  3. Chris says:

    Hello
    I am stack on section 12, when I run the sql i get error #1005 – Can’t create table ‘EmailServer_db.Alias_tbl’ (errno: 150) (Details…)

    the details of the error is pasted here under
    LATEST FOREIGN KEY ERROR
    ————————
    170202 15:00:36 Error in foreign key constraint of table `EmailServer_db`.`Alias_tbl`:
    Create table `EmailServer_db`.`Alias_tbl` with foreign key constraint failed. Referenced table `EmailServer_db`.`Domains_tbl` not found in the data dictionary near ‘FOREIGN KEY (DomainId) REFERENCES Domains_tbl(DomainId) ON DELETE CASCADE
    ) ENGINE = InnoDB’

  4. Nisar says:

    Hi,

    Can someone help me on this issue. I followed all steps carefully, i don’t know where i am doing a mistake. Below are errors.

    "Connection to storage server failed"
    "imap-login: Info: Disconnected (no auth attempts in 0 secs): user=, rip= , lip=, 
    TLS handshaking: SSL_accept() failed: error:14094418:SSL 
    routines:SSL3_READ_BYTES:tlsv1 alert unknown ca: SSL alert number 48, session="
    

    A quick response will be highly appreciated.

  5. Stefan says:

    There are substantial mistakes. I spent more time debugging your tutorial than implementing it. Oh my. First and foremost: You are limiting the password column in Users_tbl to varchar(100). This results in cut-off SHA512 pw hashes because they’re longer than 100 chars. This results in authentication failures in Dovecot and postfix, because the password can never get verified. Second, you should set inet_interfaces = all in /etc/postfix/main.cf. By default it is inet_interfaces = localhost (at least on CentOS 7) which results in users not being able to connect. Thanks.

    • Gabriel Cánepa says:

      Thank you for your comment and contribution. It is possible that some typos and errors such as these were later corrected in the implementation but failed to go into the writeup. It was my understanding that the issues you’re pointing out had been corrected already.
      @Ravi,
      Please make sure that Stefan’s observations are properly added to the article. Thanks!

      • Ravi Saive says:

        @Stefan and @Gabriel,

        Issues are fixed on time, once we came to know by our readers…So, I think all issues are already corrected in the writeup, if you still see, do let us know here, we will fix it asap..

  6. Harmon20 says:

    I chose not to install the PhpMyAdmin package. Can’t see the need in one more potential security hole to maintain. So to make things easy when adding users or virtual domains, I added some procedures to MySQL.

    Probably kinda messy and I didn’t bother with input validation, but it works. (Forgive me if the formatting looks bad here. No preview button to look at this post and tweak till it looks good.)

    To create the procedure to add a virtual domain, type:

    MariaDB [EmailServer_db]> delimiter //
    MariaDB [EmailServer_db]> create procedure adddomain(IN domnm varchar(254))
    -> begin
    -> insert into Domains_tbl (DomainName) values (domnm);
    -> end //
    [output shows here]
    MariaDB [EmailServer_db]> delimiter ;
    

    To add a new virtual domain, type:

    [MariaDB [EmailServer_db]> call adddomain('domainname.tld');
    

    To create the procedure for adding users, type:

    MariaDB [EmailServer_db]> delimiter //
    MariaDB [EmailServer_db]> create procedure adduser(IN usrnm varchar(254), IN domnm varchar(254), IN psswd varchar(254))
    -> begin
    -> declare eml varchar(255) default '';
    -> declare pswd varchar(255) default'';
    -> declare domnum int default 0;
    -> select DomainID into domnum from Domains_tbl where DomainName = domnm;
    -> set pswd = ENCRYPT(psswd, CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)));
    -> set eml = concat(usrnm,'@',domnm);
    -> insert into Users_tbl (DomainId, password, Email) values (domnum, pswd, eml);
    -> end //
    [output shows here]
    MariaDB [EmailServer_db]> delimiter ;
    

    To add a user type:

    MariaDB [EmailServer_db]> call adduser('username','domainname.tld','password');
    

    To create the procedure for creating a new alias mapping, type:

    MariaDB [EmailServer_db]> delimiter //
    MariaDB [EmailServer_db]> create procedure addalias (IN ail varchar(254), IN tu varchar(254), IN dom varchar(254))
    -> begin
    -> declare src, dst varchar(254) default '';
    -> declare domnum int default 0;
    -> select DomainID into domnum from Domains_tbl where DomainName = dom;
    -> set src = concat(ail,'@',dom);
    -> set dst = concat(tu,'@',dom);
    -> insert into Alias_tbl (DomainId, Source, Destination) values (domnum, src, dst);
    -> end //
    [output shows here]
    MariaDB [EmailServer_db]> delimiter ;
    

    To add an alias, type:

    MariaDB [EmailServer_db]> call addalias('aliasusername','username','domainname.tld');
    
    • @Harmon20,
      Big kudos to you for taking the time to share your feedback with us! Let me begin by saying that I don’t usually use PhpMyAdmin either, but I thought it would be a nice addition for this series as some users may find it easier to manage the database using a web interface than the command line.
      As for the procedure, great job! And thanks again for sharing!

  7. MJH says:

    Great series on setting up virtual users and domains, just one major issue I will mention to save others some aggravation. The password column in the Users_tbl table needs to be more than 100 characters long or it truncates any SHA512-CRYPT password that was put in. This causes any authentication attempt via dovecot (when you get to part 2) to fail. I think at least varchar(200) should be plenty as it looks like the length of an encrypted password will generally be 106 characters.

    • @MJH,
      Thank you for pointing this out. I believe someone else noticed it as well and it was my understanding we had corrected it in the writeup. @Ravi, please add a note next to the image MJH is referring to.

  8. Stein says:

    In Section 12: Is not the last Query, Alias Tbl deficient?
    Where the last line should also stand: ENGINE = InnoDB;

    ??

  9. Anders Jackson says:

    Are you shore you shall run mysql_secure_installation on Debian installations? The Debian installation should make all that is done by mysql_secure_installation when the package are installed.

    And never, ever clear user database in Debian installation, as there are a user there that is needed for Debian to be able to handle administration of all databases.

    Read with zmore /usr/share/doc/apache2/README.Debian.gz
    So no, you should NOT make links to start a virtual site in Apache2. Use the right tool instead, a2ensite linuxnew.com

    See Debian Administrator’s Handbook.

    • @Anders,
      Thank you for such an insightful comment. In my defense I’ll say I wrote this series using a CentOS 7 box and while I tried to keep it as compatible with Debian as possible, it’s likely that I could have missed some of the points you’re referring to.
      On the other hand, I just installed mariadb-server and mariadb-client on a Debian Jessie box and the only part that the installation process and mysql_secure_installation have in common is the setting of a password for the root user. In addition, the only user that is removed during mysql_secure_installation is the anonymous user (which is not safe to be present in a production environment), not an administrative user.
      Last, but not least, I agree with you on the fact that a2ensite should be used whenever available. I totally missed that while trying to make this tutorial Debian-compatible. @Ravi, please replace the symbolic link creation with ‘a2ensite linuxnewz.com’.

  10. Tobi says:

    In Section 9 /etc/httpd/conf.d/phpMyAdmin.conf the IP is still visible at Apache 2.2 ;-)

Got something to say? Join the discussion.

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

Join Over 300K+ Linux Users
  1. 202,035
  2. 9,267
  3. 38,621

Are you subscribed?