I was able to query my Postgresql Db for user authentication at one time. I deleted and then rebuilt the postgresql DB and rebuilt the entire Galaxy instance in early December. Since then I cannot get ProFFTPd-1.3.4d to authenticate users from Postgresql database. At that time my existing universe_wsgi.ini
I changed the following from postgres to postgresql
#database_connection = postgres://galaxy:Galaxy2013@localhost:5434/galaxy_prod database_connection = postgresql://galaxy:galaxy2013@localhost:5434/galaxy_prod
What ftp interaction behavior changes were included in this database_connectio method renaming? (And how can I fix them?)
My Galaxy users can login to Galaxy just fine and execute programs if I manually load files into the designated FTP dir and then move these to database/files .
Screen session from interactive test mode FTP session opened. dispatching PRE_CMD command 'USER [valid email]' to mod_core . . . dispatching CMD command 'PASS (hidden)' to mod_auth no supplemental groups found for user '[valid email]' ROOT PRIVS at mod_auth_pam.c:338 RELINQUISH PRIVS at mod_auth_pam.c:508 mod_sql_passwd/0.4: expected 'PBKDF2$sha256$10000$1xxxxxxxxxx',got Œyyyyyy¹
I am not using either PBKDF2 or sha256 see below why are these prefixes appended? Xxx and yyyy are NOT the same alphanumeric strings
SQLLogFile.txt excerpt:
Jan 06 17:27:49 mod_sql/4.3[29179]: query "SELECT email,password,' mailto:'irish@musc.edu[valid email]','{UNKNOWN TAG}',¹/PATH/galaxy-dist/database/files/ mailto:'/shared/app/Galaxy/galaxy-dist/database/files/irish@musc.edu[vali d email]','/bin/ bash' FROM galaxy_user WHERE email=' mailto:email='irish@musc.edu[valid email]'" Jan 06 17:27:49 mod_sql/4.3[29179]: entering postgres cmd_close Jan 06 17:27:49 mod_sql/4.3[29179]: connection 'default' count is now 1 Jan 06 17:27:49 mod_sql/4.3[29179]: exiting postgres cmd_close Jan 06 17:27:49 mod_sql/4.3[29179]: exiting postgres cmd_select Jan 06 17:27:49 mod_sql/4.3[29179]: <<< process_named_query 'LookupGalaxyUser' Jan 06 17:27:49 mod_sql/4.3[29179]: <<< sql_lookup Jan 06 17:27:49 mod_sql/4.3[29179]: user UID 0 below SQLMinUserUID 999, using SQLDefaultUID 65533 Jan 06 17:27:49 mod_sql/4.3[29179]: user GID 0 below SQLMinUserGID 999, using SQLDefaultGID 65533 Jan 06 17:27:49 mod_sql/4.3[29179]: cache miss for user ' mailto:'irish@musc.edu[valid email]' Jan 06 17:27:49 mod_sql/4.3[29179]: user ' mailto:'irish@musc.edu[valid email]' cached Jan 06 17:27:49 mod_sql/4.3[29179]: + pwd.pw_name : [valid email] Jan 06 17:27:49 mod_sql/4.3[29179]: + pwd.pw_uid : 65533 Jan 06 17:27:49 mod_sql/4.3[29179]: + pwd.pw_gid : 65533 Jan 06 17:27:49 mod_sql/4.3[29179]: + pwd.pw_dir : /PATH/galaxy-dist/database/files/ mailto:/shared/app/Galaxy/galaxy-dist/database/files/irish@musc.edu[valid email] Jan 06 17:27:49 mod_sql/4.3[29179]: + pwd.pw_shell : /bin/bash Jan 06 17:27:49 mod_sql/4.3[29179]: <<< cmd_getpwnam Jan 06 17:27:49 mod_sql/4.3[29179]: >>> cmd_auth Jan 06 17:27:49 mod_sql/4.3[29179]: entering postgres cmd_escapestring Jan 06 17:27:49 mod_sql/4.3[29179]: entering postgres cmd_open Jan 06 17:27:49 mod_sql/4.3[29179]: connection 'default' count is now 2 Jan 06 17:27:49 mod_sql/4.3[29179]: exiting postgres cmd_open Jan 06 17:27:49 mod_sql/4.3[29179]: entering postgres cmd_close Jan 06 17:27:49 mod_sql/4.3[29179]: connection 'default' count is now 1 Jan 06 17:27:49 mod_sql/4.3[29179]: exiting postgres cmd_close Jan 06 17:27:49 mod_sql/4.3[29179]: exiting postgres cmd_escapestring Jan 06 17:27:49 mod_sql/4.3[29179]: cache hit for user '[valid email]' Jan 06 17:27:49 mod_sql/4.3[29179]: >>> cmd_check Jan 06 17:27:49 mod_sql/4.3[29179]: checking password using SQLAuthType 'sha1' Jan 06 17:27:49 mod_sql/4.3[29179]: 'sha1' SQLAuthType handler reports failure
ProFTPd conf file excerpt:
(based on https://wiki.galaxyproject.org/Admin/Config/Upload%20via%20FTP)
# Set up mod_sql_password - Galaxy passwords are stored as hex-encoded SHA1 SQLPasswordEngine on SQLPasswordEncoding hex
# Set up SQLLogfile SQLLogFile /PATH/ProFFTPd-1.3.4d/etc/SQLLogFile.txt
# Set up mod_sql to authenticate against the Galaxy database SQLEngine on SQLBackend postgres SQLConnectInfo galaxy_prod@:5xxx galaxy galpwd # port and password are changed here; they are correct in the working files. SQLAuthTypes SHA1 SQLAuthenticate users
# An empty directory in case chroot fails SQLDefaultHomedir /shared/app/ProFFTPd-1.3.4d/default
# Define a custom query for lookup that returns a passwd-like entry. UID and GID should match your Galaxy user. SQLUserInfo custom:/LookupGalaxyUser SQLNamedQuery LookupGalaxyUser SELECT "email,password,¹1233¹,'1234',¹/PATH/galaxy-dist/database/files/%U ','/bin/bash' FROM galaxy_user WHERE email='%U'"