# ProFTPd and MySQL configuration

## davidfowler2000

I'm having major headaches trying to get proftpd to work. 

The way i would like to get it up and working is the MySQL method - keep the user name and passwords in a mysql table etc....

To get the worst of it done, i followed the wiki at http://gentoo-wiki.com/HOWTO_ProFTPD and used the proftpd.conf section they used. Next, i created a database called proftpd and within that., a table called ftp. I also created a user called proftpd with password userlist - I can use this in phpmyadmin to "see" the database" so my matching config file should read...

```
# Password type 

SQLAuthTypes              Plaintext 

# Authentication type 

SQLAuthenticate           users 

# Use only SQL when authenticating, and not the system's /etc/passwd 

# If the user's information is not in SQL, they're not a user to use 

# this server. 

AuthOrder mod_sql.c 

# DB connect info. Format: database_name@server_address database_username database_password 

SQLConnectInfo           proftpd@localhost proftpd userlist

# Default UID/GID. Change to suit needs. 

SQLDefaultUID             5000 

SQLDefaultGID             5000 

# Mininum UID/GID. Change to suit needs. 

SQLMinUserUID            1000 

SQLMinUserGID            1000 

# Database query. Format: ** defined below ** 

SQLUserInfo                ftp username password uid gid ftpdir homedir 

# Jail users in ftpdir 

DefaultRoot             ~
```

Stop me if i'm going wrong

Next i executed the wee bit of sql at the bottom of the wiki page to create the rows so now within the ftp table, i have..

 *Quote:*   

> ftp(
> 
> userid        int(11)
> 
> username  varchar(255)
> ...

 

This all look fines to me.

Next i ran my own little query to insert a username - 

```
Insert into ftp values(1, "david", "test", 5000, 5000, "/home/david/", "/home/david/")
```

(don't think this will be the only user...i will be adding a few users so that they can all log in and see their home dir's)

I checked phpmyadmin and saw all the data was in the right place.

So to me this should all work - i should be able to use an ftp client from any machine on the internet, log in with david and test and see my home directory. But whenever i do it, the client says ftp session terminated.

I can post full config files here if anyone would like to see them or i will open the ports on my router so that you can ssh, phpmyadmin and ftp in yourself to see if anyone can see what silly thing's i've done.

One other thing, even though i compiled with mysql use flag, the line "AuthOrder mod_sql.c" made me think so i done a search...

```
find / -type f -iname 'mod_php.c'
```

But it didnt return anything. Just incase this may be the problem

Cheers

-David

----------

## MrUlterior

Check:

 That you restarted MySQL or flushed privs after creating the proftpd user's privs in the db

 That you can connect as proftpd's MySQL user from the command line

Post:

 your complete proftpd.conf

 the CREATE statements for the entire database

 Set your logging level to paranoid & show us the error in the log file when you attempt to connect

 Connect with a commandline ftp client & post the complete error message

----------

## MrUlterior

 *davidfowler2000 wrote:*   

> 
> 
> ```
> find / -type f -iname 'mod_php.c'
> ```
> ...

 

What does mod_php have to do with anything?

----------

## davidfowler2000

 *Quote:*   

>  That you restarted MySQL or flushed privs after creating the proftpd user's privs in the db

 

Restart MySQL? I restarted the whole machine and the user's priv's all are all fine

 *Quote:*   

>  That you can connect as proftpd's MySQL user from the command line 

 

Yup, works too

Full proftpd config - it's based on the proftpd.conf.distrib file that comes with the software:

```
## This is a basic ProFTPD configuration file (rename it to 

# 'proftpd.conf' for actual use.  It establishes a single server

# and a single anonymous login.  It assumes that you have a user/group

# "nobody" and "ftp" for normal operation and anon.

ServerName         "FTP Server on DF2K.ATH.CX"

ServerType         standalone

DefaultServer         on

# Port 21 is the standard FTP port.

Port            21

# Umask 022 is a good standard umask to prevent new dirs and files

# from being group and world writable.

Umask            022

# To prevent DoS attacks, set the maximum number of child processes

# to 30.  If you need to allow more than 30 concurrent connections

# at once, simply increase this value.  Note that this ONLY works

# in standalone mode, in inetd mode you should use an inetd server

# that allows you to limit maximum number of processes per service

# (such as xinetd).

MaxInstances         30

# Set the user and group under which the server will run.

User            nobody

Group            nogroup

# 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

<Limit SITE_CHMOD>

  DenyAll

</Limit>

# A basic anonymous configuration, no upload directories.  If you do not

# want anonymous users, simply delete this entire <Anonymous> section.

## SQL User Auth Section##

# Password type

SQLAuthTypes              Plaintext

# Authentication type

SQLAuthenticate           users

# Use only SQL when authenticating, and not the system's /etc/passwd

# If the user's information is not in SQL, they're not a user to use

# this server.

 

AuthOrder mod_sql.c

# DB connect info. Format: database_name@server_address database_username database_password

SQLConnectInfo            proftpd@localhost proftpd userlist

# Default UID/GID. Change to suit needs.

SQLDefaultUID             5000

SQLDefaultGID             5000

# Mininum UID/GID. Change to suit needs.

SQLMinUserUID            1000

SQLMinUserGID            1000

# Database query. Format: ** defined below **

SQLUserInfo                ftp usrname passwd uid gid ftpdir homedir

# Jail users in ftpdir

DefaultRoot             ~

```

The create staements are at the bottom of the wiki page:

```
CREATE TABLE ftp (

    user_id int(11) NOT NULL auto_increment,

    username varchar(100) DEFAULT '' NOT NULL,

    passwd varchar(50) DEFAULT '' NOT NULL,

    uid int(5) DEFAULT '5000' NOT NULL,

    gid int(5) DEFAULT '5000' NOT NULL,

    ftpdir varchar(255) DEFAULT '' NOT NULL,

    homedir varchar(255) DEFAULT '' NOT NULL,

    PRIMARY KEY (user_id),

    UNIQUE  (username)

 );
```

 *Quote:*   

>  Set your logging level to paranoid & show us the error in the log file when you attempt to connect 

 

How do i go about that?

 *Quote:*   

>  Connect with a commandline ftp client & post the complete error message 

 

I don't have one installed at the minute but i iwll do so when i get home tonight...i'm SSH'ing on my laptop at work and the battery isn't going to last much longer. If you like, you can log in at ftp://df2k.ath.cx and use the david / test password combination

 *Quote:*   

> What does mod_php have to do with anything?

 

After installing php, i had mods on the brain. it should read mod_sql.c

----------

## MrUlterior

 *davidfowler2000 wrote:*   

> 
> 
>  *Quote:*    Set your logging level to paranoid & show us the error in the log file when you attempt to connect  
> 
> How do i go about that?
> ...

 

Add the following to your protftpd.conf within the <GLOBAL> section

```

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

```

Check that /var/log/proftpd exists, if not create it &

then restart proftpd, attempt to connect & post the contents of /var/log/proftpd/paranoid.log

and

/var/log/proftpd/mysql.log

Also check this line: *Quote:*   

> 
> 
> SQLUserInfo                ftp usrname passwd uid gid ftpdir homedir 

 

Notice that the user name field is "usrname" instead of "username", if this is not a typo in this post, then correct that & I suspect it will all work.

If not,  post the output of the following commands:

```

ls -lathd /home/david

cat /etc/passwd | egrep "(5|1)000"

cat /etc/group | egrep "(5|1)000"

```

 *davidfowler2000 wrote:*   

> 
> 
> I don't have one installed at the minute but i iwll do so when i get home tonight...i'm SSH'ing on my laptop at work and the battery isn't going to last much longer. If you like, you can log in at ftp://df2k.ath.cx and use the david / test password combination
> 
> 

 

I'm firewalled presently & can't test -- but I will later

----------

## davidfowler2000

 *Quote:*   

> post the contents of /var/log/proftpd/paranoid.log 
> 
> and 
> 
> /var/log/proftpd/mysql.log

 

I only have a mysql.log and here it is

```
Jan 04 19:39:28 mod_sql/4.11[4469]: backend module 'mod_sql_mysql/4.04'

Jan 04 19:39:28 mod_sql/4.11[4469]: backend api    'mod_sql_api_v1'

Jan 04 19:39:28 mod_sql/4.11[4469]: >>> sql_getconf

Jan 04 19:39:28 mod_sql/4.11[4469]: entering         mysql cmd_defineconnection

Jan 04 19:39:28 mod_sql/4.11[4469]:  name: 'default'

Jan 04 19:39:28 mod_sql/4.11[4469]:  user: 'proftpd'

Jan 04 19:39:28 mod_sql/4.11[4469]:  host: 'localhost'

Jan 04 19:39:28 mod_sql/4.11[4469]:    db: 'proftpd'

Jan 04 19:39:28 mod_sql/4.11[4469]:  port: '3306'

Jan 04 19:39:28 mod_sql/4.11[4469]:   ttl: '0'

Jan 04 19:39:28 mod_sql/4.11[4469]: exiting         mysql cmd_defineconnection

Jan 04 19:39:28 mod_sql/4.11[4469]: entering         mysql cmd_open

Jan 04 19:39:28 mod_sql/4.11[4469]: connection 'default' opened

Jan 04 19:39:28 mod_sql/4.11[4469]: connection 'default' count is now 1

Jan 04 19:39:28 mod_sql/4.11[4469]: exiting         mysql cmd_open

Jan 04 19:39:28 mod_sql/4.11[4469]: backend successfully connected.

Jan 04 19:39:28 mod_sql/4.11[4469]: mod_sql status     : on

Jan 04 19:39:28 mod_sql/4.11[4469]: negative_cache     : off

Jan 04 19:39:28 mod_sql/4.11[4469]: authenticate       : users 

Jan 04 19:39:28 mod_sql/4.11[4469]: usertable          : ftp

Jan 04 19:39:28 mod_sql/4.11[4469]: userid field       : username

Jan 04 19:39:28 mod_sql/4.11[4469]: password field     : passwd

Jan 04 19:39:28 mod_sql/4.11[4469]: uid field          : uid

Jan 04 19:39:28 mod_sql/4.11[4469]: gid field          : gid

Jan 04 19:39:28 mod_sql/4.11[4469]: homedir field      : ftpdir

Jan 04 19:39:28 mod_sql/4.11[4469]: shell field        : homedir

Jan 04 19:39:28 mod_sql/4.11[4469]: homedirondemand    : false

Jan 04 19:39:28 mod_sql/4.11[4469]: SQLMinUserUID      : 1000

Jan 04 19:39:28 mod_sql/4.11[4469]: SQLMinUserGID      : 1000

Jan 04 19:39:28 mod_sql/4.11[4469]: <<< sql_getconf

Jan 04 19:39:31 mod_sql/4.11[4469]: >>> cmd_getpwnam

Jan 04 19:39:31 mod_sql/4.11[4469]: entering         mysql cmd_escapestring

Jan 04 19:39:31 mod_sql/4.11[4469]: exiting         mysql cmd_escapestring

Jan 04 19:39:31 mod_sql/4.11[4469]: cache miss for user 'david'

Jan 04 19:39:31 mod_sql/4.11[4469]: : entering         mysql cmd_select

Jan 04 19:39:31 mod_sql/4.11[4469]: entering         mysql cmd_open

Jan 04 19:39:31 mod_sql/4.11[4469]: connection 'default' count is now 2

Jan 04 19:39:31 mod_sql/4.11[4469]: exiting         mysql cmd_open

Jan 04 19:39:31 mod_sql/4.11[4469]: query "SELECT username, passwd, uid, gid, ftpdir, homedir FROM ftp WHERE (username='david') LIMIT 1"

Jan 04 19:39:31 mod_sql/4.11[4469]: entering         mysql cmd_close

Jan 04 19:39:31 mod_sql/4.11[4469]: connection 'default' count is now 1

Jan 04 19:39:31 mod_sql/4.11[4469]: exiting         mysql cmd_close

Jan 04 19:39:31 mod_sql/4.11[4469]: exiting         mysql cmd_select

Jan 04 19:39:31 mod_sql/4.11[4469]: cache miss for user 'david'

Jan 04 19:39:31 mod_sql/4.11[4469]: user 'david' cached

Jan 04 19:39:31 mod_sql/4.11[4469]: + pwd.pw_name  : david

Jan 04 19:39:31 mod_sql/4.11[4469]: + pwd.pw_uid   : 5000

Jan 04 19:39:31 mod_sql/4.11[4469]: + pwd.pw_gid   : 5000

Jan 04 19:39:31 mod_sql/4.11[4469]: + pwd.pw_dir   : /home/david/

Jan 04 19:39:31 mod_sql/4.11[4469]: + pwd.pw_shell : /home/david/

Jan 04 19:39:31 mod_sql/4.11[4469]: <<< cmd_getpwnam

Jan 04 19:39:31 mod_sql/4.11[4469]: >>> cmd_auth

Jan 04 19:39:31 mod_sql/4.11[4469]: entering         mysql cmd_escapestring

Jan 04 19:39:31 mod_sql/4.11[4469]: exiting         mysql cmd_escapestring

Jan 04 19:39:31 mod_sql/4.11[4469]: cache hit for user 'david'

Jan 04 19:39:31 mod_sql/4.11[4469]: >>> cmd_check

Jan 04 19:39:31 mod_sql/4.11[4469]: checking auth_type Plaintext

Jan 04 19:39:31 mod_sql/4.11[4469]: 'Plaintext' auth handler reports success

Jan 04 19:39:31 mod_sql/4.11[4469]: cache hit for user 'david'

Jan 04 19:39:31 mod_sql/4.11[4469]: <<< cmd_check

Jan 04 19:39:31 mod_sql/4.11[4469]: <<< cmd_auth

Jan 04 19:39:35 mod_sql/4.11[4469]: entering         mysql cmd_close

Jan 04 19:39:35 mod_sql/4.11[4469]: connection 'default' closed

Jan 04 19:39:35 mod_sql/4.11[4469]: connection 'default' count is now 0

Jan 04 19:39:35 mod_sql/4.11[4469]: exiting         mysql cmd_close
```

Even i can see there seems to be a problem with the thing not getting the password properly

As for the one liner commands, here's what they spit out

ls -lathd /home/david

```
drwxr-xr-x   10   david   users    4.0K   Jan 4 19:39 /home/david
```

cat /etc/passwd | egrep "(5|1)000"

 - Nothing

cat /etc/group | egrep "(5|1)000"

 - Nothing

The console ftp client says (u60 being my hostname)

```
Name (u60:root) : david

500 Auth Not Understood

SSL Not Available

331 Password Required for david

Password : 

503 Login Incorrect

Login Failed

Remote System type Is UNIX

Using binary mode to transfer files
```

----------

## MrUlterior

 *davidfowler2000 wrote:*   

> 
> 
> Even i can see there seems to be a problem with the thing not getting the password properly
> 
> 

 

It gets the password fine.

 *davidfowler2000 wrote:*   

> 
> 
> ls -lathd /home/david
> 
> ```
> ...

 

Aha, I think I see the light ... 

You have an OS user defined in /etc/passwd called "david", you have then created a DIFFERENT user in Proftpd's mysql db with the same name; this is bad & causing your problems. The home dir /home/david belongs to the OS user and the ftpd is unable to CD into it (that paranoid.log would really help to determine this ... did you restart  proftpd after adding those log directives?) Secondly, the UID/GID 5000/5000 associated with the FTPD don't exist.

I would suggest you do the following:

Run the SQL: (note the lack of trailing slashes ... )

```

Insert into ftp values(1, "testuser", "testuser", 5000, 5000, "/home/testuser", "/home/testuser")

```

Add a user with UID/GID 5000/5000 called ftpuser. Remember ALL your ftp users will appear to the OS to be this user. So each user's home dirs will need to have appropriate permissions allowing UID 5000 to read or write as needed. 

Execute the commands:

```

mkdir -p /home/testuser

chown 5000:5000 /home/testuser

chmod 0770 /home/testuser

```

And try login. It will work.

I strongly recommend you re-read the proftpd docs & sample configurations, there are still several elements of your config that're a little strange.

----------

