ProFTPD支持MySQL数据库添加虚拟用户认证及Quotas_MySQL

作者：手机用户2502857517_939 | 来源：互联网 | 2018-04-17 15:18

ProFTPD支持MySQL数据库添加虚拟用户认证及Quotas

者：北南南北来自：LinuxSir.Org 摘要：本文讲述了ProFTPD 支持MySQL数据库认证，比如添加虚拟用户、用户空间大小的限制（磁盘限额 Quotas）；本文是实践文档；

0、前言 1、什么是ProFTPD； 2、ProFTPD的编译和安装；

2.1、ProFTPD的下载； 2.2、编译安装； 3、ProFTPD认证中的MySQL数据库；
3.1、创建一个ProFTPD的数据库proftpd； 3.2、导入proftpd数据库； 4、ProFTPD的配置文件proftpd.conf 5、系统用户ftp和用户组ftp的UID和GID的调整； 6、启动ProFTPD，并测试； 7、关于ProFTP的服务器管理、用户管理和磁盘限额管理

7.1 ProFTPD 服务器的管理； 7.2 通过MySQL来管理FTP用户； 7.3 ProFTPD 用户磁盘限额管理； 8、本文未尽事宜； 9、关于本文 10、参考文档 11、相关文档

++++++++++++++++++++++++++++++++++++++++++++++++ 正文 ++++++++++++++++++++++++++++++++++++++++++++++++

前言

本文是根据实践而来，最主要是帮助新手知道怎么自架FTP服务器，本文也可以说是一个简单的使用例子；但不会把ProFTPD的所有文档都详细说明；也就是说简单的安装和配置，以及最简单的使用。让不懂ProFTPD的弟兄，比着“瓢”也能画出“葫芦”。至于哪个FTP服务器程序更好，我想都是好的，只要会用就行；vsftpd 现在很流行；但ProFTPD也是一种选择... ...

1、什么是ProFTPD；

ProFTPD 是一个FTP服务器程序，和vsftpd、wuftp 类似的FTP服务器，他们最终实现功能和目的都是一样的，都是为了传输文件；

2、ProFTPD的编译和安装；

2.1、ProFTPD的下载；

ProFTPD的主页： http://www.proftpd.org 本教程选用版本： proftpd-1.3.0rc3.tar.gz 下载地址：

ftp://ftp.proftpd.org/distrib/source/ http://www.proftpd.org/download.html

2.2、编译安装；

编译安装时得用编译环境，比如需要gcc，如果少什么包，您可以在系统安装盘中找到；或者在线升级也行；现在大多的发行版都有支持在线升级的软件包管理工具；比如RPM的系统有yum和apt可用，debian有apt可用 ... ... 另外Proftpd 的用户认证是通过MySQL数据库来实现的，我们也得把Mysql数据库服务器也得安装上吧；这个不详细说了，您自己看着办吧；

我们想把ProFTPD 安装在 /opt/proftpd 目录中，我们把软件下载到 root用户的家目录中，因为安装软件需要 root权限，如果您在普通用户下执行编译是，到make install 这步，得通过su命令切换到root用户，然后执行 make install ； [root@localhost ~]# pwd /root [root@localhost ~]# ls proftpd-1.3.0rc3.tar.gz 解压、编译、安装

[root@localhost ~]# tar zxvf proftpd-1.3.0rc3.tar.gz [root@localhost ~]# cd proftpd-1.3.0rc3 [root@localhost proftpd-1.3.0rc3]#./configure --with-modules=mod_sql:mod_sql_mysql:mod_quotatab:mod_quotatab_sql / --with-includes=/usr/include/mysql / --with-libraries=/usr/lib/mysql / --prefix=/opt/proftpd

说明：

--with-modules=mod_sql:mod_sql_mysql:mod_quotatab:mod_quotatab_sql

这句是让编译ProFTPD 支持MySQL的模块，并有磁盘限额支持；

--with-includes=

这是来指定MySQL服务器includes所在的位置；在这里我设置的是/usr/include/mysql，因为我的MySQL服务器的includes的确在这个目录；你可以根据自己的机器环境来调整；

--with-libraries= 这是来指定MySQL服务器libraries所在的位置；在这里我设置的是/usr/lib/mysql，因为我的MySQL服务器的libraries的确在这个目录；你可以根据自己的机器环境来调整；

--prefix=

这是用来指定要把ProFTPD安装在哪个位置，在这里我把ProFTPD安装在了 /opt/proftpd 目录下，您不必自己建目录 /opt/proftpd ，在安装的时候这个目录会自动生成；当然您也可以自己来指定ProFTPD的安装位置；当我们不需要proftpd的时候，就可以直接删除proftpd目录；这样做好象是有点方便；

在configure过程中的错误排查：

如果在configure过程中，也就是上面的./configure 一长串指令执行后，有错误发生，无非是编译工具缺少或者Mysql的includes和libraries的目录指定的不对；自己想想看？

编译和安装：

[root@localhost proftpd-1.3.0rc3]# make [root@localhost proftpd-1.3.0rc3]# make install

如果没有错误发生，这样就安装好了，您可以在 /opt/目录下看到一个/opt/proftpd 的目录；

[root@localhost proftpd-1.3.0rc3]# ls -ld /opt/proftpd/ drwxr-xr-x 8 root root 4096 1月 2 09:37 /opt/proftpd

3、ProFTPD认证中的MySQL数据库

3.1、创建一个ProFTPD的数据库proftpd；

首先您应该会把MySQL数据库服务器打开，以MySQL的超级管理员root进入创建名为proftpd的数据库； [root@localhost ~]# mysql -uroot -p Enter password: 注：在这里请您输入MySQL的管理密码； Welcome to the MySQL monitor. Commands end with ; or /g. Your MySQL connection id is 41 to server version: 4.1.11 Type 'help;' or '/h' for help. Type '/c' to clear the buffer. mysql>create database proftpd; mysql>Grant select,insert,update,delete,create,drop,index,alter,create temporary tables,lock tables on proftpd.* to proftpd@localhost Identified by "123456"; mysql>quit

说明：

create database proftpd; 这行是创建名为proftpd的数据库； Grant 这行是为proftpd 数据库授权，让用户名为proftpd，密码为123456（这只是一个例子，密码自己定义），这个用来管理proftpd这个数据库； quit 这行是退出mysql界面；

3.2、导入proftpd数据库；

下面是一个现成的数据库，你只需要导入就行了，比较简单；把下面的代码拷贝下来，然后另存为 proftpd.sql；然后通过下面的命令来导入；

[root@localhost ~]# mysql -uproftpd -p proftpd 下面是proftpd的数据库，您可以拷贝下来，另存为 proftpd.sql ，然后用上面的命令来导入；

-- 数据库: `proftpd` -- -- -------------------------------------------------------- -- -- 表的结构 `ftpgroups` -- CREATE TABLE `ftpgroups` ( `groupname` varchar(30) NOT NULL default '', `gid` int(11) NOT NULL default '1000', `members` varchar(255) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- 表的结构 `ftpusers` -- CREATE TABLE `ftpusers` ( `userid` varchar(30) NOT NULL default '', `passwd` varchar(80) NOT NULL default '', `uid` int(10) unsigned NOT NULL default '1000', `gid` int(10) unsigned NOT NULL default '1000', `homedir` varchar(255) NOT NULL default '', `shell` varchar(255) NOT NULL default '/sbin/nologin', `count` int(10) unsigned NOT NULL default '0', `host` varchar(30) NOT NULL default '', `lastlogin` varchar(30) NOT NULL default '', UNIQUE KEY `userid` (`userid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- 导出表中的数据 `ftpusers` -- INSERT INTO `ftpusers` VALUES ('test', 'test', 1000, 1000, '/home/test', '/sbin/nologin',0,'',''); -- -------------------------------------------------------- -- -- 表的结构 `quotalimits` -- CREATE TABLE `quotalimits` ( `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` float NOT NULL default '0', `bytes_out_avail` float NOT NULL default '0', `bytes_xfer_avail` float 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' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- 表的结构 `quotatallies` -- CREATE TABLE `quotatallies` ( `name` varchar(30) NOT NULL default '', `quota_type` enum('user','group','class','all') NOT NULL default 'user', `bytes_in_used` float NOT NULL default '0', `bytes_out_used` float NOT NULL default '0', `bytes_xfer_used` float 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' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 4、ProFTPD的配置文件proftpd.conf

在我们这个例子中，ProFTPD的配置文件在/opt/proftpd/etc目录中，就是proftpd.conf文件；您可以把它改名备份；

[root@localhost ~]# cd /opt/proftpd/etc/ [root@localhost etc]# mv proftpd.conf proftpd.confBAK

然后再新建一个 proftpd.conf 文件，内容如下；您可以对这个文件进行相应的调整；其中#号部份就是注掉的，不会生效；注意一下MySQL连接数据库部份；另外如果您不是把ProFTPD安装在了/opt/proftpd目录下，一些东西也是需要调整的；自己看着办吧；

# 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. ServerName "My TestFTP" #这是您的FTP服务器的名字，自己写定 ServerType standalone ServerAdmin xxxxx@xxxxxxxxxxx.com #这是管理员信箱，自己来写； DefaultServer On # Display message DisplayLogin /opt/proftpd/etc/ftplogin.msg #DisplayConnect /net/messages/ftp.pre #DisplayFirstChdir index.txt # Port 21 is the standard FTP port. Port 21 # Limit users to login by username AllowAll # Umask 022 is a good standard umask to prevent new dirs and files # from being group and world writable. Umask 022 # Limit login attempts # MaxLoginAttempts 5 # Set the maximum number of seconds a data connection is allowed # to "stall" before being aborted. TimeoutStalled 600 TimeoutLogin 900 TimeoutIdle 600 TimeoutNoTransfer 600 # 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. DefaultRoot ~ # Users needs a valid shell # RequireValidShell off # Performance: skip DNS resolution when we process the logs... UseReverseDNS off # Turn off Ident lookups IdentLookups off # Restart session support # AllowStoreRestart on AllowRetrieveRestart on #-------- load sql.mod for mysql authoritative --------# SQLConnectInfo proftpd@localhost proftpd 123456 #注：上面这行是MySQL连接服务器部份，自己根据情况来改一改； SQLAuthTypes Plaintext SQLUserInfo ftpusers userid passwd uid gid homedir shell SQLGroupInfo ftpgroups groupname gid members SQLAuthenticate users groups SQLNegativeCache on SQLHomedirOnDemand on SQLLogFile /var/log/proftpd.sql.log SQLNamedQuery getcount SELECT "count from ftpusers where userid='%u'" SQLNamedQuery getlastlogin SELECT "lastlogin from ftpusers where userid='%u'" SQLNamedQuery updatelogininfo UPDATE "count=count+1,host='%h',lastlogin=current_timestamp() WHERE userid='%u'" ftpusers SQLShowInfo PASS "230" "You've logged on %{getcount} times, last login at %{getlastlogin}" SQLLog PASS updatelogininfo #-------- load sql.mod for mysql authoritative --------# #--------- load qudes.mod for Quota limit --------# QuotaDirectoryTally on QuotaDisplayUnits "Mb" QuotaEngine on #QuotaLog /var/log/proftpd.quota.log 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 #--------- load qudes.mod for Quota limit --------# # Logging options # Debug Level # emerg, alert, crit (empfohlen), error, warn. notice, info, debug # SyslogLevel emerg SystemLog /var/log/proftpd.system.log TransferLog /var/log/proftpd.xferlog # Some logging formats # LogFormat default "%h %l %u %t /"%r/" %s %b" LogFormat auth "%v [%P] %h %t /"%r/" %s" LogFormat write "%h %l %u %t /"%r/" %s %b" # Log file/dir access # ExtendedLog /var/log/proftpd.access_log WRITE,READ write # Record all logins ExtendedLog /var/log/proftpd.auth_log AUTH auth # Paranoia logging level.... ExtendedLog /var/log/proftpd.paranoid_log ALL default #注；上面几行是存放log的设置，不必改动也行；查看log就到上面相应的文件看吧； # 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 #注最多30个ip同时登录使用ftp; # Maximum clients with message #MaxClients 2 "Sorry, max %m users -- try again later" MaxClientsPerHost 2 "Sorry, only 2 session for one host" #注每个ip，只能两个线程程，请自己调整； # Normally, we want files to be overwriteable. AllowOverwrite on RootLogin off RequireValidShell off # alphanumeric characters for uploads (and not shell code...) #PathAllowFilter "^[a-zA-Z0-9_.-]()'+$" #PathAllowFilter "^[a-zA-Z0-9 _.-]()'+$" # We don't want .ftpaccess or .htaccess files to be uploaded #PathDenyFilter "(/.ftp)|(/.ht)[a-z]+$" #pathDenyFilter "/.ftp[a-z]+$" # Do not allow to pass printf-Formats (security! see documentation!): #AllowFilter "^[a-zA-Z0-9@~ /,_.-]*$" #DenyFilter "%" 5、系统用户和用户组ftp的UID和GID的调整；

由于我们在配置文件中，把ftp的用户和用户组的UID和GID都设置为了1000；所以我们得调整一下/etc/passwd 和/etc/group中有关ftp用户和用户组的行；把UID和GID都改为1000；

您可以在/etc/passwd 中找到 ftp用户一行，比如类似下面这行

ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin

在这行中，14是ftp用户的UID,我们要改为1000，50为ftp用户组的GID，也改为1000,其它的可以不变；也就是

ftp:x:1000:1000:FTP User:/var/ftp:/sbin/nologin

接着我们再找到 /etc/group ；在这个文件中找到一行，类似如下的；

ftp:x:50:

把这行中的50改为1000；也就是这样的；

ftp:x:1000: 6、启动ProFTPD，并测试；

[root@localhost ~]# /opt/proftpd/sbin/proftpd [root@localhost ~]# pgrep proftpd 17965

说明：上面就把proftpd 启动起来了；我们通过pgrep 来查看是否有ProFTPD的进程，查看得知已经有了；证明服务器已经启动。我们进入测试阶段；

测试：测试帐号是test，密码是test；您可以用ftp命令来测试，也可以用lftp来测试，也可以用gftp来测试；找一个FTP客户端就行；这个test帐号是怎么来的呢。回头看一下proftpd.sql那段代码中，是否有下面这行；

INSERT INTO `ftpusers` VALUES ('test', 'test', 1000, 1000, '/home/test', '/sbin/nologin',0,'','');

这是我在写文档时，添加的一个测试帐号；只要您把proftpd.sql文件导入了，就有这个帐号；您当然也可以删除它；上面这行说的是在ftpusers的表中，添加一个录；也就是ftp用户的记录；在本文的最下面有说明如何通过MySQL来管理帐号；在这里我们只是测试是不是FTP能用了；

在默认的情况下，test用户的家目录是在/home/test，密码是test；而/home/test是当您第一次以用户test登录时，系统自动建立的；这些都自动的；

如果您用ftp命令连接

[root@localhost home]# ftp localhost Connected to localhost.localdomain. 220 ProFTPD 1.3.0rc3 Server (My TestFTP) [127.0.0.1] 500 AUTH not understood 500 AUTH not understood KERBEROS_V4 rejected as an authentication type Name (localhost:beinan): test 注：用户名 331 Password required for test. 注：密码 Password: 230-You've logged on 5 times, last login at 2006-01-02 12:50:27 230 User test logged in. Remote system type is UNIX. Using binary mode to transfer files. ftp> ls 注：ls 查看； 227 Entering Passive Mode (127,0,0,1,128,31). 150 Opening ASCII mode data connection for file list 226 Transfer complete. ftp> mkdir mytestdir 注：创建目录看是否成功； 257 "/mytestdir" - Directory successfully created ftp> dir 注：查看是否有mytestdir 目录； 227 Entering Passive Mode (127,0,0,1,128,32). 150 Opening ASCII mode data connection for file list drwxr-xr-x 2 test ftp 4096 Jan 2 04:53 mytestdir 226 Transfer complete. ftp>

如果您用lftp 来连接测试

[root@localhost home]# lftp test:test@localhost 注：登录； lftp test@localhost:~> ls 注：列目录； lftp test@localhost:/> put /home/beinan/fcitx-3.2-051108.tar.bz2 4164490 bytes transferred 注：上传测试； lftp test@localhost:/> ls 注：查看是否成功；OK成功 -rw-r--r-- 1 test ftp 4164490 Jan 2 05:03 fcitx-3.2-051108.tar.bz2

说明：

如果您想远程连接，请把localhost改为实际ip地址，比如我的机器在本地网是192.168.1.5，那就把localhost改为192.168.1.5；如果您想在公网测试，请把localhost改为公网的IP地址；

请查看是否有/home/test这个目录？是不是FTP服务器自己建了一个？答案是肯定的... ...

7、关于ProFTP的服务器管理、用户管理和磁盘限额管理

7.1 ProFTPD 服务器的管理；

ProFTPD服务器的启动程序是在安装目录的sbin中，也就是proftpd；我们还是以安装目录/opt/proftpd 为准来说明；

[root@localhost ~]# /opt/proftpd/sbin/proftpd 注：服务器的启动； [root@localhost ~]# pgrep proftpd 注：查看服务器是不是启动起来了；如果没有进程，说明失败； [root@localhost ~]# pkill proftpd 注：杀死proftpd的进程；也就是关掉服务器；

注意：我们更改proftpd.conf后，要重启proftpd ，否则改动不会生效。这时就要用到pkill proftpd ，然后再重启proftpd 服务器；每次改动proftpd.conf都要这么做；

下面是几个查看ProFTPD服务器状态的命令，您也可以尝试一下，具体用法自己尝试吧；

[root@localhost ~]# /opt/proftpd/bin/ftpcount [root@localhost ~]# /opt/proftpd/bin/ftpwho [root@localhost ~]# /opt/proftpd/bin/ftptop [root@localhost ~]# /opt/proftpd/bin/ftpdctl 7.2 通过MySQL来管理FTP用户

在本文档中，ProFTPD 对FTP用户是通过MySQL来进行的，现在我们分析一下我们前面所提到proftpd数据库；ftp所有的用户都装在ftpusers这个表中，我们先分析一下这个表；分析这个表的目的是我们能明白如何添加用户；

首先，我们以proftpd 用户和密码登录到MySQL；并查看ftpusers表的结构；

[root@localhost ~]# mysql -uproftpd -p Enter password: 注：在这里输入proftpd用户的密码； mysql> 注：成功进入；

mysql> use proftpd; 注：要对proftpd 数据库进行操作，我们要先USE（用）proftpd数据库；

我们要查看ftpusers 这个表的结构，我们要用到 DESCRIBE 这个指令，后面接表的名称；

说明：

userid 就是FTP的用户名，这个是必填写字段； passwd 是FTP用户的密码，这个是必填写字段； uid和gid字段默认是1000； homedir 是FTP用户的家目录放在哪里，要自己指定； shell 这个是用来指定用户是否能登录系统，这里默认的是不能登录，因为是虚拟用户，所以不能让虚拟用户来登录系统；所以默认是/sbin/nologin； count 是访问次数，默认是0； host 是登录FTP服务器的IP地址记录，可以不设置；服务器会自己纪录； lastlogin 是最后登录时间，这个也是自动生成，可以不必理会；

如何添加用户呢？

其实添加用户的过程，也就是向proftpd 数据库中的表ftpusers插入纪录的过程；我们上面分析了表的结构；那我们就按其要求来插入用户纪录；

尝试插入一条纪录

比如我们想加一个用户，用户名为test2，密码为test2，UID和GID为都为1000,家目录位于/home/test2；值得注意的是UID和GID的只能是1000,因为我们在系统用户设置中做了改动；前面有提到；我们添加所有的FTP用户UID和GID都是 1000；

所以如果您添加用户时，只是改一下userid、passwd和homedir字段处就行了。其它不必改动，当然您非常性MySQL也没有什么不可；

INSERT INTO `ftpusers` VALUES ('test2', 'test2', 1000, 1000, '/home/test2', '/sbin/nologin',0,'','');

查看是否插入成功

再举一例；添加一个用户名为test3，密码为test3 ，并且把test3的家目录放在/opt/test3中；

mysql> INSERT INTO `ftpusers` VALUES ('test3', 'test3', 1000, 1000, '/opt/test3', '/sbin/nologin',0,'','');

添加好后，测试一下用户test3是否能登录，并且上传文件；下面的例子证明是成功的；

[root@localhost ~]# lftp test3:test3@localhost lftp test3@localhost:~> ls lftp test3@localhost:/> put /home/beinan/fcitx-3.2-051108.tar.bz2 4164490 bytes transferred lftp test3@localhost:/> ls -rw-r--r-- 1 test3 ftp 4164490 Jan 2 06:08 fcitx-3.2-051108.tar.bz2 lftp test3@localhost:/>

如果你想删除一个用户，您可以用 MySQL的delete 指令；比如我想删除test2这个用户；可以用.....

mysql> DELETE FROM ftpusers WHERE userid="test2";

如果想更新一条用户纪录，比如test用户密码字段；

mysql> update ftpusers set passwd="aaasss" where userid="test";

也就是说，你想更新用户纪录的那个字段就更新什么，下面公式；

mysql>update 数据表 set 字段="赋值" where 关健字段="字段值";

在这里关健字段是唯一的，这样才能找到你所要更新的纪录，表达不太好；如果不太熟悉，慢慢理解吧；我也不会MySQL；呵，难为人师...... 在这里，我们还是把userid 做为关健字段，因为只有这个字段才是唯一的；

再比如，我们想更新用户的家目录，比如我想把test用户的家目录改到 /opt/test；

通过上面的，我们得知test目前的家目录在/home/test，下面我们来更改到/opt/test；

mysql> update ftpusers set homedir="/opt/test" where userid="test"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select userid,homedir from ftpusers where userid="test"; +--------+-----------+ | userid | homedir | +--------+-----------+ | test | /opt/test | +--------+-----------+

7.3 ProFTPD 用户磁盘限额管理；

我们在前面所导入的proftpd 数据库中，有这样一段；

CREATE TABLE `quotalimits` ( `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` float NOT NULL default '0', `bytes_out_avail` float NOT NULL default '0', `bytes_xfer_avail` float 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' ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

说明：

上面其实就是在proftpd库中创建一个表quotalimits；我们在proftpd的数据库中，再来查看一下quotalimits表的结构，这样方便我们理解和使用磁盘限额；

quotalimits name - username quota_type - user, group, class, all (we use user) per_session - true or false (we use false) limit_type - quota limit type - hard or soft (we use soft) bytes_in_avail - upload limit in bytes - allowed bytes on disk (eg diskquota) bytes_out_avail - download limit in bytes - allowed bytes a user can download bytes_xfer_avail - allowed bytes a user can transfer in/out files_in_avail - upload limit in files - allowed number of uploaded files files_out_avail - allowed number of downloaded files files_xfer_avail - allowed number of files a user can transfer in/out

name 应该这样理解，既能表示单个用户，也能表示用户组名；如果我们在quota_type（限额类型）中使用group来认证的话，那就得在这里设置组名，这样整组都具有统一的磁盘限额的特性；当然您要在ftpgroups表中插入组纪录；并且在member字段中得把用户一个一个的列进去，这是后话了；先自己研究一下，只是插入纪录的事；我们只说最简单的单个用户的磁盘限额；默认值可以为空NULL，如果为空则针对所在有quota_type中设置的类型，比如在quota_type中设置为user ，就是针对所有ftpusers 中的用户起作用；如果是group名，也是对ftpgroups 所有组作用；

quota_type 磁盘限额类型，可以设置为用户，也可以设置为用户组group ；如果您的name写的是用户组，那在这里就得设置为group来认定；默认为user认证；

per_session 默认为false； limit_type 默认为soft； bytes_in_avail 用户占用空间大小，也就是家目录的空间最大可以让用户占用多少，单位是byte；默认为0，0是不受限制，以下同理；

bytes_out_avail 注；所有下载文件的总和，默认为0；

bytes_xfer_avail 注；一个用户上传下载流量总和，默认为0

files_in_avail 注：限制上传文件总数，默认为0；

files_out_avail 注；限制下载文件个数总计，默认为0

files_xfer_avail 注：允许下载和上传的文件总和我，默认为0；

由此看来，我们比如想让test用户，约束空间大小为100M，其它不受限制；则可用下面的mysql命令添加；

先让我们对照quotalimits表的结构，然后根据表的结构来添加；

mysql>insert into quotalimits VALUES ('test','user','false','soft','104857600','0','0','0','0','0');

运算公式：

1Kb=1024 byte 1M=1024 Kb 100M=100x1024 Kb= 100x1024x1024 byte=104857600 byte

注意：磁盘限额生效，必须让FTP用户重新登录才有效；比如test用户正在ftp上，这时要先退出，然后再登录，这是磁盘限额就有效了；

查看用户空间使用情况

登录FTP后用下面的命令；

quote site quota;

举例：

lftp test@192.168.1.5:/> quote site quota; 200-The current quota for this session are [current/limit]: Name: test Quota Type: User Per Session: False Limit Type: Soft Uploaded Mb: 19.00/95.37 Downloaded Mb: unlimited Transferred Mb: unlimited Uploaded files: unlimited Downloaded files: unlimited Transferred files: unlimited 200 Please contact xxxxx@xxxxxxxxxxx.com if these entries are inaccurate 8、本文未尽事宜；

9、关于本文

10、参考文档

Mandrake 10.1 - Proftpd + MySQL authentication + Quotas Howto

11、相关文档

By 北南南北 at 2006/01/02 - 20:27 | 文件服务器 | 参与评论 | 1574 阅读参考文档 Mandrake 10.1 - Proftpd + MySQL authentication + Quotas Howto

Author: Stephen Khoo

– Please read the notes on the main Mandrake install guide first! –

Updated: 11 March 2005 Summary This Howto describes how to build and configure proftpd (v 1.2.9) using a MySQL database for authentication and quota management. The base machine is setup using Mandrake 10.1, so we assume you have installed the base set of MySQL rpms including the development libraries and headers which places the headers in /usr/include/mysql and the libraries in /usr/lib. Please see our earlier Howto guide: Installation

The easiest is to install proftpd from the Mandrake rpms and overwrite /usr/sbin/proftpd with a symlink to the one you create here in /usr/local/sbin. You can't use the rpm version of proftpd as it doesn't have MySQL support built in.

You should be able to copy and paste the text in the yellow boxes straight into your Mandrake PC. We have tested this on a clean server and it all seems to work fine. If you have any problems use the Discussion Forum on this site to let us know. You can always let us know if this is useful anyway! Get the sources

# as user cd mkdir src cd src

# Proftpd source - note that this contains mod_sql and mod_quotatab wget ftp://ftp.proftpd.org/distrib/source/proftpd-1.2.10.tar.gz

Note: make sure that your path to the installation directory does not contain spaces in any of the directory names as this confuses the ./configure script totally! Unpack sources

# as user cd mkdir proftpd cd proftpd

# Unpack sources tar -xzf ../src/proftpd-1.2.10.tar.gz

Compile and install

This assumes that the MySQL libraries and headers are placed where Mandrake puts them (i.e. /usr/lib and /usr/include/mysql respectively). If you have used a different MySQL installation you just need to check that you get the path to these MySQL files correct.

cd proftpd-1.2.10

./configure --with-modules=mod_sql:mod_sql_mysql:mod_quotatab:mod_quotatab_sql / --with-includes=/usr/include/mysql / --with-libraries=/usr/lib

make su make install

# make a symbolic link to the installed binary in /usr/sbin - overwriting the one from the rpm if installed # so that the startup script works OK

mv /usr/sbin/proftpd /usr/sbin/proftpd.rpmsave ln -s /usr/local/sbin/proftpd /usr/sbin/proftpd

Unix Configuration

As far as UNIX is concerned we use one real group account ftpgroup (id 5500) as every user will be jailed to their home directory using the 'DefaultRoot ~/' directive. We set up one main ftpuser account (id: 5500) with this group and a guest account ftpguest (id 5501).

groupadd -g 5500 ftpgroup adduser -u 5500 -s /bin/false -d /bin/null -c "proftpd user" -g ftpgroup ftpuser

Note: I also add a guest user so that your client sites can have a place for their guest users to transfer files. Just run:

adduser -u 5501 -s /bin/false -d /bin/null -c "proftpd guest" -g ftpgroup ftpguest

add a password and then remember to add them to your database as members of the ftpgroup by adding a new record there.

To add a guest user, add a record for a user with userid of 5501 and set the permissions on the folders you want to share like this: chmod 755 From_folder chmod 731 To_folder leaving the user and group as ftpuser.ftpgroup

Their clients will now only be able to download from the From_folder and drop files, but not list files in the To_folder.

MySQL Configuration

Create the database tables in MySQL for authentication and logging.

We use a database called ftpdb which we access using a MySQL user proftpd with password 'password'. (Clearly substitute your own password in here).

You need to log into MySQL using your root account and password.

mysql -u root -p create database ftpdb; grant select, insert, update on ftpdb.* to proftpd@localhost identified by 'password';

use ftpdb;

# # Table structure for table `ftpgroup` #

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';

# # Dumping data for table `ftpgroup` #

INSERT INTO `ftpgroup` VALUES ('ftpgroup', 5500, 'ftpuser'); INSERT INTO `ftpgroup` VALUES ('ftpgroup', 5500, 'ftpguest');

# --------------------------------------------------------

# # Table structure for table `ftpquotalimits` #

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;

# --------------------------------------------------------

# # Table structure for table `ftpquotatallies` #

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;

# # Table structure for table `ftpquotatallies` #

CREATE TABLE `ftpquotatallies` ( `name` varchar(30) NOT NULL default '', `quota_type` enum('user','group','class','all') NOT NULL default 'user', `bytes_in_used` float NOT NULL default '0', `bytes_out_used` float NOT NULL default '0', `bytes_xfer_used` float 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;

# --------------------------------------------------------

# # Table structure for table `ftpuser` #

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';

INSERT INTO `ftpuser` VALUES (1, 'testaccount', 'ftppasswd', 5500, 5500, '/home/testdomain.com', '/sbin/nologin',0,'','');

exit;

Notes:

* The group table lists the members of each group. This does not need any records for a simple setup. * The user table lists each of your ftp users and records their stats and is used for authentication and pointing proftpd to the correct home directory. Using the directive 'asdfasf' we make proftpd create the home directory if one does not already exist. * The quotalimits table is used to se the current quota for the user matching its entry. * The quotatallies table is used by proftpd to keep track of the user's quotas and will be filled if there is a matching userfield 'name' when a user logs in. * We have added a user test account just called "testaccount" to check things are working ok.

Field Explanations:

quotalimits

name - username quota_type - user, group, class, all (we use user) per_session - true or false (we use true) limit_type - quota limit type - hard or soft (we use hard) bytes_in_avail - upload limit in bytes - allowed bytes on disk (eg diskquota) bytes_out_avail - download limit in bytes - allowed bytes a user can download bytes_xfer_avail - allowed bytes a user can transfer in/out files_in_avail - upload limit in files - allowed number of uploaded files files_out_avail - allowed number of downloaded files files_xfer_avail - allowed number of files a user can transfer in/out

quotatallies

name quota_type bytes_in_used - upload tally in bytes bytes_out_used - download tally in bytes bytes_xfer_used - transfer tally in bytes files_in_used - upload tally in files files_out_used - download tally in files files_xfer_used - transfer tally in files

If a value of any limit field is set to '0' it is unlimited.

You can read the documentation that comes with mod_quotatab for mysql here.

Author's Note:

You can test a 15MB quota limit on 'testaccount' by inserting this record into 'quotalimits': INSERT INTO quotalimits VALUES('testaccount','user','true','hard','15728640','0','0','0','0','0');

I think you have to log in with this record in place before it works. It also will allow you to upload, but then deletes the file if you are over the limit. This is a bit irritating because on normal user disk quotas, you get a message as you try to upload, not after. So you might get some unhappy users who don't know why they can't upload. Also the message that you have exceeded the limit appears in the ftp client transcript and your users will miss this if they don't show transcripts.

On a related note, I can seem to get any joy out of the quotatallies stuff. When I get more time, I may investigate this. In the meantime, if any of you find out what is going on there, it would be cool if you leave a note in the forum here - then everyone can benefit.

Proftpd Config File

Here is our complete configuration file setup with MySQL and quota support. Keep a copy of the installation one in /usr/local/etc/ and replace with one like this - change to suit your ServerName and ServerAdmin and MySQL database login details.

ServerName "Khoosys Proftpd Server" ServerType Standalone ServerAdmin stephen@khoosys.net

# Hide as much as possible to outside users ServerIdent on "Welcome to the Khoosys FTP server. Please login..." DeferWelcome on

DefaultServer on

# Allow FTP resuming. # Remember to set to off if you have an incoming ftp for upload. AllowStoreRestart 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

# 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

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

# Normally, we want files to be overwriteable.

AllowOverwrite on

# The passwords in MySQL are encrypted using CRYPT SQLAuthTypes Plaintext Crypt SQLAuthenticate users* groups*

# used to connect to the database # databasename@host database_user user_password SQLConnectInfo ftpdb@localhost proftpd password

# Here we tell ProFTPd the names of the database columns in the "usertable" # we want it to interact with. Match the names with those in the db SQLUserInfo ftpuser userid passwd uid gid homedir shell

# Here we tell ProFTPd the names of the database columns in the "grouptable" # we want it to interact with. Again the names match with those in the db SQLGroupInfo ftpgroup groupname gid members

# set min UID and GID - otherwise these are 999 each SQLMinID 500

# create a user's home directory on demand if it doesn't exist SQLHomedirOnDemand on

# Update count every time user logs in SQLLog PASS updatecount SQLNamedQuery updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" ftpuser

# Update modified everytime user uploads or deletes a file SQLLog STOR,DELE modified SQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuser

# 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_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}'" 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

RootLogin off RequireValidShell off

Proftpd startup file

As you can see we start proftpd as a standalone process and not as an xinetd service. We use the proftpd startup script provided by the rpm distribution. You can download it here. Testing

Start the service using /etc/rc.d/init.d/proftpd and login to the ftp server using 'testaccount' and 'ftppasswd'. If the home directory '/home/testdomain.com' does not already exist it will be created.

Have fun!! A few useful tips

If you want to check that queries are coming through to MySQL, turn on logging by adding a line to your MySQL configuration file such as: log=/var/lib/mysql/mysql.log

Remember to turn off logging when you are done as it will slow things down with it on!

(Note that the MySQL config file is usually '/etc/my.cnf'. If you don't have one MySQL usually provides a set which you can copy according to the size of server you want to run. For example, a huge server can use : '/usr/share/mysql/my-huge.cnf')