How To Set Up ProFTPD with a MySQL Backend on Ubuntu 12.10

Introduction

This article will guide you thorough setting up ProFTPD on Ubuntu with a MySQL backend for users and groups.

Warning: FTP is inherently insecure! Consider configuring ProFTPd to use SFTP, a secure alternative to FTP implemented under SSH.

Prerequisite

This tutorial presumes you have already installed and configured MySQL and phpMyAdmin.

If you have not, you can follow How to Install and Secure phpMyAdmin on Ubuntu 12.04.

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

Go to your phpMyAdmin installation and log in.

Select the "Privileges" tab and click "Add user".

Now you will need to select the desired username, I suggest "ftpd" (without the quotes) and generate the password for the user. Store that information, we will need it later.

In the "Database for user" section, select "Create database with same name and grant all privileges".

This will create a user and a database that ProFTPD can use. Now we need to populate the database with some tables. On the left, you should now see a new database (that has the same name as the user, in our case "ftpd") - click on it.

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='ProFTP 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 '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `userid` (`userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='ProFTP user table';

This will create the tables we need.

Create Your User

Click on the "ftpuser" table in the left column and then on the "Insert" tab.

This allows you to insert a user. Populate the 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 server shell) 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`

Of course replace "password" with your/users desired password.

Here you can see some other field that are self explanatory and are usually used later (login count, last logged in...).

There, you have the correct database, now we just need to configure ProFTPD to use it.

Configure ProFTPD

First edit the ProFTPD configuration file:

sudo nano /etc/proftpd/proftpd.conf

Change the ServerName directive 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) add:

DefaultRoot ~

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

Include /etc/proftpd/sql.conf

RequireValidShell         off

Now edit the sql configuration file:

sudo nano /etc/proftpd/sql.conf

So it'll look like this (I suggest you delete everything that is in the file now):

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'" ftpuser

SqlLogFile /var/log/proftpd/sql.log

Of course 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  ftpd@localhost ftpd password

To enable the MySQL modules in ProFTPD edit:

sudo nano /etc/proftpd/modules.conf

And uncomment (or simply add) the two lines:

LoadModule mod_sql.c
LoadModule mod_sql_mysql.c

Now we need to restart ProFTPD:

sudo service proftpd restart

Test it

Now you can use your favourite FTP client (such as FileZilla) to connect to the server with the sample user you added before.

Common issues

• The user cannot log in - Always check that you pasted the generated user password line correctly
• The user cannot write to the home directory - You should set the correct permissions on the home directory:
  • The permissions have to be set to the uid and guid fields you populated when you added the user
  • For example, using default values:

    sudo chown 5500:5500 /path/to/home/directory/

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

AuthOrder                       mod_sql.c mod_auth_unix.c
#mod_auth_pam.c*

Job for proftpd.service failed because the control process exited with error code. See "systemctl status proftpd.service" and "journalctl -xe" for details.

/var/log/proftpd/proftpd.log
/var/log/proftpd/controls.log
/var/log/proftpd/sql.log

Status:       	Connecting to mydomain...
Response: 	fzSftp started
Command: 	open "ftpuser@mydomain" 22
Command: 	Trust new Hostkey: Once
Command: 	Pass: *******************************
Error:         	Authentication failed.
Error:         	Critical error: Could not connect to server