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.

Log in as your user account to do the installation. Instructions in bold should be done as root.

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