Tutorial Proftpd con Mysql y Administrador Web

Proftpd es un servidor FTP, altamente configurable, del cual podemos administrar los usuarios mediante PAM,Mysql,etc.

Cuando tienes muchos usuarios dentro de tu servicio FTP se puede volver algo engorroso o quizás no funcional la administración de dichos usuarios, para ello debemos recurrir a una nueva forma mas eficaz,rapida y mas amigable. En ello encontramos Proftpd_Admin el cual nos permite agregar,eliminar y crear cuotas de almacenamiento o de archivos, de una forma gráfica, fácil y rápida.

Con esta herramienta se crean usuarios delimitados por grupos y cuotas, y todo aquello queda almacenado en el motor Mysql.

sin mas preámbulo, la instalación comienza descargando Proftptd y el modulo mysql para la autenticacion 

como prerequisito debes tener instalado apache,php5, el modulo php-mysql y por lo mínimo conocimientos básicos de apache.

1) instalacion de proftpd

#apt-get install proftpd proftpd-mod-mysql mysql-server

Descargamos Proftpd_Admin desde aquí , que es básicamente una pagina web.

3) 

 Descomprimimos en /var/www/nombredominio :

 4)

  Configurar Proftpd:

  a) debes cargar la siguiente estructura en mysql, te recomiendo phpmyadmin para gestionar el siguiente script:

CREATE DATABASE proftpd_admin;
USE proftpd_admin;

CREATE TABLE usertable (
  userid text,
  passwd text,
  homedir text,
  shell text,
  uid int(11) NOT NULL auto_increment,
  gid int(11) default NULL,
  count int(11) NOT NULL default '0',
  lastlogin datetime NOT NULL default '0000-00-00 00:00:00',
  lastlogout datetime NOT NULL default '0000-00-00 00:00:00',
  expiration datetime NOT NULL default '0000-00-00 00:00:00',
  disabled tinyint(4) default '0',
  det_name tinytext,
  det_mail tinytext,
  det_adress tinytext,
  det_notes tinytext,
  PRIMARY KEY  (uid)
) TYPE=MyISAM;

CREATE TABLE grouptable (
  groupname text,
  gid int(11) NOT NULL auto_increment,
  members text,
  description tinytext,
  PRIMARY KEY  (gid),
  UNIQUE KEY gid_2 (gid),
  KEY gid (gid)
) TYPE=MyISAM;

CREATE TABLE xfer_stat (
  userid text,
  file text,
  size bigint(20) default '0',
  address_full text,
  address_ip text,
  command text,
  timespent text,
  time text,
  cmd text,
  dunno text
) TYPE=MyISAM;

CREATE TABLE `ftpquotalimits` (
  `name` varchar(30) NOT NULL default '',
  `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 'hard',
  `bytes_in_avail` float NOT NULL default '0',
  `bytes_out_avail` float NOT NULL default '0',
  `bytes_xfer_avail` float 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',
  PRIMARY KEY  (`name`)
) TYPE=MyISAM;

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;

CREATE TABLE admintable (
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    password CHAR(64) NOT NULL,
    email VARCHAR(100) NOT NULL,
    lastlogin datetime NOT NULL default '0000-00-00 00:00:00',
    disabled tinyint(4) default '0',
    falseLogins int(11) NOT NULL default '0'
) TYPE=MyISAM;

INSERT INTO usertable (uid) VALUES (9999);
DELETE FROM usertable WHERE uid=9999;
INSERT INTO grouptable (gid) VALUES (9999);
DELETE FROM grouptable WHERE gid=9999;
INSERT INTO grouptable (groupname, description) VALUES ("admins", "Administrators");
INSERT INTO grouptable (groupname, description) VALUES ("users", "Ordinary users");

/* prodtpd-administratror user */
GRANT ALL ON usertable TO proftpd@ftp IDENTIFIED BY 'test';
GRANT ALL ON grouptable TO proftpd@ftp IDENTIFIED BY 'test';
GRANT ALL ON xfer_stat TO proftpd@ftp IDENTIFIED BY 'test';
GRANT ALL ON ftpquotatallies TO proftpd@ftp IDENTIFIED BY 'test';
GRANT ALL ON ftpquotalimits TO proftpd@ftp IDENTIFIED BY 'test';
GRANT ALL ON admintable TO proftpd@ftp IDENTIFIED BY 'test';

/* userQuota.php  DB User */
GRANT SELECT ON * TO userview@ftp IDENTIFIED BY 'test123';

b) entraremos a el proftpd_admin,desde el navegador http://localhost/eldominio o http://eldominio. posiblemente nos salga esto:

Si no tienes este inconveniente pasa al paso C. Esto ocurre porque nuestro archivo configuration.xml no tienes los permisos de lectura y escritura adecuados:

#chmod 776 configuration.xml

c) Ahora tenemos nos salda esto:

esto nos dice que debemos configurar el accesso a la base de datos: nos vamos a la paleta configure y nos dira:

lo recomendable es que la base de datos creada proftpd_admin tenga su propio usuario, y no el root del sistema, ademas.

este punto no necesita mucha explicacion; username: nombre dueño base datos

password:"lacontraseña"

hostname"donde esta mysql, en este caso localhost"

database:"en este caso proftpd_admin" (como sale en el script superior)

d) Configuraremos los archivos de proftpd para gestionar los usuarios con mysql:

estos en Debian squeeze se encuntran en: /etc/proftpd de los archivos alojados nos interesa proftpd.conf,modules.conf y agregaremos include_mysql.conf

vamos a hacer un backup de este:

cp proftpd.conf proftpd.conf.back

remplazamos el proftpd.conf por:

ServerName                      "Server Ncw"
ServerType                      standalone
ServerIdent                     on              "Bienvenido"
DeferWelcome                    on
DefaultServer                   on

DisplayLogin                    .welcome        # Textfile to display on login
DisplayConnect                  .connect        # Textfile to display on connection
#DisplayFirstChdir               .firstchdir     # Textfile to display on first changedir

UseReverseDNS                   off
IdentLookups                    off

Port                            21
Umask                           022
MaxInstances                    15
MaxClientsPerHost               3               "Only %m connections per host allowed"
MaxClients                      10              "Only %m total simultanious logins allowed"
MaxHostsPerUser                 1

User                            ftp
Group                           nogroup

ScoreboardFile                  /var/log/scoreboard

#carga modulos 
Include /etc/proftpd/modules.conf
# Some logging formats
LogFormat                       default         "%h %l %u %t \"%r\" %s %b"
LogFormat                       auth            "%v [%P] %h %t \"%r\" %s"
LogFormat                       write           "%h %l %u %t \"%r\" %s %b"

# Define log-files to use
TransferLog                     /var/log/proftpd.xferlog
ExtendedLog                     /var/log/proftpd.access_log    WRITE,READ write
ExtendedLog                     /var/log/proftpd.auth_log      AUTH auth
ExtendedLog                     /var/log/proftpd.paranoid_log  ALL default
SQLLogFile                      /var/log/proftpd.mysql
QuotaLog                        /var/log/proftpd.quota

# Set up authentication via SQL
# ===========
AuthOrder                       mod_sql.c
SQLAuthTypes                    Backend
SQLConnectInfo                  proftpd_admin@localhost usuario contraseña 
SQLUserInfo                     usertable userid passwd uid gid homedir shell 
SQLGroupInfo                    grouptable groupname gid members 
SQLUserWhereClause              "disabled=0 and (NOW()<=expiration or expiration=-1 or expiration=0)"

# Log the user logging in
SQLLog PASS counter
SQLNamedQuery counter UPDATE "lastlogin=now(), count=count+1 WHERE userid='%u'" usertable

# logout log
SQLLog EXIT time_logout
SQLNamedQuery time_logout UPDATE "lastlogout=now() WHERE userid='%u'" usertable

# display last login time when PASS command is given
SQLNamedQuery login_time SELECT "lastlogin from usertable where userid='%u'"
SQLShowInfo PASS "230" "Last login was: %{login_time}"

# xfer Log in mysql
SQLLog RETR,STOR transfer1
SQLNamedQuery  transfer1 INSERT "'%u', '%f', '%b', '%h', '%a', '%m', '%T', now(), 'c', NULL" xfer_stat
SQLLOG ERR_RETR,ERR_STOR transfer2
SQLNamedQuery  transfer2 INSERT "'%u', '%f', '%b', '%h', '%a', '%m', '%T', now(), 'i', NULL" xfer_stat

# 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


AllowStoreRestart               on
AllowRetrieveRestart            on
RequireValidShell               off
PathDenyFilter                  "\\.ftp)|\\.ht)[a-z]+$" 
DefaultRoot                     ~
DenyFilter                      \*.*/



        AllowOverwrite          on
        HideNoAccess            off
        
                AllowAll
        

        
                DenyGroup       !admins
        



        AllowOverwrite          on
        HideNoAccess            on

        
                DenyGroup       !admins
        

        
                AllowAll
        

en la linea 45 hay que cambiar usuario por el  dueño de la base de datos proftpd_admin y la contraseña de esta misma

2) modificar (reemplazar) el archivo modules.conf  por:

#
# This file is used to manage DSO modules and features.
#

# This is the directory where DSO modules reside

ModulePath /usr/lib/proftpd

# Allow only user root to load and unload modules, but allow everyone
# to see which modules have been loaded

ModuleControlsACLs insmod,rmmod allow user root
ModuleControlsACLs lsmod allow user *

LoadModule mod_ctrls_admin.c
LoadModule mod_tls.c

# Install one of proftpd-mod-mysql, proftpd-mod-pgsql or any other
# SQL backend engine to use this module and the required backend.
# This module must be mandatory loaded before anyone of
# the existent SQL backeds.
LoadModule mod_sql.c

# Install proftpd-mod-ldap to use this
#LoadModule mod_ldap.c

#
# 'SQLBackend mysql' or 'SQLBackend postgres' directives are required 
# to have SQL authorization working. You can also comment out the
# unused module here, in alternative.
#

# Install proftpd-mod-mysql and decomment the previous
#mod_sql.c module to use this.
LoadModule mod_sql_mysql.c

# Install proftpd-mod-pgsql and decommen the previous 
# mod_sql.c module to use this.
#LoadModule mod_sql_postgres.c

# Install proftpd-mod-sqlite and decomment the previous
# mod_sql.c module to use this
#LoadModule mod_sql_sqlite.c

# Install proftpd-mod-odbc and decomment the previous
# mod_sql.c moduleto use this
#LoadModule mod_sql_odbc.c

#LoadModule mod_radius.c
LoadModule mod_quotatab.c
#LoadModule mod_quotatab_file.c

# Install proftpd-mod-ldap to use this
#LoadModule mod_quotatab_ldap.c

# Install proftpd-mod-pgsql or proftpd-mod-mysql to use this
LoadModule mod_quotatab_sql.c
#LoadModule mod_quotatab_radius.c
#LoadModule mod_wrap.c
LoadModule mod_rewrite.c
LoadModule mod_load.c
LoadModule mod_ban.c
#LoadModule mod_wrap2.c
#LoadModule mod_wrap2_file.c
# Install proftpd-mod-pgsql or proftpd-mod-mysql to use this
#LoadModule mod_wrap2_sql.c
#LoadModule mod_dynmasq.c


# keep this module the last one
LoadModule mod_ifsession.c

#cuota tabs

nos falta el include_mysql.conf:

AllowStoreRestart 		on
AllowRetrieveRestart		on
RequireValidShell               off
PathDenyFilter 			"\\.ftp)|\\.ht)[a-z]+$" 
DefaultRoot 			~
DenyFilter 			\*.*/


# Set up authentication via SQL
# ===========
AuthOrder                       mod_sql.c
SQLAuthTypes			Backend
SQLConnectInfo       		proftpd_admin@localhost proftpd new159753
SQLUserInfo       		usertable userid passwd uid gid homedir shell 
SQLGroupInfo       		grouptable groupname gid members 
SQLUserWhereClause    		"disabled=0 and (NOW()<=expiration or expiration=-1 or expiration=0)"

# Log the user logging in
SQLLog PASS counter
SQLNamedQuery counter UPDATE "lastlogin=now(), count=count+1 WHERE userid='%u'" usertable

# logout log
SQLLog EXIT time_logout
SQLNamedQuery time_logout UPDATE "lastlogout=now() WHERE userid='%u'" usertable

# display last login time when PASS command is given
SQLNamedQuery login_time SELECT "lastlogin from usertable where userid='%u'"
SQLShowInfo PASS "230" "Last login was: %{login_time}"

# xfer Log in mysql
SQLLog RETR,STOR transfer1
SQLNamedQuery  transfer1 INSERT "'%u', '%f', '%b', '%h', '%a', '%m', '%T', now(), 'c', NULL" xfer_stat
SQLLOG ERR_RETR,ERR_STOR transfer2
SQLNamedQuery  transfer2 INSERT "'%u', '%f', '%b', '%h', '%a', '%m', '%T', now(), 'i', NULL" xfer_stat

# 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

SQLDefaultUID   65534
CreateHome on 1770 uid 0 gid 0


### Shaper ###
TransferRate RETR 1000.0
TransferRate STOR 1000.0

reiniciamos nuestro proftpd /etc/init.d/proftpd restart

nuevamente accedemos al dominio  que asignamos a apache o http://localhost/proftpd_admin y debemos ir a configure > Filepaths saldrá:

en el campo who va: /usr/bin/who

en el campo ftpwho:

/usr/bin/ftpwho

en kernel configuration file:

/usr/src/linux-headers-2.6.32-5-amd64 (esto depende de tu kernel)

y finalmente proftpd:

/usr/sbin/proftpd

es probable que en  configure -> extensions->quota, nodeje habilitarlas; dirigete al archivo configuration.xml que esta en /var/www/eldominio y encuentra:

quota>
			0
			
			user

donde sale un 0 cambialo por un 1, y listo, la demas configuracion es intitutiva, la creación, modificación , asignación de reglas y quota,etc.
Francisco.