Home arrow Configure arrow Ftp server with Proftpd
Proftpd with mysql, quota and OpenSSL PDF
Thursday, 02 February 2006
This HOWTO is for setting proftpd ftp server on FreeBSD with virtual users using mysql, with quota for user accounts
 and using OpenSSL for authentication.

1. Compile proftpd from /usr/ports/ftp/proftpd with mysql, openssl and quota:
cd /usr/ports/ftp/proftpd
make

2.Create the tables that proftpd will need:
create database proftpd;
use proftpd;
CREATE TABLE users (
userid VARCHAR(30) NOT NULL UNIQUE,
passwd VARCHAR(80) NOT NULL,
uid INTEGER UNIQUE,
gid INTEGER,
homedir VARCHAR(255),
shell VARCHAR(255)
)
CREATE TABLE quotalimits (
      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 quotatallies (
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
);

3.Grant rights to user proftpd for the new tables:

GRANT ALL ON users TO proftpd@localhost IDENTIFIED BY 'password';
GRANT ALL ON quotatallies TO proftpd@localhost IDENTIFIED BY 'password';
GRANT ALL ON quotalimits TO proftpd@localhost IDENTIFIED BY 'password';

4. Edit /usr/local/etc/proftpd.conf file, adding the following:

#---------------------------- proftpd.conf -----------------------------

ServerName "ProFTPD FTP Server"
ServerType standalone
DefaultServer on
Port 21
Umask 022
MaxInstances 30
User nobody
Group nogroup
DefaultRoot ~
AllowOverwrite on
<Limit SITE_CHMOD>
AllowAll
</Limit>

SQLLogFile /var/log/proftpd.log
#the form for SQLConnectInfo will be:
# database[@hostname][:port] mysql_user mysql_password
SQLConnectInfo proftpd@localhost:3306 proftpd password
SQLAuthenticate users
SQLAuthTypes OpenSSL Crypt
RequireValidShell off

QuotaEngine on
QuotaShowQuotas on
QuotaDisplayUnits Mb
QuotaLog /var/log/proftpd.quota

SQLNamedQuery get-quota-limit SELECT "name, quota_type, per_session, \
 limit_type, bytes_in_avail,bytes_out_avail, bytes_xfer

SQLNamedQuery get-quota-tally SELECT "name, quota_type, bytes_in_used, \
 bytes_out_used, bytes_xfer_used, files_in_used, files

SQLNamedQuery update-quota-tally UPDATE "bytes_in_used = bytes_in_used \
 + %{0}, bytes_out_used = bytes_out_used + %{1}, byte

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

QuotaLock /var/run/proftpd/tally.lock
QuotaLimitTable sql:/get-quota-limit
QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally

#---------------------------- end proftpd.conf -------------------------

5. Start proftpd server:
/usr/local/etc/rc.d/proftpd.sh start


Hint:
1. The password for an user can be generated with the following line:
/bin/echo "{md5}"`/bin/echo -n "password" | openssl dgst -binary -md5 \
 | openssl enc -base64`

2.If you get following errors:
-unable to open QuotaLock '/var/run/ftpd/tally.lock':
No such file or directory
-error opening scoreboard: No such file or directory
, just create a directory proftpd in /var/run and create the two files reported
missing in it.

Last Updated ( Thursday, 02 February 2006 )
 

Other BSD Systems

OpenBSD

Misc

Solaris

Polls

Best BSD firewall?