FTP server authentication issues back end SQL

[root@ ~]# proftpd -vv
ProFTPD Version: 1.3.5e (maint)
Scoreboard Version: 01040003
Built: Mon Nov 4 2019 08:49:41 UTC

Loaded modules:
  mod_sql_passwd/0.7
  mod_sql_postgres/4.0.4
  mod_sql/4.3
  mod_lang/1.0
  mod_ctrls/0.9.5
  mod_cap/1.1
  mod_memcache/0.1
  mod_tls/2.6
  mod_auth_pam/1.2
  mod_readme/1.0
  mod_ident/1.0
  mod_dso/0.5
  mod_facts/0.4
  mod_delay/0.7
  mod_site.c
  mod_log.c
  mod_ls.c
  mod_auth.c
  mod_auth_file/1.0
  mod_auth_unix.c
  mod_rlimit/1.0
  mod_xfer.c
  mod_core.c

[root@<hostname> ~]# cat /etc/proftpd.conf
# Basics, some site-specific
ServerName                      "IIHG Galaxy FTP"
ServerType                      standalone
DefaultServer                   on
Port                            21
Umask                           077
SyslogFacility                  DAEMON
SyslogLevel                     debug
MaxInstances                    30

# This User & Group should be set to the actual user and group name which matche the UID & GID you will specify later in the SQLNamedQuery.
User                            svc-resgalprod
Group                           svc-resgalprod
DisplayConnect                  /etc/opt/local/proftpd_welcome.txt

# Passive port range for the firewall
PassivePorts                    30000 40000

# Cause every FTP user to be "jailed" (chrooted) into their home directory
DefaultRoot                     ~

# Automatically create home directory if it doesn't exist
CreateHome                      on dirmode 700

# Allow users to overwrite their files
AllowOverwrite                  on

# Allow users to resume interrupted uploads
AllowStoreRestart               on

# Bar use of SITE CHMOD
<Limit SITE_CHMOD>
    DenyAll
</Limit>

# Bar use of RETR (download) since this is not a public file drop
<Limit RETR>
    DenyAll
</Limit>

# Do not authenticate against real (system) users
<IfModule mod_auth_pam.c>
AuthPAM                         off
</IfModule>

LoadModule mod_sql.c
LoadModule mod_sql_postgres.c
LoadModule mod_sql_passwd.c

# set Authentication order
AuthOrder                       mod_sql.c




# Common SQL authentication options
SQLEngine                       on
SQLPasswordEngine               on
SQLBackend                      postgres
SQLConnectInfo                  <dbname redacted>@<dbserver redacted>:5432 resgalaxyprod <pw redacted>
SQLAuthenticate                 users

# Configuration that handles PBKDF2 encryption
# Set up mod_sql to authenticate against the Galaxy database
SQLAuthTypes                    PBKDF2
SQLPasswordPBKDF2               SHA256 10000 24
SQLPasswordEncoding             base64

# For PBKDF2 authentication
# See http://dev.list.galaxyproject.org/ProFTPD-integration-with-Galaxy-td4660295.html
SQLPasswordUserSalt             sql:/GetUserSalt

# Define a custom query for lookup that returns a passwd-like entry. Replace 512s with the UID and GID of the user running the Galaxy server
SQLUserInfo                     custom:/LookupGalaxyUser
SQLNamedQuery                   LookupGalaxyUser SELECT "email, (CASE WHEN substring(password from 1 for 6) = 'PBKDF2' THEN substring(password from 38 for 69) ELSE password END) AS password2,<UID>,<GID>,'/Dedicated/resgalproddata/database/ftp/%U','/bin/bash' FROM galaxy_user WHERE email='%U'"

# Define custom query to fetch the password salt
SQLNamedQuery                   GetUserSalt SELECT "(CASE WHEN SUBSTRING (password from 1 for 6) = 'PBKDF2' THEN SUBSTRING (password from 21 for 16) END) AS salt FROM galaxy_user WHERE email='%U'"   

ProFTP Install and access to the users_table in the Galaxy postgress database works. The issues I am having is that the password being sent by FTP is not matching the salted PW in the database. Here is the table entry:

2019-01-16 19:19:59.167781 | 2019-11-22 21:54:09.197171 | | PBKDF2$sha256$10000$T0BHZtlXyMkE6n7p$MQOu8yJyhwhBB/7MpxJxF1G+2dEvBtDz | f | f | f | | | 596 | | t | 2019-01-16 13:19:59.165682

FTP log output is:

2019-11-26 08:46:04,958 proftpd[23116] (2620:0:e50:3014:419e:d419:eb1e:f278[2620:0:e50:3014:419e:d419:eb1e:f278]): dispatching CMD command ‘PASS (hidden)’ to mod_auth
2019-11-26 08:46:04,958 proftpd[23116] (2620:0:e50:3014:419e:d419:eb1e:f278[2620:0:e50:3014:419e:d419:eb1e:f278]): retrieved UID 2317600 for user ‘’
2019-11-26 08:46:04,958 proftpd[23116] r (2620:0:e50:3014:419e:d419:eb1e:f278[2620:0:e50:3014:419e:d419:eb1e:f278]): no supplemental groups found for user ‘’
2019-11-26 08:46:04,967 proftpd[23116] (2620:0:e50:3014:419e:d419:eb1e:f278[2620:0:e50:3014:419e:d419:eb1e:f278]): mod_sql_passwd/0.7: expected ‘MQOu8yJyhwhBB/7MpxJxF1G+2dEvBtDz’, got ‘97iWXO4B/wrtIn9jFAJKbQNXb4QgPWi2’

From items in bold I believe the query is working correctly in grabbing the encrypted password. I believe I have something configured incorrectly in the proftpd.conf file that is generating the hashed PW using the GetSalt incorrectly. I followed the FTP setup from the galaxy docs as well as referenced GalacticEngineer’s blog post.

I did not compile from source (CentOS 7 yum package install, centos is a nightmare to get compiled sources working).

Galaxy Version is 18.09.

Just to clarify, is this really the Galaxy version, or did you mean 19.09 (the most current release)?

No it’s 18.09.

This issue is resolved. Configured Authentication for LDAP/AD. For some reason the password in the table is not the AD password, but the original PW that was entered during account creation. Guessing that DB Table is not synched with LDAP.