On Wed, Apr 17, 2002 at 05:53:42PM +0200, Arnaud Pignard wrote: > I would like to know if somebody can share me his MYSQL database use > with Courier. > > I would like to know how much complex we can design a database with > Courier (with Postfix). I haven't look in all documentation but it's > seems that's we can't use very flexible database format.
Please excuse my lack of wrapped lines. Hopefully it will make this post easier to read via mailing list archives if they exist. Postfix configuration: parent_domain_matches_subdomains = relay_domains = $mydestination, $transport_maps setgid_group = maildrop smtpd_recipient_restrictions = check_client_access mysql:/usr/local/etc/postfix/pop3_relay.cf, reject transport_maps = mysql:/usr/local/etc/postfix/transport.cf virtual_gid_maps = static:1002 virtual_mailbox_base = / virtual_mailbox_maps = mysql:/usr/local/etc/postfix/aliases.cf virtual_maps = mysql:/usr/local/etc/postfix/remote_aliases.cf aliases.cf: user = user password = password dbname = dbname table = aliases select_field = maildir where_field = alias hosts = localhost remote_aliases.cf: user = user password = password dbname = dbname table = remote_aliases select_field = rcpt where_field = alias hosts = localhost transport.cf: user = user password = password dbname = dbname table = transport select_field = transport where_field = domain hosts = localhost pop3_relay.cf user = user password = password dbname = dbname table = aliases select_field = lastlogin where_field = lastip additional_conditions = AND (lastlogin> (UNIX_TIMESTAMP()-1800)) hosts = localhost MySQL tables: mysql> select * from aliases; +------+---------------------------+--------------------------------------------------+----------+------------+-----------+ | id | alias | maildir || password | lastlogin | lastip | +------+---------------------------+--------------------------------------------------+----------+------------+-----------+ | 2000 | [EMAIL PROTECTED] | /virtual/2000/onevirtualdomain.tld/john/Maildir/ || password | 0 | | | 2001 | [EMAIL PROTECTED] | /virtual/2001/twovirtualdomain.tld/john/Maildir/ || password | 1018757975 | 127.0.0.1 | +------+---------------------------+--------------------------------------------------+----------+------------+-----------+ mysql> describe aliases; +-----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+-------+ | id | int(6) | YES | | NULL | | | alias | varchar(255) | | PRI | | | | maildir | varchar(255) | YES | | NULL | | | password | varchar(20) | | | | | | lastlogin | int(10) unsigned | | | 0 | | | lastip | varchar(15) | | MUL | | | +-----------+------------------+------+-----+---------+-------+ mysql> select * from remote_aliases; +--------------------------+--------------------------------+ | alias | rcpt | +--------------------------+--------------------------------+ | [EMAIL PROTECTED] | [EMAIL PROTECTED],[EMAIL PROTECTED] | | @twovirtualdomain.tld | [EMAIL PROTECTED] | +--------------------------+--------------------------------+ mysql> describe remote_aliases; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | alias | varchar(255) | | PRI | | | | rcpt | varchar(255) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ mysql> select * from transport; +----------------------+-----------+ | domain | transport | +----------------------+-----------+ | onevirtualdomain.tld | virtual: | | twovirtualdomain.tld | virtual: | +----------------------+-----------+ mysql> describe transport; +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | domain | varchar(255) | | PRI | | | | transport | varchar(8) | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+ Courier's MySQL configuration: --- authmysqlrc.dist Sat Apr 13 23:11:42 2002 +++ authmysqlrc Sat Apr 13 23:18:05 2002 @@ -178,7 +178,10 @@ # WHERE popbox.local_part = '$(local_part)' \ # AND popbox.domain_name = '$(domain)' \ # AND popbox.domain_name = domain.domain_name -# + +MYSQL_SELECT_CLAUSE SELECT alias, '', password , 1002, 1003, maildir , maildir, +'', 'Full Name' \ + FROM aliases WHERE alias = '$(local_part)@$(domain)' + ##NAME: MYSQL_CHPASS_CLAUSE:0 # # (EXPERIMENTAL) @@ -203,3 +206,5 @@ # WHERE local_part='$(local_part)' \ # AND domain_name='$(domain)' # + +MYSQL_UPDATE_CLAUSE UPDATE aliases SET lastip = '$(ip)', lastlogin = +UNIX_TIMESTAMP() WHERE alias = '$(local_part)@$(domain)' MYSQL_UPDATE_CLAUSE is something that Chris Sears and I added which will insert the remote IP address of an authenticated client into a MySQL table. If you look above at the Postfix configuration, you'll see that we allow relaying based on the MySQL map that we've made. If you're interested in a patch for this, e-mail me privately. -- John Kerbawy <[EMAIL PROTECTED]> _______________________________________________ courier-users mailing list [EMAIL PROTECTED] Unsubscribe: https://lists.sourceforge.net/lists/listinfo/courier-users
