Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (CentOS 6.3 x86_64) - Page 2

6 Set MySQL Passwords And Configure phpMyAdmin

Start MySQL:

chkconfig --levels 235 mysqld on
/etc/init.d/mysqld start

Then set passwords for the MySQL root account:

mysql_secure_installation

[root@server1 ~]# mysql_secure_installation




NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
 <-- ENTER
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n]
 <-- ENTER
New password: <-- yourrootsqlpassword
Re-enter new password: <-- yourrootsqlpassword
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
 <-- ENTER
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
 <-- ENTER
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
 <-- ENTER
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
 <-- ENTER
 ... Success!

Cleaning up...



All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


[root@server1 ~]#

Now we configure phpMyAdmin. We change the Apache configuration so that phpMyAdmin allows connections not just from localhost (by commenting out the <Directory /usr/share/phpMyAdmin/> stanza):

vi /etc/httpd/conf.d/phpMyAdmin.conf

[...]
#<Directory /usr/share/phpMyAdmin/>
# Order Deny,Allow
# Deny from All
# Allow from 127.0.0.1
# Allow from ::1
#</Directory>
[...]

Then we create the system startup links for Apache and start it:

chkconfig --levels 235 httpd on
/etc/init.d/httpd start

Now you can direct your browser to http://server1.example.com/phpMyAdmin/ or http://192.168.0.100/phpMyAdmin/ and log in with the user name root and your new root MySQL password.

 

7 Create The MySQL Database For Postfix/Courier

We create a database called mail:

mysqladmin -u root -p create mail

Next, we go to the MySQL shell:

mysql -u root -p

On the MySQL shell, we create the user mail_admin with the passwort mail_admin_password (replace it with your own password) who has SELECT,INSERT,UPDATE,DELETE privileges on the mail database. This user will be used by Postfix and Courier to connect to the mail database:

GRANT SELECT, INSERT, UPDATE, DELETE ON mail.* TO 'mail_admin'@'localhost' IDENTIFIED BY 'mail_admin_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mail.* TO 'mail_admin'@'localhost.localdomain' IDENTIFIED BY 'mail_admin_password';
FLUSH PRIVILEGES;

Still on the MySQL shell, we create the tables that Postfix and Courier need:

USE mail;

CREATE TABLE domains (
domain varchar(50) NOT NULL,
PRIMARY KEY (domain) )
ENGINE=MyISAM;

CREATE TABLE forwardings (
source varchar(80) NOT NULL,
destination TEXT NOT NULL,
PRIMARY KEY (source) )
ENGINE=MyISAM;

CREATE TABLE users (
email varchar(80) NOT NULL,
password varchar(20) NOT NULL,
quota bigint(20) DEFAULT '10485760',
PRIMARY KEY (email)
) ENGINE=MyISAM;

CREATE TABLE transport (
domain varchar(128) NOT NULL default '',
transport varchar(128) NOT NULL default '',
UNIQUE KEY domain (domain)
) ENGINE=MyISAM;

quit;

As you may have noticed, with the quit; command we have left the MySQL shell and are back on the Linux shell.

The domains table will store each virtual domain that Postfix should receive emails for (e.g. example.com).

domain
example.com

The forwardings table is for aliasing one email address to another, e.g. forward emails for info@example.com to sales@example.com.

source destination
info@example.com sales@example.com

The users table stores all virtual users (i.e. email addresses, because theemail address and user name is the same) and passwords (in encrypted form!) and a quota value for each mail box (in this example the default value is 10485760 bytes which means 10MB).

email password quota
sales@example.com No9.E4skNvGa. ("secret" in encrypted form) 10485760

The transport table is optional, it is for advanced users. It allows to forward mails for single users, whole domains or all mails to another server. For example,

domain transport
example.com smtp:[1.2.3.4]

would forward all emails for example.com via the smtp protocol to the server with the IP address 1.2.3.4 (the square brackets [] mean "do not make a lookup of the MX DNS record" (which makes sense for IP addresses...). If you use a fully qualified domain name (FQDN) instead you would not use the square brackets.).

 

8 Configure Postfix

Now we have to tell Postfix where it can find all the information in the database. Therefore we have to create six text files. You will notice that I tell Postfix to connect to MySQL on the IP address 127.0.0.1 instead of localhost. This is because Postfix is running in a chroot jail and does not have access to the MySQL socket which it would try to connect if I told Postfix to use localhost. If I use 127.0.0.1 Postfix uses TCP networking to connect to MySQL which is no problem even in a chroot jail (the alternative would be to move the MySQL socket into the chroot jail which causes some other problems).

Now let's create our six text files.

vi /etc/postfix/mysql-virtual_domains.cf

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT domain AS virtual FROM domains WHERE domain='%s'
hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_forwardings.cf

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT destination FROM forwardings WHERE source='%s'
hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_mailboxes.cf

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'
hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_email2email.cf

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT email FROM users WHERE email='%s'
hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_transports.cf

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT transport FROM transport WHERE domain='%s'
hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_mailbox_limit_maps.cf

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT quota FROM users WHERE email='%s'
hosts = 127.0.0.1

chmod o= /etc/postfix/mysql-virtual_*.cf
chgrp postfix /etc/postfix/mysql-virtual_*.cf

Now we create a user and group called vmail with the home directory /home/vmail. This is where all mail boxes will be stored.

groupadd -g 5000 vmail
useradd -g vmail -u 5000 vmail -d /home/vmail -m

Next we do some Postfix configuration. Go sure that you replace server1.example.com with a valid FQDN, otherwise your Postfix might not work properly!

postconf -e 'myhostname = server1.example.com'
postconf -e 'mydestination = server1.example.com, localhost, localhost.localdomain'
postconf -e 'mynetworks = 127.0.0.0/8'
postconf -e 'virtual_alias_domains ='
postconf -e ' virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf'
postconf -e 'virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf'
postconf -e 'virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf'
postconf -e 'virtual_mailbox_base = /home/vmail'
postconf -e 'virtual_uid_maps = static:5000'
postconf -e 'virtual_gid_maps = static:5000'
postconf -e 'smtpd_sasl_auth_enable = yes'
postconf -e 'broken_sasl_auth_clients = yes'
postconf -e 'smtpd_sasl_authenticated_header = yes'
postconf -e 'smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination'
postconf -e 'smtpd_use_tls = yes'
postconf -e 'smtpd_tls_cert_file = /etc/postfix/smtpd.cert'
postconf -e 'smtpd_tls_key_file = /etc/postfix/smtpd.key'
postconf -e 'transport_maps = proxy:mysql:/etc/postfix/mysql-virtual_transports.cf'
postconf -e 'virtual_create_maildirsize = yes'
postconf -e 'virtual_maildir_extended = yes'
postconf -e 'virtual_mailbox_limit_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailbox_limit_maps.cf'
postconf -e 'virtual_mailbox_limit_override = yes'
postconf -e 'virtual_maildir_limit_message = "The user you are trying to reach is over quota."'
postconf -e 'virtual_overquota_bounce = yes'
postconf -e 'proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks $virtual_mailbox_limit_maps'
postconf -e 'inet_interfaces = all'

Afterwards we create the SSL certificate that is needed for TLS:

cd /etc/postfix
openssl req -new -outform PEM -out smtpd.cert -newkey rsa:2048 -nodes -keyout smtpd.key -keyform PEM -days 365 -x509

Country Name (2 letter code) [XX]: <-- Enter your Country Name (e.g., "DE").
State or Province Name (full name) []:
<-- Enter your State or Province Name.
Locality Name (eg, city) [Default City]:
<-- Enter your City.
Organization Name (eg, company) [Default Company Ltd]:
<-- Enter your Organization Name (e.g., the name of your company).
Organizational Unit Name (eg, section) []:
<-- Enter your Organizational Unit Name (e.g. "IT Department").
Common Name (eg, your name or your server's hostname) []:
<-- Enter the Fully Qualified Domain Name of the system (e.g. "server1.example.com").
Email Address []:
<-- Enter your Email Address.

Then change the permissions of the smtpd.key:

chmod o= /etc/postfix/smtpd.key

Share this page:

12 Comment(s)

Add comment

Comments

From: pepo at: 2013-04-05 21:26:12

I had some problems to send email though the server. Checking the maillogs I found these errors:

Apr  5 22:46:06 pepomail postfix/smtpd[23884]: connect from ******.nl[80.100.0.0]
Apr  5 22:46:06 pepomail postfix/smtpd[23884]: warning: SASL authentication failure: cannot connect to Courier authdaemond: No such file or directory
Apr  5 22:46:06 pepomail postfix/smtpd[23884]: warning: SASL authentication failure: Password verification failed
Apr  5 22:46:06 pepomail postfix/smtpd[23884]: warning: *****.nl[80.100.0.0]: SASL PLAIN authentication failed: generic failure

After a little Googleling I found that the jail of postfix causes this problem. You can solve this by adding a simlink to the authdaemon socket file inside of the chroot:

cd /var/spool/postfix
ln /var/spool/authdaemon/socket courier-authdaemon-socket

Then change the authdaemond_path in your smtpd.conf to just ‘courier-authdaemon-socket’. Restart postfix and it should work

Check http://www.brandonchecketts.com/archives/configuring-postfix-sasl-to-authenticate-against-courier-authlib for details.

Happy mailing!

pepo

From: Roneil Balbarino at: 2013-04-23 20:49:51

Hi,  

What if my mailbox directory looks like in below table.?

Another thing is I am using vquota in main.cf where I set the quotas per mail addresses.

 virtual_mailbox_limit_maps = hash:/etc/postfix/vquota

What should the script looks like? I was having trouble making the script works. Would really appreciate if someone could help me. Thanks! 

#            |
#            |
#        home/vmail
#            |
#            |
#    -----------------
#    |       |       |
#    |       |       |
# user1/   user2/  user3/
#                    |
#                    |
#                maildirsize
#

From: Lokendra at: 2013-07-30 10:18:32

Hi there,

I have successfully integrated Vadmin plugin in to squirrelmail.

Using vadmin I am able to create users, but when I try to login using new user credentials, I get "ERROR: Connection dropped by IMAP server."

and when I send welcome message using mailx command and try to login again, it logs-in successfully.

 

My question is can we send welcome mail to new users using vadmin plugin or some other way.

 

Please reply back as soon as possible..

 

Also there is no sql table structure defined required by vadmin , i have to improvise and created tables and columns by looking its queries.

Can you please help me locate table structure required by vadmin.

 

Thank you in advance..!

 

From: ggajic at: 2013-11-23 16:30:22

Hi, package cyrus-sasl-plain is missing. When  I tried:

]# telnet localhost 25
Trying ::1...
Connected to localhost.
Escape character is '^]'.


Connection closed by foreign host.
this is what I got in /var/log/maillog:

Nov 23 17:20:40 mail postfix/smtpd[13580]: connect from localhost[::1]
Nov 23 17:20:40 mail postfix/smtpd[13580]: warning: xsasl_cyrus_server_get_mechanism_list: no applicable SASL mechanisms
Nov 23 17:20:40 mail postfix/smtpd[13580]: fatal: no SASL authentication mechanisms
Nov 23 17:20:41 mail postfix/master[8319]: warning: process /usr/libexec/postfix/smtpd pid 13580 exit status 1
Nov 23 17:20:41 mail postfix/master[8319]: warning: /usr/libexec/postfix/smtpd: bad command startup -- throttling

  yum install cyrus-sasl-plain

solves problem

From: Samuel Vera at: 2013-05-29 22:03:36

error 

los log del mailog

chdir example.com/sales/: No such file or directory

May 29 11:49:47 server1 imapd: sales@example.com: No such file or directory

ERROR: Connection dropped by IMAP server

 

 

pliss help

From: at: 2013-06-01 20:24:31

error squirrelmail Submitted by Samuel Vera (not registered) on Wed, 2013-05-29 23:03. error los log del mailog chdir example.com/sales/: No such file or directory May 29 11:49:47 server1 imapd: sales@example.com: No such file or directory ERROR: Connection dropped by IMAP server pliss help

From: Anonymous at: 2013-06-06 04:20:57

hi, i have the same issue with your email server Connection drop by IMAP server, how's your email server, did you solve the problem? how can i fix it? thanks

From: deny yulianto at: 2013-09-23 18:43:23

If you had an error like ERROR: Connection dropped by IMAP server when you log into you squirrelmail and you check on your email log you found something like this>>>> chdir example.com/sales/: No such file or directory server1 imapd: sales@example.com: No such file or directory. DONT DO THIS >>> INSERT INTO `transport` (`domain`, `transport`) VALUES ('example.com', 'smtp:mail.example.com'); If you do that, your email will loopback by sending your email into your linux system user that used mailx for send the message. Your mail will be delivered to /var/spool/mail/your_user_name. try to vi /var/spool/mail/your_user_name to check your mail that was looped back. The solution is DROP your 'transport' table values on your mail database. Then your mail server should be okay.

From: Anonymous at: 2013-12-08 05:12:47

hi all, I followed all the tutorial regarding this postfix mail server configuration. Everything was going fine. But at last when i logged into the squirrelmail page, previously there was error like "Error: Connection Drop by IMAP server" so, i drop the 'transport' table values. But now, when i logged into the squirrelmail, a blank page appear with no data. Please guys help me. I cannot find the solution.

From: Anonymous at: 2013-12-08 05:29:37

hi all, I have following this tutorial for mail server configuration. Everything was working fine.But finally when i logged in to the squirrel mail, previously there was error like "Error: Connection Drop by IMAP server" and i drop the 'transport' table values and again try to logged in to the squirrel, the IMAP error message is gone but there is no data on squirrel mail page. Guys please help.

the log message when i logged into the squirrel mail home page is following:

tail -f /var/log/maillog

Dec 5 11:23:26 mail imapd: Connection, ip=[::ffff:127.0.0.1] Dec 5 11:23:26 mail imapd: LOGIN, user=sic@pokhara.com, ip=[::ffff:127.0.0.1], port=[33857], protocol=IMAP

Dec 5 11:23:26 mail imapd: LOGOUT, user=sic@pokhara.com, ip=[::ffff:127.0.0.1], headers=0, body=0, rcvd=30, sent=238, time=0 Dec 5 11:23:46 mail clamd[1839]: SelfCheck: Database status OK.

From: Hadi at: 2014-11-27 16:02:48


Any one will follow this tutorial will have a problem: Connection dropped by IMAP

 The Solution ( if you have the user : admin @ example.com )

create the directory example.com in "/home/vmail"

 then create the user admin mailbox:

 maildirmake  /home/vmail/example.com/admin

 chown vmail.vmail -R /home/vmail/example.com

 

From: admin at: 2014-11-28 07:21:59

The Mailbox gets xreated automatically by postfix when the first email arrives. Thats why the guide contains instructions to send a mail to the Mailbox first before you use it.