Difference between revisions of "Mail Tables (MySQL)"

From Hexwiki
Jump to navigation Jump to search
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
This is a suggested table layout for a Postfix + Dovecot + MySQL setup. I tend to manage entries manually, if only because I do not have a whole lot of users. Most of this is for webmaster and abuse accounts that need to handle bounces and such. If there is interest I could whip together a script to help manage this over the web.
+
This is a suggested table layout for a Postfix + Dovecot + MySQL setup.  
−
 
 
 
== The Tables ==
 
== The Tables ==
  
 
  CREATE DATABASE mail;
 
  CREATE DATABASE mail;
 
  USE mail;
 
  USE mail;
−
  GRANT USAGE ON mail.* TO 'vmreader'@'localhost' IDENTIFIED BY 'passwordhere';
+
  GRANT USAGE ON *.* TO 'vmreader'@'localhost' IDENTIFIED BY 'passwordhere';
  
 
  CREATE TABLE `mail_domains` (
 
  CREATE TABLE `mail_domains` (
Line 21: Line 20:
 
   `mailpass` varbinary(126) NOT NULL DEFAULT 'defaultmailpasswordhashhere',
 
   `mailpass` varbinary(126) NOT NULL DEFAULT 'defaultmailpasswordhashhere',
 
   `isactive` tinyint(1) NOT NULL DEFAULT '1',
 
   `isactive` tinyint(1) NOT NULL DEFAULT '1',
−
  `privilegedemail` tinyint(1) NOT NULL DEFAULT '1',
 
−
  `publicemail` tinyint(1) NOT NULL DEFAULT '1',
 
 
   PRIMARY KEY (`ID_USER`),
 
   PRIMARY KEY (`ID_USER`),
 
   UNIQUE KEY `users_namedomain` (`ID_DOMAIN`,`username`),
 
   UNIQUE KEY `users_namedomain` (`ID_DOMAIN`,`username`),
Line 43: Line 40:
 
  GRANT SELECT ON `mail`.`mail_domains` TO 'vmreader'@'localhost';
 
  GRANT SELECT ON `mail`.`mail_domains` TO 'vmreader'@'localhost';
 
  GRANT SELECT ON `mail`.`mail_users` TO 'vmreader'@'localhost';
 
  GRANT SELECT ON `mail`.`mail_users` TO 'vmreader'@'localhost';
 +
 +
== Usage ==
 +
 +
I should probably setup a web interface script for this, but the vast majority of my addresses are semi-automated abuse/postmaster/webmaster addresses.
 +
 +
# Insert mailing domains as desired. If 'active' is false, effectively turns the domain off for most purposes in MTA/MDA configuration presented.
 +
# Inserting users is somewhat more complex
 +
## mailpass must be in a format recognizable by dovecot (from dovecotpw)
 +
## isactive - enables or disables the account entirely
 +
## username should be valid, and not include the recipient_delimiter specified in Postfix
 +
# Alias management
 +
## The configuration we specify later automatically considers characters from the first recipient_delimiter on to be valid aliases, and no configuration for these is required.
 +
## The unique key allows us to specify an alias as belonging to multiple users. All such users will receive email on that alias.
 +
## alias_ext is appended to the address given by the specified user id, for their own folder management convenience.
 +
 +
{{Bottom Buster}}

Latest revision as of 03:58, 24 January 2021

This is a suggested table layout for a Postfix + Dovecot + MySQL setup.

The Tables

CREATE DATABASE mail;
USE mail;
GRANT USAGE ON *.* TO 'vmreader'@'localhost' IDENTIFIED BY 'passwordhere';

CREATE TABLE `mail_domains` (
  `ID_DOMAIN` smallint(6) NOT NULL AUTO_INCREMENT,
  `domain_name` varbinary(126) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID_DOMAIN`),
  UNIQUE KEY `domain_name` (`domain_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `mail_users` (
  `ID_USER` int(11) NOT NULL AUTO_INCREMENT,
  `ID_DOMAIN` smallint(6) NOT NULL,
  `username` varbinary(63) NOT NULL,
  `mailpass` varbinary(126) NOT NULL DEFAULT 'defaultmailpasswordhashhere',
  `isactive` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID_USER`),
  UNIQUE KEY `users_namedomain` (`ID_DOMAIN`,`username`),
  CONSTRAINT `users_domainfk` FOREIGN KEY (`ID_DOMAIN`) REFERENCES `mail_domains` (`ID_DOMAIN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `mail_aliases` (
  `ID_ALIAS` int(11) NOT NULL AUTO_INCREMENT,
  `ID_USER` int(11) NOT NULL,
  `alias_ext` varbinary(126) NOT NULL DEFAULT ,
  `alias_local` varbinary(126) NOT NULL,
  `ID_DOMAIN` smallint(6) NOT NULL,
  PRIMARY KEY (`ID_ALIAS`),
  UNIQUE KEY `alias_unique` (`alias_local`,`ID_DOMAIN`,`ID_USER`),
  CONSTRAINT `alias_domainfk` FOREIGN KEY (`ID_DOMAIN`) REFERENCES `mail_domains` (`ID_DOMAIN`),
  CONSTRAINT `alias_userfk` FOREIGN KEY (`ID_USER`) REFERENCES `mail_users` (`ID_USER`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

GRANT SELECT ON `mail`.`mail_aliases` TO 'vmreader'@'localhost';
GRANT SELECT ON `mail`.`mail_domains` TO 'vmreader'@'localhost';
GRANT SELECT ON `mail`.`mail_users` TO 'vmreader'@'localhost';

Usage

I should probably setup a web interface script for this, but the vast majority of my addresses are semi-automated abuse/postmaster/webmaster addresses.

  1. Insert mailing domains as desired. If 'active' is false, effectively turns the domain off for most purposes in MTA/MDA configuration presented.
  2. Inserting users is somewhat more complex
    1. mailpass must be in a format recognizable by dovecot (from dovecotpw)
    2. isactive - enables or disables the account entirely
    3. username should be valid, and not include the recipient_delimiter specified in Postfix
  3. Alias management
    1. The configuration we specify later automatically considers characters from the first recipient_delimiter on to be valid aliases, and no configuration for these is required.
    2. The unique key allows us to specify an alias as belonging to multiple users. All such users will receive email on that alias.
    3. alias_ext is appended to the address given by the specified user id, for their own folder management convenience.
Debian Buster
Initial: Designing a Server - IPMI Installation - Second Steps

Core: Network - OpenSSH - Packages - sysctl.conf - Iptables - Security - User Management

Stack: Unbound - MariaDB - Nginx - PHP (7)

Mail

Mail Setup - Mail Tables - OpenDKIM - Mail and DNS - Spamassassin - ClamAV - Postfix - Dovecot - Reputation Management

Logs and Security: RKHunter - Logging - Logcheck - Backups - AppArmor
Retrieved from "http://hexwiki.com/w/index.php?title=Mail_Tables_(MySQL)&oldid=435"