用MySQL来管理Proftpd的帐号：Unix和Linux中，vsftpd是一款非常安全的FTP服务器。任何发行版本中的vsftpd都非常安全。像RedHat、Debian、GNU、GNOME、KDE等等一些大型站点，都采用vsftpd作为它们的FTP服务器。本文将介绍用MySQL来管理Proftpd的帐号。一般
用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 就完成
京公网安备 11010802041100号