用MySQL来管理Proftpd 的帐号：Unix和Linux中，vsftpd是一款非常安全的FTP服务器。任何发行版本中的vsftpd都非常安全。像RedHat、Debian、GNU、GNOME、KDE 等等一些大型站点，都采用vsftpd作为它们的FTP服务器。本文将介绍用MySQL来管理Proftpd 的帐号。
一般建 FTP 帐号, 都是直接在系统上直接开个使用者的帐号, 但是如果是要开帐号给安全观念不够的人, 还是另外找个方法来做.
所以想到的是, FTP 帐号独立出来, 让 MySQL 来管理 FTP 的帐号, 而且 FTP 顺便限制存取的范围, 至少会比较安全一点(另外就是上传后的程式执行问题, 就不在此讨论).
在 Debian 上面跑 Proftpd, 使用 MySQL 来管理帐号已经有不少人做过这种事了~ 下述叁篇文章都有写, 在此主要是参考第叁篇:
一、安装 MySQL Apache Proftpd 和 proftpd-mysql
apt-get install mysql-server mysql-client libmysqlclient15-dev apache2 proftpd-mysql
二、设定 FTP 使用的 User 和 Group
groupadd -g 2001 ftpgroup
useradd -u 2001 -s /bin/false -d /bin/null -c "proftpd user" -g ftpgroup ftpuser
三、建立 MySQL ftpuser For Proftpd
因为要用 MySQL 来管理 Proftpd 的 User, 所以要建下面的 Table 来存放 User data.
- mysql -u root -p
- create database ftp;
- GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftpd'@'localhost' IDENTIFIED BY 'ftp_password';
- GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftpd'@'localhost.localdomain' IDENTIFIED BY 'ftp_password';
- FLUSH PRIVILEGES;
- use ftp;
- CREATE TABLE ftpgroup (
- groupname varchar(16) NOT NULL default '',
- gid smallint(6) NOT NULL default '5500',
- members varchar(16) NOT NULL default '',
- KEY groupname (groupname)
- ) TYPE=MyISAM COMMENT='ProFTP group table';
- CREATE TABLE ftpuser (
- id int(10) unsigned NOT NULL auto_increment,
- userid varchar(32) NOT NULL default '',
- passwd varchar(32) NOT NULL default '',
- uid smallint(6) NOT NULL default '5500',
- gid smallint(6) NOT NULL default '5500',
- homedir varchar(255) NOT NULL default '',
- shell varchar(16) NOT NULL default '/sbin/nologin',
- count int(11) NOT NULL default '0',
- accessed datetime NOT NULL default '0000-00-00 00:00:00',
- modified datetime NOT NULL default '0000-00-00 00:00:00',
- PRIMARY KEY (id),
- UNIQUE KEY userid (userid)
- ) TYPE=MyISAM COMMENT='ProFTP user table';
- # 这边是要建立一个 Group 和两个帐号
- INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES ('ftpgroup', 2001, 'ftpuser');
- INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES (1, 'USERNAME', 'PASSWORD', 2001, 2001, '/home/www.demo.com', '/sbin/nologin', 0, '', '');
- INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES (2, 'USERNAME2', 'PASSWORD2', 2002, 2001, '/home/www.demo2.com', '/sbin/nologin', 0, '', '');
- quit; # 离开 MySQL
四、建立使用者 FTP 登入后的目录环境
- mkdir /home/www.demo.com
- mkdir /home/www.demo2.com
- chown ftpuser:ftpgroup /home/www.demo.com
- chown ftpuser:ftpgroup /home/www.demo2.com
五、修改 Proftpd 设定, 读取 MySQL 当帐号来源
- vim /etc/proftpd/proftpd.conf
- UseIPv6 on => UseIPv6 off
- # 不加这行, 此帐号就存取到根目录的所有资源
- DefaultRoot ~
- RootLogin off
- RequireValidShell off
- # The passwords in MySQL are encrypted using CRYPT
- SQLAuthTypes Plaintext Crypt
- SQLAuthenticate users groups
- SQLConnectInfo ftp@localhost proftpd ftp_password
- SQLUserInfo ftpuser userid passwd uid gid homedir shell
- SQLGroupInfo ftpgroup groupname gid members
- SQLLog PASS updatecount
- SQLNamedQuery updatecount UPDATE "countcount=count+1, accessed=now() WHERE userid='%u'" ftpuser
- SQLLog STOR,DELE modified
- SQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuser
- #SQLLogFile /var/log/sql.log
- #SQLMinID 500
- # 如果使用这帐号的 Home Directory 不存在的话, 会自动去建立
- #SQLHomedirOnDemand on
六、设定 Proftpd 载入的模组
- vim /etc/proftpd/modules.conf
- #LoadModule mod_sql_postgres.c # 不用载入 PostgreSQL 的 Module
七、重新启动 Proftpd 即完成
- /etc/init.d/proftpd restart # 重新启动 Proftpd即可
八、快速 开帐号 的步骤
- INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES (3, 'USERNAME', 'PASSWORD', 2003, 2001, '/home/www.demo.com', '/sbin/nologin', 0, '', '');
- (修改 3, 'USERNAME', 'PASSWORD', 2003, '/home/www.demo.com' 这些值即可.)
- mkdir /home/www.demo.com # 建目录
- chown ftpuser:ftpgroup /home/www.demo.com # 改权限
这样子就完成啰~
九、FTP 要限制使用容量(Quota)
以下只是做笔记, 尚未测试.
MySql 加这两个 Table 和 资料:
- CREATE TABLE ftpquotalimits (
- name varchar(30) default NULL,
- quota_type enum('user','group','class','all') NOT NULL default 'user',
- per_session enum('false','true') NOT NULL default 'false',
- limit_type enum('soft','hard') NOT NULL default 'soft',
- bytes_in_avail int(10) unsigned NOT NULL default '0',
- bytes_out_avail int(10) unsigned NOT NULL default '0',
- bytes_xfer_avail int(10) unsigned NOT NULL default '0',
- files_in_avail int(10) unsigned NOT NULL default '0',
- files_out_avail int(10) unsigned NOT NULL default '0',
- files_xfer_avail int(10) unsigned NOT NULL default '0'
- ) TYPE=MyISAM;
- CREATE TABLE ftpquotatallies (
- name varchar(30) NOT NULL default '',
- quota_type enum('user','group','class','all') NOT NULL default 'user',
- bytes_in_used int(10) unsigned NOT NULL default '0',
- bytes_out_used int(10) unsigned NOT NULL default '0',
- bytes_xfer_used int(10) unsigned NOT NULL default '0',
- files_in_used int(10) unsigned NOT NULL default '0',
- files_out_used int(10) unsigned NOT NULL default '0',
- files_xfer_used int(10) unsigned NOT NULL default '0'
- ) TYPE=MyISAM;
- INSERT INTO `ftpquotalimits` (`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`) VALUES ('exampleuser', 'user', 'true', 'hard', 15728640, 0, 0, 0, 0, 0);
- INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES (1, 'USERNAME', 'PASSWORD', 2001, 2001, '/home/www.example.com', '/sbin/nologin', 0, '', '');
十、修改 Proftpd 设定
以支援 MySQL 的 Quote data
- vim /etc/proftpd/proftpd.conf 加入
- # User quotas
- # ===========
- QuotaEngine on
- QuotaDirectoryTally on
- QuotaDisplayUnits Mb
- 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 ftpquotalimits 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 ftpquotatallies WHERE name = '%{0}' AND quota_type = '%{1}'"
- SQLNamedQuery update-quota-tally UPDATE "bytes_in_usedbytes_in_used = bytes_in_used + %{0}, bytes_out_usedbytes_out_used = bytes_out_used + %{1}, bytes_xfer_usedbytes_xfer_used = bytes_xfer_used + %{2}, files_in_usedfiles_in_used = files_in_used + %{3}, files_out_usedfiles_out_used = files_out_used + %{4}, files_xfer_usedfiles_xfer_used = files_xfer_used + %{5} WHERE name = '%{6}' AND quota_type = '%{7}'" ftpquotatallies
- SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" ftpquotatallies
- QuotaLimitTable sql:/get-quota-limit
- QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally
- SQLNamedQuery gettally SELECT "ROUND((bytes_in_used/1048576),2) FROM ftpquotatallies WHERE name='%u'"
- SQLNamedQuery getlimit SELECT "ROUND((bytes_in_avail/1048576),2) FROM ftpquotalimits WHERE name='%u'"
- SQLNamedQuery getfree SELECT "ROUND(((ftpquotalimits.bytes_in_avail-ftpquotatallies.bytes_in_used)/1048576),2) FROM ftpquotalimits,ftpquotatallies WHERE ftpquotalimits.name = '%u' AND ftpquotatallies.name = '%u'"
- SQLShowInfo LIST "226" "Used %{gettally}MB from %{getlimit}MB. You have %{getfree}MB available space."
- RootLogin off
- RequireValidShell off
再来重新启动 Proftpd: /etc/init.d/proftpd restart 就完成啰
【编辑推荐】
