[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.
Both sides previous revisionPrevious revisionNext revision | Previous revisionLast revisionBoth sides next revision | ||
howtos:network_services:postfix_dovecot_mysql:database [2015/02/15 08:33 (UTC)] – 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: | ||
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. | 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. | ||
Line 6: | Line 7: | ||
If you have a full Slackware installation with MySQL already working then you may skip to the next section. | 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 from your repo of choice, verify it, then install it with: | + | If you need to install MySQL (MariaDB) then grab the package |
- | < | + | < |
+ | # 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, / | 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 | + | mysql_install_db --user=mysql |
... | ... | ||
- | chmod +x / | + | chmod +x / |
/ | / | ||
... | ... | ||
- | mysql_secure_installation | + | mysql_secure_installation |
</ | </ | ||
Line 33: | Line 43: | ||
</ | </ | ||
- | Remember the mailuser password for postfix and dovecot | + | Remember the mailuser password for when we configure |
==== Creating Mail Database Tables ==== | ==== Creating Mail Database Tables ==== | ||
Line 39: | Line 49: | ||
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! | 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. | Figure 2 below, shows the schema of our virtual mail database. | ||
Line 46: | Line 59: | ||
* virtual_users - Contains an entry for each virtual mail box served | * virtual_users - Contains an entry for each virtual mail box served | ||
* virtual_aliases - Maps aliases (source) to actual mail boxes (destination) | * virtual_aliases - Maps aliases (source) to actual mail boxes (destination) | ||
+ | to create these tables, type | ||
+ | < | ||
+ | |||
+ | mysql --user=" | ||
+ | mysql --user=" | ||
+ | </ | ||
==== Adding Domain Entries ==== | ==== Adding Domain Entries ==== | ||
- | To add entries to the virtual_domains table use the following query: | + | 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 mail server. | + | This table must include entries for each domain served by your virtual |
You should add at least the following rows: | You should add at least the following rows: | ||
Line 59: | Line 81: | ||
* The hostname - darkstar | * The hostname - darkstar | ||
* The FQDN of the host - darkstar.my-domain.com | * The FQDN of the host - darkstar.my-domain.com | ||
- | * The localhost.domain variant - localhost@my-domain.com | + | * The localhost.domain variant - localhost.my-domain.com |
- | To see the current rows in this table... | + | To see the current rows in this table using the mysql client: |
- | < | + | < |
+----+-------------------------+ | +----+-------------------------+ | ||
| id | name | | | id | name | | ||
Line 74: | Line 96: | ||
</ | </ | ||
- | Note the automatically generated id value for my-domain.com, | + | 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 ==== | ==== 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. | 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. | + | 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 | + | To insert a virtual user mailbox |
- | < | + | <code>mysql> INSERT INTO virtual_users SET |
id=NULL, | id=NULL, | ||
- | domain_id=1, | + | domain_id=1, |
password=ENCRYPT(' | password=ENCRYPT(' | ||
email=' | email=' | ||
</ | </ | ||
- | To see currently assigned mail boxes for a domain (with password suppressed), use the following query... | + | To see currently assigned mail boxes for a domain (with password |
< | < | ||
JOIN virtual_users U ON(U.domain_id=D.id) WHERE name=' | JOIN virtual_users U ON(U.domain_id=D.id) WHERE name=' | ||
Line 99: | Line 121: | ||
| 3 | 1 | us@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 ==== | ==== Adding Mail Box Alias Entries ==== | ||
- | To add entries for each mail box alias, you will again need the domain id from the first step corresponding to the domain name of the alias. | + | 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 use the following query... | + | To insert a virtual mailbox alias using the mysql client: |
- | < | + | <code>mysql> INSERT INTO virtual_aliases SET |
id=NULL, | id=NULL, | ||
domain_id=1, | domain_id=1, | ||
Line 113: | Line 141: | ||
In the example given, mail sent to alias@my-domain.com will be accepted and delivered to you@my-domain.com. | 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, use the following query... | + | To see current aliases in the database: |
< | < | ||
JOIN virtual_aliases A ON(A.domain_id=D.id) WHERE name=' | JOIN virtual_aliases A ON(A.domain_id=D.id) WHERE name=' | ||
Line 123: | Line 151: | ||
</ | </ | ||
+ | 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: | [[howtos: | ||
+ | ====== Sources ====== | ||
+ | * Originally written by [[wiki: | ||
{{tag> | {{tag> |