Proftpd mySQL Authentication & Quotas on FreeBSD

Tonight I tried setting up Proftpd with mySQL authentication on a development box running FreeBSD 4.10. I got it to work using the steps below, but some security concerns arise from using mySQL user accounts without a system account behind them. Since this setup uses one FTP account to create user home directories and upload files, a compromise to this FTP user would cause the attacker to gain access to all FTP user home directories. I guess it just depends on how much you trust the DefaultRoot directive in Proftpd. I run Proftpd in its own chroot environment ( http://jim.centerfuse.net/projects/proftpd_chroot/ ) in addition to using DefaultRoot, so I'm used to feeling pretty safe with my Proftpd install. Anyway, here's how I did the install/configuration

1. install proftpd-mysql from the ports with WITH_QUOTA set:
cd /usr/ports/ftp/proftpd-mysql/
env WITH_QUOTA=yes make
env WITH_QUOTA=yes make install

2. Add the global proftpd user & Proftpd group to your system.
I used uid & gid 5500 simply because that's what was used at one of the sites I was referencing (listed below).

pw groupadd -n Proftpd -g 5500
pw useradd proftpd -u 5500 -g Proftpd -s /sbin/nologin -d /dev/null -c "Proftpd User"

3. Create the mySQL database

create database proftpd;
grant all on proftpd.* to 'proftpd'@'localhost' identified by 'password'

( change 'password' to something secret! )

4. Create the mySQL tables for the users & quota

create table proftpdUsers (

sqlUID int unsigned auto_increment not null,
userName varchar(30) not null unique,
passwd varchar(80) not null,
uid int unsigned not null unique,
gid int unsigned not null,
homedir tinytext,
shell tinytext,
primary key(sqlUID)

) ;

create table proftpdGroups (

sqlGID int unsigned auto_increment not null,
groupName varchar(30) not null unique,
gid int unsigned not null unique,
members tinytext,
primary key(sqlGID)
);

CREATE TABLE proftpdQuotaLimits (
name VARCHAR(30),
quota_type ENUM("user", "group", "class", "all") NOT NULL,
per_session ENUM("false", "true") NOT NULL,
limit_type ENUM("soft", "hard") NOT NULL,
bytes_in_avail FLOAT NOT NULL,
bytes_out_avail FLOAT NOT NULL,
bytes_xfer_avail FLOAT NOT NULL,
files_in_avail INT UNSIGNED NOT NULL,
files_out_avail INT UNSIGNED NOT NULL,
files_xfer_avail INT UNSIGNED NOT NULL
);


CREATE TABLE proftpdQuotaTallies (
name VARCHAR(30) NOT NULL,
quota_type ENUM("user", "group", "class", "all") NOT NULL,
bytes_in_used FLOAT NOT NULL,
bytes_out_used FLOAT NOT NULL,
bytes_xfer_used FLOAT NOT NULL,
files_in_used INT UNSIGNED NOT NULL,
files_out_used INT UNSIGNED NOT NULL,
files_xfer_used INT UNSIGNED NOT NULL
);

5. Add a test user to the proftpd database

(assumes /home/ftp is where you keep your ftp users. Otherwise, change the homedir location). This is certainly not a necessary step, but you should probably check to see if your configuration is working. You can delete this user later.

insert into proftpdUsers values ( 0, 'test', 'test', 5500, 5500, '/home/ftp/test', '/sbin/nologin' );

6. Set your proftpd configuration to use the mySQL authentication and quotas:
(NOTE: this is not a complete configuration file, it's basically just the default config file with mySQL auth & quotas added, but note that the User and Group directives are the user & group we added in step 2. )

MaxInstances 30

# Set the user and group under which the server will run.
User proftpd
Group Proftpd

# 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

# Bar use of SITE CHMOD by default
<Limit SITE_CHMOD>
DenyAll
</Limit>

DenyAll

# 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 proftpd@localhost proftpd yourdatabasepassword

# 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 proftpdUsers userName 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 proftpdGroups groupName gid members

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

#============
# User quotas
# ===========
QuotaEngine on
QuotaDirectoryTally on
QuotaDisplayUnits Mb
QuotaShowQuotas on

# create a user's home directory on demand if it doesn't exist
SQLHomedirOnDemand 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 proftpdQuotaLimits 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 proftpdQuotaTallies 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}'" proftpdQuotaTallies

SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" proftpdQuotaTallies

QuotaLimitTable sql:/get-quota-limit
QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally


Thanks to the following sites for being great references, and providing most of the information in this post:
http://www.khoosys.net/single.htm?ipg=848
http://www.castaglia.org/proftpd/modules/mod_quotatab_sql.html