I need a powerful multi-user ftp deamon, yet I don't want to build a shadok-factory, that's why i'm installing proFTPd on a [[install|shiny new ubuntu box]].

{{proftpd_shadok.jpg?500}}
===== installing pro-ftpd =====
Installing ProFTPd, that's the easy part :
<code bash>sudo apt-get install proftpd</code>

{{proftpd_standalone.png}}

Using from inetd or not ? Choose Standalone.

And of course I gonna install proftpd-mod-mysql. As I am on a standard LAMP configuration of Ubuntu Server it is quite easy and I need few depedencies, but if you haven't installed mysql-server yet, this step could be a little longer.
<code bash>sudo apt-get install proftpd-mod-mysql</code>
===== Creating a dedicated user =====

As I want those stuffs to be available from a specific user, internally, people can easily upload their files using AFP (see dedicated [[bonjour|Bonjour on ubuntu's Tutorial]]).

Externally, many users can download or upload files, all restrictions will be coped by the proFTPd server.


<code bash>sudo adduser shared</code>

set a password as prompted, make it strong because anyone could use it to log from openssh-server.

===== setting-up the database =====
mod_sql advantages



installing mysql-server and phpmyadmin
configuring /etc/proftpd/sql.conf and /etc/proftpd/proftpd.conf


installing proftpd-mod-mysql

activate in modules.conf


create database ftp and a proftpd user into mysql


<code sql>CREATE DATABASE ftp;
GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftpd'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftpd'@'localhost.localdomain' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
</code>


Replace the string 'password' with whatever password you want to use for the MySQL user proftpd. Still on the MySQL shell, we create the database tables we need:

Get into the ftp database
<code sql>USE ftp;</code>

<code sql>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';</code>

<code sql>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;</code>

<code sql>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;</code>

<code sql>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';</code>

<code sql>quit;</code>

As you may have noticed, with the quit; command we have left the MySQL shell and are back on the Linux shell.
===== configuring proftpd =====

==== enabling modules ====
Let's edit the proftpd modules configuration file
<code bash>sudo vi /etc/proftpd/modules.conf</code>

Let's enable those 3 modules
<code>
LoadModule mod_sql.c
LoadModule mod_sql_mysql.c
LoadModule mod_quotatab_sql.c
</code>


==== configuring main protpd behavior====

<code bash>sudo vi /etc/proftpd/proftpd.conf</code>

Enable DefaultRoot ~ and set RequireValidShell to off
<code># Use this to jail all users in their homes 
DefaultRoot                     ~

# Users require a valid shell listed in /etc/shells to login.
# Use this directive to release that constrain.
RequireValidShell off

</code>
FIXME RootLogin off ?

As we will use just one dedicated user for sharing stuffs, here is some other line to change.

<code>
# Set the user and group that the server normally runs at.
User                            shared
Group                           shared
</code>

FIXME This may cause conflict with some apparmor stuffs, let's note this here and check later on


Of course we gonna enable sql.conf

<code>
#
# Alternative authentication frameworks
#
#Include /etc/proftpd/ldap.conf
Include /etc/proftpd/sql.conf

</code>


==== Set up mysql specificities ====

Great, now let's edit sql.conf as always using 
<code bash>sudo vi /etc/proftpd/sql.conf </code>

All is inside the <IfModule mod_sql.c> section, let's start by specifying mysql as Backend
<code>
SQLBackend              mysql
SQLEngine on
SQLAuthenticate on
</code>

Here is some authentification and connection
<code>
# The passwords in MySQL are encrypted using CRYPT
SQLAuthTypes            Plaintext Crypt
SQLAuthenticate         users groups


# Connection
# databasename@host database_user user_password
SQLConnectInfo  ftp@localhost proftpd password
</code>
Of course, change the password.

Finally, let's do some boring tables and fields definitions.

<code>
# 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
</code>


====Restart====
<code bash>sudo /etc/init.d/proftpd restart</code>


===== playing with mysql =====
Now that all is configured, there is still some testing and some playing with mysql

for setting the proper uid, su you onto the shared user and type id
<code bash>sudo su shared</code>
then
<code bash>id</code>

Adding a new group
<code sql>INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES ('ftpgroup', 2001, 'ftpuser');</code>

Now we are done with the table ftpgroup. We do not have to create further entries here. Whenever you create a new virtual ftp user, you do this in the tables ftpquotalimits and ftpuser. So let us create our first user exampleuser with a quota of 15MB and the password secret (we are still in the MySQL shell):

<code sql>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 ('exampleuser', 'user', 'true', 'hard', 15728640, 0, 0, 0, 0, 0);</code>

<code sql>INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES (1, 'exampleuser', 'secret', 2001, 2001, '/home/www.example.com', '/sbin/nologin', 0, '', '');</code>