How to install and Configure ProFTPd with integrate the MySQL

21 10 2008

What is ProFTPd ?

ProFTPd is a FTP daemon for unix and unix-like operating systems. With we using ProFTPd we can develop a FTP Server. And also ProFTPd easy to configuration.

System requirements
1. Apache
2. Mysql
3. PhpMyadmin

You can use yum to install these packages:

[root@heri ~] yum install mysql mysql-server phpMyAdmin httpd

Step by step instalation:

1. Install Proftpd-mysql with “YUM”.

[root@heri ~]# yum install proftpd-mysql

2. Use the following command to make sure instalation of ProFTPd.

[root@heri ~]# rpm -qa | grep proftpd
proftpd-mysql-1.3.1-1.el5.rf
proftpd-1.3.1-1.el5.rf

3. Create user(ftpuser) and group(ftpgroup) proftpd, I’am assumsing that the group and userid is 2001. Using a number that is free on your system.

[root@heri ~]# groupadd -g 2001 ftpgroup
[root@heri ~]# useradd -u 2001 -s /bin/false -d /bin/null -c "proftpd user" -g ftpgroup ftpuser

4. Log into your mysql, and then please enter your username and password your mysql.

[root@heri ~]# mysql -u root -p

5. Create database to proftpd with the database name is “ftp”.

mysql> create database ftp;

6. Create an user and give the priveleges to management the database “ftp”.

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftpd'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON ftp.* TO 'proftpd'@'localhost.localdomain' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

7. Create tables to database “ftp“.

mysql> use ftp;
mysql> 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';

mysql> 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;

mysql> CREATE TABLE ftpquotatallies (
    -> name varchar(30) NOT NULL default '',
    -> quota_type enum('user','group','class','all') NOT NULL default 'user',
    -> bytes_in_used int(10) unsigned NOT NULL default '0',
    -> bytes_out_used int(10) unsigned NOT NULL default '0',
    -> bytes_xfer_used int(10) unsigned 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;

mysql> 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'

Configuration:

1. Edit file configuration your ProFTPd in /etc/proftpd.conf. And Comment the AuthPAMConfig and AuthOrder lines.

[root@heri ~]# vi /etc/proftpd.conf
[..]
# Use pam to authenticate (default) and be authoritative
#AuthPAMConfig                  proftpd
#AuthOrder                      mod_auth_pam.c* mod_auth_unix.c
[..]

2. Add the following lines bellow after this line <IfModule mod_dso.c>…</IfModule>.

[..]
# SQL authentication Dynamic Shared Object (DSO) loading
# See README.DSO and howto/DSO.html for more details.
#<IfModule mod_dso.c>
#   LoadModule mod_sql.c
#   LoadModule mod_sql_mysql.c
#   LoadModule mod_sql_postgres.c
#</IfModule>

LoadModule mod_sql.c
LoadModule mod_sql_mysql.c
LoadModule mod_quotatab.c
LoadModule mod_quotatab_sql.c
# The passwords in MySQL are encrypted using CRYPT
SQLAuthTypes            Plaintext Crypt
SQLAuthenticate         users groups

# used to connect to the database
# databasename@host database_user user_password
SQLConnectInfo  ftp@localhost 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

# 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

[..]

Please make sure about password of user “proftpd” for the Mysql user in line SQLConnectInfo!.

3. Start service proftpd.

[root@heri ~]# /etc/init.d/proftpd start

4. Log into your mysql and use the following command to create a new user and group in table “ftpgroup”.

[root@heri ~]# mysql -u root -p
mysql> use ftp;
mysql> INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES ('ftpgroup', 2001, 'ftpuser');

5. Then use the following command to create a new virtual FTP user(example:heri) and with a quota of 15MB and password “heri1234“.

mysql> INSERT INTO `ftpquotalimits` (`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`) VALUES ('heri', 'user', 'true', 'hard', 15728640, 0, 0, 0, 0, 0);
mysql> INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES (1, 'heri', 'heri1234', 2001, 2001, '/home/www.heri.com', '/sbin/nologin', 0, '', '');

6. Now, tried log in to your FTP server like this picture bellow, Please enter username and password of heri.

7. After you running the command like above then you will see directory “http://www.heri.com” in your “home” directory, like this picture bellow.

Database Administration:

For management users FTP in your database you can use phpmyadmin to management it. Please point your browser to http://ip_address_your_server/phpmyadmin. Then you will see like this picture bellow.

Anonymous User:
1. Add user and group for anonymous.

[root@heri ~]# groupadd -g 2002 anonymous_ftp
[root@heri ~]# useradd -u 2002 -s /bin/false -d /home/anonymous_ftp -m -c "Anonyous FTP User" -g anonymous_ftp anonymous_ftp

2. Change directory to “/home/anonymous_ftp“.

[root@heri ~]#cd /home/anonymous_ftp
[root@heri anonymous_ftp]# rm -rf *

3. Create a directory with name “incoming“.

[root@heri anonymous_ftp]# mkdir incoming

4. Now, change ownner of incoming directory.

[root@heri anonymous_ftp]# chown anonymous_ftp:nobody incoming/
[root@heri anonymous_ftp]# ll
total 4
drwxr-xr-x 2 anonymous_ftp nobody 4096 Oct 21 01:22 incoming

5. And finally, open file your configuration proftpd and changes your configuration until like this bellow.

# A basic anonymous configuration, with an upload directory.
<Anonymous ~anonymous_ftp>
  User                          anonymous_ftp   Group                         nobody
  AccessGrantMsg                "Anonymous login ok, restrictions apply."
#
#  # We want clients to be able to login with "anonymous" as well as "ftp"
  UserAlias                     anonymous anonymous_ftp
#
#  # Limit the maximum number of anonymous logins
  MaxClients                    10 "Sorry, max %m users -- try again later"
#
#  # Put the user into /pub right after login
#  #DefaultChdir                        /pub
#
#  # We want 'welcome.msg' displayed at login, '.message' displayed in
#  # each newly chdired directory and tell users to read README* files.
  DisplayLogin                  /welcome.msg
  DisplayFirstChdir             .message
#  DisplayReadme                        README*
#
#  # Some more cosmetic and not vital stuff
  DirFakeUser                   on anonymous_ftp
  DirFakeGroup                  on anonymous_ftp
#
#  # Limit WRITE everywhere in the anonymous chroot
  <Limit WRITE SITE_CHMOD>
    DenyAll
  </Limit>
#
#  # An upload directory that allows storing files but not retrieving
#  # or creating directories.
  <Directory uploads/*>
    AllowOverwrite              no
    <Limit READ>
      DenyAll
    </Limit>

    <Limit STOR>
      AllowAll
    </Limit>
  </Directory>
#
#  # Don't write anonymous accesses to the system wtmp file (good idea!)
#  WtmpLog                      off
#
#  # Logging for the anonymous transfers
#  ExtendedLog          /var/log/proftpd/access.log WRITE,READ default
#  ExtendedLog          /var/log/proftpd/auth.log AUTH auth
#
</Anonymous>

6. Now, tried log into your FTP server with username anonymous and without password, you will see like the picture bellow.

About these ads

Aksi

Information

7 responses

25 10 2008
Artikel Linux&komputer Update Oktober 2008 « Banjarnegara buka matamu!!

[...] How to install and Configure ProFTPd with integrate the MySQL [...]

26 10 2008
Heri Bambang Santoso

Ok, silahkan.. :)

18 05 2009
Daniel

I have a CentOS5.3 with httpd, mysql, php, phpmyadmin and proftpd-mysql, all installed from yum.
I follow all steps but my proftpd not up | [FAILED]. I did not find the log about failed….nothing! I search in all folders in the /var, but nothing!
What I do?

20 05 2009
Heri Bambang Santoso

@Daniel:

Maybe it about conflict port, You can check service running on your server with command “netstat -ant | grep LISTEN”. If you see port 21 is running then you can kill that service with command “kill -9 ID_service (example= kill -9 2345)”.

2 06 2009
Steph

Dear Bung Heri, sebelumnya aku ucapkan thanks untuk artikel FTPnya.
Aku dah coba install dan seting. Awalnya sudah jalan. Nggak tau salah setting dimana sekarang malah nggak bisa jalan. Pessan errornya :
[root@ditekserver /]# /usr/local/sbin/proftpd
– Fatal: User: Unknown user ‘proftpd’ on line 23 of ‘/usr/local/etc/proftpd.conf’
Apa yang salah ya ?
Atau jika saya ingin uninstall lalu saya install ulang bagaimana ya, maklum msh belajar.
Thanjks

1 08 2009
Hackey

SQLHomedirOnDemand This directive has been deprecated with ProFTPD 1.3.1rc1. Please use CreateHome instead.

7 10 2009
Username

Thank you for the tutorial.

ProFTPD version 1.3.2 failed to start on CentOS 5.3 with the error:
– Fatal: unknown configuration directive ‘SQLHomedirOnDemand’ on line 180 of ‘/etc/proftpd.conf’

Reason: SQLHomedirOnDemand is deprecated, replaced by CreateHome
Ref: http://www.proftpd.org/docs/directives/linked/config_ref_SQLHomedirOnDemand.html

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

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

Berikan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Logout / Ubah )

Twitter picture

You are commenting using your Twitter account. Logout / Ubah )

Facebook photo

You are commenting using your Facebook account. Logout / Ubah )

Google+ photo

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s




Ikuti

Get every new post delivered to your Inbox.

%d blogger menyukai ini: