ProFTPD - logging to MySQL database |My Technical Blog

Intro

ProFTPD is a powerfull and versatile FTP server however because of its rare usage, or even because of rare usage of FTP nowadays, it suffers from lack of the support as for the HowTos, forum topics, etc. One of the greatest features of the ProFTPD is the capability of storing logs in the database instead of files that is provided by its built-in mechanisms. My goal was to store all the read/write attempts. I created custom log for that purpose that is then being parsed by the ProFTPD MySQL module to extract the following information:

who attempted to access the FTP server (username + IP)
when did the attempt take place
what exact operation was attempted

However ProFTPD supports many different backends I used the MySQL with default settings.

Note: The following HowTo assumes that you have both ProFTPD and MySQL servers installed and configured. The MySQL server doesn't necessarily need to be installed on the same box as the ProFTPD, but if so it needs to be accessible over the network. In my case both ProFTPD and MySQL were installed on the same box that was running Ubuntu Server 12.04 amd64.

Configuration

1) Install the MySQL module for ProFTPD:

apt-get install proftpd-mod-mysql

2) Add the following line into the main ProFTPD configuration file (/etc/proftpd/proftpd.conf) to include the MySQL module configuration file:

Include /etc/proftpd/sql.conf

3) Add the following lines into the main ProFTPD configuration file to create and define the extended log file that will track all the read / write attempts:

ExtendedLog /var/log/proftpd/ftp_access.log WRITE,READ write
LogFormat write "%h %l %u %t \"%r\" %s %b"

4) Make sure that the following lines are not commented out inside the ProFTPD modules configuration file (/etc/proftpd/modules.conf):

LoadModule mod_sql.c

LoadModule mod_sql_mysql.c

5) Edit the MySQL module configuration file (/etc/proftpd/sql.conf) that it looks like on the example below:

SQLBackend mysql

SQLEngine on

SQLLogFile /var/log/proftpd/sql.log

SQLConnectInfo [database]@[host] [username] [password]

SQLLogFile /var/log/proftpd/ftp_access.log

SQLLog DELE,MKD,RETR,RMD,RNFR,RNTO,STOR,APPE extendedlog

SQLNamedQuery extendedlog FREEFORM "INSERT INTO proftpd_access_log (`IP`, `username`, `time`, `operation`) VALUES ('%a', '%u', NOW(), '%r')"

</IfModule>

First part of the configuration should stay as it is.

Inside the SQLConnectInfo directive you should provide your MySQL server details as for the database name, IP address or FQDN of the MySQL server and finally username and password that has RW access into the database.

The third part of the configuration is responsible for extracting and parsing the content of ProFTPD access log file and putting the values into the database. Unless you don't want to change the expected content of the log described in the Intro section, you shouldn't change this configuration.

6) Create the table in MySQL database for log information storing purpose:

CREATE TABLE proftpd_access_log (id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, IP VARCHAR(50) NOT NULL, username VARCHAR(50), time DATETIME NOT NULL, operation VARCHAR(500) NOT NULL, PRIMARY KEY (id)) COLLATE=utf8_general_ci ENGINE=MyISAM;

7) Finally, restart the ProFTPD server:

/etc/init.d/proftpd restart

You'll notice your proftpd_access_log table keeps filling in:

mysql> select * from proftpd_access_log;

+----+--------------+------------------+---------------------+---------------+

+----+--------------+------------------+---------------------+---------------+

| 1 | 1.2.3.4 | someuser | 2013-05-16 10:48:49 | RETR conf.txt |

+----+--------------+------------------+---------------------+---------------+

1 row in set (0.01 sec)

References

The above HowTo shows only one particular example of using ProFTPD built-in mechanisms of storing logs in the database. The available amount of possibilities and information to store is almost infinit. Please, refer to the following articles for more information:

My Technical Blog