Over FTP we can validate users stored in a database with MySQL, PostgreSQL, ODBC or SQLite. In this post we’ll configure proftpd a ftp server running in a Debian squeeze that permit authentication with virtual users stored in a MySQL database.

First we have to install the basic package proftpd, mysql server and o module of proftpd to allow the connection to the database:

# aptitude install proftpd mysql-server proftpd-mod-mysql

Now we have to connect to the database:

# mysql -u root -p

Create a new database named ftpusers:

mysql> create database ftpusers;

Grant all the permissions to the user proftpd:

mysql> grant all on ftpusers.* to [email protected] identified by "PASSWORD";

Create the user table and group table and insert test users:

mysql> CREATE TABLE users (
userid varchar(12) NOT NULL,
passwd varchar(100) NOT NULL,
uid smallint(6) default NULL,
gid smallint(6) default NULL,
homedir varchar(50) default NULL,
shell varchar(20) default '/bin/false');
mysql> CREATE TABLE groups (
groupname varchar(30) NOT NULL,
gid int NOT NULL,
members varchar(255));
mysql> INSERT INTO users VALUES (
'usu1',
PASSWORD('usu123'),
500,
500,
'/var/www/web1',
'/bin/false');
mysql> INSERT INTO users VALUES (
'usu2',
PASSWORD('usu123'),
501,
500,
'/var/www/web1',
'/bin/false');
mysql> INSERT INTO users VALUES (
'usu1.2',
PASSWORD('usu123'),
502,
500,
'/var/www/web2',
'/bin/false');
mysql> INSERT INTO groups VALUES (
'ftpusers',
500,
'usu1,usu2,usu1.2');

To exit of the database run:

mysql> quit

Edit the proftpd file configuration /etc/proftpd/proftpd.conf with your favourite text editor, uncomment the option DefaultRoot, RequireValidShell and PassivePorts. This options is not essential for the mysql configuration, but it’s recommended:

Image

Add a directive AuthOrder with the value mod_sql.c, it’s used for the authentication with sql database:

Image

Uncomment the entry Include for the file /etc/proftpd/sql.conf for the database configuration file:

Image

Image

Edit the file configuration /etc/proftpd/sql.conf, for the database configuration:

  • SQLBackend: the DBMS used.
  • SQLEngine: specify how mod_sql will operate, default on (allow user authentication).
  • SQLAuthenticate on: configure the behavior of the authentication with the database.
  • SQLLogFile: the log file of sql authentication.
  • SQLMinUserUID and SQLMinUserGID: the minimum number of the uid and gid users.
  • SQLAuthTypes: specify the encryption password of the database.
  • SQLConnectInfo: [email protected] user password
  • SQLUserInfo and SQLGroupInfo: the rows of the tables that contains the users information.

Image

Edit /etc/proftpd/modules.conf:

uncomment the directive LoadModule mod_sql_mysql.c and LoadModule mod_sql.c:

Image

Create the group for the ftp users used in the database:

# groupadd -g 500 ftpusers

Create the tree directories of the ftp home directories with the correct permissions:

# mkdir -p /var/www/web1
# mkdir /var/www/web2
# echo web1 >/var/www/web1/fic1
# echo web2 > /var/www/web2/fic2
# chgrp -R ftpusers /var/www/
# chmod -R 775 /var/www/

Restart the proftpd service and test to connect with any user:

# service proftpd restart

Image

ProFTPD + MySQL
Tagged on:             

2 thoughts on “ProFTPD + MySQL

  • May 16, 2012 at 23:26
    Permalink

    I have only one question, what is the best way to assign www-data permissions to files , with the simple fact of uploading a file.

    Reply
    • May 17, 2012 at 21:32
      Permalink

      Hi! changing the gid of the users, when I insert a new group in the database in this tutorial i used gid 500 in the insert command, you can choose the same gid that www-data in /etc/group file in your system. I hope it is useful.

      Reply

Leave a Reply

Follow

Get every new post delivered to your Inbox

Join other followers: