Posted on
by

ProFTP is a configurable FTP server available on most *nix platforms.

I recently had the need to get this working and authenticating off a PHP maintained MySQL backend, and this post is primarily to aid my own memory should I ever have to do it again.

Installing ProFTP

In order to use MySQL as a back end you need to install some packages. If you’re using a Debian based distro like Ubuntu, this is easy:

apt-get install mysql-server proftpd proftpd-mod-mysql

The database schema

Next, you need to install the database schema to store your users and passwords.

CREATE TABLE IF NOT EXISTS users (
userid varchar(30) NOT NULL default '',
passwd varchar(128) NOT NULL default '',
uid int(11) default NULL,
gid int(11) default NULL,
homedir varchar(255) default NULL,
shell varchar(255) default NULL,
UNIQUE KEY uid (uid),
UNIQUE KEY userid (userid)
) TYPE=MyISAM;

CREATE TABLE IF NOT EXISTS groups (
groupname varchar(30) NOT NULL default '',
gid int(11) NOT NULL default '0',
members varchar(255) default NULL
) TYPE=MyISAM;

One important thing to note here – that caused me a fair amount of hair pulling when I tried to use encrypted passwords – is that the password field shown in many howtos on the internet is much too short. This causes the hashed password to be quietly truncated by MySQL when saved.

This results in a somewhat misleading “No such user found” error to appear in the logs when using encrypted passwords.

To end all argument I’ve allowed passwords up to 128 chars, but this field could probably be a good deal shorter.

The user table looks much like /etc/passwd and is largely self explanatory. The uid & gid fields correspond to a system user in most cases, but since we’re using virtual users they can largely be ignored. Homedir points to a location which will serve as the user’s default directory. Shell is largely unused and can be set to /bin/false or similar.

Configuring ProFTP

Next, you need to make some changes to the ProFTP configuration files stored in /etc/proftpd. While doing this it is handy to run proftp in debug mode from the console:

proftpd -nd6

proftpd.conf

  1. Make sure the AuthOrder line looks like:

    AuthOrder mod_sql.c

  2. Ensure that the following line is uncommented:

    Include /etc/proftpd/sql.conf

  3. For belts and braces I’ve included the following at the end, although I’m not entirely sure it’s strictly required:

    <IfModule mod_auth_pam.c>
    AuthPAM off
    </IfModule>

  4. Our users don’t need a valid shell, so:

    RequireValidShell off

modules.conf

  1. Make sure the following lines are uncommented:

    LoadModule mod_sql.c
    LoadModule mod_sql_mysql.c

sql.conf

  1. Set your SQL backend and ensure that authentication is turned on:

    SQLBackend mysql
    SQLEngine on
    SQLAuthenticate on

  2. Tell proftp how passwords are stored. You have a number of options here, but since I was using mysql’s PASSWORD function, I’ll defer to the backend.

    SQLAuthTypes backend

  3. Tell proftp how to connect to your database by providing the required connection details, ensure that the user has full access to these tables.

    SQLConnectInfo database@host user password

  4. Define your table structure in the format tablename fields….

    SQLUserInfo users userid passwd uid gid homedir shell
    SQLGroupInfo groups groupname gid members

Adding users

I manage users from within a PHP web application that I’m developing, but in a nutshell adding FTP users from this point is a simple insert statement looking something like:

mysql_query("REPLACE INTO users
(userid, passwd, uid, gid, homedir, shell)
VALUES
('$userid', PASSWORD('$password'), $uid, $gid, '$homedir', '$shell')");

Have fun!

12 thoughts on “How to set up ProFTP, MySQL and Virtual Users”

  1. THAAAAAAANKS, just searching to do the same on my Ubuntu server, will give it a try this week.

    Thank you again!

  2. Am tying to do a similar job using ubuntu 10 but get the following error when trying to install proftpd with the mysql support.

    I am new to ubuntu so would apprieciate any help?

    Regards
    Sandy

    webmaster@discsql5:/$ sudo apt-get install proftpd proftpd-mod-mysql
    Reading package lists… Done
    Building dependency tree
    Reading state information… Done
    Note, selecting proftpd-basic instead of proftpd
    The following extra packages will be installed:
    libfile-copy-recursive-perl openbsd-inetd proftpd-basic update-inetd
    Suggested packages:
    proftpd-doc proftpd-mod-pgsql proftpd-mod-ldap proftpd-mod-odbc proftpd-mod-sqlite
    The following NEW packages will be installed
    libfile-copy-recursive-perl openbsd-inetd proftpd-basic proftpd-mod-mysql update-inetd
    0 upgraded, 5 newly installed, 0 to remove and 30 not upgraded.
    Need to get 0B/1,308kB of archives.
    After this operation, 3,031kB of additional disk space will be used.
    Do you want to continue [Y/n]? y
    E: Sub-process /usr/sbin/dpkg-preconfigure –apt || true returned an error code (100)
    E: Failure running script /usr/sbin/dpkg-preconfigure –apt || true

  4. Thanks Marcus

    the offending item was the shell

    cured with
    ln -s /bin/bash /bin/sh

    Can now crack on and get the thing configured.
    Once again many thanks.

  5. Marcus, many thanks, I am new to the whole UBUNTU thing but have managed to set up a fully virtulized server in just a few days with the notes from the ubuntu site, and a few well put together documents by third parties such as yourself.

    Realy pleased with the result and the fantastic notes.

    Thanks again
    Sandy

  7. Hi Marcus,

    I have seen your script it very useful. And i also used in my conf file but still im facing the 530 login incorrect problem. please let me know the solution asap.

    Thanks in Advance
    Kapil

  8. Hard to say what the cause would be without more information, I suggest running the server in debug mode and seeing what log messages you get.

    Probably its trying to connect to your db server and failing somewhere, so check your connection string / password etc.

  10. Thanks to your article, Marcus Povey, I could not find enough errors had other sources of documentation. Also so my students from IES RUIZ GIJON (UTRERA-SEVILLE-SPAIN) can enjoy this great tool ProFTPd

  12. Thank you dude,

    You saved me much time.

    I already do this type of installation 3 years ago, and it was really boring (i got all the time errors, because informations on the net was outdated).

    I validate your tutoriel for Debian 6 Squeeze up to date at this time.

Leave a Reply