【proftpd】 MySQLでアカウント管理するリターンズ 201109

twitterで @mikeda からこんなメンションきた。

どうやらこのエントリを読んでいただいたようですが、確かこれCentOS4.4の時代じゃなかったかなーと記憶してます。前職で急性胆嚢炎やっちまって入院だの自宅療養だのやってたときに書いた記憶だけが鮮明に残ってますが、CentOSだって6.0が出てProftpdもepelからインストールすると1.3.3eです。せっかくなのでアップデートしようかと。

※ 2015/11/07 追記

こちらに新しい記事がありますので、Googleなどから検索された方はこちらをどうぞ。

※注！

実はまだ作りかけで、パッケージは入ったはいいものの認証がうまくいってません！＞＜

■ OSインストール

OSはCentOS6.0のBASEパッケージのみを選択してインストール。追加したパッケージは以下の通り。

ftp-0.17-51.1.el6.x86_64
gcc-4.4.4-13.el6.x86_64
gcc-c++-4.4.4-13.el6.x86_64
glibc-devel-2.12-1.7.el6_0.5.x86_64
glibc-headers-2.12-1.7.el6_0.5.x86_64
kernel-headers-2.6.32-71.29.1.el6.x86_64
keyutils-libs-devel-1.4-1.el6.x86_64
krb5-devel-1.8.2-3.el6_0.7.x86_64
libacl-devel-2.2.49-4.el6.x86_64
libattr-devel-2.4.44-4.el6.x86_64
libcap-devel-2.16-5.2.el6.x86_64
libcom_err-devel-1.41.12-3.el6.x86_64
libselinux-devel-2.0.94-2.el6.x86_64
libsepol-devel-2.0.41-3.el6.x86_64
libstdc++-devel-4.4.4-13.el6.x86_64
make-3.81-19.el6.x86_64
mysql-devel-5.1.52-1.el6_0.1.x86_64
mysql-server-5.1.52-1.el6_0.1.x86_64
ncurses-devel-5.7-3.20090208.el6.x86_64
openldap-devel-2.4.19-15.el6_0.2.x86_64
openssl-devel-1.0.0-4.el6_0.2.x86_64
pam-devel-1.1.1-4.el6_0.1.x86_64
patch-2.6-6.el6.x86_64
postgresql-devel-8.4.7-1.el6_0.1.x86_64
rpm-build-4.8.0-12.el6.x86_64
zlib-devel-1.2.3-25.el6.x86_64

■ epelリポジトリインストール
[cc lang='text' ]
$ wget http://ftp.riken.jp/Linux/fedora/epel/6/x86_64/epel-release-6-5.noarch.rpm
$ sudo rpm -ivh epel-release-6-5.noarch.rpm
[/cc]

■ epelを一旦無効にする

[cc lang='text' ]
$ sudo cp -p /etc/yum.repos.d/epel.repo /etc/yum.repos.d/epel.repo.orig
$ sudo vi /etc/yum.repos.d/epel.repo
$ diff /etc/yum.repos.d/epel.repo /etc/yum.repos.d/epel.repo.orig
6c6
< enabled=0 --- > enabled=1
[/cc]

■ proftpd proftpd-mysql インストール

[cc lang='text' ]
$ sudo yum --enablerepo=epel install proftpd proftpd-mysql
[/cc]

■ config作成

[cc lang='text' ]
$ sudo cp -p /etc/proftpd.conf /etc/proftpd.conf.orig
$ sudo vi /etc/proftpd.conf
[/cc]

以下configファイル

[cc lang='text' ]
# This is the ProFTPD configuration file
# $Id: proftpd.conf,v 1.1 2004/02/26 17:54:30 thias Exp $

ServerName                      "ProFTPD server"
ServerIdent                     on "FTP Server ready."
ServerAdmin                     root@localhost
ServerType                      standalone
#ServerType                     inetd
DefaultServer                   on
AccessGrantMsg                  "User %u logged in."
#DisplayConnect                 /etc/ftpissue
#DisplayLogin                   /etc/ftpmotd
#DisplayGoAway                  /etc/ftpgoaway
DeferWelcome                    off
PassivePorts                    10000   10010

# Use this to excude users from the chroot
DefaultRoot                     ~ !adm

# Use pam to authenticate (default) and be authoritative
AuthPAMConfig                   proftpd
AuthOrder                       mod_auth_pam.c* mod_auth_unix.c

# Do not perform ident nor DNS lookups (hangs when the port is filtered)
IdentLookups                    off
UseReverseDNS                   off

# Port 21 is the standard FTP port.
Port                            21

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

# Default to show dot files in directory listings
ListOptions                     "-a"

# See Configuration.html for these (here are the default values)
#MultilineRFC2228               off
#RootLogin                      off
#LoginPasswordPrompt            on
#MaxLoginAttempts               3
#MaxClientsPerHost              none
#AllowForeignAddress            off     # For FXP

# Allow to resume not only the downloads but the uploads too
AllowRetrieveRestart            on
AllowStoreRestart               on

# 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                    20

# Set the user and group that the server normally runs at.
User                            nobody
Group                           nobody

# Disable sendfile by default since it breaks displaying the download speeds in
# ftptop and ftpwho
UseSendfile                     no

# This is where we want to put the pid file
ScoreboardFile                  /var/run/proftpd.score

# Normally, we want users to do a few things.

  AllowOverwrite                yes
  
    AllowAll
  


# Define the log formats
LogFormat                       default "%h %l %u %t "%r" %s %b"
LogFormat                       auth    "%v [%P] %h %t "%r" %s"

# TLS
# Explained at http://www.castaglia.org/proftpd/modules/mod_tls.html
#TLSEngine                      on
#TLSRequired                    on
#TLSRSACertificateFile          /etc/pki/tls/certs/proftpd.pem
#TLSRSACertificateKeyFile       /etc/pki/tls/certs/proftpd.pem
#TLSCipherSuite                 ALL:!ADH:!DES
#TLSOptions                     NoCertRequest
#TLSVerifyClient                off
##TLSRenegotiate                ctrl 3600 data 512000 required off timeout 300
#TLSLog                         /var/log/proftpd/tls.log

# SQL authentication Dynamic Shared Object (DSO) loading
# See README.DSO and howto/DSO.html for more details.

   LoadModule mod_sql.c
   LoadModule mod_sql_mysql.c
#   LoadModule mod_sql_postgres.c

ModulePath      /usr/libexec/proftpd/

# A basic anonymous configuration, with an upload directory.
#
#  User                         ftp
#  Group                                ftp
#  AccessGrantMsg               "Anonymous login ok, restrictions apply."
#
#  # 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 "Sorry, max %m users -- try again later"
#
#  # Put the user into /pub right after login
#  #DefaultChdir                        /pub
#
#  # We want 'welcome.msg' displayed at login, '.message' displayed in
#  # each newly chdired directory and tell users to read README* files.
#  DisplayLogin                 /welcome.msg
#  DisplayFirstChdir            .message
#  DisplayReadme                        README*
#
#  # Some more cosmetic and not vital stuff
#  DirFakeUser                  on ftp
#  DirFakeGroup                 on ftp
#
#  # Limit WRITE everywhere in the anonymous chroot
#  
#    DenyAll
#  
#
#  # An upload directory that allows storing files but not retrieving
#  # or creating directories.
#  
#    AllowOverwrite             no
#    
#      DenyAll
#    
#
#    
#      AllowAll
#    
#  
#
#  # Don't write anonymous accesses to the system wtmp file (good idea!)
#  WtmpLog                      off
#
#  # Logging for the anonymous transfers
#  ExtendedLog          /var/log/proftpd/access.log WRITE,READ default
#  ExtendedLog          /var/log/proftpd/auth.log AUTH auth
#
#

ServerIdent                     on ""

RootLogin                       off
ListOptions                     "-la"
DefaultRoot                     ~ !wheel
RequireValidShell               off
UseReverseDNS                   off
IdentLookups                    off
TimesGMT                        off
TimeoutIdle                     600
TimeoutLogin                    300
TimeoutNoTransfer               600
TimeoutStalled                  600
ShowSymlinks                    on
MaxClientsPerHost               8
MaxHostsPerUser                 2
LogFormat allinfo "%t :  %u (%a [%h]) : [%s], %T, %m (%f)"
LogFormat write   "%t : %u : %F (%a)"
LogFormat read    "%t : %u : %F (%a)"
LogFormat auth    "%t : %u (%a [%h])"
ExtendedLog /var/log/proftpd/all.log   ALL   allinfo
ExtendedLog /var/log/proftpd/write.log WRITE write
ExtendedLog /var/log/proftpd/read.log  READ  read
ExtendedLog /var/log/proftpd/auth.log  AUTH  auth

  AllowOverwrite                on
  AllowStoreRestart             on
  AllowRetrieveRestart          on



    SQLAuthenticate     users
    #SQLAuthenticate     on
    SQLConnectInfo      proftpd@localhost:3306 proftpd PASSWORD
    #SQLAuthTypes        Crypt
    SQLAuthTypes        Plaintext
    SQLUserInfo         users userid password uid gid homedir shell
    SQLGroupInfo        groups groupname gid members
    #AuthOrder           mod_sql.c


    QuotaEngine         on
    QuotaLog            /var/log/proftpd/quota-log
    QuotaLimitTable     sql:/get-quota-limit
    QuotaTallyTable     sql:/get-quota-tally/update-quota-tally/
        insert-quota-tally

    SQLNamedQuery       get-quota-limit SELECT "userid, 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 userid = '%{0}' 
        AND quota_type = '%{1}'"

    SQLNamedQuery       get-quota-tally SELECT "userid, quota_type, 
        bytes_in_used, bytes_out_used, bytes_xfer_used, files_in_used, 
        files_out_used, files_xfer_used FROM quotatallies WHERE 
        userid = '%{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 userid = '%{6}' 
        AND quota_type = '%{7}'" quotatallies

    SQLNamedQuery       insert-quota-tally INSERT "%{0}, %{1}, %{2}, 
        %{3}, %{4}, %{5}, %{6}, %{7}" quotatallies

    QuotaLock           /tmp/proftpd-quota-lock
    QuotaShowQuotas     on
    QuotaDisplayUnits   Gb
    QuotaDirectoryTally on

[/cc]

MySQLのスキーマを以下のように作ります。

[cc lang='text' ]
$ cat proftpd.schema
CREATE TABLE groups (
    groupname VARCHAR(30) NOT NULL ,
    gid SMALLINT(5) UNSIGNED NOT NULL DEFAULT 1000,
    members varchar(255) default NULL,
    PRIMARY KEY ( groupname ),
    UNIQUE KEY gid (gid)
);

CREATE TABLE users (
    userid varchar(30) NOT NULL,
    password varchar(30) NOT NULL,
    uid SMALLINT(5) UNSIGNED NOT NULL DEFAULT 1000,
    gid SMALLINT(5) UNSIGNED NOT NULL DEFAULT 1000,
    homedir varchar(255) default NULL,
    shell varchar(255) default '/bin/true',
    PRIMARY KEY (userid),
    UNIQUE KEY uid (uid)
);

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

CREATE TABLE quotatallies (
    userid VARCHAR(30) NOT NULL,
    quota_type ENUM("user", "group", "class", "all") DEFAULT 'user' NOT NULL,
    bytes_in_used FLOAT DEFAULT '0' NOT NULL,
    bytes_out_used FLOAT DEFAULT '0' NOT NULL,
    bytes_xfer_used FLOAT DEFAULT '0' NOT NULL,
    files_in_used INT UNSIGNED DEFAULT '0' NOT NULL,
    files_out_used INT UNSIGNED DEFAULT '0' NOT NULL,
    files_xfer_used INT UNSIGNED DEFAULT '0' NOT NULL
);
GRANT SELECT,UPDATE,INSERT ON proftpd.*
    TO proftpd@localhost IDENTIFIED BY 'proftpd';
INSERT INTO groups VALUES ('testgroup',1000,'');
#INSERT INTO users VALUES (
#    'testuser',encrypt('testuser'),1001,1000,'/var/ftpdata','/bin/true');
INSERT INTO users VALUES (
    'testuser','password',1001,1000,'/var/ftpdata','/bin/true');
INSERT INTO quotalimits VALUES (
    'testuser','user','false','hard', 524288000,0,0,0,0,0);
[/cc]

ここまでできたら、あらかじめ「proftpd」データベースを作り、以下のコマンドでスキーマファイルからテーブル作成、データ投入を行います。

[cc lang='text' ]
$ mysql -u root -p < proftpd.schema proftpd
[/cc]

mysqldとproftpdを起動して繋がればOK！・・・のはずが、繋がらない(´・ω・｀)

モジュールは読み込まれているんだけどなー。。。

[cc lang='text' ]
$ sudo /usr/sbin/proftpd -vv
ProFTPD Version: 1.3.3e (maint)
  Scoreboard Version: 01040003
  Built: Thu Apr 7 2011 14:29:12 UTC

Loaded modules:
  mod_sql_mysql/4.0.8
  mod_sql/4.2.5
  mod_lang/0.9
  mod_ctrls/0.9.4
  mod_cap/1.0
  mod_vroot/0.9.2
  mod_tls/2.4.2
  mod_auth_pam/1.1
  mod_readme.c
  mod_ident/1.0
  mod_dso/0.5
  mod_facts/0.1
  mod_delay/0.6
  mod_site.c
  mod_log.c
  mod_ls.c
  mod_auth.c
  mod_auth_file/0.8.3
  mod_auth_unix.c
  mod_xfer.c
  mod_core.c
[/cc]

(っ´∀｀)っゃー | 一撃

(´･_･`) nullpopopoは今日も結婚できなかった

More from my site

2011年9月
月	火	水	木	金	土	日
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30