【proftpd】 MySQLでアカウント管理する


■■ rpm-build インストール

[root@hoge ~]# yum install rpm-build
(中略)
Dependencies Resolved

=============================================================================
Package                 Arch       Version          Repository        Size
=============================================================================
Installing:
rpm-build               i386       4.3.3-23_nonptl  base              458 k

Transaction Summary
=============================================================================
Install      1 Package(s)
Update       0 Package(s)
Remove       0 Package(s)
Total download size: 458 k
Is this ok [y/N]: y

■■ nullpopopo(管理ユーザ)のrpmビルド環境を作る

rpmのビルドは「必ず」一般ユーザで行います。なので、一般ユーザのホームディレクトリの下に
rpmディレクトリと .rpmmacros ファイルを作成してください。

[root@hoge ~]# su - nullpopopo
[nullpopopo@hoge ~]$ vi .rpmmacros
%_topdir                %(echo $HOME)/rpm
%_builddir              %{_topdir}/BUILD
%_rpmdir                %{_topdir}/RPMS
%_sourcedir             %{_topdir}/SOURCES
%_specdir               %{_topdir}/SPECS
%_srcrpmdir             %{_topdir}/SRPMS

[nullpopopo@hoge ~]$ mkdir -p rpm/{BUILD,RPMS,SOURCES,SPECS,SRPMS}

■■ MySQLサーバのインストール

[root@hoge ~]# yum --enablerepo=centosplus install mysql-server mysql-devel mysqlclient10 mysqlclient10-devel php-mysql
(中略)
Dependencies Resolved

=============================================================================
Package                 Arch       Version          Repository        Size
=============================================================================
Installing:
mysql-devel             i386       5.0.54-1.el4.centos  centosplus        2.8 M
mysql-server            i386       5.0.54-1.el4.centos  centosplus        9.6 M
mysqlclient10           i386       3.23.58-9.2.c4   centosplus        238 k
mysqlclient10-devel     i386       3.23.58-9.2.c4   centosplus         48 k
php-mysql               i386       5.1.6-3.el4s1.8  centosplus         77 k
Installing for dependencies:
e2fsprogs-devel         i386       1.35-12.11.el4_6.1  update            487 k
krb5-devel              i386       1.3.4-54         base              824 k
mysql                   i386       5.0.54-1.el4.centos  centosplus        2.8 M
mysql-libs              i386       5.0.54-1.el4.centos  centosplus        1.8 M
openssl-devel           i586       0.9.7a-43.17.el4_6.1  base              1.6 M
perl-Compress-Zlib      i386       1.42-1.el4       centosplus         54 k
perl-DBD-MySQL          i386       3.0008-1.el4.centos  centosplus        145 k
perl-DBI                i386       1.54-1.el4s1     centosplus        673 k
perl-HTML-Parser        i386       3.35-6           base               82 k
perl-HTML-Tagset        noarch     3.03-30          base               12 k
perl-URI                noarch     1.30-4           base               79 k
perl-libwww-perl        noarch     5.805-1.1.1      centosplus        371 k
php-pdo                 i386       5.1.6-3.el4s1.8  centosplus        220 k
zlib-devel              i386       1.2.1.2-1.2      base               89 k
Updating for dependencies:
perl                    i386    &nbs
p;  4:5.8.8-5.el4s1_2  centosplus         11 M

Transaction Summary
=============================================================================
Install     19 Package(s)
Update       1 Package(s)
Remove       0 Package(s)
Total download size: 33 M
Is this ok [y/N]: y

■■ MySQLの起動

■ MySQLの起動

[root@hoge ~]# /etc/init.d/mysqld start
Initializing MySQL database:                               [  OK  ]
Starting MySQL:                                            [  OK  ]

■ 自動起動設定

[root@hoge ~]# chkconfig mysqld --list
mysqld          0:off   1:off   2:off   3:off   4:off   5:off   6:off

[root@hoge ~]# chkconfig mysqld on

[root@hoge ~]# chkconfig mysqld --list
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

■■ MySQL Rootパスワードの設定

■ まずはデフォルトのユーザとパスワードを確認する

[nullpopopo@hoge ~]$ mysql -h localhost -u root
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 6
Server version: 5.0.54 Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> select user,host,password from mysql.user;
+------+------------------+----------+
| user | host             | password |
+------+------------------+----------+
| root | localhost        |          |
| root | hoge.example.com |          |
| root | 127.0.0.1        |          |
|      | localhost        |          |
|      | hoge.example.com |          |
+------+------------------+----------+
5 rows in set (0.00 sec)

■ rootのパスワードを設定する

mysql> set password for root@localhost=password('root_password');
Query OK, 0 rows affected (0.00 sec)

mysql> set password for root@127.0.0.1=password('root_password');
Query OK, 0 rows affected (0.00 sec)

mysql> set password for root@hoge.example.com=password('root_password');
Query OK, 0 rows affected (0.00 sec)

■ パスワードがかかったことを確認する

mysql> select user,host,password from mysql.user;
+------+------------------+------------------+
| user | host             | password         |
+------+------------------+------------------+
| root | localhost        | 3cfeabb26241321d |
| root | hoge.example.com | 3cfeabb26241321d |
| root | 127.0.0.1        | 3cfeabb26241321d |
|      | localhost        |                  |
|      | hoge.example.com |                  |
+------+------------------+------------------+
5 rows in set (0.01 sec)

■ 匿名ユーザを削除する

mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+------+------------------+------------------+
| user | host             | password         |
+------+------------------+------------------+
| root | localhost        | 3cfeabb26241321d |
| root | hoge.example.com | 3cfeabb26241321d |
| root | 127.0.0.1        | 3cfeabb26241321d |
+------+------------------+------------------+
3 rows in set (0.00 sec)

mysql> quit
Bye

■ パスワードつきのrootユーザでログインできることを確認する

[nullpopopo@hoge ~]$ mysql -h localhost -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 7
Server version: 5.0.54 Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.01 sec)

mysql> quit
Bye

[nullpopopo@hoge ~]$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 5.0.54 Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> quit
Bye

[nullpopopo@hoge ~]$ mysql -h hoge.example.com -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 5.0.54 Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> quit
Bye

■■ MySQL ライブラリの設定

■ 設定ファイルに追記

[root@hoge ~]# cp -p /etc/ld.so.conf /etc/ld.so.conf.orig
[root@hoge ~]# vi /etc/ld.so.conf
ここを
include ld.so.conf.d/*.conf

こうする
include ld.so.conf.d/*.conf
/usr/lib/mysql

■ 設定反映

[root@hoge ~]# ldconfig

■■ ProFTPD インストール

■ ProFTPDのインストールに必要なパッケージをインストールする

[nullpopopo@hoge SRPMS]$ su -
[root@hoge ~]# yum install pam-devel ncurses-devel pkgconfig gcc-c++ openldap-devel libacl-devel postgresql-devel
(中略)
Dependencies Resolved

=============================================================================
Package                 Arch       Version          Repository        Size
=============================================================================
Installing:
gcc-c++                 i386       3.4.6-9          base              2.4 M
libacl-devel            i386       2.2.23-5.3.el4   base               78 k
ncurses-devel           i386       5.4-15.el4       base              1.4 M
openldap-devel          i386       2.2.13-8.el4_6.2  update            1.3 M
pam-devel               i386       0.77-66.23       base               85 k
pkgconfig               i386       1:0.15.0-3       base               47 k
postgresql-devel        i386       7.4.19-1.el4_6.1  update            1.0 M
Installing for dependencies:
cyrus-sasl-devel        i386       2.1.19-14        base              1.3 M
libattr-devel           i386       2.4.16-3.1.el4   base               28 k
libstdc++-devel         i386       3.4.6-9          base              8.6 M
postgresql              i386       7.4.19-1.el4_6.1  update            2.0 M
postgresql-libs         i386       7.4.19-1.el4_6.1  update            147 k

Transaction Summary
=============================================================================
Install     12 Package(s)
Update       0 Package(s)
Remove       0 Package(s)
Total download size: 18 M
Is this ok [y/N]: y

■ パッケージのダウンロード

[root@hoge ~]# logout
[nullpopopo@hoge ~]$ cd rpm/SRPMS/
[nullpopopo@hoge SRPMS]$ wget http://apt.sw.be/redhat/el4/en/i386/SRPMS.dag/proftpd-1.2.10-10.rf.src.rpm
[nullpopopo@hoge SRPMS]$ rpm -ivh proftpd-1.2.10-10.rf.src.rpm

warning: group dag does not exist - using root
warning: user dag does not exist - using root
は気にしない

[nullpopopo@hoge SRPMS]$ cd ../SPECS/
[nullpopopo@hoge SPECS]$ rpmbuild -ba proftpd.spec --with mysql
[nullpopopo@hoge SPECS]$ rpm -ivh --test ../RPMS/i386/proftpd-1.2.10-10.rf.i386.rpm
[nullpopopo@hoge SPECS]$ su
[root@hoge SPECS]# rpm -ivh ../RPMS/i386/proftpd-1.2.10-10.rf.i386.rpm
Preparing...                ########################################### [100%]
1:proftpd                ########################################### [100%]

[root@hoge SPECS]# /usr/sbin/proftpd -l
Compiled-in modules:
mod_core.c
mod_xfer.c
mod_auth_unix.c
mod_auth_file.c
mod_auth.c
mod_ls.c
mod_log.c
mod_site.c
mod_readme.c
mod_auth_pam.c
mod_sql.c
mod_sql_mysql.c
mod_tls.c
mod_cap.c

[root@hoge SPECS]# /usr/sbin/proftpd -vv
- ProFTPD Version: 1.2.10 (stable)
-   Scoreboard Version: 01040002
-   Built: Mon Feb 11 17:33:48 JST 2008
-     Module: mod_core.c
-     Module: mod_xfer.c
-     Module: mod_auth_unix.c
-     Module: mod_auth_file.c
-     Module: mod_auth.c
-     Module: mod_ls.c
-     Module: mod_log.c
-     Module: mod_site.c
-     Module: mod_readme.c
-     Module: mod_auth_pam.c
-     Module: mod_sql.c
-     Module: mod_sql_mysql.c
-     Module: mod_tls.c
-     Module: mod_cap/1.0

[root@hoge SPECS]# ldd /usr/sbin/proftpd
libcrypt.so.1 => /lib/libcrypt.so.1 (0x0084d000)
libssl.so.4 => /lib/libssl.so.4 (0x00a5d000)
libcrypto.so.4 => /lib/libcrypto.so.4 (0x008f4000)
libm.so.6 => /lib/tls/libm.so.6 (0x00806000)
libz.so.1 => /usr/lib/libz.so.1 (0x0083b000)
libmysqlclient.so.15 => /usr/lib/mysql/libmysqlclient.so.15 (0x00111000)
libpam.so.0 => /lib/libpam.so.0 (0x00a93000)
libc.so.6 => /lib/tls/libc.so.6 (0x006d2000)
libgssapi_krb5.so.2 => /usr/lib/libgssapi_krb5.so.2 (0x009e0000)
libkrb5.so.3 => /usr/lib/libkrb5.so.3 (0x009f6000)
libcom_err.so.2 => /lib/libcom_err.so.2 (0x0082b000)
libk5crypto.so.3 => /usr/lib/libk5crypto.so.3 (0x008d1000)
libresolv.so.2 => /lib/libresolv.so.2 (0x008
7d000)
libdl.so.2 => /lib/libdl.so.2 (0x00800000)
/lib/ld-linux.so.2 (0x006b8000)
libnsl.so.1 => /lib/libnsl.so.1 (0x00892000)
libaudit.so.0 => /lib/libaudit.so.0 (0x008ab000)

mod_sql.c と mod_sql_mysql.c がモジュールに組み込まれ、 libmysqlclient.so.15 がリンクされてるのでOK。

[root@hoge SPECS]# exit
[nullpopopo@hoge SPECS]$ su -
Password:
[root@hoge ~]#

■ ProFTPD 1.2.10 自動起動設定

[root@hoge ~]# chkconfig proftpd on
[root@hoge ~]# chkconfig proftpd --list
proftpd         0:off   1:off   2:on    3:on    4:on    5:on    6:off

■ MySQL ProFTPD用データベースの作成

[root@hoge ~]# mkdir /etc/proftpd
[root@hoge ~]# cd /etc/proftpd
[root@hoge proftpd]# vi 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)
);

■ データベース「proftpd」を作成する。

[root@hoge proftpd]# mysqladmin -u root -p create proftpd
Enter password:

[root@hoge proftpd]# mysql -u root -p < proftpd.schema proftpd
Enter password:

[root@hoge proftpd]# mysql -u root -p proftpd
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 5.0.54 Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>

■ proftpd データベースのユーザ proftpd を作成する

mysql> GRANT SELECT,UPDATE,INSERT ON proftpd.* TO proftpd@localhost IDENTIFIED BY 'proftpd_password';
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

■ DBをreloadする

[root@hoge proftpd]# mysqladmin -u root -p reload
Enter password:

■ ProFTPD設定

[root@hoge proftpd]# cd /etc/
[root@hoge etc]# cp -p proftpd.conf proftpd.conf.orig
[root@hoge etc]# vi 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                     on ""
ServerName                      "hoge.example.com"
ServerType                      standalone
DefaultServer                   on

# 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

RootLogin                       off
#ListOptions                     "-la"
ListOptions                     "-a"

# 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                          nogroup
User                            webmaster
Group                           webmaster

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

RequireValidShell               off
UseReverseDNS                   off
IdentLookups                    off
TimesGMT                        off
#TimesGMT                        on
TimeoutIdle                     600
TimeoutLogin                    300
TimeoutNoTransfer               600
TimeoutStalled                  600
ShowSymlinks                    on
MaxClientsPerHost               3
MaxHostsPerUser                 10

#AllowStoreRestart
on
#AllowRetrieveRestart           on
#MaxStoreFileSize               100Mb

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
SQLConnectInfo              proftpd@localhost:3306 proftpd proftpd_password
SQLAuthTypes                Plaintext
SQLUserInfo                 users userid password uid gid homedir shell
SQLGroupInfo                groups groupname gid members
AuthOrder                   mod_sql.c

# Normally, we want files to be overwriteable.
AllowOverwrite          on

# Bar use of SITE CHMOD by default
##  DenyAll
#

# chmodコマンドが叩けないので、叩けるようにした
# by nullpopopo 2008/02/11
AllowAll
DenyAll
# A basic anonymous configuration, no upload directories.  If you do not
# want anonymous users, simply delete this entire section.
#
#  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
#  #    DenyAll
#  #

■ ProFTPD 1.2.10 グループ・ユーザをDBに追加

[root@hoge etc]# mysql -u root -p proftpd
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 10
Server version: 5.0.54 Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>

mysql> show tables;
+-------------------+
| Tables_in_proftpd |
+-------------------+
| groups            |
| users             |
+-------------------+
2 rows in set (0.00 sec)

グループを追加する。
mysql> INSERT INTO groups VALUES ('vhost',1000,'');
Query OK, 1 row affected (0.02 sec)

ユーザを追加する。
テーブル「users」へ追加するデータは
「ユーザ名」「パスワード」「UID」「GID」「ホームディレクトリ」「シェル」の順番である。
mysql> INSERT INTO users VALUES ('www_example_com','www_example_com',1000,1000,'/home/vhost/www.example.com','/bin/true');
Query OK, 1 row affected (0.00 sec)

グループの確認
mysql> select * from groups;
+-----------+------+---------+
| groupname | gid  | members |
+-----------+------+---------+
| vhost     | 1000 |         |
+-----------+------+---------+
1 row in set (0.00 sec)

ユーザの確認
mysql> select * from users;
+-----------------+-----------------+------+------+-----------------------------+-----------+
| userid          | password        | uid  | gid  | homedir                     | shell     |
+-----------------+-----------------+------+------+-----------------------------+-----------+
| www_example_com | www_example_com | 1000 | 1000 | /home/vhost/www.example.com | /bin/true |
+-----------------+-----------------+------+------+-----------------------------+-----------+
1 row in set (0.00 sec)

mysql> exit
Bye

■■ proftpd 起動スクリプト修正

TimesGMT の'(on|off|FALSE)'にもかかわらず、FTPクライアントに表示されたりxferlogに書き込まれたりする
時刻が9時間遅い問題があるので、起動スクリプトを修正することで解決する。

[root@hoge etc]# vi /etc/init.d/proftpd

ここを
start() {
echo -n $"Starting $prog: "
daemon proftpd
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/proftpd
}

こうする
start() {
echo -n $"Starting $prog: "
export TZ=JST-9
daemon proftpd
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/proftpd
}

■■ proftpd 起動

[root@hoge etc]# /etc/init.d/proftpd start
Starting
proftpd:                                          [  OK  ]

あとは、FTPユーザ www_example_com でログインし、ファイルのDOWN/UPを試してください。
ファイルやディレクトリのタイムスタンプが9時間ズレていなければOKです。