Awesome tutorial! Works like a champ. I got everything under the sun running perfectly. So how the heck do I admin this thing? User creation, password changing, etc...Is there a program or web front end, or am I stuck working with phpmyadmin? I've seen this question posted on the tutorial, so I figured I'd throw it out here and get a public answer. It's my first time working with postfix, so be gentle... The1Rob
it's only for administration create, delete, alias (domain and users) If you use IMAP (webmail) you can try with HORDE/IMP http://www.horde.org + passwd app for horde or roundcube or squirrelmail with passwd options
You can't use Postfixadmin with this tutorial because the table structures are different. But the users can change their passwords with Squirrelmail (that's also described in that tutorial).
CREATE DATABASE emailbase; GRANT SELECT ON emailbase.* TO mailuser@localhost IDENTIFIED BY 'xxxxxxxxxxxxxxx'; GRANT SELECT, CREATE, INSERT, DELETE, UPDATE ON emailbase.* TO mailadmin@localhost IDENTIFIED BY 'xxxxxxxxxxxxxxxx'; USE emailbase; CREATE TABLE `admin` ( `username` varchar(255) character set latin1 NOT NULL default '', `password` varchar(255) character set latin1 NOT NULL default '', `created` datetime NOT NULL default '0000-00-00 00:00:00', `modified` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`username`), KEY `username` (`username`) ) ENGINE=InnoDB COMMENT='Virtual Admins'; CREATE TABLE `alias` ( `address` varchar(255) character set latin1 NOT NULL default '', `goto` text character set latin1 NOT NULL, `domain` varchar(255) character set latin1 NOT NULL default '', `created` datetime NOT NULL default '0000-00-00 00:00:00', `modified` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`address`), KEY `address` (`address`) ) ENGINE=InnoDB COMMENT='Virtual Aliases'; CREATE TABLE `alias_domain` ( `alias_domain` varchar(255) character set latin1 NOT NULL default '', `target_domain` varchar(255) character set latin1 NOT NULL default '', `created` datetime NOT NULL default '0000-00-00 00:00:00', `modified` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`alias_domain`), KEY `active` (`active`), KEY `target_domain` (`target_domain`) ) ENGINE=InnoDB COMMENT='Domain Aliases'; CREATE TABLE `config` ( `id` int(11) NOT NULL auto_increment, `name` varchar(20) character set latin1 NOT NULL default '', `value` varchar(20) character set latin1 NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB COMMENT='PostfixAdmin settings'; CREATE TABLE `domain` ( `domain` varchar(255) character set latin1 NOT NULL default '', `description` varchar(255) character set latin1 NOT NULL default '', `aliases` int(10) NOT NULL default '0', `mailboxes` int(10) NOT NULL default '0', `maxquota` bigint(10) NOT NULL default '0', `quota` bigint(20) NOT NULL default '0', `transport` varchar(255) character set latin1 default NULL, `backupmx` tinyint(1) NOT NULL default '0', `created` datetime NOT NULL default '0000-00-00 00:00:00', `modified` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`domain`), KEY `domain` (`domain`) ) ENGINE=InnoDB COMMENT='Virtual Domains'; CREATE TABLE `domain_admins` ( `username` varchar(255) character set latin1 NOT NULL default '', `domain` varchar(255) character set latin1 NOT NULL default '', `created` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL default '1', KEY `username` (`username`) ) ENGINE=InnoDB COMMENT='Domain Admins'; CREATE TABLE `fetchmail` ( `id` int(11) unsigned NOT NULL auto_increment, `mailbox` varchar(255) character set latin1 NOT NULL default '', `src_server` varchar(255) character set latin1 NOT NULL default '', `src_auth` enum('password','kerberos_v5','kerberos','kerberos_v4','gssapi','cram-md5','otp','ntlm','msn','ssh','any') character set latin1 default NULL, `src_user` varchar(255) character set latin1 NOT NULL default '', `src_password` varchar(255) character set latin1 NOT NULL default '', `src_folder` varchar(255) character set latin1 NOT NULL default '', `poll_time` int(11) unsigned NOT NULL default '10', `fetchall` tinyint(1) unsigned NOT NULL default '0', `keep` tinyint(1) unsigned NOT NULL default '0', `protocol` enum('POP3','IMAP','POP2','ETRN','AUTO') character set latin1 default NULL, `extra_options` text character set latin1, `returned_text` text character set latin1, `mda` varchar(255) character set latin1 NOT NULL default '', `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB COMMENT='Fetchmail'; CREATE TABLE `log` ( `timestamp` datetime NOT NULL default '0000-00-00 00:00:00', `username` varchar(255) character set latin1 NOT NULL default '', `domain` varchar(255) character set latin1 NOT NULL default '', `action` varchar(255) character set latin1 NOT NULL default '', `data` varchar(255) character set latin1 NOT NULL default '', KEY `timestamp` (`timestamp`) ) ENGINE=InnoDB COMMENT='Log'; CREATE TABLE `mailbox` ( `username` varchar(255) character set latin1 NOT NULL default '', `password` varchar(255) character set latin1 NOT NULL default '', `name` varchar(255) character set latin1 NOT NULL default '', `maildir` varchar(255) character set latin1 NOT NULL default '', `quota` bigint(20) NOT NULL default '-1', `domain` varchar(255) character set latin1 NOT NULL default '', `created` datetime NOT NULL default '0000-00-00 00:00:00', `modified` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`username`), KEY `username` (`username`) ) ENGINE=InnoDB COMMENT='Virtual Mailboxes'; CREATE TABLE `vacation` ( `email` varchar(255) character set latin1 NOT NULL default '', `subject` varchar(255) character set latin1 NOT NULL default '', `body` text NOT NULL, `cache` text NOT NULL, `domain` varchar(255) character set latin1 NOT NULL default '', `created` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(4) NOT NULL default '1', PRIMARY KEY (`email`), KEY `email` (`email`) ) ENGINE=InnoDB COMMENT='Virtual Vacation'; CREATE TABLE `vacation_notification` ( `on_vacation` varchar(170) NOT NULL, `notified` varchar(170) NOT NULL, `notified_at` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`on_vacation`,`notified`), CONSTRAINT `vacation_notification_ibfk_1` FOREIGN KEY (`on_vacation`) REFERENCES `vacation` (`email`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Virtual Vacation Notifications'; Here is dabastructure! It's not so different.
worth to invest some time? Falko: Really - kudos. Great tutorial. So I've decided to fudge it all up. I installed postfixadmin (2.2.1.1), then modified the /etc/postfix/mysql-virtual_*.cf files and authmysqlrc to reflect the table changes. I can't authenticate anything now, and I'm just slightly out of my element in tracking this down. I know your tutorial wants to use the built-in mysql encrypt command. I know postfixadmin gives some options that include md5, which I've read is different than mysql's md5. My mail logs show authdaemond burping out the following sql query when attempting to login and I'm not sure how it's coming up with it: SELECT username, password, "", 5000, 5000, maildir, CONCAT(SUBSTRING_INDEX(username,'@',-1),'/',SUBSTRING_INDEX(username,'@',1),'/'), quota, name, "" FROM mailbox WHERE username = "[email protected]" I'm no stranger to SQL but this confuses me. How does one select a null field? I presume I may have typoed in a file somewhere, but I've triple-checked everything and also done diffs against your original files and only the field and table names are different. Next: The error right after in the log is: supplied password 'password' does not match encrypted password 'c8cc8e10a6fb2fa4ax1fd6fa4a6a9c097'(not the real values, but the hash is the correct length) Where would I begin looking for this? authdaemon? postfix configs? Is there a preferred method of encryption that would allow the postfixadmin play nicely with your wonderful setup? I realize you probably cannot much (if any?) for the postfixadmin, but any insight you're able to point me toward would be greatly appreciated!
Awesome guide Falko, thanks a lot for it, I have now a server running smoothly. I tried to use webmin to admin the postfix server, but it didnt work because this configuration for this mail server is not default. But in the webmin from the Servers-Postfix-Users accounts there is an option to make it read from an specific user mailbox, i need to know if there is any tool to make it list every virtual user for each domain and list theirs email.
