# ProFTPd + MySQL problem.

## WilliamHeuts

I've setup a ProFTPd Server with MySQL authentication but can't seem to get it to work.

I've used this guide, and it did work on an earlier install (I had a crash and reinstalled gentoo from scratch).

Software used:

ProFTPd 1.2.9.

```

/etc/proftpd/proftpd.conf

ServerName "FTP SERVER"

ServerType standalone

ServerAdmin xxxx

#DisplayLogin /home/william/ftp/welcome.msg

DefaultServer on

Port 21

Umask 022

MaxClients 30

MaxInstances 30

User proftpd

Group proftpd

SQLAuthenticate     on

SQLUserInfo         ftpusers username password NULL NULL homedir shell

SQLAuthTypes        Plaintext Backend

SQLConnectInfo      proftpd@server xxxx xxxxr

SQLUserWhereClause  "LoginAllowed = 'Y'"

SQLDefaultGID       65534

SQLDefaultUID       65534

SQLDefaultHomedir   /home/william/ftp

SQLGroupInfo        ftpusers groupname gid username

#SQLLog PASS updatelogin

#SQLNamedQuery updatelogin UPDATE "count=count+1 WHERE username='%u'" ftpusers

TransferLog /var/log/xferlog

DefaultRoot ~

# Normally, we want files to be overwriteable.

<Directory /*>

AllowOverwrite on

</Directory>

```

Then I added /bin/false to /etc/shells

```

# /etc/shells: valid login shells

# $Header: /home/cvsroot/gentoo-src/rc-scripts/etc/shells,v 1.5 2003/07/15 20:36:32 azarah Exp $

/bin/sh

/bin/bash

/bin/tcsh

/bin/csh

/bin/esh

/bin/ksh

/bin/zsh

/bin/sash

/bin/false

```

Finally I created a a table in MySQL 4.17

```

mysql> DESCRIBE ftpusers;

+--------------+-------------+------+-----+-------------------+-------+

| Field        | Type        | Null | Key | Default           | Extra |

+--------------+-------------+------+-----+-------------------+-------+

| loginallowed | char(1)     |      |     | Y                 |       |

| username     | varchar(60) |      | PRI | NULL              |       |

| uid          | int(11)     |      |     | 65534             |       |

| gid          | int(11)     |      |     | 65534             |       |

| groupname    | varchar(50) |      |     | nobody            |       |

| password     | varchar(30) | YES  |     | NULL              |       |

| homedir      | varchar(50) | YES  |     | /home/william/ftp |       |

| shell        | varchar(15) |      |     | /bin/false        |       |

+--------------+-------------+------+-----+-------------------+-------+

8 rows in set (0.00 sec)

mysql> SELECT * FROM ftpusers;

+--------------+----------+-------+-------+-----------+----------+-------------------+------------+

| loginallowed | username | uid   | gid   | groupname | password | homedir           | shell      |

+--------------+----------+-------+-------+-----------+----------+-------------------+------------+

| Y            | william  | 65534 | 65534 | nobody    | heuts    | /home/william/ftp | /bin/false |

+--------------+----------+-------+-------+-----------+----------+-------------------+------------+

1 row in set (0.00 sec)

```

So what did I do wrong because when i try to test the server with ftp://william:heuts@localhost it gives me a 530 Login Incorrect error?

Thanks in advance for all the help.

----------

## Bobbster55

I also seam to be having a problem. After hours of googling, I am no closer to a solution then this morning. 

My issue seams to be that ProFtpD is not authenticating against the entries in the MySQL database, just authenticating against the system. Using the directive "SQLLogFile /var/log/proftpd.sql" I can see that proftpd is querying the database and returning information. I've setup an account on the system named "test" and also placed that entry in the database. I changed the password in the database to something different then what the system would use, but when logging in, only the system password would be accepted and not the one in the database. I've also added a SQLUserWhereClause "FTPAccess='Yes'" entry in the conf file and can see that proftpd is querying the database for entries and returning with nothing (because I set FTPAccess='No' for the user), but it still allows me to login.

Here is my proftpd.conf file:

```
AuthPAM         Off

SQLMinUserGID           99

SQLConnectInfo        userauth@localhost proftpd password

SQLAuthenticate       users

SQLAuthTypes          Plaintext Backend

SQLUserInfo           FTPUsers Username Password UID GID HomeDir Shell

SQLUserWhereClause "FTPAccess='Yes'"

SQLLogFile      /var/log/proftpd.sql

```

Here is the entry in the MySQL database:

```
Username: test

  Password: test

   HomeDir: /home/test

     Count: 0

       UID: 1000

       GID: 100

     Shell: /bin/bash

 FTPAccess: No

```

The password for the system is set to "pass". When logging in, I successfully login using the password "pass" and fail when I use the password "test".

In the proftpd.sql file, I can see that it indeed is querying the database but failing:

```
Mar 02 17:25:35 mod_sql/4.11[7516]: query "SELECT Username, Password, UID, GID, HomeDir, Shell FROM FTPUsers WHERE (Username='test') and ((FTPAccess='Yes')) LIMIT 1"
```

Which returns with nothing. Can anyone help me out? They ultimate goal is to control FTP access via the database.

Note: If I set FTPAccess='Yes', the proftpd.sql file shows that information is returned, but still, only the system password is accepted and not the database password.

----------

## j-m

Hmm, this is not a good test to have the same user with different passwords in MySQL and system. Test MySQL authentication with another user that does not exist in system.

----------

## Bobbster55

 *Quote:*   

> Hmm, this is not a good test to have the same user with different passwords in MySQL and system. Test MySQL authentication with another user that does not exist in system.

 

Gave that a shot. Added a user named "test2" with a password of "testing" and it doesn't authenticate at all.  It just gives me: 530 Login incorrect.

Checking the proftpd.sql log file, it shows that the daemon does get the correct entry for "test2". But it does not authenticate.

Here is the snippet from the proftpd.sql file:

```
Mar 02 19:40:00 mod_sql/4.11[8321]: query "SELECT Username, Password, UID, GID, HomeDir, Shell FROM FTPUsers WHERE (Username='test2') and ((FTPAccess='Yes')) LIMIT 1"

Mar 02 19:40:00 mod_sql/4.11[8321]: entering    mysql cmd_close

Mar 02 19:40:00 mod_sql/4.11[8321]: connection 'default' count is now 1

Mar 02 19:40:00 mod_sql/4.11[8321]: exiting     mysql cmd_close

Mar 02 19:40:00 mod_sql/4.11[8321]: exiting     mysql cmd_select

Mar 02 19:40:00 mod_sql/4.11[8321]: cache miss for user 'test2'

Mar 02 19:40:00 mod_sql/4.11[8321]: user 'test2' cached

Mar 02 19:40:00 mod_sql/4.11[8321]: + pwd.pw_name  : test2

Mar 02 19:40:00 mod_sql/4.11[8321]: + pwd.pw_uid   : 1000

Mar 02 19:40:00 mod_sql/4.11[8321]: + pwd.pw_gid   : 100

Mar 02 19:40:00 mod_sql/4.11[8321]: + pwd.pw_dir   : /home/test2

Mar 02 19:40:00 mod_sql/4.11[8321]: + pwd.pw_shell : /bin/bash

Mar 02 19:40:00 mod_sql/4.11[8321]: <<< cmd_getpwnam

Mar 02 19:40:02 mod_sql/4.11[8321]: entering    mysql cmd_close

Mar 02 19:40:02 mod_sql/4.11[8321]: connection 'default' closed

Mar 02 19:40:02 mod_sql/4.11[8321]: connection 'default' count is now 0

Mar 02 19:40:02 mod_sql/4.11[8321]: exiting     mysql cmd_close
```

Running the same query that proftpd does directly into the database using the user i gave it gives me the correct result:

```
mysql> SELECT Username, Password, UID, GID, HomeDir, Shell FROM FTPUsers WHERE (Username='hany') and ((FTPAccess='Yes')) LIMIT 1;

+----------+----------+------+------+--------------+-----------+

| Username | Password | UID  | GID  | HomeDir      | Shell     |

+----------+----------+------+------+--------------+-----------+

| test2    | testing  | 1000 |  100 | /home/test2  | /bin/bash |

+----------+----------+------+------+--------------+-----------+

1 row in set (0.00 sec)
```

Any suggestions?

----------

## dudestir

Did anyone find a solution to this issue?

I've tried all kinds of permutions from plaintext to crypt in both the mysql table and the conf but get the same failure in/after the call to cmd_getpwnam.

----------

## Quincy

Whats your proftpd.conf regarding mysql?

Have you set the "RequireValidShell" setting in your proftpd.conf?

Whats your proftpd sql log saying?

Whats in your user table for the user you are trying to log in?

----------

## dudestir

Woow thanks for the quick reply.

Running "proftp -nd6" to get the debug information I get the following

```
localhost (192.168.1.1[192.168.1.1]) - CURRENT-CLIENTS

localhost (192.168.1.1[192.168.1.1]) - USER

localhost (192.168.1.1[192.168.1.1]) - USER usbfriend: Login successful.

localhost (192.168.1.1[192.168.1.1]) - opening TransferLog '/var/log/proftpd/proftpd.xferlog'

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "getpwnam" to module mod_sql

localhost (192.168.1.1[192.168.1.1]) - notice: unable to resolve '~/': Permission denied

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "setpwent" to module mod_sql

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "setpwent" to module mod_ldap

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "setpwent" to module mod_auth_file

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "setpwent" to module mod_auth_unix

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "setgrent" to module mod_sql

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "setgrent" to module mod_ldap

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "setgrent" to module mod_auth_file

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "setgrent" to module mod_auth_unix

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "getpwent" to module mod_sql

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "getpwent" to module mod_auth_file

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "getpwent" to module mod_auth_unix

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "getgrent" to module mod_sql

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "getgrent" to module mod_auth_file

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "getgrent" to module mod_auth_unix

localhost (192.168.1.1[192.168.1.1]) - Preparing to chroot() the environment, path = '~/'

localhost (192.168.1.1[192.168.1.1]) - usbfriend chroot("~/"): No such file or directory

localhost (192.168.1.1[192.168.1.1]) - error: unable to set default root directory

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "endpwent" to module mod_sql

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "endpwent" to module mod_ldap

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "endpwent" to module mod_auth_file

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "endpwent" to module mod_auth_unix

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "endgrent" to module mod_sql

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "endgrent" to module mod_ldap

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "endgrent" to module mod_auth_file

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "endgrent" to module mod_auth_unix

localhost (192.168.1.1[192.168.1.1]) - FTP session closed.

```

The applicable row in the mysql table has the following values

mysql> select userid,name,homedir,uid,gid,passwd,shell from users;

```

+-----------+---------------+-----------------+-------+-------+-------------------------------------------+----------+

| userid    | name          | homedir         | uid   | gid   | passwd                                    | shell    |

+-----------+---------------+-----------------+-------+-------+-------------------------------------------+----------+

| usbfriend | for friends | /home/shares/D/ | 10000 | 10000 | *36B8CEBF25DF7E90223A17E128D66766661A89C4 | /bin/ftp |

+-----------+---------------+-----------------+-------+-------+-------------------------------------------+----------+

1 row in set (0.00 sec)

```

The directory rights are

```

penguin icepalace # ls -l /home/shares/

total 32

drwxr--r--  5 usbfriend ftpusers 32768 Dec 31  1969 D

```

My Proftpd.conf contains

```
 

ServerName           "ProFTPD"

ServerType           standalone

DefaultServer        on

ServerAdmin          here@there.ca

ServerIdent          on         "My Place"

DeferWelcome         off

Port                21

Umask                022

MaxInstances         5

AllowStoreRestart    on

AllowRetrieveRestart on

DefaultRoot          ~

RequireValidShell    on

AuthPAM              off

User                 nobody

Group                nogroup

TransferLog          /var/log/proftpd/proftpd.xferlog

AllowStoreRestart    on

AllowRetrieveRestart on

LogFormat            default "%h %l %u %t \"%r\" %s %b"

LogFormat            auth "%v [%P] %h %t \"%r\" %s"

LogFormat            write "%h %l %u %t \"%r\" %s %b"

# Log file/dir access

ExtendedLog          /var/log/proftpd/proftpd.access_log WRITE,READ write

# Record all logins

ExtendedLog          /var/log/proftpd/proftpd.auth_log AUTH auth

# Paranoia logging level....

ExtendedLog          /var/log/proftpd/proftpd.paranoid_log ALL default

<Directory /home/shares/D/*>

  AllowOverwrite     on

  <Limit ALL>

     AllowGroup ftpusers

     DenyAll

  </Limit>

  <Limit READ DIRS>

     AllowGroup readonly

  #       DenyAll

  </Limit>

</Directory>

<Limit LOGIN>

     AllowGroup ftpusers

     AllowGroup readonly

     DenyAll

</Limit>

<Global>

     AllowForeignAddress  on

</Global>

SQLConnectInfo       proftpd@localhost proftpd theendisnear

SQLAuthenticate      users groups

SQLAuthTypes         Crypt Backend

SQLUserInfo          users userid passwd uid gid homedir shell

SQLGroupInfo         groups groupid gid members

SQLLogFile           /var/log/proftpd/proftpd.sql

# Default UID/GID. Change to suit needs.

#SQLDefaultUID             5000

#SQLDefaultGID             5000

# Mininum UID/GID. Change to suit needs.

#SQLMinUserUID            1000

#SQLMinUserGID            1000

```

Thanks

----------

## Quincy

Your login looks succesful, the user is authenticated, but the homedir is not reachable. There is nothing wrong with your passwords.

Your problems are:

```
localhost (192.168.1.1[192.168.1.1]) - notice: unable to resolve '~/': Permission denied
```

and

```
localhost (192.168.1.1[192.168.1.1]) - Preparing to chroot() the environment, path = '~/'

localhost (192.168.1.1[192.168.1.1]) - usbfriend chroot("~/"): No such file or directory

localhost (192.168.1.1[192.168.1.1]) - error: unable to set default root directory 
```

Try setting first <Directory /home/shares/D/*> to <Directory /home/shares/D>, because the first syntax says "All files, but not the directory itself", so you get no access to the directory while changing and chrooting.

If this doesn't help you should look into this file: /var/log/proftpd/proftpd.sql

There you can see if the select statement gets the right fields and if there is the right homedir. Perhaps it is right, but it is not accessable, try to set different permissions to the homedir for testing.

If you can't find it yourself, please post the relevant parts of the sql.log

----------

## dudestir

Thanks

I agree that is user is being validated

My proftpd.sql file doesn't offer a lot 

```

Jul 10 10:54:06 mod_sql/4.11[10558]: cache hit for user 'usbfriend'

Jul 10 10:54:06 mod_sql/4.11[10558]: <<< cmd_check

Jul 10 10:54:06 mod_sql/4.11[10558]: <<< cmd_auth

Jul 10 10:54:06 mod_sql/4.11[10558]: >>> cmd_getpwnam

Jul 10 10:54:06 mod_sql/4.11[10558]: cache hit for user 'usbfriend'

Jul 10 10:54:06 mod_sql/4.11[10558]: <<< cmd_getpwnam

Jul 10 10:54:06 mod_sql/4.11[10558]: entering   mysql cmd_close

Jul 10 10:54:06 mod_sql/4.11[10558]: connection 'default' closed

Jul 10 10:54:06 mod_sql/4.11[10558]: connection 'default' count is now 0

Jul 10 10:54:06 mod_sql/4.11[10558]: exiting    mysql cmd_close

```

The auth.log also seems to agree 

```

ProFTPD [10429] 192.168.1.1 [10/Jul/2005:10:50:06 -0700] "USER usbfriend" 331

ProFTPD [10457] 192.168.1.1 [10/Jul/2005:10:52:06 -0700] "USER usbfriend" 331

ProFTPD [10558] 192.168.1.1 [10/Jul/2005:10:54:06 -0700] "USER usbfriend" 331

ProFTPD [10570] 192.168.1.1 [10/Jul/2005:10:56:06 -0700] "USER usbfriend" 331

```

So it comes down to getting the chroot to work.

I corrected the error in the conf file you pointed out and also removed the Limit Reads

```

<Directory /home/shares/D>

  AllowOverwrite     on

  <Limit ALL>

     AllowGroup ftpusers

     DenyAll

  </Limit>

#  <Limit READ DIRS>

#     AllowGroup readonly

#     DenyAll

#  </Limit>

</Directory>

```

The error I get in debug mode remains the same.

I am pointing to the mount point of a ntfs drive if that matters.  ( I have tried on a reiserfs path also)

----------

## Quincy

It would be interesting to see the parts of these two from your log

```
Jul 10 16:17:04 mod_sql/4.11[6339]: + pwd.pw_name  : testing_ftp01

Jul 10 16:17:04 mod_sql/4.11[6339]: + pwd.pw_uid   : 10009

Jul 10 16:17:04 mod_sql/4.11[6339]: + pwd.pw_gid   : 10001

Jul 10 16:17:04 mod_sql/4.11[6339]: + pwd.pw_dir   : /home/test

Jul 10 16:17:04 mod_sql/4.11[6339]: + pwd.pw_shell : /bin/bash

```

```
Jul 10 16:17:04 mod_sql/4.11[6339]: + grp.gr_name : testing

Jul 10 16:17:04 mod_sql/4.11[6339]: + grp.gr_gid  : 10001

Jul 10 16:17:04 mod_sql/4.11[6339]: + grp.gr_mem  : testing_ftp01

```

Do the UID/GIDs match the users mentioned in the tables? The point is to find out who blocks the directory: System or Proftpd. You can ensure that which very loose restrictions only for testing ("AllowAll") just to see if it logs in. Then the settings can be hardened again...

----------

## dudestir

Thanks

I've been playing with all the settings I can think of todat so here is the current state of everything

debug statements

```

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "getgrent" to module mod_auth_unix

localhost (192.168.1.1[192.168.1.1]) - Preparing to chroot() the environment, path = '~/'

localhost (192.168.1.1[192.168.1.1]) - usbfriend chroot("~/"): No such file or directory

localhost (192.168.1.1[192.168.1.1]) - error: unable to set default root directory

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "endpwent" to module mod_sql

localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "endpwent" to module mod_ldap

```

Proftpd.sql text

```

Jul 10 16:52:49 mod_sql/4.11[10473]: backend module 'mod_sql_mysql/4.04'

Jul 10 16:52:49 mod_sql/4.11[10473]: backend api    'mod_sql_api_v1'

Jul 10 16:52:49 mod_sql/4.11[10473]: >>> sql_getconf

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_defineconnection

Jul 10 16:52:49 mod_sql/4.11[10473]:  name: 'default'

Jul 10 16:52:49 mod_sql/4.11[10473]:  user: 'proftpd'

Jul 10 16:52:49 mod_sql/4.11[10473]:  host: 'localhost'

Jul 10 16:52:49 mod_sql/4.11[10473]:    db: 'proftpd'

Jul 10 16:52:49 mod_sql/4.11[10473]:  port: '3306'

Jul 10 16:52:49 mod_sql/4.11[10473]:   ttl: '0'

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_defineconnection

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' opened

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 1

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: backend successfully connected.

Jul 10 16:52:49 mod_sql/4.11[10473]: mod_sql status     : on

Jul 10 16:52:49 mod_sql/4.11[10473]: negative_cache     : off

Jul 10 16:52:49 mod_sql/4.11[10473]: authenticate       : users groups

Jul 10 16:52:49 mod_sql/4.11[10473]: usertable          : users

Jul 10 16:52:49 mod_sql/4.11[10473]: userid field       : userid

Jul 10 16:52:49 mod_sql/4.11[10473]: password field     : passwd

Jul 10 16:52:49 mod_sql/4.11[10473]: uid field          : uid

Jul 10 16:52:49 mod_sql/4.11[10473]: gid field          : gid

Jul 10 16:52:49 mod_sql/4.11[10473]: homedir field      : homedir

Jul 10 16:52:49 mod_sql/4.11[10473]: shell field        : shell

Jul 10 16:52:49 mod_sql/4.11[10473]: homedirondemand    : false

Jul 10 16:52:49 mod_sql/4.11[10473]: group table        : groups

Jul 10 16:52:49 mod_sql/4.11[10473]: groupname field    : groupid

Jul 10 16:52:49 mod_sql/4.11[10473]: grp gid field      : gid

Jul 10 16:52:49 mod_sql/4.11[10473]: grp members field  : members

Jul 10 16:52:49 mod_sql/4.11[10473]: SQLMinUserUID      : 999

Jul 10 16:52:49 mod_sql/4.11[10473]: SQLMinUserGID      : 999

Jul 10 16:52:49 mod_sql/4.11[10473]: SQLDefaultUID      : 65533

Jul 10 16:52:49 mod_sql/4.11[10473]: SQLDefaultGID      : 65533

Jul 10 16:52:49 mod_sql/4.11[10473]: <<< sql_getconf

Jul 10 16:52:49 mod_sql/4.11[10473]: >>> cmd_getgroups

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_escapestring

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_escapestring

Jul 10 16:52:49 mod_sql/4.11[10473]: cache miss for user 'usbfriend'

Jul 10 16:52:49 mod_sql/4.11[10473]: : entering         mysql cmd_select

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 2

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: query "SELECT userid, passwd, uid, gid, homedir, shell FROM users WHERE (userid='usbfriend') LIMIT 1"

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_close

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 1

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_close

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_select

Jul 10 16:52:49 mod_sql/4.11[10473]: cache miss for user 'usbfriend'

Jul 10 16:52:49 mod_sql/4.11[10473]: user 'usbfriend' cached

Jul 10 16:52:49 mod_sql/4.11[10473]: + pwd.pw_name  : usbfriend

Jul 10 16:52:49 mod_sql/4.11[10473]: + pwd.pw_uid   : 1005

Jul 10 16:52:49 mod_sql/4.11[10473]: + pwd.pw_gid   : 65533

Jul 10 16:52:49 mod_sql/4.11[10473]: + pwd.pw_dir   : /home/shares/ftp/

Jul 10 16:52:49 mod_sql/4.11[10473]: + pwd.pw_shell : /bin/bash

Jul 10 16:52:49 mod_sql/4.11[10473]: cache miss for gid '65533'

Jul 10 16:52:49 mod_sql/4.11[10473]: : entering         mysql cmd_select

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 2

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: query "SELECT groupid FROM groups WHERE (gid = 65533) LIMIT 1"

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_close

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 1

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_close

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_select

Jul 10 16:52:49 mod_sql/4.11[10473]: : entering         mysql cmd_select

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 2

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: query "SELECT groupid, gid, members FROM groups WHERE (members = 'usbfriend' OR members LIKE 'usbfri$

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_close

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 1

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_close

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_select

Jul 10 16:52:49 mod_sql/4.11[10473]: cache miss for group 'ftpusers'

Jul 10 16:52:49 mod_sql/4.11[10473]: group 'ftpusers' cached

Jul 10 16:52:49 mod_sql/4.11[10473]: + grp.gr_name : ftpusers

Jul 10 16:52:49 mod_sql/4.11[10473]: + grp.gr_gid  : 505

Jul 10 16:52:49 mod_sql/4.11[10473]: + grp.gr_mem  : usbfriend

Jul 10 16:52:49 mod_sql/4.11[10473]: <<< cmd_getgroups

Jul 10 16:52:49 mod_sql/4.11[10473]: >>> cmd_getgroups

Jul 10 16:52:49 mod_sql/4.11[10473]: cache hit for user 'usbfriend'

Jul 10 16:52:49 mod_sql/4.11[10473]: cache miss for gid '65533'

Jul 10 16:52:49 mod_sql/4.11[10473]: : entering         mysql cmd_select

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 2

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: query "SELECT groupid FROM groups WHERE (gid = 65533) LIMIT 1"

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_close

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 1

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_close

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_select

Jul 10 16:52:49 mod_sql/4.11[10473]: : entering         mysql cmd_select

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 2

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: query "SELECT groupid, gid, members FROM groups WHERE (members = 'usbfriend' OR members LIKE 'usbfri$

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_close

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 1

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_close

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_select

Jul 10 16:52:49 mod_sql/4.11[10473]: cache hit for group 'ftpusers'

Jul 10 16:52:49 mod_sql/4.11[10473]: <<< cmd_getgroups

Jul 10 16:52:49 mod_sql/4.11[10473]: >>> cmd_getpwnam

Jul 10 16:52:49 mod_sql/4.11[10473]: cache hit for user 'usbfriend'

Jul 10 16:52:49 mod_sql/4.11[10473]: <<< cmd_getpwnam

Jul 10 16:52:49 mod_sql/4.11[10473]: >>> cmd_gid2name

Jul 10 16:52:49 mod_sql/4.11[10473]: cache miss for gid '65533'

Jul 10 16:52:49 mod_sql/4.11[10473]: : entering         mysql cmd_select

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 2

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_open

Jul 10 16:52:49 mod_sql/4.11[10473]: query "SELECT groupid FROM groups WHERE (gid = 65533) LIMIT 1"

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_close

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 1

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_close

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_select

Jul 10 16:52:49 mod_sql/4.11[10473]: <<< cmd_gid2name

Jul 10 16:52:49 mod_sql/4.11[10473]: >>> cmd_auth

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_escapestring

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_escapestring

Jul 10 16:52:49 mod_sql/4.11[10473]: cache hit for user 'usbfriend'

Jul 10 16:52:49 mod_sql/4.11[10473]: >>> cmd_check

Jul 10 16:52:49 mod_sql/4.11[10473]: checking auth_type Crypt

Jul 10 16:52:49 mod_sql/4.11[10473]: checking auth_type Backend

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_checkauth

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_checkauth

Jul 10 16:52:49 mod_sql/4.11[10473]: 'Backend' auth handler reports success

Jul 10 16:52:49 mod_sql/4.11[10473]: cache hit for user 'usbfriend'

Jul 10 16:52:49 mod_sql/4.11[10473]: <<< cmd_check

Jul 10 16:52:49 mod_sql/4.11[10473]: <<< cmd_auth

Jul 10 16:52:49 mod_sql/4.11[10473]: >>> cmd_getpwnam

Jul 10 16:52:49 mod_sql/4.11[10473]: cache hit for user 'usbfriend'

Jul 10 16:52:49 mod_sql/4.11[10473]: <<< cmd_getpwnam

Jul 10 16:52:49 mod_sql/4.11[10473]: entering   mysql cmd_close

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' closed

Jul 10 16:52:49 mod_sql/4.11[10473]: connection 'default' count is now 0

Jul 10 16:52:49 mod_sql/4.11[10473]: exiting    mysql cmd_close

```

Data from Mysql

```

mysql> select * from groups;

+----------+-----+-----------+

| groupid  | gid | members   |

+----------+-----+-----------+

| ftpusers | 505 | usbfriend |

+----------+-----+-----------+

1 row in set (0.00 sec)

mysql> select userid,name,homedir,uid,gid,passwd,shell from users;

+-----------+---------------+-------------------+------+-----+-------------------------------------------+-----------+

| userid    | name          | homedir           | uid  | gid | passwd                                    | shell     |

+-----------+---------------+-------------------+------+-----+-------------------------------------------+-----------+

| usbfriend | ftp friends | /home/shares/ftp/ | 1005 | 505 | *36B8CEBF25DF7E90223A17E128D66766661A89C4 | /bin/bash |

+-----------+---------------+-------------------+------+-----+-------------------------------------------+-----------+

1 row in set (0.00 sec)

```

/etc/group

```

proftpd:x:504:

ftpusers:x:505:

```

/etc/passwd

```

proftpd:x:1004:504::/home/shares:/bin/false

usbfriend:x:1005:505:,,,:/home/shares/ftp:/bin/bash

```

Directory I'm aiming at 

```

 ls -la /home/shares/ftp

total 3039

drwx--x--x  3 usbfriend ftpusers     224 Jul 10 14:42 .

drwxrw----  8 root      netusers     192 Jul 10 13:58 ..

-rw-r--r--  1 usbfriend ftpusers    1301 Jul 10 13:58 ._cfg0000_.bashrc

-rw-r--r--  1 usbfriend ftpusers     240 Jul 10 13:58 .bash_logout

-rw-r--r--  1 usbfriend ftpusers     308 Jul 10 13:58 .bash_profile

-rw-r--r--  1 usbfriend ftpusers    1306 Jul 10 13:58 .bashrc

ls -la /home/shares

total 33

drwxrw----   8 root      netusers   192 Jul 10 13:58 .

drwxr-xr-x  16 root      root       584 Jul  9 00:31 ..

rwx--x--x   3 usbfriend ftpusers   224 Jul 10 14:42 ftp

```

and finally my conf file as it currently exists

```

ServerName           "ProFTPD"

ServerType           standalone

DefaultServer        on

ServerAdmin          here@there.ca

ServerIdent          on         "My Place"

DeferWelcome         off

Port                 27092

#Port                21

Umask                022

MaxInstances         5

AllowStoreRestart    on

AllowRetrieveRestart on

RequireValidShell    off

AllowForeignAddress  on

DefaultRoot          ~       !users

AuthPAM              off

User                 nobody

Group                nogroup

TransferLog          /var/log/proftpd/proftpd.xferlog

AllowStoreRestart    on

AllowRetrieveRestart on

LogFormat            default "%h %l %u %t \"%r\" %s %b"

LogFormat            auth "%v [%P] %h %t \"%r\" %s"

LogFormat            write "%h %l %u %t \"%r\" %s %b"

# Log file/dir access

ExtendedLog          /var/log/proftpd/proftpd.access_log WRITE,READ write

# Record all logins

ExtendedLog          /var/log/proftpd/proftpd.auth_log AUTH auth

# Paranoia logging level....

ExtendedLog          /var/log/proftpd/proftpd.paranoid_log ALL default

<Directory /home/shares/ftp/*>

<Directory /home/shares/ftp/*>

  AllowOverwrite off

  HideNoAccess on

  <Limit READ>

     AllowAll

  </Limit>

  <Limit Write>

     DenyAll

  </Limit>

  AllowRetrieveRestart on

  DeleteAbortedStores on

  AllowAll

</Directory>

SQLConnectInfo       proftpd@localhost proftpd mypassword

SQLAuthenticate      users groups

SQLAuthTypes         Crypt Backend

SQLUserInfo          users userid passwd uid gid homedir shell

SQLGroupInfo         groups groupid gid members

SQLLogFile           /var/log/proftpd/proftpd.sql

# Default UID/GID. Change to suit needs.

#SQLDefaultUID             5000

#SQLDefaultGID             5000

# Mininum UID/GID. Change to suit needs.

#SQLMinUserUID            1000

#SQLMinUserGID            1000

<Global>

RootLogin off

RequireValidShell off

</Global>

```

Thanks for all your help walking through this issue!!!!!

----------

## Quincy

I tried to reproduce your situation, but i was not able to do so!

Things that are wrong in your config:

- You set again <Directory /home/shares/ftp/*> instead of <Directory /home/shares/ftp> that inhibits definitvely logging in, but does not produce the error message.

- Your GID for the group ftpusers is lower than SQLMinUserGID so the SQLDefaultGID is used, but that again is not the cause for the error message reagrading the chrooting.

- There are some typos/duplicates in your config, but proftpd woul dnot have started if they were critical

- Your password is looking quite strange, but i'm not using "Backend", but only Crypt

What's obvious from my (working) server is the difference in output in debug mode:

```
localhost (quincy-pc.bbc[192.168.111.25]) - Preparing to chroot() the environment, path = '/home/quincy'

localhost (quincy-pc.bbc[192.168.111.25]) - Environment successfully chroot()ed.
```

Yours:

```
localhost (192.168.1.1[192.168.1.1]) - dispatching auth request "getgrent" to module mod_auth_unix

localhost (192.168.1.1[192.168.1.1]) - Preparing to chroot() the environment, path = '~/' 

localhost (192.168.1.1[192.168.1.1]) - usbfriend chroot("~/"): No such file or directory
```

/home/quincy is the home directory from MySQL and it replaces the ~ (home directory) as set in the DefaultRoot server setting. In your config the homedir is read correctly from MySQL, but does not replace the ~

Just to be sure: I'm using proftpd-1.2.10-r1

I'm still trying...

----------

## dudestir

Thanks for all your time on this also.

I have tried removing the "Backend"

Changing the database path to "/home/shares/ftp" (no trailing slash)

and removed the "/*" from the conf file

Restarted proftpd and still get

```

localhost (192.168.1.1[192.168.1.1]) - Preparing to chroot() the environment, path = '~/'

localhost (192.168.1.1[192.168.1.1]) - usbfriend chroot("~/"): No such file or directory

localhost (192.168.1.1[192.168.1.1]) - error: unable to set default root directory

```

The GID is commented out so I don't think that hurts.

The password is set using the password() function in mysql, although I did try it as plaintext in the database and then changed crypt to plaintext in the conf with the same result.

I will play more tonight.

----------

## Quincy

I have got it...it's totally easy, totally clear, but i saw it when i tried to install EVERYTHING like you...

Look at this:

```
ls -la /home/shares

total 33

drwxrw----   8 root      netusers   192 Jul 10 13:58 .

drwxr-xr-x  16 root      root       584 Jul  9 00:31 ..

rwx--x--x   3 usbfriend ftpusers   224 Jul 10 14:42 ftp 
```

The rights for the ftp directory are sufficient, but the rights of the parent directory (shares) are to restrictive, only root can open it..

Hope this will solve your problem  :Smile: 

----------

## dudestir

Thanks

It was a permissions issues.  I still couldn't get it to work on the previous path.  I reset everything to /ftp and set the correct permissios and now it works fine.

Once I'm more comfortable with the program I may play with the lnger path again to see what I screwed up.

----------

## Quincy

If you mount a NTFS partition (like mentioned earlier) you will have to set a "umask" in the moutn command, otherwise it will be mounted, but is only readable for root.

----------

