Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (CentOS 6.2 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).

The forwardings table is for aliasing one email address to another, e.g. forward emails for [email protected] to [email protected].

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

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,

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