ProFTPD with virtual users in MySQL

A very simple installation and setup of ProFTPD with MySQL.
Ref.:http://www.proftpd.org/docs/howto/SQL.html
I have added a feature, where I can enable/disable the usage of FTP on userlevel, with the use of MySQL.

Prerequirements

An already installed and working MySQL server, so if you do not have this, install it.

#aptitude install mysql-server mysql-client

Installation

#aptitude install proftpd-basic proftpd-mod-mysql

Configuration

We will start by creating a local user and a local group, because we will need to use the UID and GID in the MySQL database.

Create a user and group, with no home directory, no shell, and add the user to the new group.

#groupadd ftpgroup
#useradd -s /bin/false -d /dev/null -g ftpgroup ftpuser

Now get the UID for ftpuser and the GID for ftpgroup

#grep ftp /etc/passwd /etc/group

You will get an output like this.

/etc/passwd:ftp:x:109:65534::/home/ftp:/bin/false
/etc/passwd:proftpd:x:108:65534::/var/run/proftpd:/bin/false
/etc/passwd:ftpuser:x:2001:2001::/dev/null:/bin/false
/etc/group:ftpgroup:x:2001:

As you can see ftpuser have an UID of 2001, and ftpgroup an GID of 2001 as well. This is important informations as we will use later on.
ftpuser have two entries of 2001 (2001:2001), the second on after ":" is because it is member of the group which have GID 2001, which is ftpgroup. Now we will create the database for MySQL.
This will create an MySQL database called proftpd, where the user is proftpd and the password for the proftpd user is santa123.
You might want to change some of this info.
Change the UID and GID, to match the values to your system.

Copy the content, of the SQL info below, and save it as ftp.sql on the server. Or you can use an MySQL manager like, PHPMyAdmin.

DROP DATABASE IF EXISTS proftpd;
CREATE DATABASE proftpd;

USE proftpd;

CREATE TABLE users (
 userid VARCHAR(30) NOT NULL UNIQUE,
 passwd VARCHAR(80) NOT NULL,
 uid INTEGER default 2001,
 gid INTEGER default 2001,
 homedir VARCHAR(255),
 shell VARCHAR(255) default NULL,
 LoginAllowed BOOLEAN default 1
);

CREATE TABLE groups (
 groupname VARCHAR(30) NOT NULL,
 gid INTEGER NOT NULL,
 members VARCHAR(255)
);

GRANT ALL ON proftpd.* TO proftpd@'localhost' IDENTIFIED BY 'santa123';

Import the file to the MySQL server.

#mysql -p < ftp.sql

The MySQL server is now ready, and we can continue configuring ProFTPD.

Enable the mysql module for ProFTPD Remove the comment line for mod_sql.c in modules.conf, and save the file.

Should look like this below, when the comment is removed.

/etc/proftpd/modules.conf
LoadModule mod_sql.c

Now when ProFTPD is understanding MySQL, we will change the proftpd.conf, so it loads the MySQL server settings from the sql.conf file, and next change the sql.conf.
Also set "RequireValidShell Off" since, the users will not need a valid shell, like bash. The MySQL table is setup to use a NULL value.
And finally set DefaultRoot ~, so the users are forced to their homedir.

Configure proftpd.conf.
Remove the comment from "#Include /etc/proftpd/sql.conf"

/etc/proftpd/proftpd.conf
Include /etc/proftpd/sql.conf
RequireValidShell Off
DefaultRoot ~

Now we are ready to configure the settings for the MySQL server proftpd should use.

I will use Backend authtype, so I can use the password() function directly from MySQL. This will encrypt the passwords inside the MySQL database.
Please see here from other authtypes. http://www.proftpd.org/docs/contrib/mod_sql.html#SQLAuthTypes
Note that the password will still be send in cleartext, over the wire. So for encryptet connections over the wire, SSL / TLS is required. We will not go through this here.

Change the sql.conf so the following parameters apply.
An MySQL Query have been added. This query controls, if a user can login or not. If the value is 1, the user can login. If it is 0 the user cannot login.

/etc/proftpd/sql.conf
<IfModule mod_sql.c>
SQLBackend	      mysql
SQLAuthTypes          Backend Crypt 
SQLConnectInfo        proftpd@localhost proftpd santa123
SQLUserInfo           users userid passwd uid gid homedir shell
SQLGroupInfo          groups groupname gid members
SQLUserWhereClause    "LoginAllowed = '1'"
</IfModule>

Now the configuration should be ready, so restart ProFTPD, and check for configuration errors in the logs.

#/etc/init.d/proftpd restart

Create the ftpgroup in MySQL and add the user to the group, exactly like we did on the system.

#mysql -p
USE proftpd;
INSERT INTO groups (groupname, gid, members) VALUES ("ftpgroup", 2001, "ftpuser");

Ok, everything, should now be ready, and we can create our users in the MySQL database.

Go to the MySQL prompt, if you are not here already, and add a new user. Remember to select the database if not selected.

INSERT INTO users (userid, passwd, homedir) VALUES ("user1", PASSWORD("superpass"), "/var/ftp/user1");

Create the homefolder for the user.

#mkdir -p /var/ftp/user1

Set permissions to directories, which the homefolder points to. If you set the home folder for the user in the MySQL to be "/var/ftp/user1" set the rights for "/var/ftp/user1" or the "/var/ftp" recursive.

#chown -R ftpuser:ftpgroup /var/ftp

Try to logon to the FTP. Here I have just tested, locally on the server.

ftp localhost
Connected to localhost.
220 ProFTPD 1.3.3a Server (Debian) [127.0.0.1]
Name (localhost:lassebm): user1
331 Password required for user1
Password:
230 User user1 logged in
Remote system type is UNIX.
Using binary mode to transfer files.