Transportation Administration System
Snoezelen Pillows for Dementia
Begeleiders voor gehandicapten
Laat uw hond het jaarlijkse vuurwerk overwinnen
Betuweroute en Kunst
Hey Vos! Je eigen naam@vos.net emailadres?
Kunst in huis? Nicole Karrèr maakt echt bijzonder mooie dingen
Kunst in huis? Netty Franssen maakt ook bijzonder mooie dingen | Setup ProFTPD with MySQL backendHow 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.
Tweet
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:
CREATE TABLE users (
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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
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`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
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 (http://www.proftpd.org/docs/contrib/mod_sql.html)
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)
# (http://www.proftpd.org/docs/contrib/mod_sql.html)
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 http://www.proftpd.org/docs/contrib/mod_sql.html
# What to log
SQLLog APPE,DELE,RETR,STOR,STOU,ERR_STOR,EXIT 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 (www.lassosoft.com) 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;
FLUSH PRIVILEGES;
