Re: [Proftpd-user] migrating from proftp on RH7 to proftp on RH8

Don’t know if this helps..

I ran proftpd in debug mode and this is what I captured.

: dispatching CMD command 'PASS (hidden)' to mod_auth
: retrieved UID 10002 for user '[email protected]'
: no supplemental groups found for user '[email protected]'
: USER [email protected] (Login failed): No such user found

Was curious about the no supplemental groups.

Tim

-----Original Message-----
From: Tim McIntire [mailto:[email protected]] 
Sent: Thursday, January 20, 2022 4:55 PM
To: [email protected]
Subject: Re: [Proftpd-user] migrating from proftp on RH7 to proftp on RH8

Hi John

Thanks for looking into this.

I ran the 

"SELECT userid, passwd, uid, gid, homedir, shell FROM usertable WHERE (userid='[email protected]') AND (((disabled=0 and (NOW()<=expiration or expiration=-1 or expiration=0)))) LIMIT 1"

From both the RH8 and RH7 proftp servers and it returned the same password string.

The db server is running on RH7 and is running mariadb 5.5.65.   
As you noted, the client on RH8 is 10.3.6,  the client version on rh7 is MySQL client version: 5.5.68-MariaDB

I tried to get an older version of the client on the RH8 node, but no luck.

Unfortunately I can't upgrade the db server right now.. its stuck on RH7.

Looking at the comment you had on the char set, when I look at both the working and non-working I see

mod_sql/4.3[30245]: MySQL connection character set now 'utf8mb4' (from 'UTF-8')

Something is hanging up on the encryption..  If I run proftp as plaintext and use the encoded text from the db, I can log in, but I really don’t want to use plaintext authentication. 

I have also tried multiple options for SQLPasswordEncoding ( hex/HEX/etc ) but that did not seem to help.

Tim







-----Original Message-----
From: John Stoffel [mailto:[email protected]]
Sent: Thursday, January 20, 2022 2:56 PM
To: [email protected]
Subject: Re: [Proftpd-user] migrating from proftp on RH7 to proftp on RH8

>>>>> "Tim" == Tim McIntire <[email protected]> writes:

Tim> Running into a problem when migrating to a new RH8 host.  I have 
Tim> working configuration running proftp 1.3.5e-9 running on RH7. The 
Tim> database is on a remote mariadb server running RH7.  The RH proftp 
Tim> version is proftpd-1.3.6e-4.


Can you do a plain mysql client connection from the RH8 system to RH7 mysql server and run your query by hand and make sure it matches the working RH7 proftpd system?  

The thing that sorta sticks out to me the utf8mb4 character connection set.  Maybe that needs to be ASCII or plain UTF8 or something else?

I also see how it's mysqlclient 10.x talking to the 5.5.x server, which is why I suspect there's some escaping going on that's not quite right.

What version of mysql is on the working RH7 server?  And maybe you can upgrade the mysql server first?  Then add in the RH8 proftpd server?

John

Tim> From the original proftp instance, I can authenticate remote users 
Tim> successfully. The same user/ password fails from the RH8 instances.
Tim> The DB connection is successful, but the remote ftp login is not:

Tim> Here is what I see in the sql log on the proftp server

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: defaulting to 'mysql' 
Tim> backend

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: backend module 'mod_sql_mysql/4.0.9'

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: backend api    'mod_sql_api_v1'

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: >>> sql_sess_init

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: entering   mysql cmd_defineconnection

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]:   name: 'default'

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]:   user: 'vsftpd'

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]:   host: 'HOST IP'

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]:     db: 'proftpd_admin'

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]:   port: '3306'

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]:    ttl: '0'

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: exiting      mysql cmd_defineconnection

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: connection 'default' 
Tim> successfully established

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: mod_sql engine     : on

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: negative_cache     : off

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: authenticate       : users

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: usertable          : usertable

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: userid field       : userid

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: password field     : passwd

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: UID field          : uid

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: GID field          : gid

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: homedir field      : homedir

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: homedir(default)   : '/var/ftp'

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: shell field        : shell

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: SQLMinUserUID      : 500

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: SQLMinUserGID      : 500

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: SQLDefaultUID      : 65533

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: SQLDefaultGID      : 500

Tim> 2022-01-19 14:53:59,862 mod_sql/4.3[86691]: <<< sql_sess_init

Tim> 2022-01-19 14:54:10,646 mod_sql/4.3[86691]: >>> sql_pre_pass

Tim> 2022-01-19 14:54:10,646 mod_sql/4.3[86691]: <<< sql_pre_pass

Tim> 2022-01-19 14:54:10,646 mod_sql/4.3[86691]: >>> cmd_getpwnam

Tim> 2022-01-19 14:54:10,646 mod_sql/4.3[86691]: entering   mysql cmd_escapestring

Tim> 2022-01-19 14:54:10,646 mod_sql/4.3[86691]: entering   mysql cmd_open

Tim> 2022-01-19 14:54:10,648 mod_sql/4.3[86691]: MySQL client version: 
Tim> 10.3.6

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: MySQL server version: 
Tim> 5.5.65-MariaDB

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: MySQL SSL connection: 
Tim> false

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: MySQL connection 
Tim> character set now 'utf8mb4' (from
Tim> 'UTF-8')

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: connection 'default' 
Tim> opened

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: connection 'default' 
Tim> count is now 2

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: exiting      mysql cmd_open

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: entering   mysql cmd_close

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: connection 'default' 
Tim> count is now 1

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: exiting      mysql cmd_close

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: exiting      mysql cmd_escapestring

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: cache miss for user ‘[email protected]’

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: entering   mysql cmd_select

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: entering   mysql cmd_open

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: connection 'default' 
Tim> count is now 2

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: exiting      mysql cmd_open

Tim> 2022-01-19 14:54:10,649 mod_sql/4.3[86691]: query "SELECT userid, 
Tim> passwd, uid, gid, homedir, shell FROM usertable WHERE
Tim> (userid=‘[email protected]’) AND (((disabled=0 and 
Tim> (NOW()<=expiration or
Tim> expiration=-1 or expiration=0)))) LIMIT 1"

Tim> 2022-01-19 14:54:10,650 mod_sql/4.3[86691]: entering   mysql cmd_close

Tim> 2022-01-19 14:54:10,650 mod_sql/4.3[86691]: connection 'default' 
Tim> count is now 1

Tim> 2022-01-19 14:54:10,650 mod_sql/4.3[86691]: exiting      mysql cmd_close

Tim> 2022-01-19 14:54:10,650 mod_sql/4.3[86691]: exiting      mysql cmd_select

Tim> 2022-01-19 14:54:10,650 mod_sql/4.3[86691]: cache miss for user ‘[email protected]’

Tim> 2022-01-19 14:54:10,650 mod_sql/4.3[86691]: user 
Tim> ‘[email protected]’ cached

Tim> 2022-01-19 14:54:10,650 mod_sql/4.3[86691]: + pwd.pw_name  : 
Tim> [email protected]

Tim> 2022-01-19 14:54:10,650 mod_sql/4.3[86691]: + pwd.pw_uid   : 10100

Tim> 2022-01-19 14:54:10,650 mod_sql/4.3[86691]: + pwd.pw_gid   : 10001

Tim> 2022-01-19 14:54:10,650 mod_sql/4.3[86691]: + pwd.pw_dir   : /opt/proftpd/userdir

Tim> 2022-01-19 14:54:10,650 mod_sql/4.3[86691]: + pwd.pw_shell : 
Tim> /bin/bash

Tim> 2022-01-19 14:54:10,650 mod_sql/4.3[86691]: <<< cmd_getpwnam

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: >>> cmd_auth

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: entering   mysql cmd_escapestring

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: entering   mysql cmd_open

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: connection 'default' 
Tim> count is now 2

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: exiting      mysql cmd_open

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: entering   mysql cmd_close

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: connection 'default' 
Tim> count is now 1

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: exiting      mysql cmd_close

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: exiting      mysql cmd_escapestring

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: cache hit for user ‘[email protected]’

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: >>> cmd_check

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: checking password using SQLAuthType 'Backend'

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: entering   mysql cmd_checkauth

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: password mismatch

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: exiting      mysql cmd_checkauth

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: 'Backend' SQLAuthType 
Tim> handler reports failure

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: <<< cmd_check

Tim> 2022-01-19 14:54:10,653 mod_sql/4.3[86691]: <<< cmd_auth

Tim> Any suggestions? 

Tim> Thanks… Tim

Tim> _______________________________________________
Tim> ProFTPD Users List   <[email protected]>
Tim> Unsubscribe problems?
Tim> http://www.proftpd.org/list-unsub.html


_______________________________________________
ProFTPD Users List   <[email protected]>
Unsubscribe problems?
http://www.proftpd.org/list-unsub.html

_______________________________________________
ProFTPD Users List   <[email protected]>
Unsubscribe problems?
http://www.proftpd.org/list-unsub.html

_______________________________________________
ProFTPD Users List   <[email protected]>
Unsubscribe problems?
http://www.proftpd.org/list-unsub.html