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 Linode referral link if you plan to buy VPS (it starts at only $10/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...

14 Responses

  1. 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.

  2. 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.

  3. 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!

  4. 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.

  5. Stein says:

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

    ??

  6. 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’.

  7. 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. 177,942
  2. 8,310
  3. 37,548

Are you subscribed?