[2024-feb-29] Sad news: Eric Layton aka Nocturnal Slacker aka vtel57 passed away on Feb 26th, shortly after hospitalization. He was one of our Wiki's most prominent admins. He will be missed.
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revisionLast revisionBoth sides next revision | ||
howtos:network_services:postfix_dovecot_mysql:database [2015/02/15 07:23 (UTC)] – created astrogeek | howtos:network_services:postfix_dovecot_mysql:database [2015/06/11 09:39 (UTC)] – [Creating Mail Database Tables] tim | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Virtual Mail Server Database ===== | + | ====== Virtual Mail Server Database |
- | <note important> | + | This page is supplemental to main article: [[howtos: |
- | [[howtos: | + | 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> | + | |
+ | 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: | ||
+ | < | ||
+ | # For 32 bit package # | ||
+ | wget http:// | ||
+ | wget http:// | ||
+ | # For 64 bit package # | ||
+ | wget http:// | ||
+ | wget http:// | ||
+ | gpg --verify mariadb-5.5.40...txz.asc {path to your downloaded package sigfile} | ||
+ | installpkg mariadb-5.5.40...txz | ||
+ | </ | ||
+ | < | ||
+ | You will then need to initialize the database server and start it. Follow the instructions found in the top comments of the start script, / | ||
+ | < | ||
+ | mysql_install_db --user=mysql | ||
+ | ... | ||
+ | chmod +x / | ||
+ | / | ||
+ | ... | ||
+ | mysql_secure_installation | ||
+ | </ | ||
+ | |||
+ | ==== 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 // | ||
+ | |||
+ | You may easily create both as follows, as root (provide the root mysql user password when prompted): | ||
+ | |||
+ | < | ||
+ | mysql -p -e " | ||
+ | mysql -p -e "GRANT ALL ON mailserver.* TO mailuser@localhost IDENTIFIED BY ' | ||
+ | </ | ||
+ | |||
+ | 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.</ | ||
+ | |||
+ | Figure 2 below, shows the schema of our virtual mail database. | ||
+ | {{ howtos: | ||
+ | |||
+ | * 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 | ||
+ | < | ||
+ | |||
+ | mysql --user=" | ||
+ | |||
+ | mysql --user=" | ||
+ | </ | ||
+ | ==== Adding Domain Entries ==== | ||
+ | To add entries to the virtual_domains table using the mysql client: | ||
+ | < | ||
+ | mysql -p mailserver | ||
+ | mysql> INSERT INTO virtual_domains SET id=NULL, name=' | ||
+ | quit | ||
+ | </ | ||
+ | |||
+ | 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: | ||
+ | |||
+ | < | ||
+ | +----+-------------------------+ | ||
+ | | id | name | | ||
+ | +----+-------------------------+ | ||
+ | | 1 | my-domain.com | ||
+ | | 2 | darkstar | ||
+ | | 3 | darkstar.my-domain.com | ||
+ | | 4 | localhost.my-domain.com | | ||
+ | +----+-------------------------+ | ||
+ | </ | ||
+ | |||
+ | Note the automatically generated id value for the domain itself, // | ||
+ | |||
+ | ==== 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: | ||
+ | < | ||
+ | id=NULL, | ||
+ | domain_id=1, | ||
+ | password=ENCRYPT(' | ||
+ | email=' | ||
+ | </ | ||
+ | |||
+ | To see currently assigned mail boxes for a domain (with password column suppressed): | ||
+ | < | ||
+ | JOIN virtual_users U ON(U.domain_id=D.id) WHERE name=' | ||
+ | +----+-----------+-------------------+ | ||
+ | | id | domain_id | email | | ||
+ | +----+-----------+-------------------+ | ||
+ | | 1 | 1 | me@my-domain.com | ||
+ | | 2 | 1 | you@my-domain.com | | ||
+ | | 3 | 1 | us@my-domain.com | ||
+ | +----+-----------+-------------------+ | ||
+ | </ | ||
+ | |||
+ | To reset the password for an email user, substitute the new password and email values into this query: | ||
+ | < | ||
+ | password=ENCRYPT(' | ||
+ | WHERE email=' | ||
+ | </ | ||
+ | |||
+ | ==== 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: | ||
+ | < | ||
+ | id=NULL, | ||
+ | domain_id=1, | ||
+ | source=' | ||
+ | destination=' | ||
+ | </ | ||
+ | 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: | ||
+ | < | ||
+ | JOIN virtual_aliases A ON(A.domain_id=D.id) WHERE name=' | ||
+ | +----+-----------+---------------------+-------------------+ | ||
+ | | id | domain_id | source | ||
+ | +----+-----------+---------------------+-------------------+ | ||
+ | | 1 | 1 | alias@my-domain.com | you@my-domain.com | | ||
+ | +----+-----------+---------------------+-------------------+ | ||
+ | </ | ||
+ | |||
+ | 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, | ||
+ | [[howtos: | ||
+ | ====== Sources ====== | ||
+ | * Originally written by [[wiki: | ||
+ | {{tag> |