Go to content Go to navigation Go to search

ProFTPD with virtual users

August 5th, 2010 by jde

Installation

# cd /usr/ports/ftp/proftpd-mysql
# make install clean

[ ] BAN              Include mod_ban (Requires CTRLS)
[ ] CLAMAV           Include mod_clamav
[ ] CTRLS            Include controls
[ ] EXEC             Include mod_exec
[X] HTMLDOCS         Include HTML documentation
[X] IFSESSION        Include mod_ifsession
[ ] IPV6             Use IPv6
[ ] LDAP             Use LDAP
[ ] LDAP_TLS         Use LDAP TLS (Requires LDAP, OPENSSL)
[X] MYSQL            MySQL auth
[X] NLS              Use nls (builds mod_lang)
[ ] ODBC             ODBC
[X] OPENSSL          Include mod_tls
[ ] PGSQL            Postgres auth
[X] QUOTA            Include mod_quota
[ ] QUOTATAB_RADIUS  include mod_quotatab_radius
[ ] SHAPER           Shaper module
[ ] SQLITE           SQLite auth
[X] RADIUS           Include mod_radius
[X] RATIO            Include mod_ratio
[X] README           Include mod_readme
[X] REWRITE          Include mod_rewrite
[ ] TLS_SHMCACHE     TLS SHM session cache (requires OPENSSL)
[ ] TDS              Include mod_sql_tds
[X] SFTP             Include mod_sftp
[X] SFTP_SQL         Include mod_sftp_sql
[ ] SFTP_PAM         Include mod_sftp_pam
[ ] SITE_MISC        Include mod_site_misc
[ ] SQL_PASSWD       Include mod_sql_passwd
[ ] UNIQUE           Include mod_unique_id
[X] WRAP             Include mod_wrap2
[ ] WRAP_FILE        Include mod_wrap2_file (requires WRAP)
[ ] WRAP_SQL         Include mod_wrap2_sql (requires WRAP)

After installation add this line to /etc/rc.conf:

proftpd_enable="YES"

As you probably know, this will ensure that ProFTPD is started automatically when the server is booted.

Dedicating a range of user id’s

You must decide on a range of user id’s which will be dedicated to ProFTPD and never be used for a unix user. Here is the rules I am following:

Range Used by…
0 – 199 the operating system. System privileged users.
200 – 999 various applications
1000 – 1999 Unix users. People with ssh access
2000 – 2999 virtual ProFTPD users

The above is just a guideline. You can of course decide on a range of your choice, but don’t mess with usernumbers below 200. As you can see I have dedicated the range 2000-2999 to virtual ProFTPD users.

Setup MySQL tables

If you wish to have ProFTPD related data in a seperate database (recommended), create the new database – remember to login to mysql first or use an interface like phpMyAdmin:

create database proftpd;

Create a mysql user for the proftpd daemon. The below will give access to all tables in the database, which is fine if you created the proftpd database. But if the tables is in an existing database, make sure that this user only has access to proftpd related tables.

GRANT SELECT , INSERT , UPDATE , DELETE ON `proftpd` . * TO 'proftpd'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Remember to replace ‘password’ with one of your choice.

If you are in a mysql prompt, use this command to make sure you have selected the proftpd database, before you attempt to create the tables:

use proftpd;

Next create the necessary tables – just copy/paste the below into your mysql prompt or use it in your mysql interface (e.g. phpmyadmin):

CREATE TABLE ftpgroup (
groupname varchar(16) NOT NULL default '',
gid smallint(6) NOT NULL default '80',
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 int(10) unsigned NOT NULL default '0',
bytes_out_avail int(10) unsigned NOT NULL default '0',
bytes_xfer_avail int(10) 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 int(10) unsigned NOT NULL default '0',
bytes_out_used int(10) unsigned NOT NULL default '0',
bytes_xfer_used int(10) 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) NOT NULL auto_increment,
userid varchar(32) NOT NULL default '',
passwd varchar(32) NOT NULL default '',
uid smallint(6) NOT NULL default '',
gid smallint(6) NOT NULL default '80',
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';

Note that the gid field in the ftpgroup and ftpuser tables are set to be 80 by default. Change this to reflect the group id of your systems webserver user.

Configure ProFTPD

Open /usr/local/etc/proftpd.conf and uncomment this line:

DefaultRoot ~

If you do not want or have support for IPv6, comment this line:

#UseIPv6 on

Add this at the end of the file and replace password with the one you chose for the proftpd user (see SQLConnectInfo):

# 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 password

# 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 2000

# create a user's home directory on demand if it doesn't exist. Create it with 755 permission (rwx-r-x-r-x)
CreateHome on dirmode 755

# 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

Create an ftp user

Before you can test your setup, let’s create an ftp user.:

First create a row in the ftpgroup table. Remember to change the values to reflect your webservers unix user:

INSERT INTO ftpgroup (groupname, gid, members) VALUES ('www', 80, 'www');

You only have to run the above query once. The inserts below is the ones you’ll use every time you add a new ftp user:

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', 15728640, 0, 0, 0, 0, 0);

INSERT INTO ftpuser (userid, passwd, uid, gid, homedir, shell, count, accessed, modified) VALUES ('username', 'password', 2000, 80, '/usr/local/www/domain.tld', '/sbin/nologin', 0, '', '');

Remember to make any changes to reflect your own settings. Each field of the ftpquotalimits and ftpuser tables are explained at the bottom of this page.

Next time you create an ftp user, you’ll set the user id to 2001 and so forth.

If the home directory (homedir) is not created yet, ProFTPD will do this automatically. But if it exist, you must ensure that it has the correct user and group. The following command will set user id to 2000 and group to www recursively:

chown -R 2000:www /usr/local/www/domain.tld

Testing…

Start ProFTPD:

# /usr/local/etc/rc.d/proftpd start

Fire up your favorite ftp client from your desktop and try to connect to your server.

ftpuser and ftpquotalimits explained

This is the fields which you’ll fill when creating a new user. Don’t touch any other fields, since they are handled by MySQL or Proftpd automatically

ftpuser table:

* userid: The name of the virtual Proftpd user (e.g. exampleuser).
* passwd: The unencrypted (i.e., clear-text) password of the user.
* uid: The users id (e.g. 2000, 2001 an so forth).
* gid: The groupid of the systems webserver (e.g. 80).
* homedir: The home directory of the virtual Proftpd user (e.g. /usr/local/www/domain.tld). If it does not exist, it will be created when the new user logs in the first time via FTP. The virtual user will be jailed into this home directory, i.e., he cannot access other directories outside his home directory.
* shell: It is ok if you fill in /sbin/nologin here by default. Ftp users do not need a shell.

ftpquotalimits table:

* name: The name of the virtual Proftpd user (e.g. exampleuser).
* quota_type: user or group. Normally, we use user here.
* per_session: true or false. true means the quota limits are valid only for a session. For example, if the user has a quota of 15 MB, and he has uploaded 15 MB during the current session, then he cannot upload anything more. But if he logs out and in again, he again has 15 MB available. false means, that the user has 15 MB at, no matter if he logs out and in again.
* limit_type: hard or soft. A hard quota limit is a never-to-exceed limit, while a soft quota can be temporarily exceeded. Normally you use hard here.
* bytes_in_avail: Upload limit in bytes (e.g. 15728640 for 15 MB). 0 means unlimited.
* bytes_out_avail: Download limit in bytes. 0 means unlimited.
* bytes_xfer_avail: Transfer limit in bytes. The sum of uploads and downloads a user is allowed to do. 0 means unlimited.
* files_in_avail: Upload limit in files. 0 means unlimited.
* files_out_avail: Download limit in files. 0 means unlimited.
* files_xfer_avail: Tranfer limit in files. 0 means unlimited.

The ftpquotatallies table is used by Proftpd internally to manage quotas so you do not have to make entries there.

Troubleshooting

If you need to debug, run the daemon in the foreground as shown here. I will output information to your screen:

#proftpd -n -d 9
- mod_tls/2.1.2: using OpenSSL 0.9.7e-p1 25 Oct 2004
- SQLAuthenticate: use of * in SQLAuthenticate has been deprecated. Use AuthOrder for setting authoritativenes
- SQLAuthenticate: use of * in SQLAuthenticate has been deprecated. Use AuthOrder for setting authoritativeness
- warning: the SQLHomedirOnDemand directive is deprecated, and will be removed in the next release
your.hostname.tld -
your.hostname.tld - Config for ProFTPD Default Installation:
your.hostname.tld - Limit
your.hostname.tld - DenyAll
your.hostname.tld - DefaultServer
[...]

In this example the loglevel is set to the highest possible value (9).

Press [CTRL]+[C] to exit and stop the proftpd daemon.

Thanks to Howto forge – Virtual Hosting With Proftpd And MySQL

Leave a Reply