Proftpd+(TLS(ftps)+SFTP+MySQL連携)をインストールする（ソースからコンパイル） for RockyLinux

##はじめに
FTPは、セキュリティの問題から嫌煙されるケースもありますが、昨今ではクライアントソフトウェアが、FTPSに対応したことなども有り、手軽なファイル交換の手段として今でも多くの場所で活用されています。
FTPにおいては、vsftpdなどの製品が多く使われることがありますが、高機能で様々な設定が可能なProftpdを利用した、環境を構築します。
今回は、MySQLによるアカウント情報連携や、FTPS(TLS)とSFTP(SSH)にも対応させる前提での構成となります。
2017年5月現在、Proftpdは、1.3.7cが最新版となります。

##事前作業
MySQL及びOpenSSLのインストールが必要となります。
-MariaDB10.1のインストール（ソースからコンパイル）for CentOS7.3
-OpenSSL(1.0.x)をインストールする（ソースからコンパイル）for CentOS 7.2

##インストール作業
(コンパイルオプションは、SFTPにも対応できるようにしています）

#事前インストール前作業
export LD_LIBRARY_PATH=/usr/local/mysql/lib:/usr/local/ssl/lib:${LD_LIBRARY_PATH}
ln -s /usr/local/mysql/lib/libmysqlclient.so /usr/lib/libmysqlclient.so
ln -s /usr/local/ssl/include/openssl /usr/local/include/openssl
ln -s /usr/local/mysql/lib/libmysqlclient.so /lib64/libmysqlclient.so
ln -s /usr/local/mysql/lib/libmariadb.so.3 /lib64/libmariadb.so.3
ln -s /usr/local/lib/libsodium.so.23 /lib64/libsodium.so.23
mkdir -p /run/proftpd

#インストール作業
cd /usr/local/src
wget ftp://ftp.proftpd.org/distrib/source/proftpd-1.3.7c.tar.gz
tar xvzf proftpd-1.3.7c.tar.gz
cd proftpd-1.3.7c
./configure \
--with-modules=mod_sftp:mod_tls:mod_sql:mod_sql_mysql:mod_quotatab:mod_quotatab_sql \
--with-includes=/usr/local/mysql/include/mysql:/usr/local/ssl/include/openssl \
--with-libraries=/usr/local/mysql/lib/mysql:/usr/local/ssl/lib

以下の結果が出ていれば問題ない

--------------
Build Summary
--------------
Building the following static modules:
  mod_ident
  mod_auth_pam
  mod_sftp
  mod_tls
  mod_sql
  mod_sql_mysql
  mod_quotatab
  mod_quotatab_sql
  mod_cap

インストールを行う

make
make install

##MySQLでテーブル作成の実行

CREATE USER 'proftpd'@'localhost' IDENTIFIED VIA mysql_native_password USING 'proftpd';
GRANT ALL PRIVILEGES ON *.* TO 'proftpd'@'localhost' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
CREATE DATABASE IF NOT EXISTS `proftpd`;
GRANT ALL PRIVILEGES ON `proftpd`.* TO 'proftpd'@'localhost';

//以下は、proftpdのデーターベースにて実行

CREATE TABLE users (
  userid varchar( 50 ) NOT NULL ,
  uid INT,
  gid INT,
  passwd varchar( 30 ) NOT NULL ,
  shell varchar( 20 ) DEFAULT '/bin/bash',
  homedir varchar( 30 ) NOT NULL ,
  count INT,
  comment varchar(100),
  primary key(userid),
  unique key k_uid(uid)
);

CREATE TABLE groups(
  groupname varchar(20) NOT NULL,
  gid int NOT NULL,
  members varchar(30) NOT NULL,
  primary key (groupname),
  key k_gid(gid)
);

CREATE TABLE quotalimits (
  name varchar(30),
  quota_type ENUM("user", "group", "class", "all") NOT NULL,
  per_session ENUM("false", "true") NOT NULL,
  limit_type ENUM("soft", "hard") NOT NULL,
  bytes_in_avail FLOAT NOT NULL,
  bytes_out_avail FLOAT NOT NULL,
  bytes_xfer_avail FLOAT NOT NULL,
  files_in_avail INT UNSIGNED NOT NULL,
  files_out_avail INT UNSIGNED NOT NULL,
  files_xfer_avail INT UNSIGNED NOT NULL );

CREATE TABLE quotatallies (
  name varchar(30) NOT NULL,
  quota_type ENUM("user", "group", "class", "all") NOT NULL,
  bytes_in_used FLOAT NOT NULL,
  bytes_out_used FLOAT NOT NULL,
  bytes_xfer_used FLOAT NOT NULL,
  files_in_used INT UNSIGNED NOT NULL,
  files_out_used INT UNSIGNED NOT NULL,
  files_xfer_used INT UNSIGNED NOT NULL
);

/* 検証用でテストのアカウントを作成する */
INSERT INTO users
VALUES('test',12000,12000,'test','/bin/bash','/home/test',0,'テストアカウント');
INSERT INTO groups values('user',12000,'');

##設定ファイルを変更する

vi /usr/local/etc/proftpd.conf

# This is a basic ProFTPD configuration file (rename it to 
# 'proftpd.conf' for actual use.  It establishes a single server
# and a single anonymous login.  It assumes that you have a user/group
# "nobody" and "ftp" for normal operation and anon.

ServerIdent off
ServerName			"Onpremiss FTPServer"
ServerType			standalone
DefaultServer		on
PidFile                         /run/proftpd/proftpd.pid

# Port 21 is the standard FTP port.
Port				21

# Don't use IPv6 support by default.
UseIPv6                         on

# Umask 022 is a good standard umask to prevent new dirs and files
# from being group and world writable.
Umask				022

# To prevent DoS attacks, set the maximum number of child processes
# to 30.  If you need to allow more than 30 concurrent connections
# at once, simply increase this value.  Note that this ONLY works
# in standalone mode, in inetd mode you should use an inetd server
# that allows you to limit maximum number of processes per service
# (such as xinetd).
MaxInstances			30

# Set the user and group under which the server will run.
User				nobody
Group				nobody

# To cause every FTP user to be "jailed" (chrooted) into their home
# directory, uncomment this line.

# Normally, we want files to be overwriteable.
<Directory />
  AllowOverwrite		on
</Directory>

#add passive setting
AllowForeignAddress   On
MasqueradeAddress   ftp.example.com
PassivePorts 8000 8024


# add by custome setting
PidFile         /run/proftpd/proftpd.pid
UseFtpUsers off
UseReverseDNS off

DefaultRoot ~
TimesGMT off

#quota
QuotaDirectoryTally on 
QuotaDisplayUnits "Mb" 
QuotaEngine on 

#QuotaLog "/usr/local/proftpd/var/quota" 

QuotaShowQuotas on 

SQLNamedQuery get-quota-limit SELECT "name, quota_type, per_session,limit_type, bytes_in_avail, bytes_out_avail, bytes_xfer_avail,files_in_avail, files_out_avail, files_xfer_avail FROM quotalimits WHERE name = '%{0}' AND quota_type = '%{1}'"
SQLNamedQuery get-quota-tally SELECT "name, quota_type, bytes_in_used,bytes_out_used, bytes_xfer_used, files_in_used, files_out_used,files_xfer_used FROM quotatallies WHERE name = '%{0}' AND quota_type = '%{1}'"
SQLNamedQuery update-quota-tally UPDATE "bytes_in_used = bytes_in_used + %{0}, bytes_out_used = bytes_out_used + %{1}, bytes_xfer_used = bytes_xfer_used + %{2}, files_in_used = files_in_used + %{3}, files_out_used = files_out_used + %{4}, files_xfer_used = files_xfer_used + %{5} WHERE name = '%{6}'  AND quota_type = '%{7}'" quotatallies
SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" quotatallies 
QuotaLimitTable sql:/get-quota-limit 
QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally 


#sql setting
SQLAuthenticate on
SQLConnectInfo  proftpd@localhost proftpd proftpd

SQLAuthTypes Plaintext
#SQLUserInfo users userid passwd uid gid homedir shell


SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE "count=count+1 where userid='%u'" users

#SQLHomedirOnDemand on
#SQLHomedirOnDemandは、サポートされなくなった。
CreateHome On

SQLDefaultGID 1001

SQLLogFile /var/log/proftpd

<IfModule mod_tls.c>
    TLSEngine on
    TLSVerifyClient off
    TLSLog          /var/log/proftpd.ftps.log
    TLSCipherSuite  ALL:!ADH:RC4+RSA:+HIGH:+MEDIUM:+LOW:+SSLv2:+EXP
    TLSProtocol     TLSv1.2
    TLSRequired     off
    #Apacheと同じものを利用する
    TLSRSACertificateFile    /usr/local/apache2/conf/cert/servercert.crt
    TLSRSACertificateKeyFile /usr/local/apache2/conf/cert/server.key
    TLSOptions      NoSessionReuseRequired
    <VirtualHost 192.168.255.255>
        Port            990
        DefaultRoot     ~
        AllowOverwrite  on
        TLSEngine       on
        TLSVerifyClient off
        TLSLog          /var/log/proftpd.ftps.log
        TLSCipherSuite  ALL:!ADH:RC4+RSA:+HIGH:+MEDIUM:+LOW:+SSLv2:+EXP
        TLSProtocol     TLSv1.2
        TLSRequired     ctrl
        #Apacheと同じものを利用する
        TLSRSACertificateFile    /usr/local/apache2/conf/cert/servercert.crt
        TLSRSACertificateKeyFile /usr/local/apache2/conf/cert/server.key
        #UseImplicitSSLにも対応させる
        TLSOptions      NoSessionReuseRequired UseImplicitSSL

        SQLAuthenticate on
        SQLConnectInfo  proftpd@localhost proftpd proftpd

        SQLAuthTypes Plaintext
        #SQLUserInfo users userid passwd uid gid homedir shell

        SQLLog PASS updatecount
        SQLNamedQuery updatecount UPDATE "count=count+1 where userid='%u'" users
    </VirtualHost>
</IfModule>

<IfModule mod_sftp.c>
    <VirtualHost 192.168.255.255>
        Port            20022
        DefaultRoot     ~/
        AllowOverwrite  on
        SFTPEngine      on
        SFTPLog         /var/log/proftpd.sftp.log
        #SSHDと同じものを利用する
        SFTPHostKey     /etc/ssh/ssh_host_dsa_key
        SFTPHostKey     /etc/ssh/ssh_host_rsa_key
        SFTPAuthMethods password

        SQLAuthenticate on
        SQLConnectInfo  proftpd@localhost proftpd proftpd

        SQLAuthTypes Plaintext
        #SQLUserInfo users userid passwd uid gid homedir shell

        SQLLog PASS updatecount
        SQLNamedQuery updatecount UPDATE "count=count+1 where userid='%u'" users
    </VirtualHost>
</IfModule>


# A basic anonymous configuration, no upload directories.  If you do not
# want anonymous users, simply delete this entire <Anonymous> section.
<Anonymous ~ftp>
  User				ftp
  Group				ftp

  # We want clients to be able to login with "anonymous" as well as "ftp"
  UserAlias			anonymous ftp

  # Limit the maximum number of anonymous logins
  MaxClients			10

  # We want 'welcome.msg' displayed at login, and '.message' displayed
  # in each newly chdired directory.
  DisplayLogin			welcome.msg
  DisplayFirstChdir		.message

  # Limit WRITE everywhere in the anonymous chroot
  <Limit WRITE>
    DenyAll
  </Limit>
</Anonymous>

##起動スクリプトを設定

vi /usr/lib/systemd/system/proftpd.service

[Unit]
Description = ProFTPD FTP Server
After = network.target nss-lookup.target local-fs.target remote-fs.target

[Service]
Type = forking
PIDFile = /run/proftpd/proftpd.pid
Environment = PROFTPD_OPTIONS=
EnvironmentFile = -/etc/sysconfig/proftpd
ExecStart = /usr/local/sbin/proftpd $PROFTPD_OPTIONS
ExecReload = /bin/kill -HUP $MAINPID

[Install]
WantedBy = multi-user.target

##自動起動設定

mkdir -p /run/proftpd
chown -R nobody:nobody /run/proftpd
systemctl daemon-reload
systemctl start proftpd
systemctl enable proftpd

##参考
http://server-helper.doorblog.jp/archives/5338382.html
http://triplesky.blogspot.jp/2013/02/proftpdsftpftps.html
http://www.proftpd.org/docs/contrib/mod_tls.html#TLSOption