RHEL5.1\CENT.OS 5.1 VSFTPD auth via mysql

[Nikitos]

Hello

I want to setup vsftpd server like Virtual Hosting With vsftpd And MySQL On Debian Etch but on RHEL5.1(Cent.os)
I`ve install pam_mysql from EPEL, create database, use debian`s config for vsftpd.conf.... add to pam/vsftpd like in debian one ... but then I tried to login I have a message that login is incorrect

Can you help me?

[falko]

What's in /etc/vsftpd.conf and the vsftpd pam file?
What's the exact error message? Any errors in your logs?

[Nikitos]

/etc/vsftpd/vsftpd.conf

listen=YES
anonymous_enable=NO
local_enable=YES
write_enable=YES
local_umask=022
dirmessage_enable=YES
xferlog_enable=YES
connect_from_port_20=YES
nopriv_user=vsftpd
chroot_local_user=YES
secure_chroot_dir=/var/run/vsftpd
pam_service_name=vsftpd
#rsa_cert_file=/etc/ssl/certs/vsftpd.pem
guest_enable=YES
guest_username=vsftpd
local_root=/home/vsftpd/$USER
user_sub_token=$USER
virtual_use_local_privs=YES
user_config_dir=/etc/vsftpd/vsftpd_user_conf

/etc/pam.d/vsftpd

auth required pam_mysql.so user=vsftpd passwd=password host=localhost db=vsftpd table=accounts usercolumn=username passwdcolumn=pass crypt=2

account required pam_mysql.so user=vsftpd passwd=password host=localhost db=vsftpd table=accounts usercolumn=username passwdcolumn=pass crypt=2

/var/log/vsftpd.log

Tue Apr 15 17:42:26 2008 [pid 3260] [test] FAIL LOGIN: Client "192.168.1.2"
Tue Apr 15 17:56:53 2008 [pid 2354] CONNECT: Client "192.168.1.2"
Tue Apr 15 17:56:53 2008 [pid 2353] [test] FAIL LOGIN: Client "192.168.1.2"
Thu Apr 17 06:59:55 2008 [pid 2322] CONNECT: Client "192.168.1.2"

[topdog]

Turn on debug and see what is being logged in syslog

[Nikitos]

i add log all querys to my.cnf and i saw in log that

080417 13:39:00 4 Connect vsftpd@localhost on vsftpd
4 Init DB vsftpd
4 Query SELECT pass FROM accounts WHERE username = 'test'
4 Query SELECT pass FROM accounts WHERE username = 'test'
4 Quit
080417 13:39:06 5 Connect vsftpd@localhost on vsftpd
5 Init DB vsftpd
5 Query SELECT pass FROM accounts WHERE username = 'nikita'
5 Query SELECT pass FROM accounts WHERE username = 'nikita'
5 Quit

/var/logs/secure

Apr 17 13:36:05 localhost vsftpd: pam_mysql - SELECT returned no result.
Apr 17 13:39:00 localhost last message repeated 4 times
Apr 17 13:40:38 localhost last message repeated 2 times

[topdog]

the query is not returning any data connect to the db as that user and run the same query.

[Nikitos]

Query as vsftpd user works normal

[root@linux log]# mysql -u vsftpd -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use vsftpd;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT pass FROM accounts WHERE username = 'test' ;
+------------------+
| pass |
+------------------+
| 428567f408994404 |
+------------------+
1 row in set (0.01 sec)

When i do your this query Log in /var/logs/secure don`t have any errors

[topdog]

Turn on pam_mysql's own debug such that you see what happens to the whole process.

logging queries from mysql will not give you the full picture.

[Nikitos]

Enable Pam_mysql log:

auth required pam_mysql.so user=vsftpd passwd=password host=localhost db=vsftpd table=accounts usercolumn=username passwdcolumn=pass crypt=2 sqllog=1 logtable=logs logmsgcolumn=msg logusercolumn=user logpidcolumn=pid loghostcolumn=host logrhostcolumn=rhost logtimecolumn=logtime

account required pam_mysql.so user=vsftpd passwd=password host=localhost db=vsftpd table=accounts usercolumn=username passwdcolumn=pass crypt=2 sqllog=1 logtable=logs logmsgcolumn=msg logusercolumn=user logpidcolumn=pid loghostcolumn=host logrhostcolumn=rhost logtimecolumn=logtime

Pam_mysql log

+------------------------------------+------+------+-----------+-------------+---------------------+
| msg | user | pid | host | rhost | logtime |
+------------------------------------+------+------+-----------+-------------+---------------------+
| AUTHENTICATION FALURE (FIRST_PASS) | test | 2757 | 127.0.0.1 | 192.168.1.2 | 2008-04-19 23:07:24 |
| AUTHENTICATION FAILURE | test | 2757 | 127.0.0.1 | 192.168.1.2 | 2008-04-19 23:07:24 |
+------------------------------------+------+------+-----------+-------------+---------------------+
2 rows in set (0.00 sec)

Mysql query log:

080419 23:07:24 4 Connect vsftpd@localhost on vsftpd
4 Init DB vsftpd
4 Query SELECT pass FROM accounts WHERE username = 'test'
4 Query INSERT INTO logs (msg, user, host, rhost, pid, logtime) VALUES ('AUTHENTICATION FALURE (FIRST_PASS)', 'test', '127.0.0.1', '192.168.1.2', '2757', NOW())

4 Query SELECT pass FROM accounts WHERE username = 'test'
4 Query INSERT INTO logs (msg, user, host, rhost, pid, logtime) VALUES ('AUTHENTICATION FAILURE', 'test', '127.0.0.1', '192.168.1.2', '2757', NOW())

4 Quit

/var/log/secure doesn`t have any errors