Software development, photography, jokes, ....

Sites by me

tas-logoTransportation Administration System
snoezelkussen-logo-kleinstSnoezelen Pillows for Dementia
ikzoekeenbegeleider-logoBegeleiders voor gehandicapten
Laat uw hond het jaarlijkse vuurwerk overwinnen
Betuweroute en Kunst
logo 50x50Hey Vos! Je eigen emailadres?
Kunst in huis? Nicole Karrèr maakt echt bijzonder mooie dingen
nettylogo2Kunst in huis? Netty Franssen maakt ook bijzonder mooie dingen
Professionele opvang bij Gastouderbureau
Salarisadministratie en belastingadvies bij
De Zaak Loont
Zutphense Bomenstichting

Hosting Favorites

ANU Internet Services
XelMedia .internet services


Google Translate
jQuery UI
YourHead Stacks API
Favicon Generator. For real.
Check HTTPS problems

Marc's Place


 Setup ProFTPD with MySQL backend

  How create FTP-only users.
Pasted Graphic 1

I have this CentOS server. And instead of creating genuine Linux users, I wanted a way to create FTP-only users without having to go into Linux and do stuff for every new user. So I was advised to use ProFTPD, which can use a MySQL backend to authenticate non-Linux users against, and, most important, if no valid user is found there checks against standard Linux user-accounts.

1) Install ProFTPD with the MySQL package

$ yum install proftpd-mysql

2) Create a MySQL database, for example: proftpd
2.a) Create a table named users (required) and an optional table for logging up- and downloads: 

  userid varchar(30) NOT NULL,
  passwd varchar(80) NOT NULL,
  uid int(11) DEFAULT NULL,
  gid int(11) DEFAULT NULL,
  homedir varchar(255) DEFAULT NULL,
  shell varchar(255) DEFAULT NULL,
  zz_comment varchar(255) DEFAULT NULL COMMENT 'Who is this?',
  PRIMARY KEY (`userid`)

CREATE TABLE `ftplog` (
  logid bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Record ID',
  loguser varchar(50) NOT NULL COMMENT 'Username',
  logdate datetime NOT NULL COMMENT 'Date of up- or download',
  logcmd char(10) NOT NULL COMMENT 'FTP command',
  logfile varchar(255) NOT NULL COMMENT 'Up- or downloaded file',
  logstatus_ftp smallint(5) unsigned NOT NULL COMMENT 'FTP Status',
  zz_proc char(1) NOT NULL COMMENT 'File processed status',
  PRIMARY KEY (`logid`),
  KEY `user_date` (`loguser`,`logdate`,`logcmd`,`logfile`),
  KEY `date_cmd` (`logdate`,`logcmd`,`loguser`),
  KEY `status_cmd` (`logstatus_ftp`,`logcmd`,`logdate`,`loguser`),
  KEY `user_proc_cmd` (`loguser`,`zz_proc`,`logcmd`)

The nice thing about this solution is that you can add your own columns at the end and use them for whatever you like. For example, the columns whose names begin with 'zz_' are my own and not used by ProFTPD.

2.b) Create a MySQL user, for example proftpd@localhost, for ProFTPD and grant access for select and insert for the database proftpd.

mysql> show grants for proftpd@localhost;
| Grants for proftpd@localhost |
| GRANT CREATE TABLESPACE ON *.* TO 'proftpd'@'localhost' IDENTIFIED BY PASSWORD '*25A81E80785004A56F7A44E7E66070725E1B0C4B' |
| GRANT SELECT, INSERT ON `proftpd`.* TO 'proftpd'@'localhost' |
2 rows in set (0,00 sec)

3) Edit the file /etc/proftpd.conf
3.a) Change the AuthOrder setting into:

AuthOrder                  mod_sql.c mod_auth_unix.c

to make sure ProFTPD looks in MySQL first, then continues to look for regular Linux-users.

3.b) Uncomment the following lines:

# General database support (
LoadModule mod_sql.c
# Support for base-64 or hex encoded MD5 and SHA1 passwords from SQL tables
# (contrib/mod_sql_passwd.html)
LoadModule mod_sql_passwd.c
# Mysql support (requires proftpd-mysql package)
# (
LoadModule mod_sql_mysql.c

3.c) Add the following lines at the end:

# MySQL authentication backend

# Enable the backend for authenticatio only
SQLEngine on

# Connection info for localhost (either via TCP or socket - I choose TCP)
#SQLConnectInfo proftpd@/var/lib/mysql/mysql.sock mysql-user password
SQLConnectInfo proftpd:3306 mysql-user password

# Passwords are stored in plain text
SQLAuthTypes Plaintext

# Only authenticate users from the SQL DB (not groups) from table 'users'
SQLAuthenticate users

# Minimum UID/GID permissible
SQLMinID 500

# Group defaults to 500
SQLDefaultGID 500

# If a user’s homedir cannot be located
SQLDefaultHomedir /var/ftp

# for more configuration options see

# What to log
SQLLog RETR,STOR insertfileinfo
# The query to use to insert a log entry in table 'ftplog'
SQLNamedQuery insertfileinfo INSERT "0, '%u', now(), '%m', '%f', %s, ' '" ftplog
# Log some stuff to a file. Is off - the file can grow very large very quick.
#SQLLogFile /var/log/proftpd/sql.log

4) De-activate vsFTPd and activate ProFTPD. 
Make sure vsFTPd is not started at boot, but ProFTPD is.

chkconfig vsftpd off
chkconfig proftpd on

5) Create an ftproot with subfolders for each virtual user, for example /var/ftp/

If you use Lasso ( to access these folders, set the group-ID of the ftproot folder to that of Lasso and allow Lasso to write:

$ chown root:lasso ftproot (lasso 8)
$ chown root:_lasso ftproot (lasso 9)
$ chmod g+w ftproot

5.a) Create an upload-only folder:

$ mkdir someuserw
$ chown _lasso:_lasso someuserw
$ chmod u=rwx,g=wx,o= someuserw

After that, it should look like this:

drwx-wx---  _lasso _lasso

5.b) Create a read-only folder:

$ mkdir someuserr
$ chown _lasso:_lasso someuserr
$ chmod u=rwx,g=rx,o= someuserr

6) Create virtual user records in the 'users'-table:

The user-ID (UID) can be anything > 500, but should be something that is not in use already for real users. So I use 999.
The group-ID (GID) should be the same as is used by either Lasso (500) or PHP/Apache, so you can do file operations.

mysql> use proftpd;
mysql> insert into users values ('someuserw', 'password', 999, 500, '/var/ftp/someuserw', '/sbin/nologin', ’S. Omeuser');
mysql> insert into users values ('someuserr', 'password', 999, 500, '/var/ftp/someuserr', '/sbin/nologin', 'A.N. Otheruser');

7) Now stop vsFTPd and start ProFTPD:

service vsftpd stop
service proftpd start

8) Test access and let the user know.

Donate via Paypal
© 1997- Marc Vos (and others)   -   Privacy Statement   -    Contact Me