Ftp – Trouble with proftpd – postgres integration

ftppostgresqlproftpd

I can't post pictures yet, so I'll do the best I can to explain everything succinctly.

I have a ProFTPd installation that works. Now I'm trying to route the authentication through postgres. There is a database that contains a table of webapp users. This will ideally allow webapp users to login to the FTP using their webapp credentials. Here are the relevant settings for that in the proftpd.conf file:

LoadModule mod_sql.c

LoadModule mod_sql_passwd.c

LoadModule mod_sql_postgres.c

#Begin SQL configuration
#mod_sql_password parameters
SQLPasswordEngine           on
SQLPasswordEncoding         hex

#mod_sql base configuration
SQLEngine               on
SQLBackend              postgres
SQLConnectInfo          [database_name]@localhost:5432 [SELECT name] [password]
SQLAuthTypes            SHA1
SQLAuthenticate         users
SQLLogFile              /var/log/proftpd/sqlLog.txt

the "SELECT name" account is an account that's been granted SELECT privileges on the webapp users table. I can log that user in from the command line using 

psql -d [database_name] -U [SELECT name] -W

and then typing in the same password I have in the proftpd.conf file.

When I try to connect, it generates the following logfile data:

Feb 21 10:20:00 mod_sql/4.2.5[43238]: entering  postgres cmd_exit
Feb 21 10:20:00 mod_sql/4.2.5[43238]: exiting   postgres cmd_exit
Feb 21 10:29:45 mod_sql/4.2.5[43334]: defaulting to 'postgres' backend
Feb 21 10:29:45 mod_sql/4.2.5[43334]: backend module 'mod_sql_postgres/4.0.4'
Feb 21 10:29:45 mod_sql/4.2.5[43334]: backend api    'mod_sql_api_v1'
Feb 21 10:29:45 mod_sql/4.2.5[43334]: >>> sql_sess_init
Feb 21 10:29:45 mod_sql/4.2.5[43334]: entering  postgres cmd_defineconnection
Feb 21 10:29:45 mod_sql/4.2.5[43334]:  name: 'default'
Feb 21 10:29:45 mod_sql/4.2.5[43334]:  user: [SELECT account]
Feb 21 10:29:45 mod_sql/4.2.5[43334]:  host: 'localhost'
Feb 21 10:29:45 mod_sql/4.2.5[43334]:    db: [database_name]
Feb 21 10:29:45 mod_sql/4.2.5[43334]:  port: '5432'
Feb 21 10:29:45 mod_sql/4.2.5[43334]:   ttl: '0'
Feb 21 10:29:45 mod_sql/4.2.5[43334]: exiting   postgres cmd_defineconnection
Feb 21 10:29:45 mod_sql/4.2.5[43334]: mod_sql engine     : on
Feb 21 10:29:45 mod_sql/4.2.5[43334]: negative_cache     : off
Feb 21 10:29:45 mod_sql/4.2.5[43334]: authenticate       : users 
Feb 21 10:29:45 mod_sql/4.2.5[43334]: usertable          : users
Feb 21 10:29:45 mod_sql/4.2.5[43334]: userid field       : userid
Feb 21 10:29:45 mod_sql/4.2.5[43334]: password field     : passwd
Feb 21 10:29:45 mod_sql/4.2.5[43334]: UID field          : uid
Feb 21 10:29:45 mod_sql/4.2.5[43334]: GID field          : gid
Feb 21 10:29:45 mod_sql/4.2.5[43334]: homedir field      : homedir
Feb 21 10:29:45 mod_sql/4.2.5[43334]: homedir(default)   : [redacted]
Feb 21 10:29:45 mod_sql/4.2.5[43334]: shell field        : shell
Feb 21 10:29:45 mod_sql/4.2.5[43334]: SQLMinUserUID      : 999
Feb 21 10:29:45 mod_sql/4.2.5[43334]: SQLMinUserGID      : 999
Feb 21 10:29:45 mod_sql/4.2.5[43334]: <<< sql_sess_init
Feb 21 10:29:45 mod_sql/4.2.5[43334]: >>> sql_pre_pass
Feb 21 10:29:45 mod_sql/4.2.5[43334]: <<< sql_pre_pass
Feb 21 10:29:45 mod_sql/4.2.5[43334]: >>> cmd_getpwnam
Feb 21 10:29:45 mod_sql/4.2.5[43334]: entering  postgres cmd_escapestring
Feb 21 10:29:45 mod_sql/4.2.5[43334]: entering  postgres cmd_open
Feb 21 10:29:45 mod_sql/4.2.5[43334]: exiting   postgres cmd_open
Feb 21 10:29:45 mod_sql/4.2.5[43334]: exiting   postgres cmd_escapestring
Feb 21 10:29:45 mod_sql/4.2.5[43334]: unrecoverable backend error
Feb 21 10:29:45 mod_sql/4.2.5[43334]: error: 'mod_sql_postgres/4.0.4'
Feb 21 10:29:45 mod_sql/4.2.5[43334]: message: 'FATAL:  Ident authentication failed for user [SELECT account]
'
Feb 21 10:29:45 mod_sql/4.2.5[43334]: entering  postgres cmd_exit
Feb 21 10:29:45 mod_sql/4.2.5[43334]: exiting   postgres cmd_exit

So I'm totally lost at the moment because I'm not sure why the same credentials that work on the command line login don't work when used in the proftpd.conf. Can anyone give me suggestions on what to try next? Thanks!

Best Answer

OK, I found the answer to this question, in case anyone runs across it in the future.

So the error logs make it look like Postgres is rejecting my valid credentials. Not so! In the proftpd.conf file, the default is for proftpd to try to login as the user and group "nobody." "nobody" is an actual, valid user on the server, but the associated ID# is usually a very low one. In my case it was 99.

Now, when using mod_sql, there is a parameter available called SQLMinID. By default this is set to 999. And that's what was preventing me from logging in!

To remedy this, I simply set the "User" and "Group" parameters in proftpd.conf to a user with a higher id number who had sufficient priveleges to access postgres. As soon as I did that, it worked.

Related Topic