LinuxQuestions.org - FTP Server and mySQL Authentication

- Linux - Networking (https://www.linuxquestions.org/questions/linux-networking-3/)

- - FTP Server and mySQL Authentication (https://www.linuxquestions.org/questions/linux-networking-3/ftp-server-and-mysql-authentication-383531/)

FTP Server and mySQL Authentication

I'm currently setting up a server for my dorm floor so we can pool photos and serve the occasional game, etc. I'm using Open-Source SuSE 10.0.

I've got Apache and mySQL working fine (as far as I can tell), but I've run into a huge stumbling block with FTP. I've tried pure-ftpd and proftpd, and have been able to get neither working- pure-ftpd I can only log in with root and the system account I made at installation, and with proftpd I can't get anyone logged in at all. I'm trying to authenticate via a mySQL database, because this would allow me to integrate FTP accounts with the web interface, and adding a system account for each user would get messy (there are some 75 people on the floor).

Obviously I'm doing something very wrong, but I don't know where to start... "Login failed" is really not very helpful as far as figuring out the problem. :\ So, if someone could let me know of a way to get more specific error information, I could get closer to the issue and ask some more specific questions. Any help from someone who's used mySQL authentication with pure-ftpd or proftpd (ar any other software I can use) would be very appreciated.

Also, I'm a bit confused about how mysql acounts translate into system accounts, and the whole linux user/group system in general... please excuse me for being a helpless newbie. :x

Thanks.

Update...

After some frustrated fiddling, I got into the system log and discovered that pure-ftpd was reporting that "The SQL server seems to be down". I'm pretty sure that this is not the case, seeing as how I can log into the mySQL command line in the terminal.

Thus, I'm guessing it has something to do with my configuration. This is the mySQL configuration for pure-ftpd. Can someone tell me if something is wrong with it?

Code:

##############################################

#                                            #

# Sample Pure-FTPd Mysql configuration file. #

# See README.MySQL for explanations.        #

#                                            #

##############################################





# Optional : MySQL server name or IP. Don't define this for unix sockets.



# MYSQLServer    127.0.0.1





# Optional : MySQL port. Don't define this if a local unix socket is used.



# MYSQLPort      3306





# Optional : define the location of mysql.sock if the server runs on this host.



MYSQLSocket    /var/lib/mysql/mysql.sock





# Mandatory : user to bind the server as.



MYSQLUser      root





# Mandatory : user password. You must have a password.



MYSQLPassword  <removed>





# Mandatory : database to open.



MYSQLDatabase  users





# Mandatory : how passwords are stored

# Valid values are : "cleartext", "crypt", "md5" and "password"

# ("password" = MySQL password() function)

# You can also use "any" to try "crypt", "md5" *and* "password"



MYSQLCrypt      cleartext





# In the following directives, parts of the strings are replaced at

# run-time before performing queries :

#

# \L is replaced by the login of the user trying to authenticate.

# \I is replaced by the IP address the user connected to.

# \P is replaced by the port number the user connected to.

# \R is replaced by the IP address the user connected from.

# \D is replaced by the remote IP address, as a long decimal number.

#

# Very complex queries can be performed using these substitution strings,

# especially for virtual hosting.





# Query to execute in order to fetch the password



MYSQLGetPW      SELECT password FROM users WHERE username="\L"





# Query to execute in order to fetch the system user name or uid



MYSQLGetUID    SELECT uid FROM users WHERE username="\L"





# Optional : default UID - if set this overrides MYSQLGetUID



#MYSQLDefaultUID 1000





# Query to execute in order to fetch the system user group or gid



MYSQLGetGID    SELECT gid FROM users WHERE username="\L"





# Optional : default GID - if set this overrides MYSQLGetGID



#MYSQLDefaultGID 1000





# Query to execute in order to fetch the home directory



MYSQLGetDir    SELECT dir FROM users WHERE username="\L"





# Optional : query to get the maximal number of files 

# Pure-FTPd must have been compiled with virtual quotas support.



# MySQLGetQTAFS  SELECT QuotaFiles FROM users WHERE User="\L"





# Optional : query to get the maximal disk usage (virtual quotas)

# The number should be in Megabytes.

# Pure-FTPd must have been compiled with virtual quotas support.



MySQLGetQTASZ  SELECT quota FROM users WHERE username="\L"





# Optional : ratios. The server has to be compiled with ratio support.



# MySQLGetRatioUL SELECT ULRatio FROM users WHERE User="\L"

# MySQLGetRatioDL SELECT DLRatio FROM users WHERE User="\L"





# Optional : bandwidth throttling.

# The server has to be compiled with throttling support.

# Values are in KB/s .



# MySQLGetBandwidthUL SELECT ULBandwidth FROM users WHERE User="\L"

# MySQLGetBandwidthDL SELECT DLBandwidth FROM users WHERE User="\L"



# Enable ~ expansion. NEVER ENABLE THIS BLINDLY UNLESS :

# 1) You know what you are doing.

# 2) Real and virtual users match.



# MySQLForceTildeExpansion 1





# If you upgraded your tables to transactionnal tables (Gemini,

# BerkeleyDB, Innobase...), you can enable SQL transactions to

# avoid races. Leave this commented if you are using the

# traditionnal MyIsam databases or old (< 3.23.x) MySQL versions.



# MySQLTransactions On

This may seem like a stupid question but, did you create a "users" database with a "users" table?

By default, mysql has a "mysql" database, and you can add others.

Are you sure about using unix sockets as opposed to the tcp/ip loopback device?

I'll admit that I'm asking some of these questions more out of curiosity than help. I am going to need to figure out how to do this myself at some point soon...

(I'm just starting to learn sql, so don't rely on me too much, though I've been tinkering with Linux for a while)

Yeah, I set up the db via the mysql console.

After some more tinkering, it seems to work. Thanks for the help gd2shoe. ^_^

I've had this same problem with pureftpd and it took me hours until I found that I'm using MySQL 5 so:

"MySQL 4.1 and up uses an authentication protocol based on a password
hashing algorithm that is incompatible with that used by older clients.
If you upgrade the server to 4.1, attempts to connect to it with an older
client may fail with the following message:

shell> mysql

Client does not support authentication protocol requested

by server; consider upgrading MySQL client"

Solution: use the older algorithm to encrypt the password of the user.

mysql> SET PASSWORD FOR 'user'@'host' = OLD_PASSWORD('here_goes_password');