Configure ProFTPd using the MySQL user database

Let’s say there is a ProFTPd server installed, for example, as I described in this article – Installing and Configuring ProFTPd in Ubuntu
And also installed MySQL server, for example, as I described in this article – Installing and configuring a MySQL server on Ubuntu

Here is an example of setting up ProFTPd using the MySQL user database.

Install an additional package:

sudo apt-get install proftpd-mod-mysql

Open the ProFTPd configuration in any text editor:

sudo nano /etc/proftpd/proftpd.conf

Uncomment or specify:

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

Create a separate system user and group to which virtual users from the MySQL database will be tied:

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

Further it will be necessary to execute sql requests through phpMyAdmin or any mysql client, I will give an example of connection to the local MySQL server:

mysql -u root -p

Performing sql query create any database and user with privileges on it, for example proftpd:

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

Create in this new database two tables, and also add the group and the FTP user:

use proftpd;
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',
 UNIQUE KEY userid (userid)
 ) ENGINE=MyISAM COMMENT='ProFTP user table';

INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES ('ftpgroup', 2001, 'ftpuser');
INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES (NULL, 'test', 'password')', '2001', '2001', '/srv/ftp/test', '/sbin/nologin', '0', '0000-00-00 00:00:00.000000', '0000-00-00 00:00:00.000000');


See also – The solution of error “ERROR 1067 (42000) at line 211: Invalid default value for ‘blablabla’”

Open the configuration file of ProFTPd modules in a text editor:

sudo nano /etc/proftpd/modules.conf

Uncomment or specify two lines:

LoadModule mod_sql.c
LoadModule mod_sql_mysql.c

Open the configuration file for SQL parameters for ProFTPd:

sudo nano /etc/proftpd/sql.conf

We specify the username and password for connecting to the MySQL database, in fact we will bring it to the following form:

<IfModule mod_sql.c>
SQLBackend mysql
SQLAuthTypes Crypt
SQLConnectInfo proftpd@localhost proftpd 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

Restart ProFTPd to apply the configuration changes:

sudo /etc/init.d/proftpd restart

This completes the ProFTPd configuration using the MySQL user database and you can connect.

I will also give you an example of the contents of the old configuration file, in which proftpd took user data from the mysql billing database:

ServerName		"Name"
ServerType		standalone
ServerIdent		off
DefaultServer		on
ScoreboardFile		/var/run/proftpd.scoreboard
Port			21
Umask			002
MaxInstances		300
CommandBufferSize	512
UseReverseDNS		off
IdentLookups		off
RequireValidShell	off

PassivePorts 		30000 50000

TimesGMT		off

TimeoutSession		1800
TimeoutStalled		300

User			ftp
Group			ftp

CharsetLocal		KOI8-U
CharsetRemote		CP1251

ExtendedLog		/var/log/proftpd.log ALL ideal
LogFormat		ideal "%t %a %U %m %D %f %b %T"
TransferLog 		NONE

AllowRetrieveRestart	on
AllowStoreRestart	on

DefaultRoot ~

AllowOverwrite	off


<Limit Write>

<Limit READ>

    AllowUser myadmin manager

AuthOrder 		mod_auth_unix.c mod_sql.c

SQLAuthTypes		Plaintext
SQLAuthenticate	users
SQLConnectInfo		base@ mysqluser password
SQLDefaultGID		1003
SQLDefaultUID		1003
SQLDefaultHomedir	/ftp
SQLUserInfo		`abon` `login` `password` "NULL" "NULL" "NULL" "NULL"
SQLUserWhereClause	"killed!='killed'"

#SQLNamedQuery	auth	SELECT "login,password,1003,1003,'/usr/home/ftp','' from abon where killed!='killed' and login = '%u'"
#SQLUserInfo		custom:/auth
#SQLLogFile		/var/log/proftpd_sql.log

<Directory ~>


See also:
Configuring ProFTPd with virtual users in a file

Leave a comment

Leave a Reply