Configure ProFTPd using the MySQL user database

Vyacheslav

9 years ago

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;

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>