Postfix、Courier、MySQL、SquirrelMailを使用した仮想ユーザーとドメイン (Ubuntu 13.10)

11 クォータ超過通知

すべてのメールアカウントがクォータを超えた場合に通知を受け取りたい場合は、ファイル /usr/local/sbin/quota_notify を作成します:

cd /usr/local/sbin/  
vi quota_notify

| #!/usr/bin/perl -w # Author <[email protected]> # # This script assumes that virtual_mailbox_base in defined # in postfix's main.cf file. This directory is assumed to contain # directories which themselves contain your virtual user's maildirs. # For example: # # -----------/ # | # | # home/vmail/domains/ # | | # | | # example.com/ foo.com/ # | # | # ----------------- # | | | # | | | # user1/ user2/ user3/ # | # | # maildirsize # use strict; my $POSTFIX_CF = "/etc/postfix/main.cf"; my $MAILPROG = "/usr/sbin/sendmail -t"; my $WARNPERCENT = 80; my @POSTMASTERS = ('[email protected]'); my $CONAME = 'My Company'; my $COADDR = '[email protected]'; my $SUADDR = '[email protected]'; my $MAIL_REPORT = 1; my $MAIL_WARNING = 1; #get virtual mailbox base from postfix config open(PCF, "< $POSTFIX_CF") or die $!; my $mboxBase; while () { next unless /virtual_mailbox_base\s*=\s*(.*)\s*/; $mboxBase = $1; } close(PCF); #assume one level of subdirectories for domain names my @domains; opendir(DIR, $mboxBase) or die $!; while (defined(my $name = readdir(DIR))) { next if $name =~ /^\.\.? next unless (-d "$mboxBase/$name"); push(@domains, $name); } closedir(DIR); #iterate through domains for username/maildirsize files my @users; chdir($mboxBase); foreach my $domain (@domains) { opendir(DIR, $domain) or die $!; while (defined(my $name = readdir(DIR))) { next if $name =~ /^\.\.? next unless (-d "$domain/$name"); push(@users, {"$name\@$domain" => "$mboxBase/$domain/$name"}); } } closedir(DIR); #get user quotas and percent used my (%lusers, $report); foreach my $href (@users) { foreach my $user (keys %$href) { my $quotafile = "$href->{$user}/maildirsize"; next unless (-f $quotafile); open(QF, "< $quotafile") or die $!; my ($firstln, $quota, $used); while () { my $line = $_; if (! $firstln) { $firstln = 1; die "Error: corrupt quotafile $quotafile" unless ($line =~ /^(\\d+)S/); $quota = $1; last if (! $quota); next; } die "Error: corrupt quotafile $quotafile" unless ($line =~ /\s*(-?\d+)/); $used += $1; } close(QF); next if (! $used); my $percent = int($used / $quota * 100); $lusers{$user} = $percent unless not $percent; } } #send a report to the postmasters if ($MAIL_REPORT) { open(MAIL, "| $MAILPROG"); select(MAIL); map {print "To: $_\n"} @POSTMASTERS; print "From: $COADDR\n"; print "Subject: Daily Quota Report.\n"; print "DAILY QUOTA REPORT:\n\n"; print "----------------------------------------------\n"; print "| % USAGE | ACCOUNT NAME |\n"; print "----------------------------------------------\n"; foreach my $luser ( sort { $lusers{$b} <=> $lusers{$a} } keys %lusers ) { printf("| %3d | %32s |\n", $lusers{$luser}, $luser); print "---------------------------------------------\n"; } print "\n--\n"; print "$CONAME\n"; close(MAIL); } #email a warning to people over quota if ($MAIL_WARNING) { foreach my $luser (keys (%lusers)) { next unless $lusers{$luser} >= $WARNPERCENT; # skip those under quota open(MAIL, "| $MAILPROG"); select(MAIL); print "To: $luser\n"; map {print "BCC: $_\n"} @POSTMASTERS; print "From: $SUADDR\n"; print "Subject: WARNING: Your mailbox is $lusers{$luser}% full.\n"; print "Reply-to: $SUADDR\n"; print "Your mailbox: $luser is $lusers{$luser}% full.\n\n"; print "Once your e-mail box has exceeded your monthly storage quota\n"; print "your monthly billing will be automatically adjusted.\n"; print "Please consider deleting e-mail and emptying your trash folder to clear some space.\n\n"; print "Contact <$SUADDR> for further assistance.\n\n"; print "Thank You.\n\n"; print "--\n"; print "$CONAME\n"; close(MAIL); } } |

変数を調整することを忘れないでください（特に [email protected] のメールアドレス）。

ファイルを実行可能にする必要があります:

chmod 755 quota_notify

実行します

crontab -e

そのスクリプトのためのcronジョブを作成します:

| 0 0 * * * /usr/local/sbin/quota_notify &> /dev/null |

12 Postfixのテスト

PostfixがSMTP-AUTHおよびTLSの準備ができているか確認するには、次のコマンドを実行します

telnet localhost 25

Postfixメールサーバーへの接続が確立された後、次のコマンドを入力します

ehlo localhost

次の行が表示される場合

250-STARTTLS

および

250-AUTH PLAIN LOGIN

すべてが正常です:

root@server1:/usr/local/sbin# telnet localhost 25
Trying 127.0.0.1…
Connected to localhost.localdomain.
Escape character is ‘^]’.
220 server1.example.com ESMTP Postfix (Ubuntu)
<– ehlo localhost
250-server1.example.com
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-STARTTLS
250-AUTH PLAIN LOGIN
250-AUTH=PLAIN LOGIN
250-ENHANCEDSTATUSCODES
250-8BITMIME
250 DSN
<– quit
221 2.0.0 Bye
Connection closed by foreign host.
root@server1:/usr/local/sbin#

次のコマンドを入力します

quit

システムシェルに戻ります。

13 データベースの人口とテスト

データベースを人口するには、MySQLシェルを使用できます:

mysql -u root -p

USE mail;

少なくとも domains および users テーブルにエントリを作成する必要があります:

INSERT INTO `domains` (`domain`) VALUES ('example.com');  
INSERT INTO `users` (`email`, `password`, `quota`) VALUES ('[email protected]', ENCRYPT('secret'), 10485760);

（パスワードを暗号化するために、2番目のINSERT文でENCRYPT構文を使用することに注意してください！）

他の2つのテーブルにエントリを作成したい場合は、次のようになります:

INSERT INTO `forwardings` (`source`, `destination`) VALUES ('[email protected]', '[email protected]');  
INSERT INTO `transport` (`domain`, `transport`) VALUES ('example.com', 'smtp:mail.example.com');

MySQLシェルを終了するには、次のコマンドを入力します

quit;

ほとんどの人にとって、MySQLのグラフィカルフロントエンドがあると便利です。したがって、phpMyAdminを使用して mail データベースを管理することもできます（この例では http://192.168.0.100/phpmyadmin/ または http://server1.example.com/phpmyadmin/）。再度、ユーザーを作成する際には、パスワードを暗号化するためにENCRYPT関数を使用することを確認してください:

ドメインとユーザーテーブルについては、さらに説明する必要はないと思います。

forwardingsテーブルには、次のようなエントリが含まれる場合があります:

*source*	*destination*
[email protected]	[email protected]	[email protected] のメールを [email protected] にリダイレクトします
@example.com	[email protected]	[email protected] のためのキャッチオールアカウントを作成します。 example.com へのすべてのメールは [email protected] に届きますが、ユーザーテーブルに存在するものを除きます（つまり、[email protected] がユーザーテーブルに存在する場合、[email protected] へのメールは依然として [email protected] に届きます）。
@example.com	@anotherdomain.tld	これは、example.com へのすべてのメールを anotherdomain.tld の同じユーザーにリダイレクトします。たとえば、[email protected] へのメールは [email protected] に転送されます。
[email protected]	[email protected], [email protected]	[email protected] のメールを2つ以上のメールアドレスに転送します。宛先にリストされたすべてのメールアドレスがメールのコピーを受け取ります。

transportテーブルには、次のようなエントリが含まれる場合があります:

*domain*	*transport*
example.com	:	example.com のメールをローカルに配信します。これは、このレコードがこのテーブルに存在しないかのようです。
example.com	smtp:mail.anotherdomain.tld	example.com のすべてのメールを smtp を介して mail.anotherdomain.com サーバーに配信します。
example.com	smtp:mail.anotherdomain.tld:2025	example.com のすべてのメールを smtp を介して mail.anotherdomain.com サーバーに配信しますが、ポート2025を使用します。ポート25はsmtpのデフォルトポートです。

詳細については、

man transport

を参照してください。

transportテーブルのエントリの順序が重要であることに注意してください！エントリは上から下へと順に処理されます。

重要: Postfixはトランスポートのキャッシュメカニズムを使用しているため、トランスポートテーブルの変更が反映されるまでに時間がかかる場合があります。変更を即座に反映させたい場合は、

postfix reload

を実行してください。

14 Maildir作成のためのウェルカムメールを送信

新しいメールアカウントを作成し、そのアカウントからメールを取得しようとすると（POP3/IMAPで）、Maildirが存在しないというエラーメッセージが表示される可能性があります。Maildirは、新しいアカウントに最初のメールが届いたときに自動的に作成されます。したがって、新しいアカウントにウェルカムメールを送信することは良いアイデアです。

まず、mailxパッケージをインストールします:

apt-get install mailutils

[メール protected] にウェルカムメールを送信するには、次のようにします:

mailx [email protected]

件名を求められます。件名を入力し（例: Welcome）、ENTERを押し、次の行にメッセージを入力します。メッセージが完了したら、再度ENTERを押して新しい行に移動し、CTRL+Dを押します。メールをCCしたくない場合は、再度ENTERを押します:

root@server1:/usr/local/sbin# mailx [email protected]
Cc: <– ENTER
Subject: Welcome <– ENTER
Welcome! Have fun with your new mail account. <– ENTER
<– CTRL+D
root@server1:/usr/local/sbin#

 注意: 同じアドレスに対してトランスポートとメールボックスを追加しないでください。

15 SquirrelMailのインストール

SquirrelMailは、ユーザーがブラウザでメールを送受信できるWebメールインターフェースです。この章では、SquirrelMailをインストールし、ユーザーがSquirrelMailインターフェースからメールアカウントのパスワードを変更できるように設定する方法を示します。

SquirrelMailをインストールするには、次のコマンドを実行します:

apt-get install squirrelmail squirrelmail-compatibility php-pear php-db

次に、SquirrelMailパッケージに付属のApache設定を /etc/apache2/conf-available ディレクトリにシンボリックリンクし、Apacheを再起動します:

ln -s /etc/squirrelmail/apache.conf /etc/apache2/conf-available/squirrelmail.conf  
/etc/init.d/apache2 restart

SquirrelMailにはいくつかのプラグインがプリインストールされていますが、残念ながら、どれもMySQLデータベース内のメールパスワードを変更することはできません。しかし、手動でインストールできるChange SQL Passwordプラグインがあります:

cd /usr/share/squirrelmail/plugins  
wget http://www.squirrelmail.org/plugins/change_sqlpass-3.3-1.2.tar.gz  
tar xvfz change_sqlpass-3.3-1.2.tar.gz  
cd change_sqlpass  
cp config.php.sample config.php

次に、config.phpを編集し、設定を調整する必要があります。次のように、$csp_dsn、$lookup_password_query、$password_update_queries、$password_encryption、$csp_salt_static、および$csp_delimiter変数を調整し、$csp_salt_queryをコメントアウトします:

vi config.php

| [...] $csp_dsn = 'mysql://mail_admin:mail_admin_password@localhost/mail'; [...] $lookup_password_query = 'SELECT count(*) FROM users WHERE email = "%1" AND password = %4'; [...] $password_update_queries = array('UPDATE users SET password = %4 WHERE email = "%1"'); [...] $password_encryption = 'MYSQLENCRYPT'; [...] $csp_salt_static = 'LEFT(password, 2)'; [...] //$csp_salt_query = 'SELECT salt FROM users WHERE username = "%1"'; [...] $csp_delimiter = '@'; [...] |

完全なファイルは次のようになります:

| * 2002-2005 Paul Lesneiwski <[email protected]> * This program is licensed under GPL. See COPYING for details * * @package plugins * @subpackage Change SQL Password * */ // Global Variables, don't touch these unless you want to break the plugin // global $csp_dsn, $password_update_queries, $lookup_password_query, $force_change_password_check_query, $password_encryption, $csp_salt_query, $csp_salt_static, $csp_secure_port, $csp_non_standard_http_port, $csp_delimiter, $csp_debug, $min_password_length, $max_password_length, $include_digit_in_password, $include_uppercase_letter_in_password, $include_lowercase_letter_in_password, $include_nonalphanumeric_in_password; // csp_dsn // // Theoretically, any SQL database supported by Pear should be supported // here. The DSN (data source name) must contain the information needed // to connect to your database backend. A MySQL example is included below. // For more details about DSN syntax and list of supported database types, // please see: // http://pear.php.net/manual/en/package.database.db.intro-dsn.php // //$csp_dsn = 'mysql://user:password@localhost/email_users'; $csp_dsn = 'mysql://mail_admin:mail_admin_password@localhost/mail'; // lookup_password_query // // This plugin will always verify the user's old password // against their login password, but an extra check can also // be done against the database for more security if you // desire. If you do not need the extra password check, // make sure this setting is empty. // // This is a query that returns a positive value if a user // and password pair are found in the database. // // This query should return one value (one row, one column), the // value being ideally a one or a zero, simply indicating that // the user/password pair does in fact exist in the database. // // %1 in this query will be replaced with the full username // (including domain), such as "[email protected]" // %2 in this query will be replaced with the username (without // any domain portion), such as "jose" // %3 in this query will be replaced with the domain name, // such as "example.com" // %4 in this query will be replaced with the current (old) // password in whatever encryption format is needed per other // plugin configuration settings (Note that the syntax of // the password will be provided depending on your encryption // choices, so you NEVER need to provide quotes around this // value in the query here.) // %5 in this query will be replaced with the current (old) // password in unencrypted plain text. If you do not use any // password encryption, %4 and %5 will be the same values, // except %4 will have double quotes around it and %5 will not. // //$lookup_password_query = ''; // TERRIBLE SECURITY: $lookup_password_query = 'SELECT count(*) FROM users WHERE username = "%1" AND plain_password = "%5"'; //$lookup_password_query = 'SELECT count(*) FROM users WHERE username = "%1" AND crypt_password = %4'; $lookup_password_query = 'SELECT count(*) FROM users WHERE email = "%1" AND password = %4'; // password_update_queries // // An array of SQL queries that will all be executed // whenever a password change attempt is made. // // Any number of queries may be included here. // The queries will be executed in the order given here. // // %1 in all queries will be replaced with the full username // (including domain), such as "[email protected]" // %2 in all queries will be replaced with the username (without // any domain portion), such as "jose" // %3 in all queries will be replaced with the domain name, // such as "example.com" // %4 in all queries will be replaced with the new password // in whatever encryption format is needed per other // plugin configuration settings (Note that the syntax of // the password will be provided depending on your // encryption choices, so you NEVER need to provide quotes // around this value in the queries here.) // %5 in all queries will be replaced with the new password // in unencrypted plain text - BEWARE! If you do not use // any password encryption, %4 and %5 will be the same // values, except %4 will have double quotes around it // and %5 will not. // // $password_update_queries = array( // 'UPDATE users SET crypt_password = %4 WHERE username = "%1"', // 'UPDATE user_flags SET force_change_pwd = 0 WHERE username = "%1"', // 'UPDATE users SET crypt_password = %4, force_change_pwd = 0 WHERE username = "%1"', // ); $password_update_queries = array('UPDATE users SET password = %4 WHERE email = "%1"'); // force_change_password_check_query // // A query that checks for a flag that indicates if a user // should be forced to change their password. This query // should return one value (one row, one column) which is // zero if the user does NOT need to change their password, // or one if the user should be forced to change it now. // // This setting should be an empty string if you do not wish // to enable this functionality. // // %1 in this query will be replaced with the full username // (including domain), such as "[email protected]" // %2 in this query will be replaced with the username (without // any domain portion), such as "jose" // %3 in this query will be replaced with the domain name, // such as "example.com" // //$force_change_password_check_query = 'SELECT IF(force_change_pwd = "yes", 1, 0) FROM users WHERE username = "%1"'; //$force_change_password_check_query = 'SELECT force_change_pwd FROM users WHERE username = "%1"'; $force_change_password_check_query = ''; // password_encryption // // What encryption method do you use to store passwords // in your database? Please use one of the following, // exactly as you see it: // // NONE Passwords are stored as plain text only // MYSQLPWD Passwords are stored using the MySQL password() function // MYSQLENCRYPT Passwords are stored using the MySQL encrypt() function // PHPCRYPT Passwords are stored using the PHP crypt() function // MD5CRYPT Passwords are stored using encrypted MD5 algorithm // MD5 Passwords are stored as MD5 hash // //$password_encryption = 'MYSQLPWD'; $password_encryption = 'MYSQLENCRYPT'; // csp_salt_query // csp_salt_static // // Encryption types that need a salt need to know where to get // that salt. If you have a constant, known salt value, you // should define it in $csp_salt_static. Otherwise, leave that // value empty and define a value for the $csp_salt_query. // // Leave both values empty if you do not need (or use) salts // to encrypt your passwords. // // The query should return one value (one row, one column) which // is the salt value for the current user's password. This // query is ignored if $csp_salt_static is anything but empty. // // %1 in this query will be replaced with the full username // (including domain), such as "[email protected]" // %2 in this query will be replaced with the username (without // any domain portion), such as "jose" // %3 in this query will be replaced with the domain name, // such as "example.com" // //$csp_salt_static = 'LEFT(crypt_password, 2)'; //$csp_salt_static = '"a4"'; // use this format with MYSQLENCRYPT //$csp_salt_static = '$2$blowsomefish$'; // use this format with PHPCRYPT //$csp_salt_static = ''; $csp_salt_static = 'LEFT(password, 2)'; //$csp_salt_query = 'SELECT SUBSTRING_INDEX(crypt_password, '$', 1) FROM users WHERE username = "%1"'; //$csp_salt_query = 'SELECT SUBSTRING(crypt_password, (LENGTH(SUBSTRING_INDEX(crypt_password, '$', 2)) + 2)) FROM users WHERE username = "%1"'; //$csp_salt_query = 'SELECT salt FROM users WHERE username = "%1"'; //$csp_salt_query = ''; // csp_secure_port // // You may ensure that SSL encryption is used during password // change by setting this to the port that your HTTPS is served // on (443 is typical). Set to zero if you do not wish to force // an HTTPS connection when users are changing their passwords. // // You may override this value for certain domains, users, or // service levels through the Virtual Host Login (vlogin) plugin // by setting a value(s) for $vlogin_csp_secure_port in the vlogin // configuration. // $csp_secure_port = 0; //$csp_secure_port = 443; // csp_non_standard_http_port // // If you serve standard HTTP web requests on a non-standard // port (anything other than port 80), you should specify that // port number here. Set to zero otherwise. // // You may override this value for certain domains, users, or // service levels through the Virtual Host Login (vlogin) plugin // by setting a value(s) for $vlogin_csp_non_standard_http_port // in the vlogin configuration. // //$csp_non_standard_http_port = 8080; $csp_non_standard_http_port = 0; // min_password_length // max_password_length // include_digit_in_password // include_uppercase_letter_in_password // include_lowercase_letter_in_password // include_nonalphanumeric_in_password // // You can set the minimum and maximum password lengths that // you accept or leave those settings as zero to indicate that // no limit should be applied. // // Turn on any of the other settings here to check that the // new password contains at least one digit, upper case letter, // lower case letter and/or one non-alphanumeric character. // $min_password_length = 6; $max_password_length = 0; $include_digit_in_password = 0; $include_uppercase_letter_in_password = 0; $include_lowercase_letter_in_password = 0; $include_nonalphanumeric_in_password = 0; // csp_delimiter // // if your system has usernames with something other than // an "@" sign separating the user and domain portion, // specify that character here // //$csp_delimiter = '|'; $csp_delimiter = '@'; // debug mode // $csp_debug = 0; ?> |

次に、SquirrelMailの設定に移動し、SquirrelMailにCourierをPOP3およびIMAPサーバーとして使用することを伝え、Change SQL PasswordおよびCompatibilityプラグインを有効にします:

/usr/sbin/squirrelmail-configure

次のメニューが表示されます。指示に従ってナビゲートします:

SquirrelMail Configuration : Read: config.php (1.4.0)

Main Menu –
1.  Organization Preferences
2.  Server Settings
3.  Folder Defaults
4.  General Options
5.  Themes
6.  Address Books
7.  Message of the Day (MOTD)
8.  Plugins
9.  Database
10. Languages

D. Set pre-defined settings for specific IMAP servers

C   Turn color on
S   Save data
Q   Quit

Command >> <– D

SquirrelMail Configuration : Read: config.php

While we have been building SquirrelMail, we have discovered some
preferences that work better with some servers that don’t work so
well with others. If you select your IMAP server, this option will
set some pre-defined settings for that server.

Please note that you will still need to go through and make sure
everything is correct. This does not change everything. There are
only a few settings that this will change.

Please select your IMAP server:
bincimap    = Binc IMAP server
courier     = Courier IMAP server
cyrus       = Cyrus IMAP server
dovecot     = Dovecot Secure IMAP server
exchange    = Microsoft Exchange IMAP server
hmailserver = hMailServer
macosx      = Mac OS X Mailserver
mercury32   = Mercury/32
uw          = University of Washington’s IMAP server
gmail       = IMAP access to Google mail (Gmail) accounts

quit = Do not change anything
Command >> <– courier

imap_server_type = courier
default_folder_prefix = INBOX.
trash_folder = Trash
sent_folder = Sent
draft_folder = Drafts
show_prefix_option = false
default_sub_of_inbox = false
show_contain_subfolders_option = false
optional_delimiter = .
delete_folder = true

Press enter to continue… <– press ENTER

SquirrelMail Configuration : Read: config.php (1.4.0)

D. Set pre-defined settings for specific IMAP servers

C   Turn color on
S   Save data
Q   Quit

Command >> <– 8

SquirrelMail Configuration : Read: config.php (1.4.0)

Plugins
Installed Plugins
1. view_as_html

Available Plugins:
2. administrator
3. bug_report
4. calendar
5. change_sqlpass
6. compatibility
7. delete_move_next
8. demo
9. filters
10. fortune
11. info
12. listcommands
13. mail_fetch
14. message_details
15. newmail
16. sent_subfolders
17. spamcop
18. squirrelspell
19. test
20. translate