Welcome to the Slackware Documentation Project

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision
Previous revision
howtos:network_services:postfix_dovecot_mysql:database [2015/02/15 07:23 (UTC)]
astrogeek created
howtos:network_services:postfix_dovecot_mysql:database [2015/06/11 14:42 (UTC)] (current)
tim [Creating Mail Database Tables]
Line 1: Line 1:
-===== Virtual Mail Server Database =====+====== Virtual Mail Server Database ​======
  
-<note important>​A work in progress...</​note>​+This page is supplemental to main article: [[howtos:​network_services:​postfix_dovecot_mysql|Creating a Virtual Mail Server with Postfix, Dovecot and MySQL]]
  
-[[howtos:​network_services:​postfix_dovecot_mysql|Return to main article]] +The database is used to provide configuration and authentication for your virtual mail domains and mail boxes. Email content is not stored in the database in this configuration. 
-{{tag>​howtos ​template}}+ 
 +If you have a full Slackware installation with MySQL already working then you may skip to the next section. 
 + 
 +If you need to install MySQL (MariaDB) then grab the package and sigfile from a repo, verify it, then install it: 
 +<​code>​ 
 +# For 32 bit package # 
 +wget http://​slackware.osuosl.org/​slackware-14.1/​patches/​packages/​mariadb-5.5.40-i486-2_slack14.1.txz 
 +wget http://​slackware.osuosl.org/​slackware-14.1/​patches/​packages/​mariadb-5.5.40-i486-2_slack14.1.txz.asc 
 +# For 64 bit package # 
 +wget http://​slackware.osuosl.org/​slackware64-14.1/​patches/​packages/​mariadb-5.5.40-x86_64-2_slack14.1.txz 
 +wget http://​slackware.osuosl.org/​slackware64-14.1/​patches/​packages/​mariadb-5.5.40-x86_64-2_slack14.1.txz.asc 
 +gpg --verify mariadb-5.5.40...txz.asc {path to your downloaded package sigfile} 
 +installpkg mariadb-5.5.40...txz 
 +</​code>​ 
 +<​note>​If installing to a Linode.com VPS you may also need to install the libaio package for your Slackware version.</​note>​ 
 +You will then need to initialize the database server and start it. Follow the instructions found in the top comments of the start script, /​etc/​rc.d/​rc.mysqld. Here is a short summary... 
 +<​code>​ 
 +mysql_install_db --user=mysql 
 +... 
 +chmod +x /​etc/​rc.d/​rc.mysqld 
 +/​etc/​rc.d/​rc.mysqld start 
 +... 
 +mysql_secure_installation 
 +</​code>​ 
 + 
 +==== Creating Mail DB User and Database ==== 
 +Your database server should now be running and have a root mysql user password set. 
 + 
 +Next we need to create a database, and a user to access it on behalf of our mail server. 
 + 
 +We will use the highly original names //​mailserver//​ for our database, and //​mailuser//​ for our user. 
 + 
 +You may easily create both as follows, as root (provide the root mysql user password when prompted):​ 
 + 
 +<​code>​ 
 +mysql -p -e "​CREATE DATABASE mailserver"​ 
 +mysql -p -e "GRANT ALL ON mailserver.* TO mailuser@localhost IDENTIFIED BY '​your-password'"​ 
 +</​code>​ 
 + 
 +Remember the mailuser password for when we configure postfix and dovecot later. 
 + 
 +==== Creating Mail Database Tables ==== 
 +Neither Postfix nor Dovecot place any restrictions on the data structures you should use to support virtual mail functions. Their only requirements are that you must supply a set of queries that return specific fields as needed. 
 + 
 +This gives you total flexibility to integrate your mail system into any arbitrary operating environment. It also means that there is no default database structure, so we //must// provide it ourselves! 
 + 
 +<note tip>We will create only a minimal database schema, sufficient to get the system working. 
 +If you need to serve multiple domains or many virtual users, you will probably want to extend it by adding other columns or tables which meet your specific needs.</​note>​ 
 + 
 +Figure 2 below, shows the schema of our virtual mail database. 
 +{{ howtos:​network_services:​postfix_dovecot_mysql:​schema.gif }} 
 + 
 +  * virtual_domains - Contains an entry for each domain name served by virtual email 
 +  * virtual_users - Contains an entry for each virtual mail box served 
 +  * virtual_aliases - Maps aliases (source) to actual mail boxes (destination) 
 +to create these tables, type 
 +<​code>​mysql --user="​mailuser"​ -p -e "​CREATE TABLE virtual_domains( id INT NOT NULL AUTO_INCREMENT,​ name VARCHAR(50) NOT NULL, PRIMARY KEY ( id ) );" mailserver 
 + 
 +mysql --user="​mailuser"​ -p -e "​CREATE TABLE virtual_users( id INT NOT NULL AUTO_INCREMENT,​ domain_id INT NOT NULL , password VARCHAR(106) NOT NULL, email VARCHAR(100) NOT NULL, PRIMARY KEY ( id ) ,FOREIGN KEY (domain_id) REFERENCES virtual_domains(id));"​ mailserver 
 + 
 +mysql --user="​mailuser"​ -p -e "​CREATE TABLE virtual_aliases( id INT NOT NULL AUTO_INCREMENT,​ domain_id INT NOT NULL, source VARCHAR(100) NOT NULL, destination VARCHAR(100) NOT NULL, PRIMARY KEY ( id ),FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) );" mailserver 
 +</​code>​ each time supplying the password you gave for the mysql mailuser user. 
 +==== Adding Domain Entries ==== 
 +To add entries to the virtual_domains table using the mysql client: 
 +<​code>​ 
 +mysql -p mailserver 
 +mysql> INSERT INTO virtual_domains SET id=NULL, name='​my-domain.com';​ 
 +quit 
 +</​code>​ 
 + 
 +This table must include entries for each domain served by your virtual mail server. 
 + 
 +You should add at least the following rows: 
 + 
 +  * The domain name - my-domain.com 
 +  * The hostname - darkstar 
 +  * The FQDN of the host - darkstar.my-domain.com 
 +  * The localhost.domain variant - localhost.my-domain.com 
 + 
 +To see the current rows in this table using the mysql client: 
 + 
 +<​code>​mysql>​ SELECT * FROM virtual_domains;​ 
 ++----+-------------------------+ 
 +| id | name                    | 
 ++----+-------------------------+ 
 +|  1 | my-domain.com ​          | 
 +|  2 | darkstar ​               | 
 +|  3 | darkstar.my-domain.com ​ | 
 +|  4 | localhost.my-domain.com | 
 ++----+-------------------------+ 
 +</​code>​ 
 + 
 +Note the automatically generated id value for the domain itself, //​my-domain.com//,​ it will be used when we enter user mail boxes and mail box aliases. 
 + 
 +==== Adding User Mail Box Entries ==== 
 +To add entries for each mail box, you will need the domain id from the previous step for the corresponding domain name. 
 + 
 +We do not store the user passwords as plain text in the database, instead we will generate an SHA512 hash of the passwords, used by dovecot for authentication. 
 + 
 +To insert a virtual user mailbox from the mysql client, substitute the domain id, actual password and email into the following query: 
 +<​code>​mysql>​ INSERT INTO virtual_users SET 
 +id=NULL, 
 +domain_id=1,​ 
 +password=ENCRYPT('​user-password',​ CONCAT('​$6$',​ SUBSTRING(SHA(RAND()),​ -16))), 
 +email='​me@my-domain.com';​ 
 +</​code>​ 
 + 
 +To see currently assigned mail boxes for a domain (with password column suppressed):​ 
 +<​code>​mysql>​ SELECT U.id, domain_id, email FROM virtual_domains D 
 +JOIN virtual_users U ON(U.domain_id=D.id) WHERE name='​my-domain.com';​ 
 ++----+-----------+-------------------+ 
 +| id | domain_id | email             | 
 ++----+-----------+-------------------+ 
 +|  1 |         1 | me@my-domain.com ​ | 
 +|  2 |         1 | you@my-domain.com | 
 +|  3 |         1 | us@my-domain.com ​ | 
 ++----+-----------+-------------------+ 
 +</​code>​ 
 + 
 +To reset the password for an email user, substitute the new password and email values into this query: 
 +<​code>​mysql>​ UPDATE virtual_users SET 
 +password=ENCRYPT('​new-password',​ CONCAT('​$6$',​ SUBSTRING(SHA(RAND()),​ -16))) 
 +WHERE email='​me@my-domain.com';​ 
 +</​code>​ 
 + 
 +==== Adding Mail Box Alias Entries ==== 
 +To add entries for each mail box alias, you will again need the domain id corresponding to the domain name of the alias. 
 + 
 +To insert a virtual mailbox alias using the mysql client: 
 +<​code>​mysql>​ INSERT INTO virtual_aliases SET 
 +id=NULL, 
 +domain_id=1,​ 
 +source='​alias@my-domain.com',​ 
 +destination='​you@my-domain.com';​ 
 +</​code>​ 
 +In the example given, mail sent to alias@my-domain.com will be accepted and delivered to you@my-domain.com. 
 + 
 +To see current aliases in the database: 
 +<​code>​mysql>​ SELECT A.id, domain_id, source, destination FROM virtual_domains D  
 +JOIN virtual_aliases A ON(A.domain_id=D.id) WHERE name='​my-domain.com';​ 
 ++----+-----------+---------------------+-------------------+ 
 +| id | domain_id | source ​             | destination ​      | 
 ++----+-----------+---------------------+-------------------+ 
 +|  1 |         1 | alias@my-domain.com | you@my-domain.com | 
 ++----+-----------+---------------------+-------------------+ 
 +</​code>​ 
 + 
 +Go ahead and enter a few virtual email users, and maybe an alias or two, so that you will have working accounts to test with after we set up postfix and dovecot. 
 + 
 +For troubleshooting,​ use the queries shown above to verify the domains, email users and aliases in the database at any time, and to reset virtual user passwords. 
 +[[howtos:​network_services:​postfix_dovecot_mysql|Return to main article ​page]] 
 +====== Sources ====== 
 +  * Originally written by [[wiki:​user:​astrogeek | astrogeek]] 
 +{{tag>​howtos ​email postfix dovecot mysql}}

In Other Languages
QR Code
QR Code howtos:network_services:postfix_dovecot_mysql:database (generated for current page)