Distroname and release: Debian Squeeze

ProFTPD with virtual users in MySQL

A very simple installation and setup of ProFTPD with MySQL.
I have added a feature, where I can enable/disable the usage of FTP on userlevel, with the use of MySQL.


An already installed and working MySQL server, so if you do not have this, install it.
#aptitude install mysql-server mysql-client


#aptitude install proftpd-basic proftpd-mod-mysql


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.
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.

USE proftpd;

 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

 groupname VARCHAR(30) 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.
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"
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.
<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'"
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) []
Name (localhost:lassebm): user1
331 Password required for user1
230 User user1 logged in
Remote system type is UNIX.
Using binary mode to transfer files.

Do not trust the authors words! POC, tests and experience is key

Copyright LinuxLasse.net 2009 - 2024 All Rights Reserved.

Valid HTML 4.01 Strict Valid CSS!