WebMail 
LiveChat 
Virtual Hosting With Proftpd And MySQL (Incl. Quota) For Arch Linux
Version 1.0
Original author: Falko Timme
Adapted text for Arch Linux and some other changes by Dretech.
This document describes how to install a Proftpd server that uses
virtual
users from a MySQL database instead of real system users. This is much
more performant and allows to have thousands of ftp users on a single
machine. In addition to that I will show the use of quota with
this setup.
For the
administration of the MySQL database you can use web based tools like phpMyAdmin
which will also be installed in this howto. phpMyAdmin is a comfortable
graphical interface which means you do not have to mess around with the
command line.
This
tutorial is based on Arch Linux and is an apdaption of the original
how to for Debian Sarge. You should
already have set up a basic Arch Linux system. On other distributions
like
SuSE, Fedora, Mandriva, etc. only the Proftpd
installation is different; the configuration of Proftpd should apply to
these distributions as well.
This
howto is meant as a practical guide; it does not cover the theoretical
backgrounds. They are treated in a lot of other documents in the web.
This
document comes without warranty of any kind! I want to say that this is
not the only way of setting up such a system. There are many ways of
achieving this goal but this is the way I take. I do not issue any
guarantee that this will work for you!
1 Install Apache, MySQL And phpMyAdmin
For installation instructions of Apache and/or MySQL see https://wiki.archlinux.org/index.php/Apache.
For installation instructions of phpMyAdmin see https://wiki.archlinux.org/index.php/Phpmyadmin.
2 Install Proftpd With MySQL support
In Arch Linux MySQL support is compiled in Proftpd. Install Proftpd
with de command:eval(ez_write_tag([[580,400],’howtoforge_com-medrectangle-4′,’ezslot_2′,108,’0′,’0′]));
pacman -S proftpdThen we create an ftp group (“ftpgroup“)
and user (“ftpuser“)
that all our virtual users will be mapped to. Replace the group- and
userid 2001
with a number that is free on your system:
groupadd -g 2001 ftpgroup
useradd -u 2001 -s /bin/false -d /bin/null -c “proftpd user” -g
ftpgroup ftpuser
3 Create The MySQL Database For Proftpd
Go to phpMyAdmin (http://localhost/phpmyadmin
or http://192.168.2.5/phpmyadmin (if 192.168.2.5 is the IP-number of
the FTP-server)) with username root and the password you gave in step 1.
Click in the upper side of the screen on “SQL” and paste the query
below in the SQL window:eval(ez_write_tag([[300,250],’howtoforge_com-box-4′,’ezslot_7′,110,’0′,’0′]));
create database ftp;
GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO ‘proftpd’@’localhost’
IDENTIFIED BY ‘password’;
GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO
‘proftpd’@’localhost.localdomain’ IDENTIFIED BY ‘password’;
FLUSH PRIVILEGES;
Replace the string password with whatever password
you want to use for the MySQL user proftpd. Still in phpMyAdmin, select
in the left side of the screen for the database ftp.
Then we create the database tables: We need to copy the SQL commands
below and paste it in the query window of phpMyAdmin:
CREATE TABLE `ftpgroup` ( `groupname` varchar(16)
NOT NULL DEFAULT ”,
`gid` smallint(6) NOT NULL DEFAULT ‘9001’,
`members` varchar(16) NOT NULL DEFAULT ”,
KEY `groupname` (`groupname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=’ProFTP group table’;
CREATE TABLE `ftpquotalimits` ( `name` varchar(30) NOT NULL DEFAULT ”,
`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` bigint(10) unsigned NOT NULL DEFAULT ‘0’,
`bytes_out_avail` bigint(10) unsigned NOT NULL DEFAULT ‘0’,
`bytes_xfer_avail` bigint(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’, PRIMARY KEY
(`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ftpquotatallies` ( `name` varchar(30) NOT NULL DEFAULT ”,
`quota_type` enum(‘user’,’group’,’class’,’all’) NOT NULL DEFAULT
‘user’, `bytes_in_used` bigint(10) unsigned NOT NULL DEFAULT ‘0’,
`bytes_out_used` bigint(10) unsigned NOT NULL DEFAULT ‘0’,
`bytes_xfer_used` bigint(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’
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
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 ‘9001’,
`gid`smallint(6) NOT NULL DEFAULT ‘9001’,
`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’,
`email` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `userid` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=’ProFTP user table’;
4 Configure Proftpd
Open /etc/proftpd.conf and add the
following lines to it:
DefaultRoot ~
# The passwords in MySQL are encrypted using CRYPT
SQLAuthTypes Crypt
SQLAuthenticate users* groups*
# used to connect to the database
# [email protected] database_user user_password
SQLConnectInfo [email protected] proftpd password PERCONNECTION
# 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
CreateHome on 770 dirmode 770
# 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 Gb
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
If you want to see a banner to see the use/available space after LIST, then add the lines below (by Martin
Mrajca) to /etc/profptd.conf:
SQLNamedQuery gettally SELECT "ROUND((bytes_in_used/1073741824),2) FROM ftpquotatallies WHERE name='%u'"
SQLNamedQuery getlimit SELECT "ROUND((bytes_in_avail/1073741824),2) FROM ftpquotalimits WHERE name='%u'"
SQLNamedQuery getfree SELECT "ROUND(((ftpquotalimits.bytes_in_avail-ftpquotatallies.bytes_in_used)/1073741824),2) FROM ftpquotalimits,ftpquotatallies WHERE ftpquotalimits.name = '%u' AND ftpquotatallies.name = '%u'"
SQLShowInfo LIST "226" "Used %{gettally}GB from %{getlimit}GB. You have %{getfree}GB available space."
Restart Proftpd:
/etc/rc.d/proftpd restart
5 Populate The Database And Test
To populate the database you can use phpMyAdmin. First we create an
entry in the table ftpgroup. It contains
the groupname, the groupid and the username of the ftp group/user we
created at the end of step two (replace the groupid appropriately if
you use another one than 9001):
INSERT INTO `ftpgroup` (`groupname`, `gid`,
`members`) VALUES (‘ftpgroup’, 9001, ‘ftpuser’);
Now we are done with the table ftpgroup.
We do not have to create further entries here. Whenever you create a
new virtual ftp user, you do this in the tables ftpquotalimits and ftpuser. So let us create our first
user (we
are still in the query window of phpMyAdmin):
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’, ‘false’, ‘hard’, 1073741824, 0, 0, 0, 0, 0);
INSERT INTO `ftpuser` (`userid`, `passwd`, `uid`, `gid`, `homedir`,
`shell`, `count`, `accessed`, `modified`, `email`) VALUES
(‘exampleuser’, encrypt(‘secret’), 2001, 2001, ‘/srv/ftp/exampleuser’,
‘/sbin/nologin’, 0, ‘0000-00-00 00:00:00’, ‘0000-00-00 00:00:00’,
‘[email protected]’);
(Do not forget to replace the group- and userid 2001 appropriately in the last INSERT statement
if you are using other values than in this tutorial!)
Now open your FTP client program on your work station (something like
WS_FTP or SmartFTP if you are on a Windows system) and try to connect.
As hostname you use the IP address of the system, the username is exampleuser, and the password is secret (if you have not chosen an other password).
If you are able to connect – congratulations! If not, something went
wrong.
Now, if you run
ls -l /srv/ftpyou should see that the directory /srv/ftp/exampleuser (exampleuser’s ftp directory) has been automatically created,
and it belongs to ftpuser and ftpgroup (the user/group we created at the end
of step two).
5.1 FTP user management
For managing the virtual FTP users I made a simple PHP script. You can
download the script here. You only
have to unpack the zipfile and copy ftpusermanagement.php
to the /srv/http directory of your Arch
Linux system. Open ftpusermanagement.php
in your web browser, for
example http://192.168.2.5/ftpusermanagement.php
(if IP number 192.168.2.5 is
the IP number is your Arch Linux system). You now are ably to add
ftp-users, edit ftp-users and delete ftp-users in a simple way. If you
delete a ftp-user you have to delete the directory of the user
manually. In the webinterface of the php-script I also mention some
idaes for
future posibilities, but at the moment I am going to spend my time in
other projects. The copyright of the script is the GPL licence, so you
are free to improve and extend the script.
5.2 Database administration
If you want to manage your virtual FTP users without webinterface, you
only have to create entries in the
tables ftpquotalimits and ftpuser. So below you can find an explanation of
the columns of
these tables here:
ftpuser Table:
The important columns are these (the others are handled by MySQL or
Proftpd automatically, so do not fill these manually!):
- userid: The name of the virtual
Proftpd user (e.g. exampleuser). - passwd: The unencrypted (i.e.,
clear-text) password of the user. - uid: The userid of the ftp user you
created at the end of step two (e.g. 9001). - gid: The groupid of the ftp group you
created at the end of step two (e.g. 2001). - homedir: The home directory of the
virtual Proftpd user (e.g. /srv/ftp/exampleuser).
If it does not
exist, it will be created when the new user logs in the first time via
FTP. The virtual user will be
jailed into this home directory, i.e., he cannot access other
directories outside his home directory. - shell: It is ok if you fill in
/sbin/nologin here by default.
ftpquotalimits Table:
The important columns are these (the others are handled by MySQL or
Proftpd automatically, so do not fill these manually!):
- name: The name of the virtual Proftpd
user (e.g. exampleuser). - quota_type: user or group. Normally,
we use user here. - per_session: true or false. true
means the quota limits are valid only for a session. For example, if
the user has a quota of 15 MB, and he has uploaded 15 MB during the
current session, then he cannot upload anything more. But if he logs
out and in again, he again has 15 MB available. false means, that the
user has 15 MB at, no matter if he logs out and in again. - limit_type: hard or soft. A hard
quota limit is a never-to-exceed limit, while a soft quota can be
temporarily exceeded. Normally you use hard here. - bytes_in_avail: Upload limit in bytes
(e.g. 15728640 for 15 MB). 0 means unlimited. - bytes_out_avail: Download limit in
bytes. 0 means unlimited. - bytes_xfer_avail: Transfer limit in
bytes. The sum of uploads and downloads a user is allowed to do. 0
means unlimited. - files_in_avail: Upload limit in
files. 0 means unlimited. - files_out_avail: Download limit in
files. 0 means unlimited. - files_xfer_avail: Tranfer limit in
files. 0 means unlimited.
The ftpquotatallies table is used by
Proftpd internally to manage quotas so you do not have to make entries
there!
References
- Virtual Hosting With Proftpd And MySQL (Incl. Quota) for Arch
Linux: http://www.howtoforge.com/proftpd_mysql_virtual_hosting - Installation Apache and/or MySQL: https://wiki.archlinux.org/index.php/Apache
- Installation phpMyAdmin: https://wiki.archlinux.org/index.php/Phpmyadmin
- Mandrake 10.1 – Proftpd + MySQL authentication + Quotas Howto: http://www.khoosys.net/single.htm?ipg=848
Links
- Proftpd: http://www.proftpd.org/
- MySQL: http://www.mysql.com/
- phpMyAdmin: http://www.phpmyadmin.net/
