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
- No Comments »
- Posted in FreeBSD
So let’s install the sieve ports…
