[Из песочницы] Настройка связки proftpd + {mysql/postgresql} с хранением паролей в md5+salt02.03.2018 20:18

habr.png

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

В результате, настроил связку 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

Структура БД для 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 DBUSER;

CREATE SEQUENCE users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE users_id_seq OWNER TO DBUSER;

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 DBUSER;

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 DBUSER;

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 DBUSER;

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 DBUSER;

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 DBUSER;

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 DBUSER;


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


   LoadModule mod_sql.c
   LoadModule mod_sql_mysql.c (при использовании mysql)
   LoadModule mod_sql_postgres.c (при использовании postgres)
   LoadModule mod_sql_passwd.c


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;
}


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

© Habrahabr.ru