with TLS and MySQL authentication

Using ProFTPd and its SQL module for MySQL, it is possible to give FTP access to non system users. By doing so, one can give access to a Virtual User which is only allowed to use the FTP service. FTP is a very insecure protocol because all passwords and all data are transferred in clear text. By using TLS, the whole communication can be encrypted, thus making FTP much more secure.

This Tutorial will show how to create and populate the user and group tables as well as configure proftpd in order to use the MySQL backend.

Conventions

Commands - White on Black text is meant for copying and pasting.

Output - Yellow on Black text is the common output from a
      command.

Alternate Prompt - Red on Black text is an alternate input
      prompt, not meant for copying, but indicating it
      should be there before continuing.

Please note the icon in the top left corner of the text box.

Requirements:

as we will be running proftpd server and we will authenticate using a mysql database, we need to install both components plus the mysql module for proftpd:

aptitude install mysql-server proftpd-basic proftpd-mod-mysql proftpd-doc

You will be asked a question:

Run proftpd from inetd or standalone? ← standalone

The default root password for MySQL is likely empty. You might want to change it by typing (PLEASE, use a different password.):

mysqladmin -u root password <Your Own Password>

Local user and group:

In our example, the ftp user uid and group gid will be 3001 (this value will be used later on). The local user we are going to create will have /dev/null as home and a shell of /bin/false.

To create the group and user type:

groupadd -g 3001 ftpgroup
useradd -s /bin/false -d /bin/null -c "proftpd user" --uid 3001 -g   ftpgroup ftpuser 

MySQL Database:

Now, it is time to create the database, grant a user permissions and finally create the tables.

The database will be called proftpd_db and user proftp_user will be able to connect from localhost using password proftpd_password.

Create the database and tables:

Create a file called proftpd-mysql.sql:

And fill it with:

CREATE DATABASE proftpd_db;
 
GRANT USAGE ON *.* TO proftpd_user@localhost IDENTIFIED BY 'proftpd_password';
 
GRANT SELECT, INSERT, UPDATE, DELETE ON proftpd_db.* TO 'proftpd_user'@'localhost';
 
USE proftpd_db;
 
CREATE TABLE ftpgroup (
  groupname varchar(16) NOT NULL default '',
  gid smallint(6) NOT NULL default '3000',
  members varchar(16) NOT NULL default '',
  KEY groupname (groupname)
) COMMENT='ProFTPd group table';
 
CREATE TABLE ftpuser (
  id int(10) unsigned NOT NULL auto_increment,
  userid varchar(32) NOT NULL default '',
  passwd varchar(80) NOT NULL default '',
  uid smallint(6) NOT NULL default '3000',
  gid smallint(6) NOT NULL default '3000',
  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)
) COMMENT='ProFTPd user table';
 

Save it and type:

mysql -u root -p  < proftpd-mysql.sql

Finally, we are going to create our first virtual ftp user and group.

First user and group:

In order to create our first user and password, we are going to connect using the database user access we have just created:

mysql -u proftpd_user -p proftpd_db

Again, the password is 'proftpd_password' (without the quotes).

Create your first group:

mysql> INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES ('ftpgroup', 3001, 'ftpuser');

and then your first user which is going to belong to this group:

mysql> INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES (1, 'firstuser', PASSWORD('userpassword'), 3001, 3001, '/var/proftp/firstuser', '/sbin/nologin', 0, '', '');

In order to have your user able to copy and delete files to their home dir, you have to create /var/proftp and give proftp_user/proftp_group write access to it:

mkdir /var/proftp
chown ftpuser:ftpgroup /var/proftp
chmod 775 /var/proftp

Well, now, we still need to configure proFTPd to use our newly create mysql database as a backend.

Configure ProFTPd:

So, get ready with your favorite text editor, edit /etc/proftpd/sql.conf and confirm the following configuration:

#
# Proftpd sample configuration for SQL-based authentication.
#
# (This is not to be used if you prefer a PAM-based SQL authentication)
#
<IfModule mod_sql.c>
#
# Choose a SQL backend among MySQL or PostgreSQL.
# Both modules are loaded in default configuration, so you have to specify the backend
# or comment out the unused module in /etc/proftpd/modules.conf.
# Use 'mysql' or 'postgres' as possible values.
#
#SQLBackend            mysql
SQLBackend             mysql
#
#SQLEngine             on
#SQLAuthenticate       on
SQLAuthenticate        users* groups*
#
# Use both a crypted or plaintext password
#SQLAuthTypes          Crypt Plaintext
#
# Use a backend-crypted or a crypted password
#SQLAuthTypes          Backend Crypt
SQLAuthTypes           Backend
#
# Connection
#SQLConnectInfo        proftpd@sql.example.com proftpd_user proftpd_password
SQLConnectInfo         proftpd_db@localhost    proftpd_user proftpd_password
#
# proftpd will dynamicaly create if the homedir does not yet exist
CreateHome             on
#
# Describes both users/groups tables
#
#SQLUserInfo           users   userid passwd uid gid homedir shell
SQLUserInfo            ftpuser userid passwd uid gid homedir shell
#SQLGroupInfo          groups   groupname gid members
SQLGroupInfo           ftpgroup groupname gid members
#
# update counter when a user logs in
SQLLog                 PASS updatecount
SQLNamedQuery          updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" ftpuser
# change modified time anytime a user delete a file or upload one
SQLLog                 STOR,DELE modified
SQLNamedQuery          modified UPDATE "modified=now() WHERE userid='%u'" ftpuser
#
</IfModule>

Configure ProFTPd for secure logins

In order to use TLS, we must create an SSL certificate. I created a shell script to do the dirty work:

Create a file called proftpd-create_certificate.sh:

Insert the following:

#!/bin/bash

# Server SSL certificate. You can generate a self-signed certificate using a command like:
openssl req -x509 -newkey rsa:1024 -keyout /etc/ssl/private/proftpd.key -out /etc/ssl/certs/proftpd.crt -nodes -days 365

# The proftpd.key file must be readable by root only. The other file can be readable by anyone.
chmod 0600 /etc/ssl/private/proftpd.key
chmod 0640 /etc/ssl/certs/proftpd.crt

Run the file:

bash ./proftpd-create_certificate.sh 

You will be asked the following questions, answer as you see fit:

Country Name (2 letter code) [AU]:                          ← Enter your Country Name (e.g., "DE").
State or Province Name (full name) [Some-State]:            ← Enter your State or Province Name.
Locality Name (eg, city) []:                                ← Enter your City.
Organization Name (eg, company) [Internet Widgits Pty Ltd]: ← Enter your Organization Name (The name of your Company).
Organizational Unit Name (eg, section) []:                  ← Enter your Organizational Unit Name (e.g. "IT Department").
Common Name (eg, YOUR name) []:                             ← Enter the Fully Qualified Domain Name of the system (e.g. "server1.example.com").
Email Address []:                                           ← Enter your Email Address.

In order to enable TLS in ProFTPd, open /etc/proftpd/tls.conf and and ensure it looks like the following:

#
# Proftpd sample configuration for FTPS connections.
#
# Note that FTPS impose some limitations in NAT traversing.
# See http://www.castaglia.org/proftpd/doc/contrib/ProFTPD-mini-HOWTO-TLS.html
# for more information.
#
<IfModule mod_tls.c>
TLSEngine                  on
TLSLog                     /var/log/proftpd/tls.log
TLSProtocol                SSLv23
#
# Server SSL certificate. You can generate a self-signed certificate using
# a command like:
#
# openssl req -x509 -newkey rsa:1024 \
#          -keyout /etc/ssl/private/proftpd.key -out /etc/ssl/certs/proftpd.crt \
#          -nodes -days 365
#
# The proftpd.key file must be readable by root only. The other file can be
# readable by anyone.
#
# chmod 0600 /etc/ssl/private/proftpd.key
# chmod 0640 /etc/ssl/private/proftpd.key
#
TLSRSACertificateFile      /etc/ssl/certs/proftpd.crt
TLSRSACertificateKeyFile   /etc/ssl/private/proftpd.key
#
# CA the server trusts
#TLSCACertificateFile      /etc/ssl/certs/CA.pem
# or avoid CA cert
TLSOptions                 NoCertRequest
#
# Authenticate clients that want to use FTP over TLS?
#
TLSVerifyClient            off
#
# Are clients required to use FTP over TLS when talking to this server?
#
#TLSRequired               on
TLSRequired                off
#
# Allow SSL/TLS renegotiations when the client requests them, but
# do not force the renegotations.  Some clients do not support
# SSL/TLS renegotiations; when mod_tls forces a renegotiation, these
# clients will close the data connection, or there will be a timeout
# on an idle data connection.
#
#TLSRenegotiate            required off
</IfModule>

If you use TLSRequired on, then only TLS connections are allowed (this locks out any users with old FTP clients that don't have TLS support); by commenting out that line or using TLSRequired off both TLS and non-TLS connections are allowed, depending on what the FTP client supports.

Now, let's modify proftpd.conf: Some if the following are included and just need to be uncommented.

[...]
Include                    /etc/proftpd/sql.conf
RootLogin                  off
RequireValidShell          off
DefaultRoot                ~
IdentLookups               off
ServerIdent                on "FTP Server ready."
Include                    /etc/proftpd/tls.conf
[...]

The include will include our customized files, RootLogin is to off to forbid root to use the ftp service RequireValidShell is to off to allow our virtual users to log in. Remember that the shell of our ftp user is /bin/false. DefaultRoot ~ keeps our users from traversing beyond there home directory.

Restart ProFTPd afterward:

/etc/init.d/proftpd restart

That's it. You can now try to connect using your FTP client; however, you should configure your FTP client to use TLS (this is a must if you use TLSRequired on) - see the next chapter how to do this with FileZilla.

If you're having problems with TLS, you can take a look at the TLS log file /var/log/proftpd/tls.log.