Distroname and release: Debian Squeeze
ProFTPD with virtual users in MySQLA 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.
PrerequirementsAn 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
ConfigurationWe 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 ftpuserNow get the UID for ftpuser and the GID for ftpgroup
#grep ftp /etc/passwd /etc/groupYou 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.sqlThe 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.cNow 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.
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 restartCreate 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/user1Set 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/ftpTry 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.