Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
HOWTO: PureFTPD with MySQL Auth + MyPhpAdmin
View unanswered posts
View posts from last 24 hours

Goto page 1, 2  Next  
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks
View previous topic :: View next topic  
Author Message
ikaro
Advocate
Advocate


Joined: 14 Jul 2003
Posts: 2526
Location: Denmark

PostPosted: Fri Dec 05, 2003 5:07 am    Post subject: HOWTO: PureFTPD with MySQL Auth + MyPhpAdmin Reply with quote

* 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
View user's profile Send private message
maalth
n00b
n00b


Joined: 06 Jun 2003
Posts: 56
Location: Can't tell you...

PostPosted: Sat Dec 13, 2003 10:51 pm    Post subject: Great one Reply with quote

I can vouch that it works as I've been on this set up for several months now.
Back to top
View user's profile Send private message
lampshad3
n00b
n00b


Joined: 16 Apr 2003
Posts: 16

PostPosted: Mon Dec 22, 2003 7:03 am    Post subject: Reply with quote

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
View user's profile Send private message
chrisyu
Apprentice
Apprentice


Joined: 10 Apr 2003
Posts: 206
Location: China

PostPosted: Mon Dec 22, 2003 7:51 am    Post subject: Reply with quote

lampshad3 wrote:

do the passwords need to be in md5 for this to work?


It should be plain text by default.
Back to top
View user's profile Send private message
lampshad3
n00b
n00b


Joined: 16 Apr 2003
Posts: 16

PostPosted: Mon Dec 22, 2003 4:27 pm    Post subject: Reply with quote

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
View user's profile Send private message
ikaro
Advocate
Advocate


Joined: 14 Jul 2003
Posts: 2526
Location: Denmark

PostPosted: Mon Dec 22, 2003 5:06 pm    Post subject: Reply with quote

lampshad3 wrote:
heres a screenie from mysqlcc
is there anyting i have messed up in there?

Guess so , isnt that windows xp ?
:twisted:
_________________
linux: #232767
Back to top
View user's profile Send private message
lampshad3
n00b
n00b


Joined: 16 Apr 2003
Posts: 16

PostPosted: Mon Dec 22, 2003 5:37 pm    Post subject: Reply with quote

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
View user's profile Send private message
ikaro
Advocate
Advocate


Joined: 14 Jul 2003
Posts: 2526
Location: Denmark

PostPosted: Mon Dec 22, 2003 6:14 pm    Post subject: Reply with quote

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
View user's profile Send private message
lampshad3
n00b
n00b


Joined: 16 Apr 2003
Posts: 16

PostPosted: Mon Dec 22, 2003 8:09 pm    Post subject: Reply with quote

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
View user's profile Send private message
ikaro
Advocate
Advocate


Joined: 14 Jul 2003
Posts: 2526
Location: Denmark

PostPosted: Mon Dec 22, 2003 9:49 pm    Post subject: Reply with quote

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
View user's profile Send private message
halucard
Tux's lil' helper
Tux's lil' helper


Joined: 22 Dec 2003
Posts: 133

PostPosted: Mon Dec 22, 2003 11:38 pm    Post subject: Reply with quote

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
View user's profile Send private message
den_RDC
Apprentice
Apprentice


Joined: 25 Aug 2002
Posts: 166
Location: beercountry, Belgium;)

PostPosted: Tue Dec 30, 2003 2:52 am    Post subject: Reply with quote

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
View user's profile Send private message
ikaro
Advocate
Advocate


Joined: 14 Jul 2003
Posts: 2526
Location: Denmark

PostPosted: Tue Dec 30, 2003 9:12 am    Post subject: Reply with quote

maybe you need to start the Mysql server ?

Code:

/etc/init.d/mysql start

_________________
linux: #232767
Back to top
View user's profile Send private message
den_RDC
Apprentice
Apprentice


Joined: 25 Aug 2002
Posts: 166
Location: beercountry, Belgium;)

PostPosted: Tue Dec 30, 2003 4:19 pm    Post subject: Reply with quote

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
View user's profile Send private message
mgillespie
Apprentice
Apprentice


Joined: 16 Dec 2003
Posts: 170

PostPosted: Wed Jan 14, 2004 11:35 pm    Post subject: Reply with quote

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:
Code:

USE pureftp;


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
View user's profile Send private message
ikaro
Advocate
Advocate


Joined: 14 Jul 2003
Posts: 2526
Location: Denmark

PostPosted: Thu Jan 15, 2004 2:09 am    Post subject: Reply with quote

ok thx
ive edited the initial post,.
_________________
linux: #232767
Back to top
View user's profile Send private message
zoom
n00b
n00b


Joined: 26 Jan 2004
Posts: 46

PostPosted: Thu Feb 19, 2004 6:20 pm    Post subject: Reply with quote

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
View user's profile Send private message
Spice
n00b
n00b


Joined: 14 Mar 2004
Posts: 28
Location: Germany

PostPosted: Wed Jun 09, 2004 3:52 pm    Post subject: Reply with quote

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
View user's profile Send private message
ikaro
Advocate
Advocate


Joined: 14 Jul 2003
Posts: 2526
Location: Denmark

PostPosted: Wed Jun 09, 2004 6:38 pm    Post subject: Reply with quote

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
View user's profile Send private message
Spice
n00b
n00b


Joined: 14 Mar 2004
Posts: 28
Location: Germany

PostPosted: Thu Jun 10, 2004 12:13 pm    Post subject: Reply with quote

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
View user's profile Send private message
blacksheep2
n00b
n00b


Joined: 12 Jun 2004
Posts: 27
Location: Switzerland/Uster

PostPosted: Tue Jun 22, 2004 1:32 pm    Post subject: Reply with quote

thank you for this great how-to.. it works really fine!
do you set the bandwith and quota in KB?
Back to top
View user's profile Send private message
KennethS
n00b
n00b


Joined: 23 Jun 2004
Posts: 15
Location: Norway / Sweden

PostPosted: Wed Jun 23, 2004 5:38 pm    Post subject: Reply with quote

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
View user's profile Send private message
blacksheep2
n00b
n00b


Joined: 12 Jun 2004
Posts: 27
Location: Switzerland/Uster

PostPosted: Wed Jun 23, 2004 7:52 pm    Post subject: Reply with quote

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
View user's profile Send private message
Spice
n00b
n00b


Joined: 14 Mar 2004
Posts: 28
Location: Germany

PostPosted: Wed Jun 23, 2004 10:02 pm    Post subject: Reply with quote

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
View user's profile Send private message
dmitrio
Tux's lil' helper
Tux's lil' helper


Joined: 10 Dec 2002
Posts: 115
Location: Pago Pago

PostPosted: Tue Jun 29, 2004 9:21 pm    Post subject: :. copied to gentoo-wiki.com Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks All times are GMT
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum