| View previous topic :: View next topic |
| Author |
Message |
ikaro
Advocate
Joined: 14 Jul 2003
Posts: 2527
Location: Denmark
|
 Posted: Fri Dec 05, 2003 5:07 am Post subject: HOWTO: PureFTPD with MySQL Auth + MyPhpAdmin |
 |
|
* 15.02.2004 updated *
Hi.
I was playing with pureftpd and mysql auth, and after reading some docs i came up with this setup.
I hope someone can use it for something 
Requirements
- Running Webserver with PHP Support
- A Running PureFTP Daemon with MySQL Auth support
- MySQL
- PhpMyAdmin
Lets Edit /etc/conf.d/pure-ftpd
and edit the auth method to this:
| Code: |
AUTH="-l mysql:/etc/pureftpd-mysql.conf"
|
note: if you want to use unix and puredb method, just add them in the same line ( auth="-l unix -l puredb:/etc/myfile.db -l mysql:/etc/myfile.conf" )
Now we create the /etc/pureftpd-mysql.conf with the follwing:
| Code: |
MYSQLSocket /var/run/mysqld/mysqld.sock
#MYSQLServer localhost
#MYSQLPort 3306
MYSQLUser ftpduser
MYSQLPassword ftpdpass
MYSQLDatabase pureftpd
MYSQLCrypt cleartext
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")
|
note: if you want to use network insted of local, just comment the socket part and uncomment the host and port
Now lets create a pureftp database:
pureftpd database
| Code: |
$mysql -u root -p
password:
CREATE DATABASE pureftpd;
USE pureftpd;
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)
) TYPE=MyISAM;
exit
|
Restart Pure-ftpd
| Code: | | /etc/init.d/pure-ftpd restart |
Now you can access Phpmyadmin and admin, the ftp users via the web 
_________________
linux: #232767
Last edited by ikaro on Thu Jan 15, 2004 1:50 am; edited 2 times in total |
|
| Back to top |
|
 |
maalth
Tux's lil' helper
Joined: 06 Jun 2003
Posts: 76
Location: Can't tell you...
|
| Posted: Sat Dec 13, 2003 10:51 pm Post subject: Great one |
|
|
| I can vouch that it works as I've been on this set up for several months now. |
|
| Back to top |
|
|
lampshad3
n00b
Joined: 16 Apr 2003
Posts: 16
|
| Posted: Mon Dec 22, 2003 7:03 am Post subject: |
|
|
Mine isn't working...
I added the user and stuff into the the db via mysqlcc and it isn't verifying the pw i think
do the passwords need to be in md5 for this to work? |
|
| Back to top |
|
|
chrisyu
Apprentice
Joined: 10 Apr 2003
Posts: 207
Location: China
|
| Posted: Mon Dec 22, 2003 7:51 am Post subject: |
|
|
| lampshad3 wrote: |
do the passwords need to be in md5 for this to work? |
It should be plain text by default. |
|
| Back to top |
|
|
lampshad3
n00b
Joined: 16 Apr 2003
Posts: 16
|
| Posted: Mon Dec 22, 2003 4:27 pm Post subject: |
|
|
heres a screenie from mysqlcc
[img:00755f1a32]http://jw.xomar.com/pics/computer/ftp-not-working-via-mysql.JPG[/img:00755f1a32]
is there anyting i have messed up in there? |
|
| Back to top |
|
|
ikaro
Advocate
Joined: 14 Jul 2003
Posts: 2527
Location: Denmark
|
| Posted: Mon Dec 22, 2003 5:06 pm Post subject: |
|
|
| lampshad3 wrote: | heres a screenie from mysqlcc
is there anyting i have messed up in there? |
Guess so , isnt that windows xp ?
_________________
linux: #232767 |
|
| Back to top |
|
|
lampshad3
n00b
Joined: 16 Apr 2003
Posts: 16
|
| Posted: Mon Dec 22, 2003 5:37 pm Post subject: |
|
|
yes, yes it is...
i could never get fluxbox to use my dual monitors so i just stuck with xp on my desktops and gentoo on my servers
BACK TO MY ftp thinggy
whats up with it? |
|
| Back to top |
|
|
ikaro
Advocate
Joined: 14 Jul 2003
Posts: 2527
Location: Denmark
|
| Posted: Mon Dec 22, 2003 6:14 pm Post subject: |
|
|
it looks like you didnt change the default values for the user ID and Group ID.
-1 its there, but you need to change that to the FTPGroup's ID
In my setup I have a FTPGroup and FTPUsers, and i add the accounts using that id /guid which home is /home/ftp/
change that, then it will work, if not post again.
_________________
linux: #232767 |
|
| Back to top |
|
|
lampshad3
n00b
Joined: 16 Apr 2003
Posts: 16
|
| Posted: Mon Dec 22, 2003 8:09 pm Post subject: |
|
|
would hte ftp id be like a number or ftpgroup
same with uid
theres two numbers in /etc/passwd and one in /etc/group
ftpuser:x:1005:100::/home/ftpuser:/bin/bash
ftpgroup:x:502: |
|
| Back to top |
|
|
ikaro
Advocate
Joined: 14 Jul 2003
Posts: 2527
Location: Denmark
|
| Posted: Mon Dec 22, 2003 9:49 pm Post subject: |
|
|
why dont you have the ftpuser in the ftp group ?
| Code: |
[root@Genbox:ikaro]$grep ftp /etc/passwd
ftpuser:x:1023:1014::/dev/null:/etc
[root@Genbox:ikaro]$grep ftp /etc/group
ftpgroup:x:1014:
[root@Genbox:ikaro]$id ftpuser
uid=1023(ftpuser) gid=1014(ftpgroup) groups=1014(ftpgroup)
|
_________________
linux: #232767 |
|
| Back to top |
|
|
halucard
Tux's lil' helper
Joined: 22 Dec 2003
Posts: 133
|
| Posted: Mon Dec 22, 2003 11:38 pm Post subject: |
|
|
I have had some problems with this howto. I have found one solution and it works well. I use apache-2.0.48, mysql-7.3.5 with phpmyadmin-2.5.4. One example :
MYSQLSocket /var/run/mysqld/mysqld.sock
#MYSQLServer localhost
#MYSQLPort 3306
MYSQLUser ftpduser
MYSQLPassword ftpdpass
MYSQLDatabase pureftpd
MYSQLCrypt cleartext
MYSQLGetPW SELECT `Password` FROM `ftpd` WHERE `User`="\L" AND `status`="1" AND (`ipaccess` = "*" OR `ipaccess` LIKE "\R") etc
The important thing is that quote ` . I have spend one week and looking how phpmyadmin writes a sql query, i copy its way.
I hope that my solution can resolve your problems.
Halucard |
|
| Back to top |
|
|
den_RDC
Apprentice
Joined: 25 Aug 2002
Posts: 166
Location: beercountry, Belgium;)
|
| Posted: Tue Dec 30, 2003 2:52 am Post subject: |
|
|
I followed the config exactly, and triple checked everything. I did change the user for authing (wich is ftpdauth here, and has USAGE privilige on the pureftp database). I added a test user to the database, verified if the mysql socket is working, and tested if the sql query and the login as "ftpdauth" worked.
But i still get this error
Dec 30 03:47:16 [pure-ftpd] (?@192.168.0.21) [INFO] New connection from 192.168.0.21
Dec 30 03:47:16 [pure-ftpd] (?@192.168.0.21) [ERROR] The SQL server seems to be down
Dec 30 03:47:16 [pure-ftpd] (?@192.168.0.21) [WARNING] Authentication failed for user [test]
Dec 30 03:47:19 [pure-ftpd] (?@192.168.0.21) [INFO] Logout.
Maybe there's an error in the guide somewhere? |
|
| Back to top |
|
|
ikaro
Advocate
Joined: 14 Jul 2003
Posts: 2527
Location: Denmark
|
| Posted: Tue Dec 30, 2003 9:12 am Post subject: |
|
|
maybe you need to start the Mysql server ?
| Code: |
/etc/init.d/mysql start
|
_________________
linux: #232767 |
|
| Back to top |
|
|
den_RDC
Apprentice
Joined: 25 Aug 2002
Posts: 166
Location: beercountry, Belgium;)
|
| Posted: Tue Dec 30, 2003 4:19 pm Post subject: |
|
|
| ikaro wrote: | maybe you need to start the Mysql server ?
| Code: |
/etc/init.d/mysql start
|
|
it runs, otherwise i would be unable to run phpmyadmin or connect manually through the socket... |
|
| Back to top |
|
|
mgillespie
Apprentice
Joined: 16 Dec 2003
Posts: 170
|
| Posted: Wed Jan 14, 2004 11:35 pm Post subject: |
|
|
Just to mention, there are some typos and mistakes in this howto, that thew me for a wee while, it will catch anyone trying to copy and paste configs...
1/ When creating the MySQL tables, after doing:
| Code: |
CREATE DATABASE pureftp;
|
Make sure you do a:
otherwise you will get an error. You can then paste the code in to create the tables.
2/
There is a mismatch in the example code. When you create the /etc/pureftpd-mysql.conf file, notice it's called:
| Code: |
MYSQLDatabase pureftpd
|
but when you create the database, it's now called:
| Code: |
CREATE DATABASE pureftp;
|
(missing 'd')
It probabily also worth mentioning at this point, that they should change the conf file to suit their MYSQLUser and MYSQLPassword
I'm not being picky, I found this guide very helpful, but these kinda small errors can send newbies (like me) into downward spiralling circles... |
|
| Back to top |
|
|
ikaro
Advocate
Joined: 14 Jul 2003
Posts: 2527
Location: Denmark
|
| Posted: Thu Jan 15, 2004 2:09 am Post subject: |
|
|
ok thx
ive edited the initial post,.
_________________
linux: #232767 |
|
| Back to top |
|
|
zoom
n00b
Joined: 26 Jan 2004
Posts: 46
|
| Posted: Thu Feb 19, 2004 6:20 pm Post subject: |
|
|
Thanks for the tutorial on setting up pureftpd with mysql. I would like to point out something for people who might be using xinetd instead of using the pureftpd daemon.
The options that are located in the /etc/conf.d/pure-ftpd work for the pureftpd daemon (not running pureftpd with xinetd). However, doing the "emerge pureftpd" will also create a file in the /etc/xinetd.d directory called pure-ftpd. This is the service file xinetd will use..
You need to edit the "server_args" to be applied to the service once a request is made. Since I didn't require much other than using mysql authentication my service is shown below. Refer to the pureftpd docs for more information on additional parameters.
service ftp
{
socket_type = stream
server = /usr/sbin/pure-ftpd
server_args = -s -a 42 -l mysql:/etc/pureftpd-mysql.conf
protocol = tcp
user = root
wait = no
disable = no
}
Hope this helps someone.. |
|
| Back to top |
|
|
Spice
n00b
Joined: 14 Mar 2004
Posts: 28
Location: Germany
|
| Posted: Wed Jun 09, 2004 3:52 pm Post subject: |
|
|
Thanks for this great tutorial.
Everything is working fine except one thing:
The MySQLGetBandwidthUL and MySQLGetBandwidthDL has no action. I set this for testing-purpose to 2 kb/s for one special user, but it's possible to up-/download files with unlimited speed (ok, up-/downloading is limited by the network, but not by pure-ftpd). Any ideas to solve this problem?
Thanks a lot...
Spice |
|
| Back to top |
|
|
ikaro
Advocate
Joined: 14 Jul 2003
Posts: 2527
Location: Denmark
|
| Posted: Wed Jun 09, 2004 6:38 pm Post subject: |
|
|
you might want to try something else these days...
I know there is a good web interface made with php to admin pure-ftpd,i dont remember the website and the ones i found on sf and fm arent it....
ill post later when i find it.
_________________
linux: #232767 |
|
| Back to top |
|
|
Spice
n00b
Joined: 14 Mar 2004
Posts: 28
Location: Germany
|
| Posted: Thu Jun 10, 2004 12:13 pm Post subject: |
|
|
Hi,
the bandwith-throttling now works also perfect. I just did a spelling mistake in the MySQL table-entries.
I've correct this mistake and now the throttling works fine.
Then I take your hint and start searching for the pure-ftpd webinterface and found that one:
http://machiel.generaal.net/index.php?subject=user_manager_pureftpd
It looks really nice, so I will try it next days.
Thank you very much...
Spice |
|
| Back to top |
|
|
blacksheep2
n00b
Joined: 12 Jun 2004
Posts: 27
Location: Switzerland/Uster
|
| Posted: Tue Jun 22, 2004 1:32 pm Post subject: |
|
|
thank you for this great how-to.. it works really fine!
do you set the bandwith and quota in KB? |
|
| Back to top |
|
|
KennethS
n00b
Joined: 23 Jun 2004
Posts: 15
Location: Norway / Sweden
|
| Posted: Wed Jun 23, 2004 5:38 pm Post subject: |
|
|
Out of curiosity, why would you want mysql authentication? Doesn't that add alot of extra overhead when you only have a few users?
I am not bashing, just wondering if there is any particular reason for doing it
_________________
-Kenneth Sundby-
http://kennethism.org |
|
| Back to top |
|
|
blacksheep2
n00b
Joined: 12 Jun 2004
Posts: 27
Location: Switzerland/Uster
|
| Posted: Wed Jun 23, 2004 7:52 pm Post subject: |
|
|
in my opinion it's for lazy gays and it's more confortable as the bash-way...
and I found the sets for quotas (in megabytes) and bandwith (kilobytes) out.. thanks anyway. |
|
| Back to top |
|
|
Spice
n00b
Joined: 14 Mar 2004
Posts: 28
Location: Germany
|
| Posted: Wed Jun 23, 2004 10:02 pm Post subject: |
|
|
| KennethS wrote: | | Out of curiosity, why would you want mysql authentication? |
That's quite simple. I build a webapplication with the possibility for the users to upload files. To use this app the user must be logged in. So i'm using mod_auth_mysql with apache2. File uploads with http is very bad and uncomfortable, so the possiblity to offers the users ftp-upload ist very nice.
At all, there is only one point, where all userdatas and settings are stored. So managing of userdatas is quite simple.
But you're right. If you only use ftp and you only have some users, there is no reason to use mysql. |
|
| Back to top |
|
|
dmitrio
Tux's lil' helper
Joined: 10 Dec 2002
Posts: 115
Location: Pago Pago
|
| Posted: Tue Jun 29, 2004 9:21 pm Post subject: :. copied to gentoo-wiki.com |
|
|
I have copied this, with permission of ikaro, to gentoo-wiki.com
http://gentoo-wiki.com/HOWTO_PureFTPD_with_MySQL_Auth_and_MyPhpAdmin
If you see anything that should be added or changed, feel free to do so.
Thank you for a great HOWTO.
_________________
... Leaving ground, destination is unknown,
into the darkness and far away from home,
Will your dream come true and what will you find,
when fate is your guide ... |
|
| Back to top |
|
|
|
Documentation, Tips & Tricks
