Linux教程網 >> Linux綜合 >> Linux資訊 >> Linux文化 >> ProFTPD 支持MySQL數據庫添加虛擬用戶認證及Quotas(磁盤限額)

ProFTPD 支持MySQL數據庫添加虛擬用戶認證及Quotas(磁盤限額)

日期：2017/2/27 12:08:50 编辑：Linux文化

作者：北南南北來自：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 [email protected] #這是管理員信箱，自己來寫； 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插入紀錄的過程；我們上面分析了表的結構；那我們就按其要求來插入用戶紀錄；

我們看看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 [email protected]:/> 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 [email protected] 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 [email protected]

# 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')