Install ProFTPD with MySQL backend on Debian / Ubuntu

Here a quick copy and paste guide on how to install ProFTPD and configure it so that users can be managed via MySQL (usually with the help of phpMyAdmin).

Update – May 2012: This page has been updated and tested with Ubuntu 12.04 and Debian Squeeze.

1. Install the needed ProFTPD packages with MySQL support

apt-get install proftpd-basic proftpd-mod-mysql

2. Edit /etc/proftpd/proftpd.conf and enable the following lines by removing the comment sign (#) or adding the lines to the end of the file.

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

3. Add a new system user and group that will later be mapped to the virtual users in the MySQL database.

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

4. Create a MySQL database and user for ProFTPD, e.g. called ftp.

5. Create two new tables in the ftp database. Watch out that gid in ftpgroup matches your user id set in step 3.

CREATE TABLE ftpgroup (
 groupname varchar(16) NOT NULL default '',
 gid smallint(6) NOT NULL default '2001',
 members varchar(16) NOT NULL default '',
 KEY groupname (groupname)
 ) ENGINE=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 '2001',
 gid smallint(6) NOT NULL default '2001',
 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)
 ) ENGINE=MyISAM COMMENT='ProFTP user table';

6. Edit /etc/proftpd/modules.conf and enable the following two modules.

LoadModule mod_sql.c
LoadModule mod_sql_mysql.c

7. Edit /etc/proftpd/sql.conf and set the following options. Replace password in SQLConnectInfo with the ftp database password (also replace ftp@localhost and/or ftp if you did not name your database and user ftp).

SQLBackend mysql
SQLAuthTypes Crypt
SQLConnectInfo ftp@localhost ftp password
SQLUserInfo ftpuser userid passwd uid gid homedir shell
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
SQLLog STOR,DELE modified
SQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuser

8. Populate the database. Set username, password and homedir to the desired values

INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES ('ftpgroup', 2001, 'ftpuser');
INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES ('', 'username', ENCRYPT('password'), 2001, 2001, '/var/www/www.example.com/', '/sbin/nologin', 0, '', '');

9. Restart ProFTPD.

/etc/init.d/proftpd restart

You should now be able to login with the username and password set in step 8. For additional users just add another entry in the ftpuser table. If you can’t connect via FTP you can stop ProFTPD with /etc/init.d/proftpd stop and start it manually with

proftpd -nd6

You will then see debugging information on the command line which can help you fix the issue, for example if ProFTPD can’t connect to the MySQL database.

 

10 Replies to “Install ProFTPD with MySQL backend on Debian / Ubuntu”

      1. In the most simple configuration the FTP folder should be owned by the user you set up in step 3. You could e.g. use the command

        chown ftpuser:ftpgroup /path/to/foldername

        to change the folder permissions. If that is not sufficient you would need to look into setting the umask directive.

  1. Hi, and thanks for the tuto.

    It’s working great, but How could i set uploaded files www-data ownership?

    I already tried inserting ftpuser in www-data group and it resulted in a connection error, so I removed it.

    For now, uploaded files are ftpuser:ftpgroup and this gives a forbidden apache error (and i would like not to change this setting! )

    Thanks again.

  2. Great Tutorial. Thank you very much.

    To write in others name use following with your specific uid and gid
    # Mininum UID/GID. Change to suit needs.
    SQLMinUserUID 1000
    SQLMinUserGID 1000

    Then change the user uid and gid in the database.

Comments are closed.