Proftpd + Mysql + TLS

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

  • log in to mysql server

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@addura.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 <IfModule mod_quotatab_sql.c> 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');

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

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

<IfModule mod_ban.c>

BanEngine on

BanTable /path_to_table (after restart table is cleaned)

BanLog /path_to_ban.log

BanMessage "You has been banned/Zostałeś zablokowany for/na 30 minutes/minut!!!"

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

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

</IfModule>

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

This site uses cookies. Some of the cookies we use are essential for parts of the site to operate and have already been set. You may delete and block all cookies from this site, but parts of the site will not work.