ProFTPd+MySQL howto

Proftpd with Mysql backend -command list

This is a quick and dirty proftpd+mysql setup Howto. It lets you setup a ProFTPd server with a MySQL backend so you can manage ftp users on a server from a database.

  • wget ftp://ftp.proftpd.org/distrib/source/proftpd-1.3.0a.tar.gz
  • tar -zxf proftpd-1.3.0a.tar.gz
  • cd proftpd-1.3.0a
  • make dist clean
  • ./configure –with-modules=mod_sql:mod_sql_mysql:mod_quotatab:mod_quotatab_sql –with-includes=/usr/include/mysql –with-libraries=/usr/lib/mysql
  • make
  • make install
  • ln -s /usr/local/sbin/proftpd /usr/sbin/proftpd
  • groupadd nogroup
  • groupadd -g 5500 ftpgroup
  • adduser -u 5500 -s /bin/false -d /bin/null -c “proftpd user” -g ftpgroup ftpuser
  • mysql -u root -p

create database ftpdb;
grant select, insert, update on ftpdb.* to [email protected] identified by ‘password’;

use ftpdb;

#
# Table structure for table `ftpgroup`
#

CREATE TABLE ftpgroup (
groupname varchar(16) NOT NULL default ”,
gid smallint(6) NOT NULL default ‘5500’,
members varchar(16) NOT NULL default ”,
KEY groupname (groupname)
) TYPE=MyISAM COMMENT=’ProFTP group table’;

#
# Dumping data for table `ftpgroup`
#

INSERT INTO `ftpgroup` VALUES (‘ftpgroup’, 5500, ‘ftpuser’);
INSERT INTO `ftpgroup` VALUES (‘ftpgroup’, 5500, ‘ftpguest’);

# ——————————————————–

#
# Table structure for table `ftpquotalimits`
#

CREATE TABLE ftpquotalimits (
name varchar(30) default NULL,
quota_type enum(‘user’,’group’,’class’,’all’) NOT NULL default ‘user’,
per_session enum(‘false’,’true’) NOT NULL default ‘false’,
limit_type enum(‘soft’,’hard’) NOT NULL default ‘soft’,
bytes_in_avail int(10) unsigned NOT NULL default ‘0’,
bytes_out_avail int(10) unsigned NOT NULL default ‘0’,
bytes_xfer_avail int(10) unsigned NOT NULL default ‘0’,
files_in_avail int(10) unsigned NOT NULL default ‘0’,
files_out_avail int(10) unsigned NOT NULL default ‘0’,
files_xfer_avail int(10) unsigned NOT NULL default ‘0’
) TYPE=MyISAM;

#
# Table structure for table `ftpquotatallies`
#

CREATE TABLE `ftpquotatallies` (
`name` varchar(30) NOT NULL default ”,
`quota_type` enum(‘user’,’group’,’class’,’all’) NOT NULL default ‘user’,
`bytes_in_used` float NOT NULL default ‘0’,
`bytes_out_used` float NOT NULL default ‘0’,
`bytes_xfer_used` float NOT NULL default ‘0’,
`files_in_used` int(10) unsigned NOT NULL default ‘0’,
`files_out_used` int(10) unsigned NOT NULL default ‘0’,
`files_xfer_used` int(10) unsigned NOT NULL default ‘0’
) TYPE=MyISAM;

# ——————————————————–

#
# Table structure for table `ftpuser`
#

CREATE TABLE ftpuser (
id int(10) unsigned NOT NULL auto_increment,
userid varchar(32) NOT NULL default ”,
passwd varchar(32) NOT NULL default ”,
uid smallint(6) NOT NULL default ‘5500’,
gid smallint(6) NOT NULL default ‘5500’,
homedir varchar(255) NOT NULL default ”,
shell varchar(16) NOT NULL default ‘/sbin/nologin’,
count int(11) NOT NULL default ‘0’,
accessed datetime NOT NULL default ‘0000-00-00 00:00:00’,
modified datetime NOT NULL default ‘0000-00-00 00:00:00′,
PRIMARY KEY (id),
UNIQUE KEY userid (userid)
) TYPE=MyISAM COMMENT=’ProFTP user table’;

INSERT INTO `ftpuser` VALUES (1, ‘testaccount’, ‘ftppasswd’, 5500, 5500, ‘/home/testdomain.com’, ‘/sbin/nologin’,0,”,”);

exit;

  • vi /usr/local/etc/proftpd.conf
  • DefaultRoot ~

    # Normally, we want files to be overwriteable.

    AllowOverwrite on

    # The passwords in MySQL are encrypted using CRYPT
    SQLAuthTypes Plaintext Crypt
    #SQLAuthenticate users* groups*
    AuthOrder mod_sql.c mod_auth_unix.c

    # used to connect to the database
    # [email protected] database_user user_password
    SQLConnectInfo [email protected] proftpd PASSWORD

    # Here we tell ProFTPd the names of the database columns in the “usertable”
    # we want it to interact with. Match the names with those in the db
    SQLUserInfo ftpuser userid passwd uid gid homedir shell

    # Here we tell ProFTPd the names of the database columns in the “grouptable”
    # we want it to interact with. Again the names match with those in the db
    SQLGroupInfo ftpgroup groupname gid members

    # set min UID and GID – otherwise these are 999 each
    SQLMinID 500

    # create a user’s home directory on demand if it doesn’t exist
    SQLHomedirOnDemand on

    # Update count every time user logs in
    SQLLog PASS updatecount
    SQLNamedQuery updatecount UPDATE “count=count+1, accessed=now() WHERE userid=’%u'” ftpuser

    # Update modified everytime user uploads or deletes a file
    SQLLog STOR,DELE modified
    SQLNamedQuery modified UPDATE “modified=now() WHERE userid=’%u'” ftpuser

    # User quotas
    # ===========
    QuotaEngine on
    QuotaDirectoryTally on
    QuotaDisplayUnits Mb
    QuotaShowQuotas on

    SQLNamedQuery get-quota-limit SELECT “name, quota_type, per_session, limit_type, bytes_in_avail, bytes_out_avail, bytes_xfer_avail, files_in_avail, files_out_avail, files_xfer_avail FROM ftpquotalimits WHERE name = ‘%{0}’ AND quota_type = ‘%{1}'”

    SQLNamedQuery get-quota-tally SELECT “name, quota_type, bytes_in_used, bytes_out_used, bytes_xfer_used, files_in_used, files_out_used, files_xfer_used FROM ftpquotatallies WHERE name = ‘%{0}’ AND quota_type = ‘%{1}'”

    SQLNamedQuery update-quota-tally UPDATE “bytes_in_used = bytes_in_used + %{0}, bytes_out_used = bytes_out_used + %{1}, bytes_xfer_used = bytes_xfer_used + %{2}, files_in_used = files_in_used + %{3}, files_out_used = files_out_used + %{4}, files_xfer_used = files_xfer_used + %{5} WHERE name = ‘%{6}’ AND quota_type = ‘%{7}'” ftpquotatallies

    SQLNamedQuery insert-quota-tally INSERT “%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}” ftpquotatallies

    QuotaLimitTable sql:/get-quota-limit
    QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally

    RootLogin off
    RequireValidShell off

  • proftpd -td5 —-config check
  • proftpd -nd6 —– debug mode
  • /etc/init.d/proftpd restart
  • Now if you add users to the ftpusers table you should be able to login with those credentials without having to do anything else.

    Please feel free to contact me if you run into a problem while setting this up.

    FTP Check

    Recently I came up with the need to test multiple ftp accounts on a server. The server has been setup using proftpd, with mysql as the backend for the authentication and qoutas.

    Anyways so here is the small script which I put together. It does not use the db but can be easily modified to use the db and run tests on multiple accounts. Place this script on the host where you want to test and call it through an http call in a webbrowser.

    < ?php
    //initialize the variables which we’ll use for the login test
    $userid=$_GET[‘uid’];
    $password=$_GET[‘pass’];

    //this too can be a query string variable if you want to test the ftp accounts on a different server
    $ftp_server = “localhost”;
    $conn_id = ftp_connect($ftp_server) or die(“Couldn’t connect to $ftp_server”);

    // Open a session to an external ftp site
    $login_result = ftp_login ($conn_id, $userid, $password);

    // Check open
    if ((!$conn_id) || (!$login_result)) {
    echo “Ftp-connect failed!”; die;
    } else {
    echo “Connected.”;
    }

    //most important clean up after you’re done!
    ftp_close()

    ?>

    The utility of this extremely simple script is more evident when you consider a PHP hosting setup where you dont really want to use a ftp client to test. There just calling the script with the variables pulled from a db is enough to test the validity of credentials.

    This is a part of the proftpd+mysql writeup which I’ll post pretty soon

    ffmpeg-php on dreamhost shared host

    Please use the server setup page to request a quote.

    How to install ffmpeg-php on a dreamhost shared host under a users account

    This HOWTO now includes setup instructions for the following stuff:

    • autoconf
    • LAME
    • ffmpeg
    • GD2
    • mplayer+mencoder
    • Ruby
    • flvtool2
    • libogg
    • libvorbis
    • PHP
    • ffmpeg-php

    Disclaimer: Some of you might not be able to make it all work due to various reasons, that does not mean it cannot be done.

    For installation assistance please also read The install prerequisites page where we list the requirements for the server setup. Clip-share, vshare, alstrasoft, social media are all scripts which we have setup successfully on Dreamhost, Bluehost, godaddy and many other shared hosting environments (as on 10May2007)

    Rayzz, PHPMotion and OSTube also successfully deployed on shared hosts (as on 10August 07)

    Prepare the directory
    structure
    mkdir bin
    mkdir lib
    mkdir tmp
    chmod 777 tmp
    chmod 775 bin
    chmod 775 lib

    Export some environment
    variables
    export TMPDIR=$HOME/tmp
    export PATH=$HOME/bin:$PATH
    export LD_LIBRARY_PATH=$HOME/lib:/usr/local/lib:$LD_LIBRARY_PATH
    export CPATH=$HOME/include:/usr/local/include:$CPATH
    export LIBRARY_PATH=$HOME/lib:/usr/local/lib:$LIBRARY_PATH
    mkdir src
    cd src

    AUTOCONF
    wget http://ftp.gnu.org/gnu/autoconf/autoconf-2.60.tar.gz
    tar -zxf autoconf-2.60.tar.gz
    cd autoconf-2.60
    ./configure "--prefix=$HOME"
    make
    make install

    LAME

    wget http://nchc.dl.sourceforge.net/sourceforge/lame/lame-3.97.tar.gz
    tar -zxvf lame-3.97.tar.gz
    cd lame-3.97
    ./configure "--prefix=$HOME" "--enable-shared"
    make
    make install

    LIBOGG

    wget http://downloads.xiph.org/releases/ogg/libogg-1.1.3.tar.gz
    tar -zxf
    libogg-1.1.3.tar.gz
    cd libogg-1.1.3
    ./configure --prefix=$HOME
    make
    make install

    LIBVORBIS

    wget http://downloads.xiph.org/releases/vorbis/libvorbis-1.1.2.tar.gz
    tar -zxf libvorbis-1.1.2.tar.gz
    cd libvorbis-1.1.2
    ./configure --prefix=$HOME
    make
    make install

    FFMPEG

    svn checkout svn://svn.mplayerhq.hu/ffmpeg/trunk ffmpeg
    cd ffmpeg/
    ./configure "--prefix=$HOME" "--cross-compile" "--enable-shared" "--enable-mp3lame" "--extra-cflags=-I$HOME/include" "--extra-ldflags=-L$HOME/lib"
    make
    make install

    GD2

    wget http://www.boutell.com/gd/http/gd-2.0.33.tar.gz
    tar -zxf gd-2.0.33.tar.gz
    cd gd-2.0.33
    ./configure --prefix=$HOME
    make
    make install

    MPlayer + Mencoder

    wget http://www3.mplayerhq.hu/MPlayer/releases/codecs/essential-20061022.tar.bz2
    bunzip2 essential-20061022.tar.bz2
    tar -xf essential-20061022.tar
    mv essential-20061022 $HOME/lib
    wget http://www3.mplayerhq.hu/MPlayer/releases/MPlayer-1.0rc1.tar.bz2
    bunzip2 MPlayer-1.0rc1.tar.bz2
    tar -xf MPlayer-1.0rc1.tar
    cd MPlayer-1.0rc1
    ./configure --prefix=$HOME --with-codecsdir=$HOME/lib/essential-20061022
    make
    make install

    RUBY
    cd $HOME/src
    wget ftp://ftp.ruby-lang.org/pub/ruby/stable-snapshot.tar.gz
    tar -zxf stable-snapshot.tar.gz
    cd ruby
    make distclean
    ./configure --prefix=$HOME
    make
    make install

    FLVTOOL2

    wget http://rubyforge.org/frs/download.php/9225/flvtool2_1.0.5_rc6.tgz
    tar -zxvf flvtool2_1.0.5_rc6.tgz
    cd flvtool2_1.0.5_rc6
    ruby setup.rb config –prefix=$HOME
    ruby setup.rb setup
    ruby setup.rb install

    PHP
    wget http://us2.php.net/distributions/php-4.4.4.tar.bz2
    bunzip2 php-4.4.4.tar.bz2
    tar xf php-4.4.4.tar
    cd php-4.4.4
    ./configure --prefix=$HOME --libdir=$HOME/lib --bindir=$HOME/bin --with-ffmpeg=$HOME
    make
    make install

    FFMPEG-PHP
    wget http://superb-east.dl.sourceforge.net/sourceforge/ffmpeg-php/ffmpeg-php-0.5.0.tbz2
    bunzip2 ffmpeg-php-0.5.0.tbz2
    tar -xf ffmpeg-php-0.5.0.tar
    cd ffmpeg-php-0.5.0
    phpize
    ./configure "--prefix=$HOME" "--libdir=$HOME/lib" "--bindir=$HOME/bin" "--with-ffmpeg=$HOME"
    make clean
    make install

    Setup users environment
    to use the php installed earlier

    cd $HOME/lib
    cp $HOME/lib/php/extensions/no-debug-non-zts-20020429/ffmpeg.so ./
    cp $HOME/src/php-4.4.4/php.ini-recommended php.ini
    vi php.ini (add extension= /home//lib/ffmpeg.so)</STRONG>< /STRONG>

    To enable site:
    cp $HOME/bin/php $HOME/cgi-bin/php.cgi

    add a .htaccess with following content

    AddHandler phpFive .php
    Action phpFive /cgi-bin/php.cgi

    You might need to also run the following

    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$HOME/lib
    ” export LIBRARY_PATH=
    $HOME/lib:$LIBRARY_PATH”
    “export
    CPATH=$HOME/include/:$HOME/include/lame/”

    as well as put it ahead of any
    call to ffmpeg in your PHP code. Please send questions through the sitecontact form
    .-Imtiaz (khanimtiaz at gmail dot com)

    We have got this procedure working on a variety
    of shared hosts besides dreamhost. For installation assistance please also read
    the install prerequisites page

    mod geoip

    Often it is desireable to Geotarget your web content. This is a way we do geotargetting using MaxMinds geoip database.

    This document is a stepwise explanation of how to
    1. obtain the relevant csv files from GeoIP
    2. create the tables
    3. import the data
    4. use this data to figure out where your visitors are coming from

    It is relevant when you use the MySQL based solution combined with PHP. There’s a simpler method to achieve this as well please use the contact form or post a comment here to discuss that.

    -by Imtiaz A. Khan -230906 [email protected]

    Step 1. -Obtain the relevant csv
    ================================
    GeoIP provides a free version of there database that can be used for pretty much all the functionality
    of geotargetting The csv version of the database is available at
    http://www.maxmind.com/download/geoip/database/GeoLiteCity_CSV/GeoLiteCity_20060901.zip

    For the latest version Please visit : http://www.maxmind.com/app/geolitecity

    Extract the compressed files after download completes. You should have two files namely..
    GeoLiteCity-Blocks.csv and GeoLiteCity-Location.csv

    Rename these to locip.csv and lookup.csv respectively. Remove all double qoutes from locip.csv.

    The data in these files is under the following heads:
    locip.csv: startIpNum,endIpNum,locId
    lookup.csv: locId,country,region,city,postalCode,latitude,longitude,dmaCode,areaCode

    We’re going to import these to the database in the next step. Before that remove
    the header rows and any copyright information from the files as we need just the data.

    Step 2. -create the database and the tables
    ============================================
    We need a database for the two tables that will be used. And a table structure for the data.

    login to your MySQL server and run the following commands:

    CREATE DATABASE geoip;

    USE geoip;
    CREATE TABLE locip (
    start_ip INT UNSIGNED NOT NULL,
    end_ip INT UNSIGNED NOT NULL,
    locId INT UNSIGNED NOT NULL
    );

    CREATE TABLE lookup (
    locId INT UNSIGNED NOT NULL,
    country CHAR(15) NOT NULL,
    region CHAR(2) NOT NULL,
    city CHAR(30) NOT NULL,
    postalcode CHAR(15) NOT NULL,
    latitude CHAR(15) NOT NULL,
    longitude CHAR(15) NOT NULL,
    dmaCode CHAR(15) NOT NULL,
    areaCode CHAR(15) NOT NULL
    );

    Step 3. -import the data
    ========================
    Next we do the actual import. From the command line run the following
    commands to import the data in csv files into the MySQL tables

    mysqlimport –delete –local –fields-terminated-by=’,’
    –fields-optionally-enclosed-by='”‘
    –lines-terminated-by=’n’ –user=’user’
    –password=’password’ geoip locip.csv

    mysqlimport –delete –local –fields-terminated-by=’,’
    –fields-optionally-enclosed-by='”‘ –lines-terminated-by=’n’
    –user=’user’ –password=’password’ geoip lookup.csv

    NOTE: The names of files and the qoutes are important.

    Check the data in the database by running random selects just to make sure all got in fine.

    Step 4. -check your work
    ========================
    You can take a look at GeoIP in action using the following php script.

    < ?php global $link; $link= mysql_connect("localhost","username","password"); if( !$link ) die("could not connect to MySQL"); mysql_select_db("geoip",$link) or die("Could't open organizer::" .mysql_error() ); $a=split(".", $REMOTE_ADDR); $ip=$a[0] . "." . $a[1] . "." . $a[2]. "." . $a[3]; $ip_num=16777216*$a[0] + 65536*$a[1] + 256*$a[2] + $a[3]; $sql_getlocid="SELECT locId FROM locip WHERE $ip_num >= start_ip AND $ip_num< = end_ip"; $result=mysql_query($sql_getlocid,$link); $row = mysql_fetch_assoc($result); $locid=$row["locId"]; $sql_getreg="SELECT * FROM lookup WHERE locId=" . $locid; $result=mysql_query($sql_getreg,$link); $row = mysql_fetch_assoc($result); $region=$row["region"]; echo "IP--->” . $REMOTE_ADDR . “”;
    echo “Country—>” . $row[“country”] . “”;
    echo “Region—>” . $row[“region”] . “”;
    echo “City—>” . $row[“city”] . “”;
    echo “Longitude—>” . $row[“longitude”] . “”;
    echo “Latitude—>” . $row[“latitude”] . “”;
    echo “Area Code—>” . $row[“areaCode”] . “”;
    echo “Postal Code—>” . $row[“postalcode”] . “”;

    ?>

    This script when run properly should give you details of your own machines IP.
    If the GeoIP database doesn’t have correct information about your IP then it’ll
    not show some of the data.

    To see different results as in how it would behave if someone else from someplace
    else tried to access your machine try changing the proxy settings on your system and
    accessing the script: Use the proxy list available here: http://www.samair.ru/proxy/

    Remember the free database is 97% accurate for IPs in the USA and approximately 60%
    for the rest of the world, which means even though it’ll tell you the country information
    correct it might not be able to give you the finer details like city or region.

    Please contact Imtiaz at [email protected] in case you need assistance with your GeoIP setup/usage.

    automate subdomain creation + Apache

    How to setup a LAMP server with mod_vhost_alias capability.

    This document tell you how to setup a webserver so that all you need to do is upload to a ftp folder and tada you have a new subdomain. Read on!

    Install MySQL

    1. prefereably install using yum or up2date. If they fail then follow
    directions on http://dev.mysql.com/doc/refman/5.0/en/quick-install.html

    Build Apache

    1. wget http://download.nextag.com/apache/httpd/httpd-2.2.3.tar.gz
    2. tar -zxf httpd-2.2.3.tar.gz
    3. cd httpd-2.2.3
    4. configure the build using following options
    
    ./configure
    "--prefix=/usr/local/apache"
    "--enable-so"
    "--enable-vhost-alias"
    "--enable-ssl=shared"
    "--enable-cgi=shared"
    "--enable-negotiation=shared"
    "--enable-setenvif=shared"
    "--enable-asis=shared"
    "--enable-alias=shared"
    "--enable-include=shared"
    "--enable-mime=shared"
    "--enable-status=shared"
    "--enable-actions=shared"
    "--enable-env=shared"
    "--enable-log_config=shared"
    "--enable-access=shared"
    "--enable-userdir=shared"
    "--enable-dir=shared"
    "--enable-auth=shared"
    "--enable-autoindex=shared"
    "--enable-imap=shared"
    "--enable-rewrite=shared"
    "--enable-suexec"
    "--with-perl=/usr/bin/perl"
    "$@"
    
    5. make | grep -i error
    6. make install
    7. /usr/local/apache/bin/apachectl configtest
    8. /usr/local/apache/bin/apachectl start

    Integrate PHP

    1. wget http://in2.php.net/get/php-5.1.6.tar.gz/from/in.php.net/mirror
    2. tar -zxf php-5.1.6.tar.gz
    3. cd php-5.1.6
    4. Configure the build using following command
    
    ./configure --prefix=/apache
    --with-apxs2=/usr/local/apache/bin/apxs
    --with-config-file-path=/usr/local/apache/conf
    --enable-libxml
    --disable-ipv6
    --disable-cgi
    --with-mysql
    --with-zlib=/usr
    --with-zlib-dir=/usr
    --without-mhash
    --without-java
    
    5. make | grep -i error
    6. make install
    
    7. Edit /usr/local/apache/conf/httpd.conf and add following lines at relevant sections
    LoadModule php5_module        modules/libphp5.so
    AddHandler php5-script php
    
    # Add index.php to your DirectoryIndex line:
    DirectoryIndex index.php index.html
    
    AddType text/html       php
    
    # PHP Syntax Coloring
    AddType application/x-httpd-php-source phps
    
    8. /usr/local/apache/bin/apachectl configtest
    9. /usr/local/apache/bin/apachectl start

    Configure mod_vhost_alias

    1. Add a user for the vhosts which will be used for running the server and uploading content.
    useradd vhost
    passwd vhost
    
    2. If it exists, uncomment the Load module line for the vhost_alias module
    LoadModule vhost_alias_module libexec/httpd/mod_vhost_alias.so
    
    3. If it exists, uncomment the AddModule one too:
    AddModule mod_vhost_alias.c
    
    4. add the User and Group directives (add these outside of
    User vhost
    Group vhost
    
    5. Look for the following section:
    < Directory />
    Options FollowSymLinks
    AllowOverride All
    Order deny,allow
    Deny from all
    < /Directory >
    change it to look as under:
    < Directory />
    Options FollowSymLinks
    AllowOverride All
    Order deny,allow
    Allow from all
    < /Directory >
    
    6. Now add this snippet at the end of the file:
    VirtualDocumentRoot /home/vhost/%2+/%1
    VirtualScriptAlias /home/vhost/%2+/%1/cgi-bin
    
    7. Add content to the /home/vhost directory
    i... ftp to the server using username and password created in step 1
    ii... create a folder with the domain name as name eg. /home/vhost/yourdomain.com
    iii... upload a site for example www.yourdomain.com in the folder www under the
    folder for domain will mean you upload content to /home/vhost/yourdomain.com/www
    
    8. /usr/local/apache/bin/apachectl configtest
    9. /usr/local/apache/bin/apachectl start
    
    If you don't see any errors, everything should be working.
    
    At this point, you should be able to add folders like /home/vhost/com-co.info/example
    and access those as http://example.com-co.info