I'm trying to find a solution that will allow for a user to connect to a server via SFTP using a provided username/key combination (stored in a Wordpress installation's MySQL database), without needing to constantly keep two or more separate systems in sync.
I asked this question on Stackoverflow, but I feel that it would be better answered here.
This is the MySQL query that gets two columns that have the username of a user and the api key, which the user would type in to their SFTP client as their password:
SELECT m.meta_value, u.user_login FROM wp_users AS u, wp_usermeta AS m WHERE m.user_id = u.ID AND m.meta_key = "user_apikey";
As far as the file system goes, there is a folder which has the same name as a user's username for each user, all in the same directory. This user's folder may or may not already exist. When a user makes an SFTP connection to the server, they should be shown only the contents of that folder once authenticated.
Hopefully someone knows of a simple solution to this problem. The only limitation I have is that this must run on a CentOS box, but I'm pretty open to any tools and languages.