How To Set Up ProFTPD with a MySQL Backend on Ubuntu

5 min readApr 15, 2018

Introduction

This article will guide you through setting up ProFTPD on Ubuntu with a MySQL backend for users and groups. I’m using Ubuntu Desktop, if you’re using Ubuntu Server I guess it’s the same.

Prerequisite

I assume you have installed and configured MySQL and phpMyAdmin.I use the localhost with the IP address 192.168.100.10. These settings might differ for you, so you have to replace them where appropriate.

Install ProFTPD with MySQL support

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

This will install all the required packages. If the installation asks, choose standalone mode.

Configure the database

Select the “User Account” tab and click “Add user account”.

I suggest use“proftpd”(without the quotes) as User Name and generate the password. Store that information, we will need it later. In the “Database for user” section, click “Create database with same name and grant all privileges”. Scroll down then click “Go”.

Then, We will create a user and a database that ProFTPD can use. We need to populate the database with some tables. On the left side you will see a new database, click the database “proftpd”. Now click the SQL tab, paste the following and click “Go”:

CREATE TABLE IF NOT EXISTS `ftpgroup` (
  `groupname` varchar(16) COLLATE utf8_general_ci NOT NULL,
  `gid` smallint(6) NOT NULL DEFAULT '5500',
  `members` varchar(16) COLLATE utf8_general_ci NOT NULL,
  KEY `groupname` (`groupname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='proftpd group table';CREATE TABLE IF NOT EXISTS `ftpuser` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userid` varchar(32) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `passwd` varchar(32) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `uid` smallint(6) NOT NULL DEFAULT '5500',
  `gid` smallint(6) NOT NULL DEFAULT '5500',
  `homedir` varchar(255) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `shell` varchar(16) COLLATE utf8_general_ci NOT NULL DEFAULT '/sbin/nologin',
  `count` int(11) NOT NULL DEFAULT '0',
  `accessed` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userid` (`userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='proftpd user table';

Create Your User

Click on the “ftpuser” table in the left column and then on the “Insert” tab. Insert a user to login FTP. Input userid (which is the username a user will login to FTP) and homedir (the FTP user home — should be present on the system).

Password in the passwd field should be encrypted, to do so you can use this snippet (on your Terminal) to generate the password string you can paste into the passwd field:

/bin/echo "{md5}"`/bin/echo -n "password" | openssl dgst -binary -md5 | openssl enc -base64`

After you type will appear a new password and fill the password in the “passwd”. Then Click “Go”.

Configure ProFTPD

Edit the ProFTPD configuration file:

sudo nano /etc/proftpd/proftpd.conf

Change the ServerName to your desired server name.
If you wish to jail your users to their home directories (so that they can only see their home directories) remove tag/uncomment on:

DefaultRoot ~

To add the SQL configuration and turn off shell validation, add on the bottom of the configuration:

Include /etc/proftpd/sql.confRequireValidShell         off

Then edit the sql configuration file:

sudo nano /etc/proftpd/sql.conf

I suggest you delete everything that is in the file now, or if you need the default configuration please backup first, copy the configuration below and save it.

SQLBackend        mysql#Passwords in MySQL are encrypted using CRYPT
SQLAuthTypes            OpenSSL Crypt
SQLAuthenticate         users groups
# used to connect to the database
# databasename@host database_user user_password
SQLConnectInfo  mysql_database@localhost mysql_user mysql_password
# 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     ftpuser userid 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    ftpgroup groupname gid members# set min UID and GID - otherwise these are 999 each
SQLMinID        500# Update count every time user logs in
SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" ftpuser# Update modified everytime user uploads or deletes a file
SQLLog  STOR,DELE modified
SQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuserSqlLogFile /var/log/proftpd/sql.log

IMPORTANT. You need to replace “mysql_database”, “mysql_user” and “mysql_password” with the correct values you choose/generated earlier in the tutorial. If you followed the suggested naming conventions, the line should look something like this:

SQLConnectInfo  proftpd@localhost proftpd password

To enable the MySQL modules in ProFTPD edit:

sudo nano /etc/proftpd/modules.conf

Remove tag/Uncomment the two lines:

LoadModule mod_sql.c
LoadModule mod_sql_mysql.c

Restart ProFTPD:

sudo /etc/init.d/proftpd stop
sudo /etc/init.d/proftpd start

Permission Folder

In the “deonico” user, I grant folder access to “/ home / revolut1on / ftpsharing / deonico /”. If you want to allow the user to have write access follow these steps:

sudo chown 5500:5500 /home/revolut1on/ftpsharing/deonico/

Troubleshooting

You can view the log files of ProFTPD itself:

tail -f /var/log/proftpd/proftpd.log

And the SQL part of ProFTPD:

tail -f /var/log/proftpd/sql.log

Trial

I will try to login using user “deonico”.

Okay successful. I can access it.