# HOWTO: PureFTPD with MySQL Auth + MyPhpAdmin

## ikaro

* 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  :Smile: 

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:

```

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:

```

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

```

$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

```
 /etc/init.d/pure-ftpd restart 
```

Now you can access Phpmyadmin and admin, the ftp users via the web  :Wink: 

----------

## maalth

I can vouch that it works as I've been on this set up for several months now.

----------

## lampshad3

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?

----------

## chrisyu

 *lampshad3 wrote:*   

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

 

It should be plain text by default.

----------

## lampshad3

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?

----------

## ikaro

 *lampshad3 wrote:*   

> heres a screenie from mysqlcc
> 
> is there anyting i have messed up in there?

 

 Guess so , isnt that windows xp ? 

 :Twisted Evil: 

----------

## lampshad3

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?

----------

## ikaro

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.

----------

## lampshad3

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:

----------

## ikaro

why dont you have the ftpuser in the ftp group ?

```

[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)

```

----------

## halucard

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

----------

## den_RDC

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?

----------

## ikaro

maybe you need to start the Mysql server ? 

```

/etc/init.d/mysql start

```

----------

## den_RDC

 *ikaro wrote:*   

> maybe you need to start the Mysql server ? 
> 
> ```
> 
> /etc/init.d/mysql start
> ...

 

it runs, otherwise i would be unable to run phpmyadmin or connect manually through the socket...

----------

## mgillespie

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:

```

CREATE DATABASE pureftp; 

```

Make sure you do a:

```

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:

```

MYSQLDatabase   pureftpd 

```

but when you create the database, it's now called:

```

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...

----------

## ikaro

ok thx

ive edited the initial post,.

----------

## zoom

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..

----------

## Spice

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

----------

## ikaro

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.

----------

## Spice

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

----------

## blacksheep2

thank you for this great how-to.. it works really fine! 

do you set the bandwith and quota in KB?

----------

## KennethS

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

----------

## blacksheep2

in my opinion it's for lazy gays and it's more confortable as the bash-way...  :Wink: 

and I found the sets for quotas (in megabytes) and bandwith (kilobytes) out.. thanks anyway.

----------

## Spice

 *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.

----------

## dmitrio

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.

----------

## cpu

 *ikaro wrote:*   

> 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/
> ...

 

But how can I co this ? :/ I already have ftpuser and ftpgroup but how can I setup an account using this UID/GID ??

Thanks in advance

----------

## ikaro

just make the accounts, and then use its UID/GID

doesnt have to be those I wrote, it can be whatever.

----------

## cpu

 *ikaro wrote:*   

> just make the accounts, and then use its UID/GID
> 
> doesnt have to be those I wrote, it can be whatever.

 

So I have an user:

uid=1003(test) gid=409(test) grupy=409(test)

Then I make his record:

test  	1  	test  	1003  	409  	/home/ftp/test  	0  	0  	   	*  	0  	0

Start pure-ftpd and while loging I get:

530 Authentication failed

----------

## ikaro

go startover, this time read everything carefully and slow.

----------

## JeffBlair

I know that this is going to be an easy question, but I want to make sure I am thinking right. In the database, where you put in the value for DIR, that is the directory that the FTP points to right?

i.e.

DIR

/var/www/jeffhome.us/jeff/ftp/

Would this send all files to that dir? Thanks.

Also, I know webmin has a way of editing the users. I don't know if this would work for MYSQL users?

----------

## ikaro

yes.

----------

## COiN3D

Do I need Quota-Support in the Kernel to make use of the FTP-Quota-Feature?

----------

## ikaro

nope.

----------

## T|TaN

wow this is a great idea I have been looking for something like this. 

My question is, has anyone implemented this into a website?  I already have a Postnuke website which stores information in mysql including username and passwords.  I run an ftp site as well, and ideally I would like to synchronize the web users and ftp users.  Currently this is done manually, and well it gets to be a pain or I just don't do it.  Through the postnuke website the users can reset their passwords and/or if they forget their password they can have it emailed to a registered address.  This would be a seamless system and would help prevent password inconsistency.

If someone has setup such a concept I would really like to hear the details.

If someone has ideas on the constructs of how I could apply the solution outlined in this original post to my application, I would love to hear them as well.

Take Care!

----------

## nuss

i need your help, i try conect to my ftp with my User and pass...but i can't becouse say me "Login failed error 530"

y followed this guide..

I use this command to Startup My PureFTPD server:

 *Quote:*   

> /usr/local/sbin/pure-ftpd - l mysql:/etc/pureftpd-mysql.conf &

 

pureftpd-mysql.conf --> is Correctly formed

My SQL server is Online, ANd All DB was Created

pliss help me, y search in all manual, but can't solve this..

PS: sorry my english is very bad :3

----------

## Aikidoka69

I also am getting the 

[09:59:56] PASS (hidden)

[09:59:56] 530 Login authentication failed

messages.

Yes mysql is running.  Pure-ftp is connecting to the db.  If I change the password in the pureftpd-mysql.conf it will spit an error in the logs so it appears to connect when the password is right.  The individual queries work when copied to the mysql command line.  But no go on the log in.  Linux ftp uid and gid are both 21.  User's status is set to 1 with a Uid and Gid of 21.  User's home dir is set to the ftp user home dir.

MySQL is version 5.0.22.

Pure-ftpd is version 1.0.12.

The problem turns out to be a difference between the Wiki version and forum version.  The Wiki uses MYSQLCrypt md5 while the top of the forum lists cleartext.  Being that the Wiki does not mention putting the password in as an md5 hash, most people will end up with a non working setup after following the wiki step by step.  If you are getting authentication errors try setting your MYSQLCrypt setting to cleartext and restarting pureftpd.

Of course, you can also just MD5 encrypt your password.  If you are doing manual inserts, just put MD5(<password>) into your statement for your password.  If you are using phpmyadmin, select md5 from the function list for that field.

----------

## vladoportos

Hmm for my its working only with cleartext which is apsolutely insecure... when i use any ecryption e.g. md5 it cant log in... it will not recognize password... any idea ?

And yes i put md5 hashed pass in mysql...

----------

## Aikidoka69

 *vladoportos wrote:*   

> Hmm for my its working only with cleartext which is apsolutely insecure... when i use any ecryption e.g. md5 it cant log in... it will not recognize password... any idea ?
> 
> And yes i put md5 hashed pass in mysql...

 

Have you checked both your pure-ftpd and mysql logs for clues?

----------

## vladoportos

I found out that it vas permission problem in mysql its working fine now...  :Smile: 

----------

## lotherk

... modify the /etc/init.d/pure-ftpd script, since it seems that the values in the /etc/conf.d/pure-ftpd are not taken from this script. Don't know why.

So i wrote a little C programm:

```
#include <stdio.h>

int main(void) {

        system("/usr/sbin/pure-ftpd -l mysql:/etc/pureftpd-mysql.conf -j -A &");

        return 0;

}

```

And i changed the start() section from /etc/init.d/pure-ftpd:

```

start() {

        checkconfig || return 1

        ebegin "Starting Pure-FTPd"

        #start-stop-daemon --start --quiet --exec /usr/sbin/pure-ftpd -- $SERVER $MAX_CONN $MAX_CONN_IP $DAEMON $DISK_FULL $USE_NAT $AUTH $LOG $.....

        start-stop-daemon --start --quiet --exec  "/usr/sbin/pftpd"

        eend $?

}

```

Then i've compiled my little C program 'gcc -o pftpd pftpd.c' and moved it to /usr/sbin.

/etc/init.d/pure-ftpd start/stop works just fine.

There might be a different solution for this problem, but this works just fine for me.

Greets, Konrad.

----------

## ikaro

ive since droped this method of using phpmyadmin and moved over some other web user administration,

the latest being this one:

http://linux.softpedia.com/progScreenshots/User-manager-for-PureFTPd-Screenshot-5446.html

works pretty well.

easy to install.

----------

## Jumento

Hello,

I got a question. I was also following this guide to setup my ftpserver.

And am also using the following user manager as front-end: http://machiel.generaal.net/index.php?subject=user_manager_pureftpd

Only a question. 

Is it possible to set a maximum of logins per userid?

Don't know of this is the right spot to ask this.

----------

## ikaro

hi,

you set that in /etc/conf.d/pure-ftpd

----------

## Jumento

 *ikaro wrote:*   

> hi,
> 
> you set that in /etc/conf.d/pure-ftpd

 

How?

In this file i can find the option for: 

"Max_Conn" ==> total connections

"Max_Conn_IP"==> total connections from IP

But can't find the option to set it per userid

So that ftpuser1 is allowed to login twice, but ftpuser2 is allowed to login only once.

----------

## ikaro

oh im sorry,

I read it as userip not user ID.   :Rolling Eyes: 

You can do that using the text mode  "pure-pw" and use the "-y" switch.

You can code that feature in php and send a patch to the author of the puremanager.

example for 1 login only:

```

pure-pw useradd TestUser -f 2121 -g 2121 -d /home/ftp/TestUser -r 192.168.0.4 -y 1

```

for more info use "pure-pw" with no switch.

 :Wink: 

----------

## Jumento

thnx.. that was the option i was looking for.

----------

