Proftpd + Mysql + TLS – old – Adrian Danek

Proftpd installation

Please install all necessary packets together with dependencies(mysql server is already installed)

apt-get install proftpd-basic proftpd-doc proftpd-mod-mysql

I had to install also libfile-copy-recursive-perl and update-inetd

During installation Proftpd asks us how to run ftp server(via inetd or as a standalone. The rule is simple,if server will be less loaded choose inetd,otherwise “standalone”.

Create user and group on which we will mapp virtual user

groupadd -g 3000 ftpgroup

useradd -u 3000 -s /bin/false -d /bin/false -d /bin/null -c “FTP User” -g ftpgroup ftpuser

Mysql configuration

mysql -u root -p

use mysql;

Create database danpol_ftp

create database danpol_ftp;

Create user responsible for quering danpol_ftp database and give him all necessary permissions

GRANT SELECT, INSERT, UPDATE, DELETE ON danpol_ftp.* TO ‘danpol_user’@’localhost’ IDENTIFIED BY ‘haslo_danpol_usera’;

Reload privileges.

flush privileges;

Create tables in our database

ftpgroup.

use danpol_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=’danpol_ftp group 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;

ftpquotatallies

CREATE TABLE ftpquotatallies (

name varchar(30) default NULL,

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;

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=’danpol_ftp user table’;

Create group and user

use danpol_ftp;

INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES (‘ftpgroup’,3000, ‘ftpuser’);

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 (‘test’, ‘user’, ‘true’, ‘hard’, 1000000, 0, 0, 0, 0, 0);

INSERT INTO `ftpuser` (`id`,`userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`,`modified`) VALUES (‘1’, ‘test’, ‘testpwd’, 3000, 3000, ‘/home/ftp/test’, ‘/sbin/nologin’, 0, ‘ ‘, ‘ ‘);

Legend:

quota_type – quota type(per user in my case)

per_session – YES/NO(per session does not updating ftpquotatallies table)

limit_type – hard or soft(hard removes file automatically the limit is reached, soft allows to finish copy that file only.

bytes_in_avail – quota on disc space on FTP server

bytes_out_avail – quota on data downloaded from FTP server

bytes_xfer_avail – limit of data user can send to/from FTP server(transfer)

files_xfer_avail – limit of files user can send to/from FTP server(transfer)

files_in_avail – limit of files user can upload onto FTP server

files_out_avail – limit of files user can download from FTP server

bytes_in_used – quantity of data uploaded onto FTP server

bytes_out_used – quantity of data downloaded from FTP server

bytes_xfer_used – quantity of data uploaded/downloaded(transfer)

files_in_used – quantity of files uploaded onto FTP server

files_out_used – quantity of files downloaded from FTP server

files_xfer_used – quantity of files uploaded/downloaded(transfer)

Value “0” means unlimited(no quoata)

Proftpd configuration

In /etc/proftpd/proftpd.conf remove “#” to jail users in their catalogue

DefaultRoot ~

Change name of your FTP server

ServerName “WELCOME TO FTP SERVER”

Set up ftp administrator e-mail addres

ServerAdmin support@domain.net”

Disable shell requirements for log in by removing “#”

RequiredValidShell off

If your firewall does not inspect FTP protocol set ports for pasive mode

PassivePorts 49152 49200

Remember to open ports on firewall!!!

If server is benind the nat enable MasqueradeAddress option

MasqueradeAddress public_ip (before natting)

Enable external authentication( in our case “sql”)

Include /etc/proftpd/sql.conf

Enable option to automatically create catalogue after succesfully login

CreateHome on

Catalogue will have permission – 0700.

Enable Quota

In file /etc/proftpd/proftpd.conf in section <Ifmodule_mod_quotatab.c> add:

QuotaEngine on

QuotaLOG /var/log/proftpd/quota.log

QuotaDirectoryTally on

QuotaDisplayUnits Mb

QuotaShowQuotas on

In file /etc/proftpd/modules.conf uncomment lines:

LoadModule mod_quotatab_sql.c

LoadModule mod_sql.c

LoadModule mod_sql_mysql.c

Enable mysql module in /etc/proftpd/sql.conf by uncommenting appropiate lines and adding the following:

SQLBackend mysql

SQLEngine on

SQLAuthenticate on

SQLAuthTypes Plaintext Crypt

SQLConnectInfo danpol_ftp@localhost danpol_user haslo_danpol_usera

SQLUserInfo ftpuser userid passwd uid gid homedir shell

SQLGroupInfo ftpgroup groupname gid members

SQLAuthenticate users* groups*

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

In file /etc/proftpd/sql.conf in directive add

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.”

Create group and user

use danpol_ftp;

INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES (‘ftpgroup’,3000, ‘ftpuser’);

When you are adding next user repeat second and third point.

Create main catalogue for ftp,e.g.. at /home/ftp and set up permission

chown -R ftpuser:ftpgroup /home/ftp

chmod 0775 /home/ftp

Restart proftpd and check if it is working as aspected…should be success!

Proftpd + TLS means FTPS

EnableTLS poprzez in /etc/proftpd/proftpd.conf

Include /etc/proftpd/tls.conf

Uncomment following lines in /etc/proftpd/tls.conf

TLSEngine on

TLSLog /var/log/proftpd/tls.log

TLSProtocol SSLv23

Set path to your key and certificate in /etc/proftpd/tls.conf

TLSRSACertificateFile /path_to_certificate

TLSRSACertificateKeyFile /path_to_key

If your firewall does not inspect FTPS set ports for passive mode

PassivePorts 49152 49200

Remember to open those ports on firewall!!!

If server is behind the nat enable MasqueradeAddress option

MasqueradeAddress public_ip (before natting)

If you want to force FTPS on users enable this option

TLSRequired on

Proftpd + Ban

Module “mod_ban.c” is enabled by default so the only things we have to do are adding directive in /etc/proftpd/proftpd.conf and add settings

BanEngine on

BanTable /path_to_table (after restart table is cleaned)

BanLog /path_to_ban.log

BanMessage “You has been banned/Zostales zablokowany for/na 30 minutes/minut!!!”

BanOnEvents MaxLoginAttempts 6/00:01:00 00:30:00

BanOnEvents ClientConnectRate 6/00:01:00 00:30:00

Settings above enable ban for host for 30 minutes if within 1 minute one of the BanOnEvent occured 6 times”.

My article has been written based on howto from site HOWTOFORGE using the latest version of Debian(Squeeze) however I put here my comments, settings and suggestion.Feel free to use it.

_{dzbanek 2011-11-27}