Solved

dump two mysql fields to create .htpasswd file

Posted on 2014-11-17
13
252 Views
Last Modified: 2014-11-19
I need to dump two fields ('username' and 'password') from a mysql table into a file which a bash script could then turn into an .htpasswd file. In other words, I need a constantly updated .htpasswd file based on the data in the table.

Or, a script which could read the database directly while creating the .htpasswd file.

Using the -b option, I can create the .htpasswd file like this;
htpasswd -b .htpasswd $USER $PASSCODE

If the script read the table directly though, would it not mean many reads instead of just one to grab all of the names/passwords? This needs to be done several times daily so I want to do this with as little impact as possible on mysql.

Looking for the Linux Bash code which would accomplish this.
0
Comment
Question by:projects
  • 7
  • 6
13 Comments
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40450269
If this will eventually be used for authentication within Apache, take a look at mod_auth_mysql.  It will allow Apache to authenticate directly against the table without the .htpasswd intermediary.

Otherwise:
mysql> create database test;
Query OK, 1 row affected (0.05 sec)

mysql> use test;
Database changed
mysql> create table users (id int auto_increment not null primary key, username varchar(30), password varchar(30));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into users (username,password) values ('user1','pass1'),('user2','pass2'),('user3','pass3'),('user4','pass4'),('user5','pass5'),('user6','pass6'),('user7','pass7'),('user8','pass8'),('user9','pass9'),('user10','pass10'),('user11','pass11'),('user12','pass12');
Query OK, 12 rows affected (0.06 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select * from users;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | user1    | pass1    |
|  2 | user2    | pass2    |
|  3 | user3    | pass3    |
|  4 | user4    | pass4    |
|  5 | user5    | pass5    |
|  6 | user6    | pass6    |
|  7 | user7    | pass7    |
|  8 | user8    | pass8    |
|  9 | user9    | pass9    |
| 10 | user10   | pass10   |
| 11 | user11   | pass11   |
| 12 | user12   | pass12   |
+----+----------+----------+
12 rows in set (0.00 sec)

mysql> exit
Bye

$> mysql -e 'select username,password from test.users' | tail -n +2 | while read i; do j=($i); echo found ${j[0]} = ${j[1]}; done;
found user1 = pass1
found user2 = pass2
found user3 = pass3
found user4 = pass4
found user5 = pass5
found user6 = pass6
found user7 = pass7
found user8 = pass8
found user9 = pass9
found user10 = pass10
found user11 = pass11
found user12 = pass12

Open in new window

0
 

Author Comment

by:projects
ID: 40450924
Yes, I had auth mysql set up but that will cost too many reads because of the way remotes are connecting non stop. Instead, I want to pull the username/passwords from the database, then convert those into the .htpasswd file until I find a better solution in the future.

Once the new .htpasswd file is created, then the script would overwrite the old one with the new one causing as little interruption as possible.

I already have the table created called 'htaccess' and the fields are 'username' and 'password'. The passwords are non encrypted at this time.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40451198
Have you considered creating an in-memory table to be used for this purpose?
0
 

Author Comment

by:projects
ID: 40451203
Do you mean using auth mysql and having the remotes authenticating to htpasswd over an in memory table?

There are hundreds of them and they connect every second, 24/7 so I am trying to find the least impacting method.

Every single connection is https and uses htpasswd. What I need to do is change this so that the base connections are simply http and the rest are using https and htpasswd.

I'll have to post another question for that in the meantime.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40451226
If you're using an in-memory table, then you would stick with mod_auth_mysql, and just point it to the table.  You can find more detail in the MySQL MEMORY Storage Engine documentation.

The basic flow is you create an in-memory table, using your standard, disk-storage table as the source of data.  Pointing mod_auth_mysql at the memory table should provide much better performance.  The only necessary scripting is an UPDATE/DELETE/INSERT trigger on the stored table designed to refresh the memory table after the data has been changed.  

If you'd still like to use .htpasswd, the sample script I posted earlier shows the basics of how to poll MySQL for the data and make it available.  The only thing left to add would be calls to htpasswd to reset/truncate the file and add the records back.
0
 

Author Comment

by:projects
ID: 40451351
For now, I want to use the simplest method so using your command line, I am able to extract the username and password.

I need a script which will convert this output into the htpasswd file now.

#!/bin/bash

mysql -u user -ppass -D somedb -e 'select username,password from htaccess' | tail -n +2 | while read i; do j=($i); echo found ${j[0]} = ${j[1]}; done;

#need to turn each result into $USER and $PASSCODE so I can use htpasswd -b .htpasswd $USER $PASSCODE
#and create the new .htpasswd file

The least amount of reads to the db would be good because it is getting lots of hits as it is.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 50

Expert Comment

by:Steve Bink
ID: 40451408
You can find more information on different options on the Apache htpasswd doc page.

The items you need to add are:
Clear the htpasswd file.  This is accomplished with -c option.  Insert the command somewhere before the loop.
Add each entry to the file.  This is accomplished by replacing the "meat" of the while loop with the commands you want to run.  In this case, something like "htpasswd /path/to/file $j[0] $j[1]".

Just a couple other considerations for you:
Including your MySQL password on the command line is considered an insecure strategy.  You should consider setting up login credentials.  You can find out how in the MySQL documentation.
Remember to put the .htpasswd file outside of your document root.  It should not be accessible from the internet.
Once you have the core functionality working, explore making the script safe.  For example, a sanity check to make sure MySQL actually returned information before clearing the file, and checking the return of htpasswd.
0
 

Author Comment

by:projects
ID: 40451505
I'm not a programmer, that's why I asked for the bash script which would do this :)

Thanks however.
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 40452823
Take a look at this:
$> mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54548
Server version: 5.6.21-70.0-log MySQL Community Server (GPL)

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mytest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------------+
| Tables_in_mytest |
+-------------------------+
| users                   |
+-------------------------+
1 row in set (0.00 sec)

mysql> show create table users;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) DEFAULT NULL,
  `password` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from users;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | user1    | pass1    |
|  2 | user2    | pass2    |
|  3 | user3    | pass3    |
|  4 | user4    | pass4    |
|  5 | user5    | pass5    |
|  6 | user6    | pass6    |
|  7 | user7    | pass7    |
|  8 | user8    | pass8    |
|  9 | user9    | pass9    |
| 10 | user10   | pass10   |
| 11 | user11   | pass11   |
| 12 | user12   | pass12   |
+----+----------+----------+
12 rows in set (0.00 sec)

mysql> exit
Bye

$> ls
total 12
drwxr-xr-x 2 someuser users 4096 Nov 19 11:42 .
drwx--x--x 4 someuser users 4096 Nov 19 11:43 ..
-rwxr--r-- 1 someuser users  350 Nov 19 11:41 pw.sh

$> cat pw.sh
#!/bin/bash

# when creating the file, you have to add at least one user ...
htpasswd -bc somefile deletethisuser nopass

# ... so we delete it immediately after
htpasswd -D somefile deletethisuser

# read the db 
mysql -D mytest -e 'select username,password from users' |
  # remove the first line (column headers) from the output)
  tail -n +2 |
  # now loop through the results  
  while read i
    do
      # parse the current line as space-delimited tokens
      j=($i);
      # echo the current line's information
      echo found ${j[0]} = ${j[1]};
      # add the current line to the password file
      htpasswd -b somefile ${j[0]} ${j[1]}
    done;


$> ./pw.sh
Adding password for user nouser
Deleting password for user nouser
found user1 = pass1
Adding password for user user1
found user2 = pass2
Adding password for user user2
found user3 = pass3
Adding password for user user3
found user4 = pass4
Adding password for user user4
found user5 = pass5
Adding password for user user5
found user6 = pass6
Adding password for user user6
found user7 = pass7
Adding password for user user7
found user8 = pass8
Adding password for user user8
found user9 = pass9
Adding password for user user9
found user10 = pass10
Adding password for user user10
found user11 = pass11
Adding password for user user11
found user12 = pass12
Adding password for user user12

$> ls
total 16
drwxr-xr-x 2 someuser users 4096 Nov 19 11:43 .
drwx--x--x 4 someuser users 4096 Nov 19 11:43 ..
-rwxr--r-- 1 someuser users  350 Nov 19 11:41 pw.sh
-rw-r--r-- 1 someuser users  531 Nov 19 11:43 somefile

$> cat somefile
user1:$apr1$tJH6ETQS$3H9DKnRZp649Wqmi18EkK/
user2:$apr1$GmnW.Dwo$v1XUZK2lRMw5hFWdm8lFS.
user3:$apr1$EjZgOECA$jpmRLR3nVfAsT4OuEXH1r/
user4:$apr1$2LCspD3A$8A4kImR/KN.h11wZBjv5o1
user5:$apr1$HdXoHtTX$k2Jni2.1CaCzSJyu/LX250
user6:$apr1$fMuItvfj$G17e97gQFGMjQr52wv4hS1
user7:$apr1$Nfiv4b57$fvFBQ7sjmZ7bq1WBZcx0k.
user8:$apr1$9tHuXCz5$9NfTalMd7bHToiZhZKDqa0
user9:$apr1$y4gLXg2S$5gx9WpoqXtAdbHZRSvGKK/
user10:$apr1$9SFjXr.k$.3/qz3Eyzqajhbtl9rkLk1
user11:$apr1$NESxemva$0jX0EX0EyjsL9GOVFE19r.
user12:$apr1$8g3G3kyo$NI0AiOyQfgbKGa.LAdWnU.

Open in new window

0
 

Author Comment

by:projects
ID: 40453546
That works of course. I have created a new user with read only permissions.

So another question would be, is there any way of reading a number of rows into memory to lower the number of reads? As things are now, this script would read the db for each and every user right? Since I would be running this multiple times per day, it would be best to limit the number of reads.

For that matter, a quick check to see if anything has changed before bothering to re-create the same list over and over again.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40453674
That script was designed with the idea of running it as a cron job every 'x' number of minutes.  It would completely recreate the .htpasswd file on each run, but you would have only a single database read each time.

Apache's interaction with the .htpasswd file is not very open to modification.  It is handled through mod_auth_* (e.g., mod_auth_basic), and Apache will pass every request off to that module, when required.  If you have a large number of users, you may find that using htpasswd may exhibit worse performance than the database.  In that case you can also opt to switch to a different auth provider, like dbm.  

What kind of traffic/hit count/number of users are you expecting or experiencing on your site?  I've use mod_auth_mysql in the past and was not troubled by its performance...
0
 

Author Comment

by:projects
ID: 40453886
The more I think about it, the more I think it would not be a big problem because I could use different servers for different things anyhow, basically balancing the load if you will.

I don't know at this moment how many users, it will depend on many factors. It could be from a few hundred to a few thousand to tens of thousands of connections per second. I really won't know until it's all done and live.

I did try to use mysql for htpasswd, which is why I have the table in mysql to begin with. However, I didn't have much luck getting it to work so I gave up on it. I could not find information which made much sense on the net in terms of setting up the .htaccess file and possibly the httpd.conf file.

Everywhere I looked, syntax was different and nothing I found worked.

Maybe I should post a new question asking for help on that :)
0
 

Author Comment

by:projects
ID: 40453958
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Utilizing an array to gracefully append to a list of EmailAddresses
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now