postfix.sql 3.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081
  1. ------------------------------------------
  2. -- ALTERNATIVE TABLE LAYOUT FOR TUTORIAL:
  3. -- Email with Postfix, Dovecot, and MySQL
  4. -- * https://www.linode.com/docs/email/postfix/email-with-postfix-dovecot-and-mysql
  5. -- * Source: https://github.com/linode/docs/blob/master/docs/email/postfix/email-with-postfix-dovecot-and-mysql.md
  6. --
  7. -- This layout is more consistence and wont require as much maintanance
  8. -- as the one in the tutorial, e.g will the setup in the tutorial
  9. -- require
  10. -- * update on every table if the domain name changes, even though this happens rarely.
  11. -- * update on aliases if the main email changes, even though this happens rarely.
  12. --
  13. -- It also ensures that
  14. -- * the child (aliases) is deleted if a user is deleted, and not just the domain.
  15. -- No "dangling" aliases...
  16. ------------------------------------------
  17. -- Layout
  18. -- | virtual_domains |
  19. -- |++++++++++++++++++++|
  20. -- | id | name |
  21. -- | int| <domain name> |
  22. -- e.g
  23. -- | 1 | domain.tld |
  24. -- | virtual_user |
  25. -- |++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
  26. -- | id | domain_id | password | username |
  27. -- | int| int | <password> | <username withouth domain> |
  28. -- e.g ------------------------------------------------------------------
  29. -- | 1 | 1 | y9w2..fudh8h9 | admin |
  30. -- | virtual_aliases |
  31. -- |++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
  32. -- | id | domain_id | user_id | alias |
  33. -- | int| int | int | <alias without domain> |
  34. -- e.g ------------------------------------------------------------------
  35. -- | 1 | 1 | 1 | adminalias |
  36. -- Create table for virtual domains
  37. CREATE TABLE `virtual_domains` (
  38. `id` int(11) NOT NULL auto_increment,
  39. `name` varchar(190) NOT NULL,
  40. PRIMARY KEY (`id`),
  41. UNIQUE KEY `domain` (`name`)
  42. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  43. -- Create a table for all of the email addresses and passwords:
  44. CREATE TABLE `virtual_users` (
  45. `id` int(11) NOT NULL auto_increment,
  46. `domain_id` int(11) NOT NULL,
  47. `password` varchar(106) NOT NULL,
  48. `username` varchar(64) NOT NULL,
  49. PRIMARY KEY (`id`),
  50. UNIQUE KEY `email` (`username`, `domain_id`),
  51. FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
  52. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  53. -- Create a table for the email aliases:
  54. CREATE TABLE `virtual_aliases` (
  55. `id` int(11) NOT NULL auto_increment,
  56. `domain_id` int(11) NOT NULL,
  57. `user_id` int(11) NOT NULL,
  58. `alias` varchar(100) NOT NULL,
  59. PRIMARY KEY (`id`),
  60. UNIQUE KEY `email` (`alias`, `domain_id`),
  61. FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE,
  62. FOREIGN KEY (user_id) REFERENCES virtual_users(id) ON DELETE CASCADE
  63. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  64. -- Insert hostname
  65. INSERT INTO `mailserver`.`virtual_domains` (`name`) VALUES ('domain.tld')
  66. -- Insert a user
  67. INSERT INTO `mailserver`.`virtual_users` (`domain_id`, `password`, `username`)
  68. VALUES ('1', ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user');
  69. -- Insert an alias for user
  70. INSERT INTO `mailserver`.`virtual_aliases` (`domain_id`, `user_id`, `alias`)
  71. VALUES ('1', '1', 'alias');