Creating FTP user from existing database

jgentes · 08-29-2008, 08:47 AM

I have scrolled through a couple of the forums and found topics:
http://www.linuxquestions.org/questi...d.php?t=286805
http://www.linuxquestions.org/questi...d.php?t=162362

however they do not quite fulfill my necessity. I am looking for a script that will connect and query our existing mysql database run within an Ubuntu distribution than take the results and for each user, create an ftp account with a specific quota.

I am not necessarily looking for someone to write the script; If I could be pointed in the right direction I do not have a problem with developing the script myself. I would like to note that I would prefer if this was developed as a bash script.

jschiwal · 08-29-2008, 10:04 PM

Using an sql command you can print out the users. Then you can use sed to remove the formatting, leaving just the names. Then create your new users from the names in the list.

Here I am taking an output from an artists table in my amarok database:

Code:

mysql> SELECT name FROM artist LIMIT 15;
+-------------------------------------------+
| name                                      |
+-------------------------------------------+
|                                           |
| Aaron Newcomb                             |
| Adam Court & Hannah Lewis                 |
| Adam Court // Paul Saunders // Hana Lewis |
| Adrian Bacon                              |
| Allan Shimle and Michel Ashley            |
| Allan, Dann, Linc, Pat                    |
| AlternaGeek                               |
| Amber MacArthur and Leo Laporte           |
| Andrew McCaskey                           |
| Ask Udi Podcast                           |
| Binary Revolution Radio                   |
| Brandon Jasper                            |
| Bruce Letke                               |
| Bryan and Chris - Jupiter Broadcasting    |
+-------------------------------------------+
15 rows in set (0.00 sec)

Code:

> sed -n '/| name *|/d;/| [[:alnum:],-\_]*  *.*|/!d;s/^| //;s/ *|$//;/^$/d;p' sqlout
Aaron Newcomb
Adam Court & Hannah Lewis
Adam Court // Paul Saunders // Hana Lewis
Adrian Bacon
Allan Shimle and Michel Ashley
Allan, Dann, Linc, Pat
AlternaGeek
Amber MacArthur and Leo Laporte
Andrew McCaskey
Ask Udi Podcast
Binary Revolution Radio
Brandon Jasper
Bruce Letke
Bryan and Chris - Jupiter Broadcasting

The sed command strips out the extra junk leaving just the "artist" information. If you display the sql result vertically, (e.g. ending command with \G) then it may be easier to strip off the extra lines. My sql output is just a sample but the sed script may be close to what you might use. Pay particular attention to creating a regular expression that only selects legal characters in a username.

You didn't indicate which ftp server you are running. I'm not certain if you meant that you will add a regular user based on the sql output. If you explore the packages available, you may find one for creating users from list. A search on Google would return a number of scripts that do the same thing.

Rather than using a one-liner like I did, you may want to create a sed script and then call sed like:
sed -f getusers.sed sqlresult >usersToAdd

While it is possible to pipe the output of one command to the input of the other, in a script, creating temporary files instead may help with debugging.

You will want to make sure that a user in the list isn't a system user or a current user.
It should be easy to extract users from /etc/passwd to create a list of current users. For example:

Code:

grep $newuser <(awk -F: '{print $0}' /etc/passwd ftpusers ) 1>/dev/null 2>&1

The result will be true if the user name is in either /etc/passwd or the ftpusers file.

Good Luck!

jgentes · 09-02-2008, 02:29 PM

I was talking about doing it automatically.
So create a batch script then run the batch script and everything is done. If I am understanding you correctly, you first would like me to query the database, then save the results, then run that sed command, then run the insert?

I am looking for something that is automated. If that isn't possible from a batch file that is fine, but I would like to do it from a batch file.

Also important to note is that each folder needs to be password protected, and I would like for the FTP password to be the same as their existing password to login to the HTTP section of the site