Apache MySQL authentication

Lets explain how to configure Apache MySQL authentication. We will suppose apache web server is already up and running on the server.
First we need to install auth_mysql module.

$ sudo aptitude search libapache2-mod-auth-mysql
p   libapache2-mod-auth-mysql       - Apache 2 module for MySQL authentication  
$ sudo aptitude install libapache2-mod-auth-mysql


Once installed we create the database where we will authenticate. Copy the following script and save it as auth.sql. Substitute <user>, <passwd> and <group> for the corresponding user, password and group to authenticate.

grant all on auth.* to auth_user@localhost identified by '<passwd>';
flush privileges;
create database auth;
use auth;
CREATE TABLE `clients` (
`username` varchar(25) NOT NULL default '',
`passwd` varchar(25) NOT NULL default '',
`groups` varchar(25) NOT NULL default '',
PRIMARY KEY (`username`),
KEY `groups` (`groups`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `clients` VALUES ('<user>', '<passwd>', '<group>');

Now execute the script.

$ mysql -u root -p < /tmp/auth.sql

Check if everything went fine.

mysql> show databases like 'auth';
+-----------------+
| Database (auth) |
+-----------------+
| auth            | 
+-----------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT * FROM auth.clients;
+----------+--------+--------+
| username | passwd | groups |
+----------+--------+--------+
| dave     | passwd   | group   | 
+----------+--------+--------+
1 row in set (0.00 sec)

mysql> 

Once this is done we need to modify apache2.conf or the virtual host config file. Add the following. Substitute <directory> and <passwd> with your credentials.

<Directory "<directory>">
AuthType Basic
AuthName "Please provide user and password."
AuthMySQL on
AuthBasicAuthoritative Off
Auth_MySQL_Authoritative on
Auth_MySQL_Host localhost
Auth_MySQL_User auth_user
Auth_MySQL_Password <passwd>
AuthMySQL_DB auth
AuthMySQL_Password_Table clients
AuthMySQL_Username_Field username
AuthMySQL_Password_Field passwd
AuthMySQL_Empty_Passwords off
AuthMySQL_Encryption_Types MySQL Plaintext Crypt_DES
require valid-user
Options +Indexes FollowSymLinks MultiViews
AllowOverride AuthConfig Options FileInfo Limit
Order allow,deny
Allow from all
</Directory>

Keep AuthBasicAuthoritative Off or else you will get errors in apache error log file like the following.

[Wed Sep 15 18:40:04 2010] [error] Internal error: pcfg_openfile() called with NULL filename
[Wed Sep 15 18:40:04 2010] [error] [client 12.14.182.34] (9)Bad file descriptor: Could not open password file: (null)
[Wed Sep 15 18:40:14 2010] [error] Internal error: pcfg_openfile() called with NULL filename
[Wed Sep 15 18:40:14 2010] [error] [client 12.14.182.34] (9)Bad file descriptor: Could not open password file: (null)

After this restart apache and you should be ready to go. You should see a login screen like the one below.

Suggestions are always welcome.

References:

  1. http://www.howtoforge.com/mod_auth_mysql_apache2_debian
  2. http://www.yolinux.com/TUTORIALS/LinuxTutorialApacheAddingLoginSiteProtection.html

Leave a Reply