その結果、バンドルproftpd + mod_sql + mod_sql_passwdをmd5 + saltの形式でパスワードを保存するように構成し、ユーザーパスワードを追加、削除、変更するための3つのスクリプトを作成しました。
PostgreSQLを別のサーバーのどこかで使用し、MySQLをどこかで使用しているという事実により、両方のDBMSの設定の説明を投稿します。
サーバーはOS ALT Linux Sisyphusを実行しています。
ステップ1.必要なものをすべてインストールする
動作するには、proftpdパッケージ自体、proftpd-mod_sql、proftpd-mod_sql_passwd、proftpd-mod_sql_mysql / postgresのモジュール、およびPerlモジュールが必要です。
必要なパッケージをインストールする
# apt-get update # apt-get install proftpd proftpd_mod_sql proftpd-mod_sql_passwd proftpd-mod_sql_mysql proftpd-mod_sql_postgres # apt-get install perl-Crypt-PasswdMD5 perl-Config-Simple perl-DBI perl-DBD-Pg perl-DBD-mysql
ステップ2.データベースの準備
この記事では、DBUSERユーザー、DBPASSWDパスワード、およびDBNAMEを使用して、DBHOSTホスト上のデータベースに接続します。
MySQLのDB構造
CREATE TABLE `log_failed_logins` ( `unic_id` int(32) NOT NULL AUTO_INCREMENT, `datetime` varchar(30) NOT NULL, `user_name` varchar(64) NOT NULL, `client_name` varchar(127) NOT NULL, `client_IP` varchar(15) NOT NULL, PRIMARY KEY (`unic_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL, `password` varchar(50) DEFAULT NULL, `salt` varchar(50) DEFAULT NULL, `groupname` varchar(24) NOT NULL, `uid` int(10) unsigned NOT NULL, `gid` int(10) unsigned NOT NULL, `homedir` varchar(70) NOT NULL, `shell` varchar(20) DEFAULT NULL, `last_login` varchar(30) DEFAULT NULL, `login_count` int(10) DEFAULT NULL, `last_error_login` varchar(30) DEFAULT NULL, `login_error_count` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `xfer_errors` ( `unic_id` int(32) NOT NULL AUTO_INCREMENT, `datetime` varchar(30) NOT NULL, `user_name` varchar(64) NOT NULL, `file_and_path` tinytext NOT NULL, `client_name` varchar(127) NOT NULL, `client_IP` varchar(15) NOT NULL, `client_command` varchar(5) NOT NULL, PRIMARY KEY (`unic_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `xfer_table` ( `unic_id` int(32) NOT NULL AUTO_INCREMENT, `datetime` varchar(30) NOT NULL, `user_name` varchar(64) NOT NULL, `file_and_path` tinytext NOT NULL, `bytes` int(15) NOT NULL DEFAULT '0', `client_name` varchar(127) NOT NULL, `client_IP` varchar(15) NOT NULL, `client_command` varchar(5) NOT NULL, `send_time` varchar(9) NOT NULL DEFAULT '0', PRIMARY KEY (`unic_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
PostgreSQLのDB構造
CREATE TABLE users ( id integer NOT NULL, username character varying(20), password character varying(50), salt character varying(50), groupname character varying(24), uid integer, gid integer, homedir character varying(70), shell character varying(20), last_login character varying(30), login_count integer, last_error_login character varying(30), login_error_count integer ); ALTER TABLE users OWNER TO <b>DBUSER</b>; CREATE SEQUENCE users_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE users_id_seq OWNER TO <b>DBUSER</b>; CREATE TABLE log_failed_logins ( id integer DEFAULT nextval('log_failed_logins_id_seq'::regclass) NOT NULL, datetime character varying(30), user_name character varying(64), client_name character varying(127), client_ip character varying(15) ); ALTER TABLE log_failed_logins OWNER TO <b>DBUSER</b>; CREATE SEQUENCE log_failed_logins_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE log_failed_logins_id_seq OWNER TO <b>DBUSER</b>; CREATE TABLE xfer_errors ( id integer DEFAULT nextval('xfer_errors_id_seq'::regclass) NOT NULL, datetime character varying(30), user_name character varying(64), file_and_path text, client_name character varying(127), client_ip character varying(15), client_command character varying(5) ); ALTER TABLE xfer_errors OWNER TO <b>DBUSER</b>; CREATE SEQUENCE xfer_errors_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE xfer_errors_id_seq OWNER TO <b>DBUSER</b>; CREATE TABLE xfer_table ( id integer DEFAULT nextval('xfer_table_id_seq'::regclass) NOT NULL, datetime character varying(30), user_name character varying(64), file_and_path text, bytes integer, client_name character varying(127), client_ip character varying(15), client_command character varying(5), send_time character varying(9) ); ALTER TABLE xfer_table OWNER TO <b>DBUSER</b>; CREATE SEQUENCE xfer_table_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE xfer_table_id_seq OWNER TO <b>DBUSER</b>; </spoiler> <spoiler title=" MySQL"> DROP TABLE IF EXISTS `log_failed_logins`; CREATE TABLE `log_failed_logins` ( `unic_id` int(32) NOT NULL AUTO_INCREMENT, `datetime` varchar(30) NOT NULL, `user_name` varchar(64) NOT NULL, `client_name` varchar(127) NOT NULL, `client_IP` varchar(15) NOT NULL, PRIMARY KEY (`unic_id`) ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL, `password` varchar(50) DEFAULT NULL, `salt` varchar(50) DEFAULT NULL, `groupname` varchar(24) NOT NULL, `uid` int(10) unsigned NOT NULL, `gid` int(10) unsigned NOT NULL, `homedir` varchar(70) NOT NULL, `shell` varchar(20) DEFAULT NULL, `last_login` varchar(30) DEFAULT NULL, `login_count` int(10) DEFAULT NULL, `last_error_login` varchar(30) DEFAULT NULL, `login_error_count` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `xfer_errors`; CREATE TABLE `xfer_errors` ( `unic_id` int(32) NOT NULL AUTO_INCREMENT, `datetime` varchar(30) NOT NULL, `user_name` varchar(64) NOT NULL, `file_and_path` tinytext NOT NULL, `client_name` varchar(127) NOT NULL, `client_IP` varchar(15) NOT NULL, `client_command` varchar(5) NOT NULL, PRIMARY KEY (`unic_id`) ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `xfer_table`; CREATE TABLE `xfer_table` ( `unic_id` int(32) NOT NULL AUTO_INCREMENT, `datetime` varchar(30) NOT NULL, `user_name` varchar(64) NOT NULL, `file_and_path` tinytext NOT NULL, `bytes` int(15) NOT NULL DEFAULT '0', `client_name` varchar(127) NOT NULL, `client_IP` varchar(15) NOT NULL, `client_command` varchar(5) NOT NULL, `send_time` varchar(9) NOT NULL DEFAULT '0', PRIMARY KEY (`unic_id`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
ステップ3. proftpdの構成
/etc/proftpd.confに追加します
UseReverseDNS on AuthOrder mod_sql.c AuthPAM off # PAM <IfModule mod_dso.c> LoadModule mod_sql.c LoadModule mod_sql_mysql.c ( mysql) LoadModule mod_sql_postgres.c ( postgres) LoadModule mod_sql_passwd.c </IfModule> SQLPasswordEngine on SQLPasswordEncoding hex SQLPasswordOptions HashEncodeSalt SQLAuthTypes Crypt SQLAuthenticate users SQLConnectInfo DBUSER@DBHOST:DBPORT DBNAME DBPASSWD SQLUserInfo users username password uid gid homedir shell SQLMinUserUID 50 SQLMinUserGID 50 RequireValidShell off SQLNamedQuery get-user-salt SELECT "salt FROM users WHERE username = '%U'" SQLPasswordUserSalt sql:/get-user-salt Append
さらに、postgresとmysqlの構成は、SQLクエリの構文によって区別されます。
PostgreSQL
SQLLog PASS counter_login SQLNamedQuery counter_login UPDATE "\ last_login=date_trunc ( 'seconds' ,\ timestamp without time zone 'now' ),\ login_count=login_count+1 WHERE \ username='%u'" users SQLLog ERR_PASS counter_err SQLNamedQuery counter_err UPDATE "\ last_error_login=date_trunc ( 'seconds' ,\ timestamp without time zone\'now' ), \ login_error_count=login_error_count+1 WHERE \ username='%U'" users SQLLog ERR_PASS log_fails SQLNamedQuery log_fails INSERT "nextval('log_failed_logins_id_seq'::regclass), date_trunc ('seconds',timestamp without time zone 'now'),'%U','%h','%a'" log_failed_logins SQLLog DELE,RETR,STOR log_story_transfer SQLNamedQuery log_story_transfer INSERT "nextval('xfer_table_id_seq'::regclass), date_trunc ('seconds',timestamp without time zone 'now'),'%u', \ '%f','%b','%h','%a','%m', '%T'" xfer_table SQLLOG ERR_RETR,ERR_STOR,ERR_DELE,ERR_RMD,ERR_RNTO \ log_err_modify SQLNamedQuery log_err_modify INSERT "nextval('xfer_errors_id_seq'::regclass), date_trunc ('seconds',timestamp without time zone 'now'),'%u', \ '%f','%h','%a','%m'" xfer_errors
MySQL
SQLNamedQuery get-user-salt SELECT "salt FROM users WHERE username = '%U'" SQLPasswordUserSalt sql:/get-user-salt Append SQLLog PASS counter_login SQLNamedQuery counter_login UPDATE "\ last_login=now(),\ login_count=login_count+1 WHERE \ username='%u'" users SQLLog ERR_PASS counter_err SQLNamedQuery counter_err UPDATE "\ last_error_login=now(), \ login_error_count=login_error_count+1 WHERE \ username='%U'" users SQLLog ERR_PASS log_fails SQLNamedQuery log_fails INSERT "'', now(),'%U','%h','%a'" log_failed_logins SQLLog DELE,RETR,STOR log_story_transfer SQLNamedQuery log_story_transfer INSERT "'', now(),'%u', \ '%f', '%b', '%h', '%a', '%m', '%T'" xfer_table SQLLOG ERR_RETR,ERR_STOR,ERR_DELE,ERR_RMD,ERR_RNTO log_err_modify SQLNamedQuery log_err_modify INSERT "'', now(), '%u', '%f', '%h', '%a', '%m'" xfer_errors
ステップ4.管理するスクリプト
データベース内のユーザーを管理するために、3つの単純なperlスクリプトを作成しました：ftpadduser、ftpdeluserおよびftppasswd +それらの単一の構成で、それぞれに同じ変数を書き込まないようにします。
/etc/proftpd_sql.conf
# SQL Configuration # sql_type can be "mysql" or "postgres" ONLY! sql_type mysql sql_host DBHOST sql_user DBUSER sql_passwd DBPASSWD sql_db DBNAME # FTP Settings # default FTP directory ftp_dir /home/ftp # , ftp_groupname ftpadm ftp_uid 507 ftp_gid 507 ftp_shell /dev/null
ftpadduser
#!/usr/bin/perl -wl use strict; use Crypt::PasswdMD5 qw(unix_md5_crypt); use DBI; use DBD::mysql; use Config::Simple; my $cfg = new Config::Simple('/etc/proftpd_sql.conf'); my %Config = $cfg->vars(); my $dsn; if ($Config{sql_type} eq "mysql") { $dsn = "dbi:mysql:dbname=$Config{sql_db};host=$Config{sql_host};"; } elsif ($Config{sql_type} eq "postgres") { $dsn = "dbi:Pg:dbname=$Config{sql_db};host=$Config{sql_host};"; } else { die("Incorrect \$sql_type in config"); } if (@ARGV < 2) { print "Usage: $0 ftpuser password."; exit; } my $ftpuser = $ARGV[0]; my $dbh = DBI->connect($dsn, $Config{sql_user},$Config{sql_passwd}, {AutoCommit => 1}); my $ftp_user_exists = usercheck($ARGV[0]); if ($ftp_user_exists!=0) { print "$0: user $ARGV[0] already exits."; exit; } elsif (-e "$Config{ftp_dir}/$ftpuser" and -d "$Config{ftp_dir}/$ftpuser") { print "$0: directory $Config{ftp_dir}/$ftpuser/ exists, check the path."; exit; } else { mkdir("$Config{ftp_dir}/$ftpuser"); chown $Config{ftp_uid},$Config{ftp_gid}, "$Config{ftp_dir}/$ftpuser"; chmod 0700, "$Config{ftp_dir}/$ftpuser"; my $ftppass = $ARGV[1]; my $salt = gensalt(8); my $encrypted = unix_md5_crypt($ftppass, $salt); my $dbh_sql; if ($Config{sql_type} eq "mysql") { $dbh_sql = "INSERT INTO users SET username='$ftpuser', password='$encrypted', salt='$salt', groupname='$Config{ftp_groupname}', uid='$Config{ftp_uid}', gid='$Config{ftp_gid}', homedir='$Config{ftp_dir}/$ftpuser', shell='$Config{ftp_shell}', login_count=0, login_error_count=0"; } elsif ($Config{sql_type} eq "postgres") { $dbh_sql = "INSERT INTO users "; $dbh_sql .= "(id, username, password, salt, groupname, uid, gid, homedir, shell, last_login, login_count, last_error_login, login_error_count) "; $dbh_sql .= "VALUES (nextval('users_id_seq'::regclass), '$ftpuser', '$encrypted', '$salt', '$Config{ftp_groupname}', '$Config{ftp_uid}', '$Config{ftp_gid}', "; $dbh_sql .= " '$Config{ftp_dir}/$ftpuser', '$Config{ftp_shell}', NULL, 0, NULL, 0);"; } $dbh->do($dbh_sql); print "FTP user $ARGV[0] added."; } $dbh->disconnect; sub usercheck { my $sth; my $ftpuser = shift; my $req = "select id from users where username='$ftpuser'"; $sth = $dbh->prepare($req); if (!$sth) { my $tmp=$dbh->errstr; print "$tmp.\n$req failed."; } elsif (!$sth->execute) { my $tmp=$sth->errstr; print "$tmp.\n$req failed."; } elsif ($sth->rows()!=1) { return 0; } else { my $ref = $sth->fetchrow_arrayref; return $$ref[0]; } $sth->finish; } sub gensalt { my $count = shift; my @salt = ( '.', '/', 0 .. 9, 'A' .. 'Z', 'a' .. 'z' ); my $salt; for (1..$count) { $salt .= (@salt)[rand @salt]; } return $salt; } </spoiler> <spoiler title="ftpdeluser"> #!/usr/bin/perl -w use strict; use Crypt::PasswdMD5 qw(unix_md5_crypt); use DBI; use DBD::mysql; use Config::Simple; my $cfg = new Config::Simple('/etc/proftpd_sql.conf'); my %Config = $cfg->vars(); my $dsn; if ($Config{sql_type} eq "mysql") { $dsn = "dbi:mysql:dbname=$Config{sql_db};host=$Config{sql_host};"; } elsif ($Config{sql_type} eq "postgres") { $dsn = "dbi:Pg:dbname=$Config{sql_db};host=$Config{sql_host};"; } else { die("Incorrect \$sql_type in config"); } my $rec = 0; my $cleanlogs = 0; my $ftpuser = $ARGV[0]; if (@ARGV < 1) { print "Usage: $0 ftpuser\n"; exit; } my $dbh = DBI->connect($dsn, $Config{sql_user},$Config{sql_passwd}, {AutoCommit => 1}); my $ftp_user_exists = usercheck($ARGV[0]); if ($ftp_user_exists==0) { print "$0: user $ARGV[0] not found."; exit; } if (-d "$Config{ftp_dir}/$ftpuser") { print "Do you want to remove user's home directory recursively? (Yes/No): "; my $ans1 = <STDIN>; if ($ans1 eq "Yes\n" or $ans1 eq "Y\n") { $rec = 1; } } print "Do you want to cleanup database user activity logs? (Yes/No): "; my $ans2 = <STDIN>; if ($ans2 eq "Yes\n" or $ans2 eq "Y\n") { $cleanlogs = 1; } $dbh->do("DELETE FROM users WHERE id=$ftp_user_exists"); if ($cleanlogs == 1) { $dbh->do("DELETE FROM log_failed_logins WHERE user_name='$ftpuser'"); $dbh->do("DELETE FROM xfer_errors WHERE user_name='$ftpuser'"); $dbh->do("DELETE FROM xfer_table WHERE user_name='$ftpuser'"); } print "FTP user $ARGV[0] deleted, "; if ($rec == 1) { system("rm -rf $Config{ftp_dir}/$ftpuser"); print "with homedir.\n"; } else { print "homedir kept.\n"; } $dbh->disconnect; sub usercheck { my $sth; my $ftpuser = shift; my $req = "select id from users where username='$ftpuser'"; $sth = $dbh->prepare($req); if (!$sth) { my $tmp=$dbh->errstr; print "$tmp.\n$req failed."; } elsif (!$sth->execute) { my $tmp=$sth->errstr; print "$tmp.\n$req failed."; } elsif ($sth->rows()!=1) { return 0; } else { my $ref = $sth->fetchrow_arrayref; return $$ref[0]; } $sth->finish; }
ftppasswd
#!/usr/bin/perl -wl use strict; use Crypt::PasswdMD5 qw(unix_md5_crypt); use DBI; use DBD::mysql; use Config::Simple; my $cfg = new Config::Simple('/etc/proftpd_sql.conf'); my %Config = $cfg->vars(); my $dsn; if ($Config{sql_type} eq "mysql") { $dsn = "dbi:mysql:dbname=$Config{sql_db};host=$Config{sql_host};"; } elsif ($Config{sql_type} eq "postgres") { $dsn = "dbi:Pg:dbname=$Config{sql_db};host=$Config{sql_host};"; } else { die("Incorrect \$sql_type in config"); } if (@ARGV < 2) { print "Usage: $0 ftpuser password."; exit; } my $ftppass = $ARGV[1]; my $dbh = DBI->connect($dsn, $Config{sql_user},$Config{sql_passwd}, {AutoCommit => 1}); my $ftp_uid = usercheck($ARGV[0]); if ($ftp_uid==0) { print "$0: user $ARGV[0] not found."; } else { my $salt = gensalt(8); my $encrypted = unix_md5_crypt($ftppass, $salt); $dbh->do("UPDATE users SET password='$encrypted',salt='$salt' where id=$ftp_uid"); print "FTP password for user $ARGV[0] changed."; } $dbh->disconnect; sub usercheck { my $sth; my $ftpuser = shift; my $req = "select id from users where username='$ftpuser'"; $sth = $dbh->prepare($req); if (!$sth) { my $tmp=$dbh->errstr; print "$tmp.\n$req failed."; } elsif (!$sth->execute) { my $tmp=$sth->errstr; print "$tmp.\n$req failed."; } elsif ($sth->rows()!=1) { return 0; } else { my $ref = $sth->fetchrow_arrayref; return $$ref[0]; } $sth->finish; } sub gensalt { my $count = shift; my @salt = ( '.', '/', 0 .. 9, 'A' .. 'Z', 'a' .. 'z' ); my $salt; for (1..$count) { $salt .= (@salt)[rand @salt]; } return $salt; }
その結果、データベースユーザーパスワードを暗号化された形式で保存し、ユーザー管理とデータベースへのログインを行うFTPサーバーを手に入れました。