dump two mysql fields to create .htpasswd file

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

Select allOpen in new window

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.

Have you considered creating an in-memory table to be used for this purpose?

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.

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.

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.

You can find more information on different options on the Apache htpasswd doc page.

The items you need to add are:

Just a couple other considerations for you:

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

Thanks however.

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.

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...

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 :)

The new question is posted;

https://www.experts-exchange.com/questions/28566034/mod-auth-mysql-syntax-and-config-help-needed.html