使用 MySQL 來當 Proftpd 的帳號管理設定(Debian Linux)

一般建 FTP 帳號, 都是直接在系統上直接開個使用者的帳號, 但是如果是要開帳號給安全觀念不夠的人, 還是另外找個方法來做.

所以想到的是, FTP 帳號獨立出來, 讓 MySQL 來管理 FTP 的帳號, 而且 FTP 順便限制存取的範圍, 至少會比較安全一點(另外就是上傳後的程式執行問題, 就不在此討論).

在 Debian 上面跑 Proftpd, 使用 MySQL 來管理帳號已經有不少人做過這種事了~ 下述三篇文章都有寫, 在此主要是參考第三篇:

• [轉貼]Proftpd + MySQL 帳號認証 - 這篇試用行不通, 應該是設定上 DB 對應有些問題, 不過沒空詳查, 可以當參考用.
• Virtual Hosting With Proftpd And MySQL (Incl. Quota)
• Virtual Hosting With Proftpd And MySQL (Incl. Quota) On Debian Etch - 主要就是參考這篇, 下述也是照這篇來做整理

安裝 MySQL Apache Proftpd 和 proftpd-mysql

• apt-get install mysql-server mysql-client libmysqlclient15-dev apache2 proftpd-mysql

設定 FTP 使用的 User 和 Group

1. groupadd -g 2001 ftpgroup
2. 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 登入後的目錄環境

1. mkdir /home/www.demo.com
2. mkdir /home/www.demo2.com
3. chown ftpuser:ftpgroup /home/www.demo.com
4. 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 "count=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即可

快速 開帳號 的步驟

1. 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, '', '');
2. (修改 3, 'USERNAME', 'PASSWORD', 2003, '/home/www.demo.com' 這些值即可.)
3. mkdir /home/www.demo.com # 建目錄
4. chown ftpuser:ftpgroup /home/www.demo.com # 改權限
5. 這樣子就完成囉~

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_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

  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 就完成囉~