Installing Proftpd with MySQL in Ubuntu Linux

29 August , 2011 bill Linux

Introduction
Proftpd is a modular FTP server that runs on Linux. One of the proftpd modules is for MySQL and enables users to be stored in the database instead of the system. Users can have a different home directories as they are set per user in the database, different quotas can be set for users as well.

Install Software
The software that needs to be installed can be installed in Ubuntu Linux with the following command:

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

A graphical package manager can also be used.

Create a User and Group
A user and group needs to be created. The group and user id of 2123 can be replaced with a number that is free on your system:

groupadd -g 2123 ftpg
useradd -u 2123 -s /bin/false -d /bin/null -c "proftpd user" -g ftpg ftpu

Create The MySQL Database
Start the MySQL client with the following command:

mysql -u root -p

The following commands will create a database and tabes required by proftpd-mod-mysql: (Change mypassword to a password you would like to use for the database)

CREATE DATABASE proftpd;
GRANT SELECT, INSERT, UPDATE, DELETE ON proftpd.* TO 'proftpd'@'localhost' IDENTIFIED BY 'mypassword';
GRANT SELECT, INSERT, UPDATE, DELETE ON proftpd.* TO 'proftpd'@'localhost.localdomain' IDENTIFIED BY 'mypassword';
FLUSH PRIVILEGES;
use proftpd;
CREATE TABLE ftpgroup (groupname varchar(16) NOT NULL default '', gid smallint(6) NOT NULL default '5500', members varchar(16) NOT NULL default '', KEY groupname (groupname)) TYPE=MyISAM COMMENT='ProFTP group table';
CREATE TABLE ftpquotalimits (name varchar(30) default NULL, quota_type enum('user','group','class','all') NOT NULL default 'user', per_session enum('false','true') NOT NULL default 'false', limit_type enum('soft','hard') NOT NULL default 'soft', bytes_in_avail bigint(20) unsigned NOT NULL default '0', bytes_out_avail bigint(20) unsigned NOT NULL default '0', bytes_xfer_avail bigint(20) unsigned NOT NULL default '0', files_in_avail int(10) unsigned NOT NULL default '0', files_out_avail int(10) unsigned NOT NULL default '0',
files_xfer_avail int(10) unsigned NOT NULL default '0') TYPE=MyISAM;
CREATE TABLE ftpquotatallies (name varchar(30) NOT NULL default '', quota_type enum('user','group','class','all') NOT NULL default 'user', bytes_in_used bigint(20) unsigned NOT NULL default '0', bytes_out_used bigint(20) unsigned NOT NULL default '0', bytes_xfer_used bigint(20) unsigned NOT NULL default '0', files_in_used int(10) unsigned NOT NULL default '0', files_out_used int(10) unsigned NOT NULL default '0', files_xfer_used int(10) unsigned NOT NULL default '0') TYPE=MyISAM;
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 '5500',
gid smallint(6) NOT NULL default '5500', 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)) TYPE=MyISAM COMMENT='ProFTP user table';

Insert Data Into Database
The follow commands will insert the data into the database: (Change username and mypassword to values you wish to use for the ftp account)

INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES ('ftpg', 2123, 'ftpu');
INSERT INTO `ftpquotalimits` (`name`, `quota_type`, `per_session`, `limit_type`, `bytes_in_avail`, `bytes_out_avail`, `bytes_xfer_avail`, `files_in_avail`, `files_out_avail`, `files_xfer_avail`) VALUES ('username', 'user', 'true', 'hard', 0, 0, 0, 0, 0, 0);
INSERT INTO `ftpuser` (`userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES ('username', 'mypassword', 2123, 2123, '/ftp/username/pub', '/sbin/nologin', 0, '', '');

Edit Config Files
Open ‘/etc/proftpd/modules.conf’ in a text editor and enable the following modules by removing the # character at the start of the line:

  1. LoadModule mod_sql.c
  2. LoadModule mod_sql_mysql.c
  3. LoadModule mod_quotatab_sql.c

Open ‘/etc/proftpd/proftpd.conf’ in an editor and comment out the following lines by adding a # character at the start of the line. Look for:

<IfModule mod_quotatab.c>
QuotaEngine off
</IfModule>

and comment the lines to make them look like:

#<IfModule mod_quotatab.c>
#QuotaEngine off
#</IfModule>

With ‘/etc/proftpd/proftpd.conf’ still open, add the following lines to the end:

# Choose a SQL backend among MySQL or PostgreSQL.
# Both modules are loaded in default configuration, so you have to specify the backend
# or comment out the unused module in /etc/proftpd/modules.conf.
# Use 'mysql' or 'postgres' as possible values.
#
#<IfModule mod_sql.c>
# SQLBackend              mysql
#</IfModule>

DefaultRoot ~

SQLBackend              mysql
# The passwords in MySQL are encrypted using CRYPT
SQLAuthTypes            Plaintext Crypt
SQLAuthenticate         users groups

# used to connect to the database
# databasename@host database_user user_password
SQLConnectInfo  proftpd@localhost proftpd mypassword

# Here we tell ProFTPd the names of the database columns in the "usertable"
# we want it to interact with. Match the names with those in the db
SQLUserInfo     ftpuser userid passwd uid gid homedir shell

# Here we tell ProFTPd the names of the database columns in the "grouptable"
# we want it to interact with. Again the names match with those in the db
SQLGroupInfo    ftpgroup groupname gid members

# set min UID and GID - otherwise these are 999 each
SQLMinID        500

# create a user's home directory on demand if it doesn't exist
CreateHome on

# Update count every time user logs in
SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" ftpuser

# Update modified everytime user uploads or deletes a file
SQLLog  STOR,DELE modified
SQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuser

# User quotas
# ===========
QuotaEngine on
QuotaDirectoryTally on
QuotaDisplayUnits Mb
QuotaShowQuotas on

SQLNamedQuery get-quota-limit SELECT "name, quota_type, per_session, limit_type, bytes_in_avail, bytes_out_avail, bytes_xfer_avail, files_in_avail, files_out_avail, files_xfer_avail FROM ftpquotalimits WHERE name = '%{0}' AND quota_type = '%{1}'"

SQLNamedQuery get-quota-tally SELECT "name, quota_type, bytes_in_used, bytes_out_used, bytes_xfer_used, files_in_used, files_out_used, files_xfer_used FROM ftpquotatallies WHERE name = '%{0}' AND quota_type = '%{1}'"

SQLNamedQuery update-quota-tally UPDATE "bytes_in_used = bytes_in_used + %{0}, bytes_out_used = bytes_out_used + %{1}, bytes_xfer_used = bytes_xfer_used + %{2}, files_in_used = files_in_used + %{3}, files_out_used = files_out_used + %{4}, files_xfer_used = files_xfer_used + %{5} WHERE name = '%{6}' AND quota_type = '%{7}'" ftpquotatallies

SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" ftpquotatallies

QuotaLimitTable sql:/get-quota-limit
QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally

RootLogin off
RequireValidShell off

If you have changed the username or password for the database, a line in the text that was just added needs to be edited. The Line:

SQLConnectInfo  proftpd@localhost proftpd mypassword

Can be changed:

SQLConnectInfo  proftpd@localhost <Your New User Name> <Your New Password>

Restart Proftpd
The system can be rebooted, but a restart of the specific daemon is easier. The following command will restart proftpd:

sudo /etc/init.d/proftpd restart

Test the FTP Server
The FTP server should now be up and running. The last thing to do is test the server. The following command will attempt to connect to the new server:

ftp 127.0.0.1

If the FTP server is not on the local computer, change 127.0.0.1 to the correct IP address. The server should ask for authentication, if all goes well the server output should look like this:

bill@K11:~$ ftp 127.0.0.1
Connected to 127.0.0.1.
220 ProFTPD 1.3.2e Server (Debian) [::ffff:113.4.13.120]
Name (127.0.0.1:username): username
331 Password required for username
Password:
230 User username logged in
Remote system type is UNIX.
Using binary mode to transfer files.
ftp>

You now have a working FTP server that uses a MySQL database for authentication.

how to, installing, linux, mysql, proftpd,

6 Responses to “Installing Proftpd with MySQL in Ubuntu Linux”


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Powered by WordPress. Designed by elogi.