Jump to content

  •  

Photo
- - - - -

How to Set up Virtual FTP with MySQL on Ubuntu and Debian

Ubuntu 12.04 Debian 6

  • Please log in to reply
No replies to this topic

#1 Wilson18

Wilson18

    Administrator

  • root
  • 112 posts
  • LocationUniversity of Birmingham

Posted 25 May 2014 - 12:37 PM

In this guide I will be showing you how you can set up ftp access to the sites we have created. This will be managed by a MySQL database so you can easily add and edit users. You could also write a script to automate this.

 

As there is a lot of writing in this guide, I would recommend you log in to your server via ssh using putty as that way you can copy and paste without having to worry about making mistakes. Once you have logged in to you will need to install pureftp. First, we will be switching to root user. Do this by typing

sudo su

and enter your password when it requests it. Next, install pureftp by typing in:

apt-get install pure-ftpd-mysql

You may be asked if you would like to continue, ensure you click Y to continue.

1.jpg

Once that has finished installing, we then create an ftp group (ftpgroup) and user (ftpuser) that all our virtual users will be mapped to. Replace the group- and userid 2001 with a number that is free on your system:

groupadd -g 2001 ftpgroup
useradd -u 2001 -s /bin/false -d /bin/null -c "pureftpd user" -g ftpgroup ftpuser

Once we have done this, we need to configure the database that we will be using for the user management. We will do this through PHPMyAdmin. You can access this by opening an internet browser and going to the ipaddress of your server followed by /phpmyadmin. For mine, I will be going to

http://10.0.0.150/phpmyadmin

You will then need to log in to your phpmyadmin.

2.jpg

Once logged in, you will see there is a bar accross the top with a tab saying SQL. Click this.

3.jpg

In the box, type in the following:

CREATE DATABASE pureftpd;

Where it says YOURPASSWORDHERE, please enter a password of your choice. Make sure this is the same in the next two snippets.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON pureftpd.* TO 'pureftpd'@'localhost' IDENTIFIED BY 'YOURPASSWORDHERE';GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON pureftpd.* TO 'pureftpd'@'localhost.localdomain' IDENTIFIED BY 'YOURPASSWORDHERE';FLUSH PRIVILEGES;

When you have typed it or pasted it in to the box as shown below, click insert. (If you see # MySQL returned an empty result set (i.e. zero rows). on the end of each line, dont worry its fine!)

4.jpg

We will now create the table to store all off the users data including things like their ftp directory, amount of their storage and number of files allowed. To do this, click on the tab at the top saying Databases and click the database called pureftpd.

5.jpg

Once open, click the SQL Tab again at the top and enter this to create the database:

CREATE TABLE ftpd (
User varchar(16) NOT NULL default '',
status enum('0','1') NOT NULL default '0',
Password varchar(64) NOT NULL default '',
Uid varchar(11) NOT NULL default '-1',
Gid varchar(11) NOT NULL default '-1',
Dir varchar(128) NOT NULL default '',
ULBandwidth smallint(5) NOT NULL default '0',
DLBandwidth smallint(5) NOT NULL default '0',
comment tinytext NOT NULL,
ipaccess varchar(15) NOT NULL default '*',
QuotaSize smallint(5) NOT NULL default '0',
QuotaFiles int(11) NOT NULL default 0,
PRIMARY KEY (User),
UNIQUE KEY User (User)
) ENGINE=MyISAM;

Click insert when done. We have finished creating the database and we can now go back on to our server via putty (or directly if you want to type everything out!).

We are going to be editing the pureftpd database information but we will be creating a copy of it just in case we cockup! We do this by typing in:

cp /etc/pure-ftpd/db/mysql.conf /etc/pure-ftpd/db/mysql.conf_orig

This copies the file and pastes it in the same directory with the name mysql.conf_orig.

Next we need to actually edit the file. we do by typing in:

cat /dev/null > /etc/pure-ftpd/db/mysql.confsudo nano /etc/pure-ftpd/db/mysql.conf

This should then open the file. If it is empty then you will need to write/paste this in:

MYSQLSocket /var/run/mysqld/mysqld.sock
#MYSQLServer localhost
#MYSQLPort 3306
MYSQLUser pureftpd
MYSQLPassword YOURPASSWORDHERE
MYSQLDatabase pureftpd
#MYSQLCrypt md5, cleartext, crypt() or password() - md5 is VERY RECOMMENDABLE uppon cleartext
MYSQLCrypt md5
MYSQLGetPW SELECT Password FROM ftpd WHERE User="\L" AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MYSQLGetUID SELECT Uid FROM ftpd WHERE User="\L" AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MYSQLGetGID SELECT Gid FROM ftpd WHERE User="\L"AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MYSQLGetDir SELECT Dir FROM ftpd WHERE User="\L"AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MySQLGetBandwidthUL SELECT ULBandwidth FROM ftpd WHERE User="\L"AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MySQLGetBandwidthDL SELECT DLBandwidth FROM ftpd WHERE User="\L"AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MySQLGetQTASZ SELECT QuotaSize FROM ftpd WHERE User="\L"AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")
MySQLGetQTAFS SELECT QuotaFiles FROM ftpd WHERE User="\L"AND status="1" AND (ipaccess = "*" OR ipaccess LIKE "\R")

Make sure to change YOURPASSWORDHERE to your chosen password.

Once done, press Ctrl + O to save and Ctrl + X to exit.

We now need to create a few files for the config. Type in the following to do so:

echo "yes" > /etc/pure-ftpd/conf/ChrootEveryone

This will make PureFTPd chroot every virtual user in his home directory so he will not be able to browse directories and files outside his home directory.

Also create the file /etc/pure-ftpd/conf/CreateHomeDir which again simply contains the string yes:

echo "yes" > /etc/pure-ftpd/conf/CreateHomeDir

We now need to resart pureftp to get everything working. We can do so by typing this in:

sudo /etc/init.d/pure-ftpd-mysql restart

By using phpMyadmin, we create the user Test1 with the status 1 (which means his ftp account is active), the password of password (which will be stored encrypted using MySQL’s MD5 function), the UID and GID 2001 (use the userid and groupid of the user/group you created in the first bit), the home directory of /var/www/test1 (the web directory for test1.com which we created in one of our first tutorials), an upload and download bandwidth of 100 KB/sec. (kilobytes per second), and a quota of 50 MB:

INSERT INTO `ftpd` (`User`, `status`, `Password`, `Uid`, `Gid`, `Dir`, `ULBandwidth`, `DLBandwidth`, `comment`, `ipaccess`, `QuotaSize`, `QuotaFiles`) VALUES ('Test1', '1', MD5('password'), '2001', '2001', '/var/www/test1', '100', '100', '', '*', '50', '0');

6.jpg

If you then try and connect with an ftp client such as FileZilla and connect using the information above, you should then be able to connect.

7.jpg

You could also transfer a file and then check on the server and see if it is there after is has uploaded using this command:

ls /var/www/test1

Adding users:

To add users, you could create a php file which you have created yourself (May show you how to do this later!) or by using PHPMyAdmin. you can do this by opening it up and going in to the database and clicking the insert tab at the top.

8.jpg

Whenever you want to create a new user, you have to create an entry in the table ftpd so I will explain the columns of this table here:

a. User: The name of the virtual PureFTPd user (e.g. exampleuser).
b. status: 0 or 1. 0 means the account is disabled, the user cannot login.
c. Password: The password of the virtual user. Make sure you use MySQL’s MD5 function to save the password encrypted as an MD5 string:

9.jpg

d. UID: The userid of the ftp user you created at the end of step two (e.g. 2001).
e. GID: The groupid of the ftp group you created at the end of step two (e.g. 2001).
f. Dir: The home directory of the virtual PureFTPd user (e.g. /var/www/test1). 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.
g. ULBandwidth: Upload bandwidth of the virtual user in KB/sec. (kilobytes per second). 0 means unlimited.
h. DLBandwidth: Download bandwidth of the virtual user in KB/sec. (kilobytes per second). 0 means unlimited.
i. comment: You can enter any comment here (e.g. for your internal administration) here. Normally you leave this field empty.
j. ipaccess: Enter IP addresses here that are allowed to connect to this FTP account. * means any IP address is allowed to connect.
k. QuotaSize: Storage space in MB (not KB, as in ULBandwidth and DLBandwidth!) the virtual user is allowed to use on the FTP server. 0 means unlimited.
l. QuotaFiles: amount of files the virtual user is allowed to save on the FTP server. 0 means unlimited.

 

All credit for this guide goes to :http://ubuntu-for-hu...d-mysql-on.html


  • Rolandcalm likes this

List what you read?

Check me out on: YouTube, Twitter, Facebook, Google+






Also tagged with one or more of these keywords: Ubuntu 12.04, Debian 6