TYPO3 has a table for backend users. Those users need to occasionally upload files to the fileadmin directory via FTP. Wouldn’t it be nice to configure the FTP server to pull from the be_users table and authenticate them, so that two separate locations for userdata don’t have to be maintained?

It’s possible. This guide documents how I was able to set up FTP on a Debian Linux server running TYPO3 4.2. Our TYPO3 installation uses MySQL 5.0 DB.

My FTP server of choice was pure-ftpd. Fortunately this server has MySQL authentication capabilities, they just had to be set up.

apt-get install pure-ftpd-common pure-ftpd-mysql

This will remove plain old pure-ftpd package if you have it, and install the MySQL enabled version, that can use a MySQL DB to authenticate users against.

Next step is to set up the configuration, so edit /etc/pure-ftpd/db/mysq.conf. You need to set some configuration variables:

• MYSQLServer: Point it to your MySQL server IP, or localhost
• MYSQLUser: DB username
• MYSQLPassword: DB password
• MYSQLCrypt: TYPO3 encodes be_users passwords with md5
• MYSQLGetPW: This query authenticates the user. Here is how I set it up:
  SELECT password FROM be_users WHERE username="\L" AND LEFT(username, 1) != '_' AND deleted=0
  In the first where clause, \L is replaced by the username of a user who is trying to login. The second clause is for security reasons – backend processes running as scheduled tasks require users with a prefix “_” (usually “_cli”). This clause prevents them from being awarded FTP access automatically. Third clause, of course, prevents deleted users from logging in.
• MYSQLDefaultUID: since each TYPO3 user might not necessarily have a user account on the server, its better to set the default UID of the file owner here.
• MYSQLDefaultGID: same as above, set the user group.
• MYSQLGetDir: This is a fun one. As you may know, TYPO3 has file mounts for users and groups, which restrict the user to a certain folder. We would want to restrict the user to the same folder, right?
  The disadvantage of the code below is that 1) it requires relative filemounts 2) it ignores multiple filemounts and 3) it only allows one usergroup per user. If a user has several filemounts selected this query will fail, and the user will be denied FTP access. This is a problem I was not able to resolve yet.

SELECT CONCAT('/srv/ftp',file.path) AS Dir
FROM be_users as user
JOIN be_groups as ugroup ON user.usergroup=ugroup.uid
JOIN sys_filemounts as file ON user.file_mountpoints=file.uid OR ugroup.file_mountpoints=file.uid
WHERE user.username="\L" LIMIT 1

The manual documents other options quite well.

Next I tried to connect to FTP, but got an error: ‘Can’t exec /usr/sbin/pure-ftpd’. I had to modify /usr/sbin/pure-ftpd-wrapper to call pure-ftpd-mysql instead of pure-ftpd. This may be different depending on which package you install.

You would also want to secure the FTP by limiting the users to their respective directory. This can be done by creating a file /etc/pure-ftpd/conf/ChrootEveryone with content ‘yes’. Now when they login, they will have access only to their file-mounted folder.

So at this point everything should work. Nothing ever works as planned though, but pure-ftpd writes log messages to syslog, so go there to pinpoint the problem in your installation.