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

Hosting Favorites

ANU Internet Services
XEL Media


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

Marc's Place


 Setup ProFTPD with MySQL backend

  How create FTP-only users.

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.

Buy me a coffee-2

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) Add the following setting on CentOS 7 and higher:

RequireValidShell no
(so you can keep on using the non-existing nologin-shells /sbin/nologin/ and/or /usr/sbin/nologin).

3.c) You might want to specify the port-range to be used with passive FTP:

PassivePorts 49152 65535

3.d) If you have just a few FTP users and the chances of an attack are nil, you might want to turn off the Delay-module:

DelayEngine Off

3.e) To continue with MySQL, 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

As of CentOS 7 and 8 it could be that this line should be removed, because this module will be loaded by the mod_sql_mysql.c module (see following LoadModule)

# Mysql support (requires proftpd-mysql package)
# (
LoadModule mod_sql_mysql.c

3.f) 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
# 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.

Note: if you are copying settings and the MySQL tables for ProFTPd from one server to another:

In my situation, I went from CentOS 6 to CentOS 8 and ProFTPd would start, but the MySQL table could not be used. After spending some time on Google, I found that the copied 'proftpd' user was set to 'expired'. After executing the following SQL statement, it all worked again:

ALTER USER `proftpd`@`localhost` IDENTIFIED BY 'a-clever-password', `proftpd`@`localhost` PASSWORD EXPIRE NEVER;

buy me something-2
© 1997- Marc Vos (and others)   -   Privacy Statement   -    Contact Me

On this website, Google Analytics is used to track visitor statistics. These are anonymised data about the number of visitors, which pages they visit on this site, from which regions they visit, which web browsers they use, etc.. You will also see non-personalised ads via Google AdSense. Cookies from Paddle or Paypal are placed when you click on a 'Buy now!' or 'Donate!' button, and possible cookies from Disqus when you use that system to comment on one or more blogposts.
Privacy Statement