Proftpd With Mysql Authentication

ProFTPD with MySQL Authentication : www.stardothosting.com Since this setup uses one FTP account to create user home directories and upload files, a compromise to this FTP user would cause the attacker to gain access to all FTP user home directories. I guess it just depends on how much you trust the DefaultRoot directive in Proftpd. I run Proftpd in its own chroot environment in addition to using DefaultRoot, so I’m used to feeling pretty safe with my Proftpd install. Anyway, here’s how I did the install/configuration 1. install proftpd-mysql from the ports with WITH_QUOTA set: cd /usr/ports/ftp/proftpd-mysql/ env WITH_QUOTA=yes make env WITH_QUOTA=yes make install 2. Add the global proftpd user & Proftpd group to your system. I used uid & gid 5500 simply because that’s what was used at one of the sites I was referencing (listed below). pw groupadd -n Proftpd -g 5500 pw useradd proftpd -u 5500 -g Proftpd -s /sbin/nologin -d /dev/null -c "Proftpd User" 3. Create the mySQL database create database proftpd; grant all on proftpd.* to 'proftpd'@'localhost' identified by 'password' ( change ‘password’ to something secret! ) 4. Create the mySQL tables for the users & quota create table proftpdUsers ( sqlUID int unsigned auto_increment not null, userName varchar(30) not null unique, passwd varchar(80) not null, uid int unsigned not null unique, gid int unsigned not null, homedir tinytext, shell tinytext, primary key(sqlUID) ) ; create table proftpdGroups ( sqlGID int unsigned auto_increment not null, groupName varchar(30) not null unique, gid int unsigned not null unique, members tinytext, primary key(sqlGID) ); CREATE TABLE proftpdQuotaLimits ( name VARCHAR(30), quota_type ENUM("user", "group", "class", "all") NOT NULL,

per_session ENUM("false", "true") NOT NULL, limit_type ENUM("soft", "hard") NOT NULL, bytes_in_avail FLOAT NOT NULL, bytes_out_avail FLOAT NOT NULL, bytes_xfer_avail FLOAT NOT NULL, files_in_avail INT UNSIGNED NOT NULL, files_out_avail INT UNSIGNED NOT NULL, files_xfer_avail INT UNSIGNED NOT NULL ); CREATE TABLE proftpdQuotaTallies ( name VARCHAR(30) NOT NULL, quota_type ENUM("user", "group", "class", "all") NOT NULL, bytes_in_used FLOAT NOT NULL, bytes_out_used FLOAT NOT NULL, bytes_xfer_used FLOAT NOT NULL, files_in_used INT UNSIGNED NOT NULL, files_out_used INT UNSIGNED NOT NULL, files_xfer_used INT UNSIGNED NOT NULL ); 5. Add a test user to the proftpd database (assumes /home/ftp is where you keep your ftp users. Otherwise, change the homedir location). This is certainly not a necessary step, but you should probably check to see if your configuration is working. You can delete this user later. insert into proftpdUsers values ( 0, 'test', 'test', 5500, 5500, '/home/ftp/test', '/sbin/nologin' ); 6. Set your proftpd configuration to use the mySQL authentication and quotas: (NOTE: this is not a complete configuration file, it’s basically just the default config file with mySQL auth & quotas added, but note that the User and Group directives are the user & group we added in step 2. ) MaxInstances 30 # Set the user and group under which the server will run. User proftpd Group Proftpd # To cause every FTP user to be "jailed" (chrooted) into their home # directory, uncomment this line. DefaultRoot ~ # Normally, we want files to be overwriteable. AllowOverwrite on # Bar use of SITE CHMOD by default DenyAll # Log format and location LogFormat default "%t %h %a %s %m %f %b %T \"%r"\" ExtendedLog /var/log/proftpd.log ALL default SystemLog /var/log/proftpd.log ALL default

TransferLog

/var/log/proftpd.log ALL default

# Uncomment this if you have "invalid shell" errors in your proftpd.log #RequireValidShell off # 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 proftpd@localhost proftpd yourdatabasepassword # 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 proftpdUsers userName 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 proftpdGroups groupName gid members # set min UID and GID - otherwise these are 999 each SQLMinID 5000 #============ # User quotas # =========== QuotaEngine on QuotaDirectoryTally on QuotaDisplayUnits Mb QuotaShowQuotas on # create a user's home directory on demand if it doesn't exist SQLHomedirOnDemand 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 proftpdQuotaLimits 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 proftpdQuotaTallies 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}'" proftpdQuotaTallies SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" proftpdQuotaTallies QuotaLimitTable sql:/get-quota-limit QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally