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'; FLUSH PRIVILEGES;
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',
PRIMARY KEY (id),
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');
exit;
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 </IfModule>
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
ServerAdmin admin@example.net
Umask 002
MaxInstances 300
CommandBufferSize 512
UseReverseDNS off
IdentLookups off
RequireValidShell off
DefaultAddress 0.0.0.0
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 SITE_CHMOD>
DenyAll
</Limit>
<Limit Write>
AllowAll
</Limit>
<Limit READ>
AllowAll
</Limit>
<Limit DELE RMD XRMD RNFR RNTO>
AllowUser myadmin manager
DenyAll
</Limit>
AuthOrder mod_auth_unix.c mod_sql.c
SQLAuthTypes Plaintext
SQLAuthenticate users
SQLConnectInfo base@192.168.0.1:3306 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 ~>
</Directory>
See also:
Configuring ProFTPd with virtual users in a file