Proftpd and Mysql

I recently had to setup FTP access on one of my servers which is running Ubuntu 8.04. I decided to go with proftpd tied in with MySQL for authentication. There were a couple of bumps and bruises along the way, but I was able to find some great resources to help me get it setup. That being said, I learned a couple of things along the way that weren't listed in the other tutorials I looked at so I though I would document the steps I used to create my FTP server.

Note: This tutorial assumes you have MySQL already installed.

Here is an overview of how to get proftpd configured to work with MySQL:

Install the following packages:

apt-get install proftpd proftpd-mysql

Note: In Ubuntu version 9.04 the proftpd-mysql package has been renamed to proftpd-mod-mysql

Now we need to create a group and user that all FTP users will belong to:

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

Create a new database and a new user that has access to the database:

mysql -u root -p

create database ftp;
GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftpd'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Note: Replace password with whatever password you want to use for this user.

Now it is time to create the database tables we need for authentication:

USE ftp;

CREATE TABLE ftpgroup (
groupname varchar(16) NOT NULL default '',
gid smallint(6) NOT NULL default '5500',
members varchar(16) NOT NULL default '',
KEY groupname (groupname)
) TYPE=MyISAM COMMENT='ProFTP group table';

CREATE TABLE ftpuser (
id int(10) unsigned NOT NULL auto_increment,
userid varchar(32) NOT NULL default '',
passwd varchar(32) NOT NULL default '',
uid smallint(6) NOT NULL default '5500',
gid smallint(6) NOT NULL default '5500',
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)
) TYPE=MyISAM COMMENT='ProFTP user table';

Now add the users that you want to have FTP access:

INSERT INTO ftpgroup (groupname, gid, members) VALUES ('ftpgroup', 2001, 'ftpuser');

INSERT INTO ftpuser (id, userid, passwd, uid, gid, homedir, shell, count, accessed, modified) VALUES (1, 'testuser', ENCRYPT('secret'), 2001, 2001, '/home/testuser', '/sbin/nologin', 0, '', '');

quit;

Note: This creates a new user named testuser with a password of secret and a home directory of /home/testuser. Feel free to change these to suit your needs.

Create a new document called mysql.conf. This is used to tell the proftp server how to communicate with mysql:

nano /etc/proftpd/mysql.conf

Copy and paste this in your new document and save it:

#Force the use of mysql backend
SQLBackend                      mysql
# The passwords in MySQL are using its own ENCRYPT function
# There is another SQLAuthTypes you could use called Backend to
# store passwords.  If you choose this make sure you use mysql's
# OLD_PASSWORD() function to store the data.  I couldn't get the
# stand PASSWORD() function to work with it.
SQLAuthTypes                    Crypt
SQLAuthenticate                 users* groups*
# used to connect to the database
# databasename@host database_user user_password
SQLConnectInfo  ftp@localhost proftpd 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
# 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
# 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

Note: Change the password in SQLConnectInfo to the same one you used in step 3.

Now we need to add a couple of lines to our main proftpd.conf file, which is located in /etc/proftpd/proftpd.conf. Right below where it says Include /etc/proftpd/modules.conf add the following lines:

## Begin Customization
######################
Include /etc/proftpd/mysql.conf
RootLogin               off
# Users require a valid shell listed in /etc/shells to login.
# Use this directive to release that constrain.
RequireValidShell       off
# Use this to jail all users in their homes
DefaultRoot             ~
# These two lines help speed up the initial connection to the server
IdentLookups            off
UseReverseDns           off
# Create a user's home directory on demand if it doesn't exist
CreateHome on
## End Customization
####################

This step is for Ubuntu 9.04. If you are using an earlier version than you can skip this step. Uncomment the two lines listed below which are located in /etc/proftpd/modules.conf:

nano /etc/proftpd/modules.conf

LoadModule mod_sql.c

LoadModule mod_sql_mysql.c

Note: Only do this step if using Ubuntu 9.04

Restart the proftp server:

/etc/init.d/proftpd restart

That's it! Open up your favorite FTP client and trying connecting to your server with the login credentials you setup earlier.

Comments

Thanks for this I've been trying to set this back up since I upgraded my server to 9.04 and was scratching my head trying to figure out what's not working. This helped get me back up and running nicely :)

in the mysql.conf it's worth swapping out SQLHomedirOnDemand on for CreateHome on this will ensure the config works on the next version of proftpd.

I removed the SQLHomedirOnDemand directive from mysql.conf and put the CreateHome directive in the proftpd.conf file

I am getting:

Fatal: unknown configuration directive 'SQLBackend' on line 2 of '/etc/proftpd/mysql.conf'

When I restart the ftp server ubuntu 9.04 also the lines you directed to comment were already commented in /etc/proftpd/modules.conf

LoadModule mod_sql.c

LoadModule mod_sql_mysql.c

Ooops - I made a mistake in the directions. You are supposed to uncomment the lines

LoadModule mod_ldap.c
LoadModule mod_sql_mysql.c

Thanks for catching that I'll update the post.

If I install proftpd-mod-mysql and I already have Mysql installed. Is it possible that this will corrupt my already installed database?

Not sure I understand the question, but to install and use proftpd-mod-mysql you need to have Mysql installed, and I'm pretty sure it won't corrupt any of your databases, but you can always do a quick backup/dump of the database if you're concerned about it.

Add new comment