Настройка связки proftpd + {mysql/postgresql} с хранением паролей в md5+salt

Давно мне не давал покоя тот факт, что пароли пользователей у меня в БД хранятся в открытом виде. Также очень неудобно было вручную добавлять/удалять пользователей и менять им пароли.

В результате, настроил связку proftpd + mod_sql + mod_sql_passwd c хранением паролей в виде md5 + salt, а также написал три скрипта для добавления, удаления и смены пароля пользователей.

Ввиду того, что на разных серверах у меня используется где-то PostgreSQL, а где-то MySQL, выкладываю описание настроек для обеих СУБД.

Сервера работают под управлением ОС 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. Подготовка базы данных

В данной статье подключаться к БД мы будем на хост DBHOST с пользователем DBUSER, паролем DBPASSWD и базе DBNAME

Структура БД для MySQL

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

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 сервер с хранением паролей пользователей БД в шифрованном виде, управление пользователями и ведение логов в БД.

FavoriteLoadingДобавить в избранное

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *