Announcement

Collapse
No announcement yet.

How to configure your FTP server to read users from a mysql DB

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to configure your FTP server to read users from a mysql DB

    A huge thanks to one of our customers, Gabriel, for putting together this tutorial. Enjoy!

    This guide describes how to configure an FTP server that reads users from a mysql database.
    It has the advantage that you can have many diferent users attached to a singular Unix account in a very easy way.

    What's needed to create an FTP server?

    1 - Unix group, a subgroup, an user, password, and a directory attached to the user
    2 - An FTP server (we'll use pure-ftpd)
    3 - In this case we're going to use MySQL to manage the virtual accounts (users that use Unix users config)
    4 - Config your linux Firewall for the ftp server

    1:

    # useradd -gftp -Gftpusers -pxxxx -d/var/www -m technician

    xxxx is your password
    /var/www is your desired directory
    technician is the username you want

    usually ftp groups are there already, if not, use: # groupadd ftp

    Note that there is an ID attached to the user (UID) and one attached to the group (GID),
    The default is 500 for both, if you set it diferent, use your ID's in the next instructions.

    2:

    In my case I use Fedora, so i did
    # yum install pure-ftpd
    In Ubunto you use
    # apt-get install pure-ftpd-mysql
    Else, Check www.pureftpd.org or your distro documentation on how to install it with mysql support

    Note, if you compile the program by hand, you also need to put quotas support.

    Configuration:

    # nano -w /etc/pure-ftpd/pure-ftpd.conf

    Change the options that you fit your needs, find these that should be uncomment and look like this:

    ChrootEveryone yes
    MySQLConfigFile /etc/pure-ftpd/pureftpd-mysql.conf
    UnixAuthentication yes
    PassivePortRange 30000 50000
    MinUID 499

    # nano -w /etc/pure-ftpd/pureftpd-mysql.conf

    MYSQLSocket /var/run/mysqld/mysqld.sock

    If mysql is in the same box, you don't need to config these 2.
    #MYSQLServer localhost
    #MYSQLPort 3306

    # this is the mysql user that you will create ahead
    MYSQLUser pureftpd

    # that user's password

    MYSQLPassword yourpass

    # the mysql schema's name
    MYSQLDatabase pureftpd


    # Supports encrypted md5, cleartext, crypt() ou password(), I recommend md5
    MYSQLCrypt md5

    # status check means if value is not 1 that user is disabled
    # I use "*" for IP so it allows any, but it may be defined.

    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")

    3:

    make a MySQL user named pureftpd and put the "yourpass" that you wrote in the pureftpd-mysql.conf file.
    make a schema(database) named pureftpd

    If you don't know how to do this, get mysql gui tools here: http://dev.mysql.com/downloads/gui-tools/5.0.html
    or use your control panel

    Run this script in the pureftpd schema (code credits to Emerson Araujo)

    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;

    Now we can add users in this table!
    Remember the technician Unix user we made? now we're going to make a virtual user named richard using technician's ID

    run this script in the pureftpd schema:

    INSERT INTO `ftpd` (`User`, `status`, `Password`, `Uid`, `Gid`, `Dir`, `ULBandwidth`, `DLBandwidth`, `comment`, `ipaccess`, `QuotaSize`, `QuotaFiles`) VALUES ('richard', '1', MD5('yourpass'), '500', '500', '/var/www', '100', '100', '', '*', '0', '0');

    Note: "yourpass" must be the same as the Unix password or it won't work.
    I use Quotafiles and QuotaSize 0 wich means theres no limit for number of files nor size.
    Upload and Download are limited to 100Kb
    Uid and Gid = 500 it's technician user ID and ftp group ID

    4:

    You're ready to go... NOT!

    Open your TCP ports 21 and TCP range 30000 to 50000 (PASV) in your firewall

    Now yes, you're all set... NOT!, you need to restart pure-ftpd

    # /etc/init.d/pure-ftpd restart

    It all should work fine now, login with your ftp client using richard/yourpass

    You should also check linux chmod command that sets files and folders attributes and access. In this example you can use:
    # chmod 666 /var/www

    hope it helped.
    www.AYKsolutions.com
    From Shared to Dedicated
    Professional. Painless. Polite.
Working...
X